mysql死锁自动检测_MySQLInnoDB设置死锁检测的方法

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,可以认为如果一个资源被锁定&＃xff0c;它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时&＃xff0c;其中每个进程拥有的锁都是其他进程所需的&＃xff0c;由此造成每个进程都无法继续下去。

InnoDB的并发写操作会触发死锁&＃xff0c;InnoDB也提供了死锁检测机制&＃xff0c;可以通过设置innodb_deadlock_detect参数可以打开或关闭死锁检测:

innodb_deadlock_detect &＃61; on  打开死锁检测&＃xff0c;数据库发生死锁时自动回滚(默认选项)

innodb_deadlock_detect &＃61; off  关闭死锁检测&＃xff0c;发生死锁的时候&＃xff0c;用锁超时来处理&＃xff0c;通过设置锁超时参数innodb_lock_wait_timeout可以在超时发生时回滚被阻塞的事务

还可以通过设置InnDB Monitors来进一步观察锁冲突详细信息

设置InnoDB Monitors方法

建立test库

mysql>create database test;

Query OK, 1 row affected (0.20 sec)

mysql> use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table innodb_monitor(a INT) engine&＃61;innodb;

Query OK, 0 rows affected (1.04 sec)

mysql> create table innodb_tablespace_monitor(a INT) engine&＃61;innodb;

Query OK, 0 rows affected (0.70 sec)

mysql> create table innodb_lock_monitor(a INT) engine&＃61;innodb;

Query OK, 0 rows affected (0.36 sec)

mysql> create table innodb_table_monitor(a INT) engine&＃61;innodb;

Query OK, 0 rows affected (0.08 sec)

可以通过show engine innodb status命令查看死锁信息

mysql> show engine innodb status \G

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

Type: InnoDB

Name:

Status:

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

2018-05-10 09:17:10 0x7f1fbc21a700 INNODB MONITOR OUTPUT

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

Per second averages calculated from the last 46 seconds

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

BACKGROUND THREAD

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

srv_master_thread loops: 53 srv_active, 0 srv_shutdown, 240099 srv_idle

srv_master_thread log flush and writes: 0

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2007

OS WAIT ARRAY INFO: signal count 1987

RW-shared spins 3878, rounds 5594, OS waits 1735

RW-excl spins 3, rounds 91, OS waits 4

RW-sx spins 1, rounds 30, OS waits 1

Spin rounds per wait: 1.44 RW-shared, 30.33 RW-excl, 30.00 RW-sx

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

TRANSACTIONS

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

Trx id counter 78405

Purge done for trx&＃39;s n:o <78404 undo n:o <10 state: running but idle

History list length 21

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 421249967052640, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

--------

FILE I/O

--------

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

.............................................................................

.............................................................................

.............................................................................

来自 “ ITPUB博客 ” &＃xff0c;链接&＃xff1a;http://blog.itpub.net/15498/viewspace-2154256/&＃xff0c;如需转载&＃xff0c;请注明出处&＃xff0c;否则将追究法律责任。