mysql innodb 死锁_mysql innodb 死锁分析

mysqlVer14.14Distrib5.7.16,forlinux-glibc2.5(x86_64)usingEditLinewrapper#mysql版本5.7.16

Connectionid:10042

Currentdatabase:china9129

Currentuser:root@localhost

SSL:Notinuse

Currentpager:stdout

Usingoutfile:''

Usingdelimiter:;

Serverversion:5.7.16-logMySQLCommunityServer(GPL)

Protocolversion:10

Connection:LocalhostviaUNIXsocket

Servercharacterset:utf8

Dbcharacterset:utf8

Clientcharacterset:utf8

Conn.characterset:utf8

UNIXsocket:/data/mysql/mysql9129/sock/mysql9129.sock

Uptime:4days6hours2min31sec

Threads:4Questions:31133Slowqueries:0Opens:254Flushtables:3Opentables:58Queriespersecondavg:0.084

SESSION1:

"root@localhost:mysql9129.sock[(none)]>setglobalTRANSACTIONISOLATIONLEVELREPEATABLEREAD;

"root@localhost:mysql9129.sock[(none)]>select@@tx_isolation;

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

|@@tx_isolation|

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

|REPEATABLE-READ|

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

1rowinset(0.00sec)

"root@localhost:mysql9129.sock[china9129]>showcreatetablet100;

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

|Table|CreateTable|

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

|t100|CREATETABLE`t100`(

`id`int(11)NOTNULL,

PRIMARYKEY(`id`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8|

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

1rowinset(0.00sec)

"root@localhost:mysql9129.sock[china9129]>select*fromt100;

+-----+

|id|

+-----+

|1|

|2|

|3|

|4|

|5|

|111|

+-----+

"root@localhost:mysql9129.sock[china9129]>begin;

QueryOK,0rowsaffected(0.00sec)

"root@localhost:mysql9129.sock[china9129]>select*fromt100whereid=5forupdate;

+----+

|id|

+----+

|5|

+----+

1rowinset(0.00sec)

"root@localhost:mysql9129.sock[china9129]>deletefromt100whereid=3;

QueryOK,1rowaffected(3.62sec)

==========================================================================================================

SESSION2:

"root@localhost:mysql9129.sock[(none)]>begin;

QueryOK,0rowsaffected(0.00sec)

"root@localhost:mysql9129.sock[(none)]>usechina9129;

Databasechanged

"root@localhost:mysql9129.sock[china9129]>select*fromt100whereid=3forupdate;

+----+

|id|

+----+

|3|

+----+

1rowinset(0.00sec)

"root@localhost:mysql9129.sock[china9129]>deletefromt100whereid=5;

ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

#查看死锁信息

"root@localhost:mysql9129.sock[china9129]>showengineinnodbstatus\G

***************************1.row***************************

Type:InnoDB

Name:

Status:

=====================================

-12-:57:450x7fa0bffff700INNODBMONITOROUTPUT

=====================================

Persecondaveragescalculatedfromthelast24seconds

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

BACKGROUNDTHREAD

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

srv_master_threadloops:1133srv_active,0srv_shutdown,362529srv_idle

srv_master_threadlogflushandwrites:363662

----------

SEMAPHORES

----------

OSWAITARRAYINFO:reservationcount4540

OSWAITARRAYINFO:signalcount5766

RW-sharedspins0,rounds4683,OSwaits474

RW-exclspins0,rounds134731,OSwaits1327

RW-sxspins6332,rounds138603,OSwaits2382

Spinroundsperwait:4683.00RW-shared,134731.00RW-excl,21.89RW-sx

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

LATESTDETECTEDDEADLOCK#监测出最近的死锁信息

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

-12-:56:290x7fa0bffff700

***(1)TRANSACTION:#第一个事物

TRANSACTION13899,ACTIVE51secstartingindexread#事物id13899，活跃了51秒

mysqltablesinuse1,locked1#mysql有一个表在使用，一个表被锁住

LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s)#有3个锁链表，内存中堆的大小1136,两行记录被锁定

MySQLthreadid10041,OSthreadhandle140330009478912,queryid31128localhostrootupdating

#mysql线程id10041,queryid31128,localhostroot用户执行update操作

deletefromt100whereid=3#执行了这个SQL语句的时候，发生了锁等待

***(1)WAITINGFORTHISLOCKTOBEGRANTED:#等待这个锁被释放

RECORDLOCKSspaceid74pageno3nbits80indexPRIMARYoftable`china9129`.`t100`trxid13899lock_modeXlocksrecbutnotgapwaiting

#类型：行锁，等待在t100的主键上pagenum3，加一个X锁(notgapwaiting)，锁住80bits。

Recordlock,heapno5PHYSICALRECORD:n_fields3;compactformat;infobits0

0:len4;hex80000003;asc;;

1:len6;hex00000000363b;asc6;;;

2:len7;hexc7000001660110;ascf;;

***(2)TRANSACTION:#第二个事物

TRANSACTION13900,ACTIVE31secstartingindexread#事物id13900，活跃了31秒

mysqltablesinuse1,locked1#mysql有一个表在使用，一个表被锁住

3lockstruct(s),heapsize1136,2rowlock(s)#有3个锁链表，内存中堆的大小1136,两行记录被锁定

MySQLthreadid10042,OSthreadhandle140328392718080,queryid31129localhostrootupdating

#mysql线程id10042,queryid31129,localhostroot用户执行update操作

deletefromt100whereid=5#执行了这个SQL语句的时候，发生了锁等待

***(2)HOLDSTHELOCK(S):#事物13900持有的锁

RECORDLOCKSspaceid74pageno3nbits80indexPRIMARYoftable`china9129`.`t100`trxid13900lock_modeXlocksrecbutnotgap

#类型：行锁，事物id13900，在t100的主键上pagenum3，加一个X锁(notgap没有间隙锁)，锁住80bits

Recordlock,heapno5PHYSICALRECORD:n_fields3;compactformat;infobits0

0:len4;hex80000003;asc;;

1:len6;hex00000000363b;asc6;;;

2:len7;hexc7000001660110;ascf;;

***(2)WAITINGFORTHISLOCKTOBEGRANTED:#当事物2在执行deletefromt100whereid=5的时候，发生锁等待

RECORDLOCKSspaceid74pageno3nbits80indexPRIMARYoftable`china9129`.`t100`trxid13900lock_modeXlocksrecbutnotgapwaiting

#类型：行锁，等待在t100的主键上pagenum3，加一个X锁(notgapwaiting没有间隙锁)，锁住80bits。

Recordlock,heapno7PHYSICALRECORD:n_fields3;compactformat;infobits0

0:len4;hex80000005;asc;;

1:len6;hex00000000363d;asc6=;;

2:len7;hexc90000014c0110;ascL;;

***WEROLLBACKTRANSACTION(2)

#事物2，TRANSACTION13900被回滚了。报错信息：ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

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

TRANSACTIONS

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

Trxidcounter13905

Purgedonefortrx'sn:o

Historylistlength568

LISTOFTRANSACTIONSFOREACHSESSION:

---TRANSACTION421807334705888,notstarted

0lockstruct(s),heapsize1136,0rowlock(s)

---TRANSACTION13899,ACTIVE127sec

3lockstruct(s),heapsize1136,2rowlock(s),undologentries1

MySQLthreadid10041,OSthreadhandle140330009478912,queryid31128localhostrootcleaningup

-------

备注:

(1)在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了

主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-keylocking

(2)把死锁信息写入到error.log。

setglobalinnodb_print_all_deadlocks=1;