MySQL死锁检查处理的正常方法
正常情况下，死锁发生时，权重最小的连接将被kill并回滚。但是为了找出语句来优化，启用可启用死锁将死锁信息记录下来。
#step1：窗口一 mysql>starttransaction; mysql>updateaasetname='aaa'whereid=1; #step2：窗口二 mysql>starttransaction; mysql>updatebbsetname='bbb'whereid=1; #step3：窗口一 mysql>updatebbsetname='bbb';
#step4：窗口三 #是否自动提交 mysql>showvariableslike'autocommit'; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |autocommit|ON| +---------------+-------+ #查看当前连接 mysql>showprocesslist; mysql>showfullprocesslist; mysql>SELECT*FROMINFORMATION_SCHEMA.PROCESSLIST; +----+------+-----------+------+---------+------+-------+------------------+ |Id|User|Host|db|Command|Time|State|Info| +----+------+-----------+------+---------+------+-------+------------------+ |4|root|localhost|test|Sleep|244||NULL| |5|root|localhost|test|Sleep|111||NULL| |6|root|localhost|NULL|Query|0|init|showprocesslist| +----+------+-----------+------+---------+------+-------+------------------+ #查看当前正在被锁的事务（锁请求超时后则查不到） mysql>SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS; +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ |lock_id|lock_trx_id|lock_mode|lock_type|lock_table|lock_index|lock_space|lock_page|lock_rec|lock_data| +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ |130718495:65:3:4|130718495|X|RECORD|`test`.`bb`|GEN_CLUST_INDEX|65|3|4|0x000000000300| |130718496:65:3:4|130718496|X|RECORD|`test`.`bb`|GEN_CLUST_INDEX|65|3|4|0x000000000300| +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ #查看当前等待锁的事务（锁请求超时后则查不到） mysql>SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ |requesting_trx_id|requested_lock_id|blocking_trx_id|blocking_lock_id| +-------------------+-------------------+-----------------+------------------+ |130718499|130718499:65:3:4|130718500|130718500:65:3:4| +-------------------+-------------------+-----------------+------------------+ #查看当前未提交的事务（如果死锁等待超时,事务可能还没有关闭） mysql>SELECT*FROMINFORMATION_SCHEMA.INNODB_TRX; +--------------------------------------------------------------------------------------------------------+ |trx_id|trx_state|trx_started|trx_requested_lock_id|trx_wait_started|trx_weight| +-----------+-----------+---------------------+-----------------------+---------------------+------------+ |130718500|RUNNING|2018-03-1209:28:10|NULL|NULL|3| |130718499|LOCKWAIT|2018-03-1209:27:59|130718499:65:3:4|2018-03-1209:32:48|5| ========================================================================================================== |trx_mysql_thread_id|trx_query|trx_operation_state|trx_tables_in_use| +---------------------+---------------------------------------+---------------------+-------------------+ |4|NULL|NULL|0| |5|updatebbsetname='bbb'|startingindexread|1| ========================================================================================================= |trx_tables_locked|trx_lock_structs|trx_lock_memory_bytes|trx_rows_locked|trx_rows_modified| +-------------------+------------------+-----------------------+-----------------+-------------------+ |0|2|360|3|1| |1|4|1184|4|1| =========================================================================================================================== |trx_concurrency_tickets|trx_isolation_level|trx_unique_checks|trx_foreign_key_checks|trx_last_foreign_key_error| +-------------------------+---------------------+-------------------+------------------------+----------------------------+ |0|REPEATABLEREAD|1|1|NULL| |0|REPEATABLEREAD|1|1|NULL| =========================================================================================================================== |trx_adaptive_hash_latched|trx_adaptive_hash_timeout|trx_is_read_only|trx_autocommit_non_locking| +---------------------------+---------------------------+------------------+----------------------------+ |0|10000|0|0| |0|10000|0|0| +---------------------------+---------------------------+------------------+----------------------------+ #查看正在被访问的表 mysql>showOPENTABLESwhereIn_use>0; +----------+-------+--------+-------------+ |Database|Table|In_use|Name_locked| +----------+-------+--------+-------------+ |test|bb|1|0| +----------+-------+--------+-------------+
#step3：窗口一（若第三步中锁请求太久，则出现锁超时而终止执行） mysql>updatebbsetname='bbb'; ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction #"窗口一"锁请求超时前，执行第五步，使死锁产生，则该连接"窗口二"执行终止，"窗口一"顺利执行 #step5：窗口二 mysql>updateaasetname='aa'; ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
查看最近一个死锁情况
#查看最近一个死锁情况 mysql>SHOWENGINEINNODBSTATUS\G; ............... ------------------------ LATESTDETECTEDDEADLOCK ------------------------ 2018-03-1211:01:067ffb4993a700#发生时间 ***(1)TRANSACTION:#事务1 TRANSACTION130718515,ACTIVE19secstartingindexread mysqltablesinuse1,locked1#正被访问的表 LOCKWAIT4lockstruct(s),heapsize1184,4rowlock(s),undologentries1#影响行数 MySQLthreadid5,OSthreadhandle0x7ffb498f8700,queryid205localhostrootupdating#线程/连接host/用户 updatebbsetname='bb'#请求语句 ***(1)WAITINGFORTHISLOCKTOBEGRANTED:#等待以下资源（锁定位置及锁模式） RECORDLOCKSspaceid65pageno3nbits72index`GEN_CLUST_INDEX`oftable`test`.`bb`trxid130718515lock_modeXwaiting Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits0 0:len6;hex000000000300;asc;; 1:len6;hex000007ca9b34;asc4;; 2:len7;hex1f000002092075;ascu;; 3:len4;hex80000001;asc;; 4:len2;hex6262;ascbb;; ***(2)TRANSACTION:#事务2 TRANSACTION130718516,ACTIVE14secstartingindexread mysqltablesinuse1,locked1 4lockstruct(s),heapsize1184,4rowlock(s),undologentries1 MySQLthreadid4,OSthreadhandle0x7ffb4993a700,queryid206localhostrootupdating updateaasetname='aa'#请求语句 ***(2)HOLDSTHELOCK(S):#持有锁资源 RECORDLOCKSspaceid65pageno3nbits72index`GEN_CLUST_INDEX`oftable`test`.`bb`trxid130718516lock_modeX Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits0 0:len8;hex73757072656d756d;ascsupremum;; Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits0 0:len6;hex000000000301;asc;; 1:len6;hex000007ca9b17;asc;; 2:len7;hex9000000144011e;ascD;; 3:len4;hex80000002;asc;; 4:len2;hex6262;ascbb;; Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits0 0:len6;hex000000000300;asc;; 1:len6;hex000007ca9b34;asc4;; 2:len7;hex1f000002092075;ascu;; 3:len4;hex80000001;asc;; 4:len2;hex6262;ascbb;; ***(2)WAITINGFORTHISLOCKTOBEGRANTED: RECORDLOCKSspaceid64pageno3nbits80index`GEN_CLUST_INDEX`oftable`test`.`aa`trxid130718516lock_modeXwaiting Recordlock,heapno7PHYSICALRECORD:n_fields5;compactformat;infobits0 0:len6;hex000000000200;asc;; 1:len6;hex000007ca9b33;asc3;; 2:len7;hex1e000001d53057;asc0W;; 3:len4;hex80000001;asc;; 4:len2;hex6161;ascaa;; ***WEROLLBACKTRANSACTION(2) ...............
#死锁记录只记录最近一个死锁信息，若要将每个死锁信息都保存到错误日志，启用以下参数： mysql>showvariableslike'innodb_print_all_deadlocks'; +----------------------------+-------+ |Variable_name|Value| +----------------------------+-------+ |innodb_print_all_deadlocks|OFF| +----------------------------+-------+ #上面【step3：窗口一】若一直请求不到资源，默认50秒则出现锁等待超时。 mysql>showvariableslike'innodb_lock_wait_timeout'; +--------------------------+-------+ |Variable_name|Value| +--------------------------+-------+ |innodb_lock_wait_timeout|50| +--------------------------+-------+ ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction #设置全局变量锁等待超时为60秒（新的连接生效） #mysql>setsessioninnodb_lock_wait_timeout=50; mysql>setglobalinnodb_lock_wait_timeout=60; #上面测试中，当事务中的某个语句超时只回滚该语句，事务的完整性属于被破坏了。为了回滚这个事务，启用以下参数： mysql>showvariableslike'innodb_rollback_on_timeout'; +----------------------------+-------+ |Variable_name|Value| +----------------------------+-------+ |innodb_rollback_on_timeout|OFF| +----------------------------+-------+
最终参数设置如下：(重启服务重新连接测试)
[mysqld] log-error=/var/log/mysqld3306.log innodb_lock_wait_timeout=60#锁请求超时时间(秒) innodb_rollback_on_timeout=1#事务中某个语句锁请求超时将回滚真个事务 innodb_print_all_deadlocks=1#死锁都保存到错误日志
#若手动删除堵塞会话，删除Command='Sleep'、无State、无Info、trx_weight权重最小的。 showprocesslist; SELECTtrx_mysql_thread_id,trx_state,trx_started,trx_weightFROMINFORMATION_SCHEMA.INNODB_TRX;
总结
到此这篇关于MySQL死锁检查处理的文章就介绍到这了,更多相关MySQL死锁检查处理内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。