MySQL innodb行锁测试

MySQL innodb行锁测试

MySQL innodb存储引擎使用与oracle相同的行锁机制，对如何查看系统中存在的行锁情况在下面的实验中，将可以看到。下面是测试过程:

<smallfont>PHP code:</smallfont>

session1:更新记录



mysql>setautocommit=off;

QueryOK,0rowsaffected(0.01sec)



mysql>updatet1setemail='[email protected]'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<imgsrc="images/smilies/33.gif"smilieid="204"border="0"alt=""/><03852350undon<imgsrc="images/smilies/33.gif"smilieid="204"border="0"alt=""/><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