mysql> show processlist; +----+------+------+------+---------+------+-------------------+--------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+------+------+---------+------+-------------------+--------------------------------------------------+ | 8 | root | | test | Query | 0 | NULL | show processlist | | 51 | root | | test | Sleep | 8 | | NULL | | 52 | root | | test | Query | 113 | copy to tmp table | alter table t1 engine=innodb | | 53 | root | | test | Query | 113 | Locked | update t1 set b='technicals' where a='therapist' | +----+------+------+------+---------+------+-------------------+--------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select * from t1; +-------------------+------------+ | a | b | +-------------------+------------+ | objectionableness | opaqueness | | caper | denational | | Terrill's | distrainer | | jammed | conceits | | quester's | ravening | | ikebana | gelds | | Kendrick | unreasonin | | liquefaction | Gujarati | | plethora | ombudsmans | | Chengchow | mousetrap | +-------------------+------------+ 10 rows in set (0.00 sec) SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 160, signal count 158 Mutex spin waits 0, rounds 25920, OS waits 54 RW-shared spins 152, OS waits 67; RW-excl spins 86, OS waits 39 ------------ TRANSACTIONS ------------ Trx id counter 0 381462 Purge done for trx's n:o < 0 381459 undo n:o < 0 0 History list length 8 Total number of lock structs in row lock hash table 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 381460, not started, process no 1283, OS thread id 2962512784 MySQL thread id 8, query id 375655 192.168.250.3 root ---TRANSACTION 0 381459, ACTIVE 162 sec, process no 1283, OS thread id 3018664848 fetching rows mysql tables in use 2, locked 2 LOCK WAIT 4 lock struct(s), heap size 320, 10 row lock(s), undo log entries 9 MySQL thread id 52, query id 375636 192.168.250.3 root copy to tmp table alter table t1 engine=innodb ------- TRX HAS BEEN WAITING 162 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 1169 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 0 381459 lock_mode X waiting Record lock, heap no 12 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003beaa; asc ;; 1: len 6; hex 00000005d210; asc ;; 2: len 7; hex 000000048c1946; asc F;; 3: len 20; hex 4368656e6763686f772020202020202020202020; asc Chengchow ;; 4: len 10; hex 56697679616e6e652773; asc Vivyanne's;; ------------------ TABLE LOCK table `test`.`t1` trx id 0 381459 lock mode IX RECORD LOCKS space id 0 page no 1169 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 0 381459 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea1; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0110; asc - ;; 3: len 20; hex 6f626a656374696f6e61626c656e657373202020; asc objectionableness ;; 4: len 10; hex 6f70617175656e657373; asc opaqueness;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea2; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0120; asc - ;; 3: len 20; hex 6361706572202020202020202020202020202020; asc caper ;; 4: len 10; hex 64656e6174696f6e616c; asc denational;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea3; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0130; asc - 0;; 3: len 20; hex 54657272696c6c27732020202020202020202020; asc Terrill's ;; 4: len 10; hex 646973747261696e6572; asc distrainer;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea4; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0140; asc - @;; 3: len 20; hex 6a616d6d65642020202020202020202020202020; asc jammed ;; 4: len 8; hex 636f6e6365697473; asc conceits;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea5; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0150; asc - P;; 3: len 20; hex 7175657374657227732020202020202020202020; asc quester's ;; 4: len 8; hex 726176656e696e67; asc ravening;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea6; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0160; asc - `;; 3: len 20; hex 696b6562616e6120202020202020202020202020; asc ikebana ;; 4: len 5; hex 67656c6473; asc gelds;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea7; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0170; asc - p;; 3: len 20; hex 4b656e647269636b202020202020202020202020; asc Kendrick ;; 4: len 10; hex 756e726561736f6e696e; asc unreasonin;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea8; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0180; asc - ;; 3: len 20; hex 6c6971756566616374696f6e2020202020202020; asc liquefaction ;; 4: len 8; hex 47756a6172617469; asc Gujarati;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003bea9; asc ;; 1: len 6; hex 00000005d206; asc ;; 2: len 7; hex 800000002d0190; asc - ;; 3: len 20; hex 706c6574686f7261202020202020202020202020; asc plethora ;; 4: len 10; hex 6f6d627564736d616e73; asc ombudsmans;; TABLE LOCK table `test`.`#sql-503_34` trx id 0 381459 lock mode IX RECORD LOCKS space id 0 page no 1169 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 0 381459 lock_mode X waiting Record lock, heap no 12 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003beaa; asc ;; 1: len 6; hex 00000005d210; asc ;; 2: len 7; hex 000000048c1946; asc F;; 3: len 20; hex 4368656e6763686f772020202020202020202020; asc Chengchow ;; 4: len 10; hex 56697679616e6e652773; asc Vivyanne's;; ---TRANSACTION 0 381456, ACTIVE 162 sec, process no 1283, OS thread id 3018062736 mysql tables in use 1, locked 1 4 lock struct(s), heap size 320, 3 row lock(s), undo log entries 1 MySQL thread id 53, query id 375637 192.168.250.3 root Table lock update t1 set b='technicals' where a='therapist' TABLE LOCK table `test`.`t1` trx id 0 381456 lock mode IX RECORD LOCKS space id 0 page no 1170 n bits 80 index `a` of table `test`.`t1` trx id 0 381456 lock_mode X Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 4368656e6763686f772020202020202020202020; asc Chengchow ;; 1: len 6; hex 00000003beaa; asc ;; RECORD LOCKS space id 0 page no 1169 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 0 381456 lock_mode X locks rec but not gap Record lock, heap no 12 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 00000003beaa; asc ;; 1: len 6; hex 00000005d210; asc ;; 2: len 7; hex 000000048c1946; asc F;; 3: len 20; hex 4368656e6763686f772020202020202020202020; asc Chengchow ;; 4: len 10; hex 56697679616e6e652773; asc Vivyanne's;; RECORD LOCKS space id 0 page no 1170 n bits 80 index `a` of table `test`.`t1` trx id 0 381456 lock_mode X locks gap before rec Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 696b6562616e6120202020202020202020202020; asc ikebana ;; 1: len 6; hex 00000003bea6; asc ;; -------- 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: 0; buffer pool: 0 35 OS file reads, 80235 OS file writes, 79829 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.13 writes/s, 0.07 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 49, node heap has 1 buffer(s) 0.00 hash searches/s, 0.12 non-hash searches/s --- LOG --- Log sequence number 0 236516484 Log flushed up to 0 236516484 Last checkpoint at 0 236516484 0 pending log writes, 0 pending chkp writes 79636 log i/o's done, 0.03 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17462506; in additional pool allocated 893440 Dictionary memory allocated 31456 Buffer pool size 512 Free buffers 416 Database pages 95 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 29, created 66, written 2495 0.00 reads/s, 0.00 creates/s, 0.15 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. 1283, id 2982816656, state: waiting for server activity Number of rows inserted 196788, updated 5174, deleted 0, read 201944 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.17 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ the test is: create table with 10 records. then, 1 thread set autocommit=0 update t1 set b='?' where a='?' update t1 set b='?' where a='?' commit and another thread: alter table t1 engine=innodb 02 threads running, 0005939 successful queries. 0000000 failed queries (494.916667 QPS). 02 threads running, 0010799 successful queries. 0000000 failed queries (405.000000 QPS). 02 threads running, 0016195 successful queries. 0000000 failed queries (449.666667 QPS). 02 threads running, 0022103 successful queries. 0000000 failed queries (492.333333 QPS). 02 threads running, 0027402 successful queries. 0000000 failed queries (441.583333 QPS). 02 threads running, 0033312 successful queries. 0000000 failed queries (492.500000 QPS). 02 threads running, 0038678 successful queries. 0000000 failed queries (447.166667 QPS). 02 threads running, 0043793 successful queries. 0000000 failed queries (426.250000 QPS). 02 threads running, 0049316 successful queries. 0000000 failed queries (460.250000 QPS). 02 threads running, 0054560 successful queries. 0000000 failed queries (437.000000 QPS). 02 threads running, 0060246 successful queries. 0000000 failed queries (473.833333 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (200.416667 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS). 02 threads running, 0062651 successful queries. 0000000 failed queries (0.000000 QPS).