mysql innodbrowlocktime_MySQL innodb行锁测试

session1:更新记录

mysql>setautocommit=off;QueryOK,0rowsaffected(0.01sec)mysql>updatet1setemail='test@test.com'whereid=0;QueryOK,4rowsaffected(0.00sec)Rowsmatched:4Changed:4Warnings:0

session2也更新相同的记录,出现等待

mysql>setautocommit=off;QueryOK,0rowsaffected(0.00sec)mysql>updatet1setemail='abc'whereid=0;session3:查看系统等待事件:mysql>showstatuslike'%lock%';

+-------------------------------+---------+

|Variable_name|Value|

+-------------------------------+---------+

|Com_lock_tables|0|

|Com_unlock_tables|0|

|Innodb_row_lock_current_waits|1|--这里|Innodb_row_lock_time|0|

|Innodb_row_lock_time_avg|0|

|Innodb_row_lock_time_max|0|

|Innodb_row_lock_waits|1|

|Key_blocks_not_flushed|0|

|Key_blocks_unused|14497|

|Key_blocks_used|0|

|Qcache_free_blocks|1|

|Qcache_total_blocks|1|

|Table_locks_immediate|2070991|

|Table_locks_waited|2|

+-------------------------------+---------+14rowsinset(0.01sec)session1:提交记录

mysql>commit;QueryOK,0rowsaffected(0.01sec)session2:update立刻完成

mysql>updatet1setemail='abc'whereid=0;QueryOK,4rowsaffected(2min43.44sec)Rowsmatched:4Changed:4Warnings:0

session3:再次查看系统等待事件

mysql>showstatuslike'%lock%';

+-------------------------------+---------+

|Variable_name|Value|

+-------------------------------+---------+

|Com_lock_tables|0|

|Com_unlock_tables|0|

|Innodb_row_lock_current_waits|0|--这里为0|Innodb_row_lock_time|163436|

|Innodb_row_lock_time_avg|163436|

|Innodb_row_lock_time_max|163436|

|Innodb_row_lock_waits|1|

|Key_blocks_not_flushed|0|

|Key_blocks_unused|14497|

|Key_blocks_used|0|

|Qcache_free_blocks|1|

|Qcache_total_blocks|1|

|Table_locks_immediate|2070991|

|Table_locks_waited|2|

+-------------------------------+---------+14rowsinset(0.01sec)查询会话session1,session2的连接ID

session1:mysql>status;

--------------mysqlVer12.22Distrib4.0.24,forpc-solaris2.10(i386)Connectionid:15

session2:mysql>status;

--------------mysqlVer12.22Distrib4.0.24,forpc-solaris2.10(i386)Connectionid:13

在上面的session1尚没有提交的时候,可以执行下列命令，查看一些事务阻塞信息

mysql>showinnodbstatusG;

------------TRANSACTIONS------------Trxidcounter03852351

Purgedonefortrx'sn<03852350undon<00

Historylistlength11

Totalnumberoflockstructsinrowlockhashtable7

LISTOFTRANSACTIONSFOREACHSESSION:

---TRANSACTION00,notstarted,OSthreadid15

MySQLthreadid18,queryid2071119localhostroot

showinnodbstatus

---TRANSACTION03852350,ACTIVE6sec,OSthreadid14startingindexread

mysqltablesinuse1,locked1

LOCKWAIT2lockstruct(s),heapsize320

MySQLthreadid13,queryid2071118localhosttestUpdating--这里可以看到等待者

updatet1setemail='abcwhereid=0--这里可以看到等待者正在执行的SQL-------TRXHASBEENWAITING6SECFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid0pageno32782nbits1056index`idx_t1_id`oftable`dc_test/t1`trxid03852350lock_modeXwaiting

Recordlock,heapno2PHYSICALRECORD:n_fields2;compactformat;infobits0

0:len4;hex80000000;asc;;1:len6;hex00000196fe5d;asc];;

------------------

---TRANSACTION03852348,ACTIVE391sec,OSthreadid12

7lockstruct(s),heapsize1024,undologentries4

MySQLthreadid15,queryid2071117loc