mysql死锁的产生及解决

summary_2018/09 mysql

1、日常

1.1、mysql死锁的产生及解决

2、技术

2.1、mysql死锁的产生及解决

2.1.1、什么是死锁？？

• 多个进程互相竞争资源，导致相互等待的情况就是死锁。
• 死锁产生的四个条件：
  
  • 互斥条件：资源本身是互斥的，只能被一个进程占有。
  • 不可剥夺条件：已经持有的资源不会被强制剥夺。
  • 请求与保持：一个进程申请新的资源，保持对已有的资源的占有。
  • 环路等待：存在一个进程等待序列{P1，P2，...，Pn}，其中P1等待P2所占有的某一资源，P2等待P3所占有的某一源，......，而Pn等待P1所占有的的某一资源，形成一个进程循环等待环。

2.1.2、MYSQL数据库中死锁？？

• 看一下官方给的例子：

// 事务A
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
// 开启A
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
// 给i=1这行加上共享锁
mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+
------ 事务B ------
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
// 给i=1这行加上排它锁，会被放入队列中进入lock wait
mysql> DELETE FROM t WHERE i = 1;
------ 事务B ------
// 此时事务A也需要给改行加上排它锁，形成死锁
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
解释：B需要给该行加排它锁，需要A的共享锁解锁，但是A共享锁解锁的条件是给该行加上排它锁，但是A加排它锁在B后面，要等B加上才能执行，所以陷入死锁。

2.1.3、死锁检测与回滚

• When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.（即死锁检测默认是开启的，当发生死锁的时候，选择最小(影响行的数量)的事务进行的回滚）
• 死锁检测
  
  • mysql在一些情况下（InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks.（有点不是很理解？？））可以检测到死锁，如果检测不到的情况，可以通过innodb_lock_wait_timeout system variable解决。
• 回滚
  
  • 选择最小(影响行的数量)的事务进行的回滚。

2.1.4、解决和避免死锁

• 主要包括两个方面，一个是死锁解决方式，第二个如何优化来尽量避免死锁。详细参考官方文档。
• issue the SHOW ENGINE INNODB STATUS command to determine the cause of the most recent deadlock.
• Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
• Keep transactions small and short in duration to make them less prone to collision.
• Use less locking. If you can afford to permit a SELECT to return data from an old snapshot。
• Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks.
• When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock.

2.1.5、参考文档

• Deadlocks in InnoDB