死锁的产生原因
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源而导致的恶性循环，因争夺资源而造成一种相互等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。锁的行为和顺序与存储引擎相关，以同样顺序执行语句有些存储引擎会产生死锁有些不会，因为死锁有双重原因：真正的数据冲突和存储引擎的实现方式。
死锁产生的关键在于两个或多个Session会话加锁的顺序不一致，解决死锁的关键在于让不同的Session枷锁有次序。
如何监测死锁
数据系统是实现了各种死锁监测和死锁超时机制，InnoDB存储引擎能监测到地锁的循环依赖并立即返回一个错误。
默认数据库是不会打印任何死锁的日志，那如何排查应用的死锁问题呢？
mysql> SHOW VARIABLES LIKE "innodb_print_all_deadlocks";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
1 row in set
$ SET GLOBAL innodb_print_all_deadlocks=on;
查询上一次死锁日志
$ SHOW ENGINE INNODB STATUS;
查看LATEST DETECTED DEADLOCK死锁日志
如何恢复死锁
死锁发生以后只有部分或完全回滚其中一个事务才能打破死锁，InnoDB目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。
查询是否锁表
SHOW OPEN TABLES WHERE in_use > 0;
查看当前的事务
SELECT * FROM information_schema.innodb_trx;
查看发生死锁的事务
SELECT * FROM information_schema.innodb_locks;
查看当前等锁的事务
SELECT * FROM information_schema.innodb_lock_waits;
MySQL存储引擎InnoDB会主动探知到死锁，并回滚某个等待的事务，InnoDB是怎么探知死锁呢？当两个事务相互等待时，一个等待时间超过设置的某一阈值时，对其中一个事务进行回滚，另一个事务就能继续执行，这种检测死锁太过被动。
MySQL有两种死锁处理方式
- 等待直到超时
在InnoDB中参数innodb_lock_wait_timeout用来设置超时时间
mysql> SHOW VARIABLES LIKE "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set
- 发起死锁监测主动回滚一条事务，让其它事务继续执行。
mysql> SHOW VARIABLES LIKE "innodb_deadlock_detect
";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
1 row in set
锁表的原因
当多个数据库连接同时对一个表的数据进行更新时，速度将会越来越慢，持续一段时间后将出现数据表被锁的现象，从而影响到其它查询以及更新。
MySQL中InnoDB存储引擎支持行级锁，InnoDB行锁通过给索引项添加锁实现的，这就意味着只有通过索引条件检索数据时，InnoDB才使用行锁否则使用表锁。
锁表会发生在INSERT、UPDATE、DELETE中，锁表的原理是因为数据库使用的是独占式封锁机制，当执行INSERT/UPDATE/DELETE语句时会对表进行锁住，直到发生commit提交或rollback回滚或退出数据库用户。
例如：锁表的原因
程序A执行了对表A的insert插入操作，还未commit提交。此时程序B也对表A进行insert插入，则此时会发生资源正忙的异常，这就是锁表。
锁表常发生于并发而非并行，并行时一个线程操作数据库时，另一个线程是不能操作数据库的，这是根据CPU和I/O分配策略锁决定的。
查询数据库阻塞的进程
SELECT * FROM information_schema.innodb_trx
innodb_trx表包括每个非只读事务在当前InnoDB中执行的信息，具体包括
- TRX_ID
唯一的事务编号，针对InnoDB内部。 - TRX_WEIGHT
事务的权重，反映影响的行数，即被事务锁定的行的数量。为解决死锁，InnoDB会选择较小权重的事务来回滚。 - TRX_STATE
事务执行状态，允许值分别为RUNNING、LOCK WAIT、POLLING BACK、COMMIT。 - TRX_STARTED
事务开始时间
MySQL异常：锁等待超时，尝试重启事务。
Lock wait timeout exceeded; try restarting transaction
MySQL锁等待超时相关的information_schema的三个表
| 表名 | 描述 |
|---|---|
| innodb_trx | 当前运行的所有事务 |
| innodb_locks | 当前出现的锁 |
| innodb_lock_waits | 锁等待的对应关系 |
innodb_trx表保存当前运行的所有事务
| 字段 | 类型 | 非空 | 默认值 | 描述 |
|---|---|---|---|---|
| trx_id | varchar(18) | N | - | 事务编号 |
| trx_state | varchar(13) | N | - | 事务状态 |
| trx_started | datetime | N | 0000-00-00 00:00:00 | 事务开始时间 |
| trx_requested_lock_id | varchar(81) | Y | NULL | innodb_locks.lock_id |
| trx_wait_started | datetime | Y | NULL | 事务开始等待时间 |
| trx_weight | bigint(21) unsigned | N | 0 | 事务权重 |
| trx_mysql_thread_id | bigint(21) unsigned | N | 0 | 事务线程编号 |
| trx_query | varchar(1024) | Y | NULL | SQL语句 |
| trx_operation_state | varchar(64) | N | 0 | 事务当前操作状态 |
| trx_tables_in_use | bigint(21) unsigned | N | 0 | 事务中有多少个表被使用 |
| trx_tables_locked | bigint(21) unsigned | N | 0 | 事务拥有多少个锁 |
| trx_lock_structs | bigint(21) unsigned | N | 0 | - |
| trx_lock_memory_bytes | bigint(21) unsigned | N | 0 | 事务锁住的内存大小单位字节 |
| trx_rows_locked | bigint(21) unsigned | N | 0 | 事务锁住的行数 |
| trx_rows_modified | bigint(21) unsnigned | N | 0 | 事务更改的行数 |
| trx_concurrency_tickets | bigint(21) unsigned | N | 0 | 事务并发票数 |
| trx_isolation_level | varchar(16) | N | - | 事务隔离级别 |
| trx_unique_checks | int(1) | N | 0 | 是否唯一性检查 |
| trx_foreign_key_checks | int(1) | N | 0 | 是否外键检查 |
| trx_last_foreign_key_error | varchar(256) | Y | NULL | 最后的外键错误 |
innodb_locks当前出现的锁
| 字段 | 类型 | 可空 | 描述 |
|---|---|---|---|
| lock_id | varchar(81) | N | 锁ID |
| lock_trx_id | varchar(18) | N | 拥有锁的事务ID |
| lock_mode | varchar(32) | N | 锁模式 |
| lock_type | varchar(32) | N | 锁类型 |
| lock_table | varchar(1024) | N | 被锁的表 |
| lock_index | varchar(1024) | N | 被锁的索引 |
| lock_space | bigint(21) unsigned | Y | 被锁的表空间号 |
| lock_rec | bigint(21) unsigned | Y | 被锁的记录号 |
| lock_data | varchar(8192) | Y | 被锁的数据 |
innodb_lock_waits 锁等待的对应关系
| 字段 | 类型 | 可空 | 描述 |
|---|---|---|---|
| requesting_trx_id | varchar(18) | N | 请求锁的事务ID |
| requested_lock_id | varchar(81) | N | 请求锁的锁ID |
| blocking_trx_id | varchar(18) | N | 当前拥有所的事务ID |
| blocking_lock_id | varchar(81) | N | 当前拥有锁的锁ID |
网友评论