Announcement

Announcement Module
Collapse
No announcement yet.

A table with a negative size in innoDb and concurrency troubles! Are my data safe?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • A table with a negative size in innoDb and concurrency troubles! Are my data safe?

    I am using innodb in a 4Gb innoDb database.

    I saw that my inserts and selects are slower and slower after a while. 6 hours later the things are running slow, but I get one table with a negative size!

    Table Xxx
    Type InnoDb
    Records 7028572
    Table Size -943296 KB
    Index Size 240000 KB
    InnoDB free: 539648 kB

    Who can I fix this? I assume I have a problem with the metadata.

    The table get its Primary Key (a long) using an auxiliary table 'ids':

    CREATE TABLE `ids` (
    `nombre` varchar(20) NOT NULL default '',
    `valor` bigint(20) NOT NULL,
    PRIMARY KEY (`nombre`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    I think I have a concurrency problem here. But if I use AUTO_INCREMENT the innoDb engine maps to a 'Select Max()'. which is more perfomance? Can I do something to avoid that lock using parameters configuration?

    I send my configuration and a show innodb status.

    My system is a Athlon 64X2 6000+, 2GB RAM, the database is inside a Mandriva Linux 2007.1 Spring + LVM + Reiserfs volume:

    The database are accesed concurrently from 90+ clients.




    This is my configuration:

    # Example MySQL config file for large systems.
    #
    # This is for a large system with memory = 512M where the system runs mainly
    # MySQL.
    #
    # You can copy this file to
    # /etc/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options (in this
    # installation this directory is /var/lib/mysql) or
    # ~/.my.cnf to set user-specific options.
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.

    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    port = 3306
    socket = /var/lib/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer = 256M
    max_allowed_packet = 1M
    table_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8

    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking

    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin

    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 1

    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    # the syntax is:
    #
    # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
    # MASTER_USER=, MASTER_PASSWORD= ;
    #
    # where you replace , , by quoted strings and
    # by the master's port number (3306 by default).
    #
    # Example:
    #
    # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    # MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    # start replication for the first time (even unsuccessfully, for example
    # if you mistyped the password in master-password and the slave fails to
    # connect), the slave will create a master.info file, and any later
    # change in this file to the variables' values below will be ignored and
    # overridden by the content of the master.info file, unless you shutdown
    # the slave server, delete master.info and restart the slaver server.
    # For that reason, you may want to leave the lines below untouched
    # (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id = 2
    #
    # The replication master for this slave - required
    #master-host =
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user =
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port =
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin

    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/
    #log-update = /path-to-dedicated-directory/hostname

    # Uncomment the following if you are using BDB tables
    #bdb_cache_size = 64M
    #bdb_max_lock = 100000

    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql/
    innodb_data_home_dir =
    #innodb_data_file_path = ibdata1:10M:autoextend
    innodb_data_file_path = /opt/sales2/mysql-data/set01/ibdata01:2000M;/opt/sales2/mysq l-data/set01/ibdata02:2000M;
    innodb_log_group_home_dir = /var/lib/mysql/
    innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 256M
    innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    # concurrencia infinita
    innodb_thread_concurrency = 0
    innodb_commit_concurrency = 0
    # seteo de logs varios
    long_query_time = 2

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout



    This is my show innodb status:

    '
    =====================================
    070920 11:42:33 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 4 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 63169, signal count 61514
    Mutex spin waits 0, rounds 1263907, OS waits 22402
    RW-shared spins 74863, OS waits 35474; RW-excl spins 5638, OS waits 1845
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 3478274
    Purge done for trx's n < 0 3478192 undo n < 0 0
    History list length 39
    Total number of lock structs in row lock hash table 44
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 19973, OS thread id 2379647904
    MySQL thread id 1717, query id 18305373 localhost 127.0.0.1 root
    SHOW INNODB STATUS
    ---TRANSACTION 0 0, not started, process no 19973, OS thread id 2396781472
    MySQL thread id 1713, query id 18304883 localhost 127.0.0.1 root
    ---TRANSACTION 0 0, not started, process no 19973, OS thread id 2379447200
    MySQL thread id 1600, query id 17609406 localhost 127.0.0.1 root
    ---TRANSACTION 0 74464, not started, process no 19973, OS thread id 2379848608
    MySQL thread id 129, query id 18272097 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477493, not started, process no 19973, OS thread id 2380049312
    MySQL thread id 125, query id 18305176 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477468, not started, process no 19973, OS thread id 2380450720
    MySQL thread id 123, query id 18304883 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477957, not started, process no 19973, OS thread id 2381253536
    MySQL thread id 119, query id 18303577 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477568, not started, process no 19973, OS thread id 2381654944
    MySQL thread id 117, query id 18301187 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477744, not started, process no 19973, OS thread id 2382257056
    MySQL thread id 114, query id 18302322 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477544, not started, process no 19973, OS thread id 2382056352
    MySQL thread id 115, query id 18305297 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477763, not started, process no 19973, OS thread id 2382859168
    MySQL thread id 111, query id 18302436 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477827, not started, process no 19973, OS thread id 2383260576
    MySQL thread id 109, query id 18302873 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478092, not started, process no 19973, OS thread id 2383661984
    MySQL thread id 107, query id 18304379 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477915, not started, process no 19973, OS thread id 2384063392
    MySQL thread id 105, query id 18303213 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478209, not started, process no 19973, OS thread id 2384264096
    MySQL thread id 104, query id 18305133 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477716, not started, process no 19973, OS thread id 2384866208
    MySQL thread id 101, query id 18302136 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477869, not started, process no 19973, OS thread id 2385267616
    MySQL thread id 99, query id 18303099 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477466, not started, process no 19973, OS thread id 2385066912
    MySQL thread id 100, query id 18304752 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477995, not started, process no 19973, OS thread id 2386070432
    MySQL thread id 95, query id 18303755 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478218, not started, process no 19973, OS thread id 2386271136
    MySQL thread id 94, query id 18305171 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477615, not started, process no 19973, OS thread id 2386672544
    MySQL thread id 92, query id 18301551 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478134, not started, process no 19973, OS thread id 2387073952
    MySQL thread id 90, query id 18304613 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477451, not started, process no 19973, OS thread id 2387475360
    MySQL thread id 88, query id 18304638 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477542, not started, process no 19973, OS thread id 2387676064
    MySQL thread id 87, query id 18301172 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477818, not started, process no 19973, OS thread id 2388679584
    MySQL thread id 82, query id 18302843 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478028, not started, process no 19973, OS thread id 2388478880
    MySQL thread id 83, query id 18303977 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477680, not started, process no 19973, OS thread id 2389080992
    MySQL thread id 80, query id 18301888 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478232, not started, process no 19973, OS thread id 2389683104
    MySQL thread id 77, query id 18305259 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477659, not started, process no 19973, OS thread id 2389883808
    MySQL thread id 76, query id 18301665 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478116, not started, process no 19973, OS thread id 2390285216
    MySQL thread id 74, query id 18304515 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477743, not started, process no 19973, OS thread id 2390686624
    MySQL thread id 72, query id 18302315 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477954, not started, process no 19973, OS thread id 2391088032
    MySQL thread id 70, query id 18303570 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477937, not started, process no 19973, OS thread id 2391690144
    MySQL thread id 67, query id 18303471 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478108, not started, process no 19973, OS thread id 2391890848
    MySQL thread id 66, query id 18304486 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477633, not started, process no 19973, OS thread id 2392292256
    MySQL thread id 64, query id 18301650 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477950, not started, process no 19973, OS thread id 2392894368
    MySQL thread id 61, query id 18303547 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478215, not started, process no 19973, OS thread id 2393095072
    MySQL thread id 60, query id 18305164 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477342, not started, process no 19973, OS thread id 2393496480
    MySQL thread id 58, query id 18304135 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477908, not started, process no 19973, OS thread id 2393697184
    MySQL thread id 57, query id 18303321 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478197, not started, process no 19973, OS thread id 2395376544
    MySQL thread id 54, query id 18305034 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477326, not started, process no 19973, OS thread id 2702482336
    MySQL thread id 53, query id 18304015 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477871, not started, process no 19973, OS thread id 2703084448
    MySQL thread id 50, query id 18302996 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478230, not started, process no 19973, OS thread id 2703485856
    MySQL thread id 48, query id 18305252 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478098, not started, process no 19973, OS thread id 2704087968
    MySQL thread id 45, query id 18304410 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477546, not started, process no 19973, OS thread id 2704489376
    MySQL thread id 43, query id 18301179 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477759, not started, process no 19973, OS thread id 2704690080
    MySQL thread id 42, query id 18302414 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477491, not started, process no 19973, OS thread id 2704890784
    MySQL thread id 41, query id 18305039 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478055, not started, process no 19973, OS thread id 2396380064
    MySQL thread id 38, query id 18304130 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477323, not started, process no 19973, OS thread id 2395777952
    MySQL thread id 37, query id 18302761 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3477540, not started, process no 19973, OS thread id 2396179360
    MySQL thread id 34, query id 18301165 localhost 127.0.0.1 sales2
    ---TRANSACTION 0 3478272, ACTIVE 0 sec, process no 19973, OS thread id 2385669024
    2 lock struct(s), heap size 320, undo log entries 9
    MySQL thread id 97, query id 18305372 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478270, ACTIVE 0 sec, process no 19973, OS thread id 2380852128
    2 lock struct(s), heap size 320, undo log entries 9
    MySQL thread id 121, query id 18305356 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478268, ACTIVE 0 sec, process no 19973, OS thread id 2705091488
    2 lock struct(s), heap size 320, undo log entries 11
    MySQL thread id 40, query id 18305341 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478266, ACTIVE 0 sec, process no 19973, OS thread id 2380250016
    2 lock struct(s), heap size 320, undo log entries 9
    MySQL thread id 124, query id 18305324 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478265, ACTIVE 0 sec, process no 19973, OS thread id 2382457760
    2 lock struct(s), heap size 320, undo log entries 11
    MySQL thread id 113, query id 18305310 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478262, ACTIVE 0 sec, process no 19973, OS thread id 2702683040
    2 lock struct(s), heap size 320, undo log entries 9
    MySQL thread id 52, query id 18305294 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478260, ACTIVE 0 sec, process no 19973, OS thread id 2393897888
    2 lock struct(s), heap size 320, undo log entries 11
    MySQL thread id 56, query id 18305279 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478257, ACTIVE 0 sec, process no 19973, OS thread id 2389281696 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 79, query id 18305261 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478257 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478255, ACTIVE 0 sec, process no 19973, OS thread id 2703285152 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 49, query id 18305254 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478255 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478253, COMMITTED IN MEMORY, process no 19973, OS thread id 2387876768 committing
    , undo log entries 9
    MySQL thread id 86, query id 18305247 localhost 127.0.0.1 sales2
    commit
    ---TRANSACTION 0 3478243, ACTIVE 0 sec, process no 19973, OS thread id 2385869728 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 96, query id 18305173 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478243 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478241, ACTIVE 0 sec, process no 19973, OS thread id 2392693664 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 62, query id 18305166 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478241 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478234, ACTIVE 0 sec, process no 19973, OS thread id 2383862688 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 106, query id 18305135 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478234 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478220, ACTIVE 0 sec, process no 19973, OS thread id 2395175840 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 55, query id 18305036 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478220 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478154, ACTIVE 1 sec, process no 19973, OS thread id 2386873248 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 91, query id 18304615 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478154 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478138, ACTIVE 1 sec, process no 19973, OS thread id 2390084512 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 75, query id 18304517 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478138 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478132, ACTIVE 1 sec, process no 19973, OS thread id 2391489440 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 68, query id 18304488 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478132 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478120, ACTIVE 1 sec, process no 19973, OS thread id 2703686560 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 47, query id 18304412 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478120 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478114, ACTIVE 1 sec, process no 19973, OS thread id 2383059872 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 110, query id 18304381 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478114 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478076, ACTIVE 1 sec, process no 19973, OS thread id 2395577248 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 39, query id 18304132 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478076 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478051, ACTIVE 1 sec, process no 19973, OS thread id 2388278176 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 84, query id 18303979 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478051 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3478014, ACTIVE 2 sec, process no 19973, OS thread id 2385468320 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 98, query id 18303757 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3478014 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477979, ACTIVE 2 sec, process no 19973, OS thread id 2380651424 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 122, query id 18303579 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477979 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477977, ACTIVE 2 sec, process no 19973, OS thread id 2390887328 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 71, query id 18303572 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477977 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477973, ACTIVE 2 sec, process no 19973, OS thread id 2392492960 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 63, query id 18303549 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477973 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477959, ACTIVE 2 sec, process no 19973, OS thread id 2391288736 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 69, query id 18303473 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477959 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477933, ACTIVE 3 sec, process no 19973, OS thread id 2393295776 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 59, query id 18303323 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477933 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477916, ACTIVE 3 sec, process no 19973, OS thread id 2383461280 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 108, query id 18303215 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477916 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477892, ACTIVE 3 sec, process no 19973, OS thread id 2384665504 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 102, query id 18303101 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477892 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477872, ACTIVE 3 sec, process no 19973, OS thread id 2702883744 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 51, query id 18302998 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477872 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477849, ACTIVE 3 sec, process no 19973, OS thread id 2382658464 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 112, query id 18302875 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477849 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477841, ACTIVE 3 sec, process no 19973, OS thread id 2388077472 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 85, query id 18302845 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477841 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477788, ACTIVE 4 sec, process no 19973, OS thread id 2381855648 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 116, query id 18302438 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477788 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477783, ACTIVE 4 sec, process no 19973, OS thread id 2704288672 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 44, query id 18302416 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477783 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477767, ACTIVE 4 sec, process no 19973, OS thread id 2381454240 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 118, query id 18302324 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477767 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477765, ACTIVE 4 sec, process no 19973, OS thread id 2390485920 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 73, query id 18302317 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477765 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477738, ACTIVE 4 sec, process no 19973, OS thread id 2384464800 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 103, query id 18302138 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477738 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477702, ACTIVE 5 sec, process no 19973, OS thread id 2388880288 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 81, query id 18301890 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477702 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477660, ACTIVE 5 sec, process no 19973, OS thread id 2389482400 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 78, query id 18301667 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477660 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477655, ACTIVE 5 sec, process no 19973, OS thread id 2392091552 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 65, query id 18301652 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477655 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477635, ACTIVE 5 sec, process no 19973, OS thread id 2386471840 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 93, query id 18301553 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477635 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477569, ACTIVE 6 sec, process no 19973, OS thread id 2381052832 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 120, query id 18301189 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477569 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477566, ACTIVE 6 sec, process no 19973, OS thread id 2703887264 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 46, query id 18301181 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477566 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477564, ACTIVE 6 sec, process no 19973, OS thread id 2387274656 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 89, query id 18301174 localhost 127.0.0.1 sales2 statistics
    select valor from ids where nombre = 'idSale' for update
    ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 62 n bits 72 index `PRIMARY` of table `sales2/ids` trx id 0 3477564 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 9; hex 69645461626c65726f; asc idSale;; 1: len 6; hex 00000035103a; asc 5 :;; 2: len 7; hex 00000180091b6e; asc n;; 3: len 8; hex 8000000000021df0; asc ;;

    ------------------
    ---TRANSACTION 0 3477562, ACTIVE 6 sec, process no 19973, OS thread id 2395978656
    2 lock struct(s), heap size 320, undo log entries 1
    MySQL thread id 35, query id 18305296 localhost 127.0.0.1 sales2
    commit
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0, aio writes: 0,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 1; buffer pool: 0
    290220 OS file reads, 3235641 OS file writes, 3225234 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 114.72 writes/s, 113.72 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2,
    103 inserts, 103 merged recs, 12 merges
    Hash table size 1106407, used cells 284145, node heap has 323 buffer(s)
    30.99 hash searches/s, 873.03 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 0 4205294761
    Log flushed up to 0 4205292069
    Last checkpoint at 0 4140907375
    1 pending log writes, 0 pending chkp writes
    3219410 log i/o's done, 114.22 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 323468180; in additional pool allocated 2274944
    Buffer pool size 16384
    Free buffers 1
    Database pages 16060
    Modified db pages 3317
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 985323, created 188632, written 201378
    0.00 reads/s, 8.50 creates/s, 14.00 writes/s
    Buffer pool hit rate 1000 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    1 read views open inside InnoDB
    Main thread process no. 19973, id 2688301984, state: sleeping
    Number of rows inserted 6937008, updated 1801798, deleted 0, read 804277106
    296.68 inserts/s, 80.98 updates/s, 0.00 deletes/s, 91.98 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    '

  • #2
    pabloa wrote on Thu, 20 September 2007 18:28

    I think I have a concurrency problem here. But if I use AUTO_INCREMENT the innoDb engine maps to a 'Select Max()'.


    You have a small miss understanding there.
    AUTO_INCREMENT does not _map_ to a SELECT MAX().

    It only _uses_ a SELECT MAX() the _first_ time you are using auto_increment on that table to initialize the auto_increment value.
    After that it stores the last increment value in RAM together with the handle for the table to be able to retrieve it really fast.
    So I suggest that you use the auto_increment feature instead of trying to invent one of your own because it has a lot of optimizations built into it.



    Second tip is that if your application has a lot of insert/updates and depending on your OS/hardware combination then this setting:

    innodb_flush_log_at_trx_commit = 0

    can speed things up a lot if you are willing to sacrifice a bit of robustness.


    As for your negative size on table I don't really have an answer unfortunately.

    Comment

    Working...
    X