MySQL--死锁的原因及解决方法-向日葵屋

MySQL--死锁的原因及解决方法

原文网址：MySQL—死锁的原因及解决方法_IT利刃出鞘的博客-CSDN博客

简介

说明

本文介绍MySQL死锁的原因及解决方法。

InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，所以可能发生死锁。

不会死锁的情况

MyISAM表不会出现死锁（Deadlock Free）。

原因：用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。

导致死锁的场景

先申请共享锁后申请排它锁（同一张表）

在事务中，更新记录时应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁。因为当申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

下面的例子中，先申请共享锁，更新时再申请排他锁，造成死锁。

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +—————+——————+—————-+ \| actor_id \| first_name \| last_name \| +—————+——————+—————-+ \| 178 \| LISA \| MONROE \| +—————+——————+—————-+ 1 row in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +—————+——————+—————-+ \| actor_id \| first_name \| last_name \| +—————+——————+—————-+ \| 178 \| LISA \| MONROE \| +—————+——————+—————-+ 1 row in set (0.00 sec)
当前session对actor_id=178的记录加共享锁： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +—————+——————+—————-+ \| actor_id \| first_name \| last_name \| +—————+——————+—————-+ \| 178 \| LISA \| MONROE \| +—————+——————+—————-+ 1 row in set (0.01 sec)
	其他session仍然可以查询记录，并也可以对该记录加共享锁： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +—————+——————+—————-+ \| actor_id \| first_name \| last_name \| +—————+——————+—————-+ \| 178 \| LISA \| MONROE \| +—————+——————+—————-+ 1 row in set (0.01 sec)
当前session对锁定的记录进行更新操作，等待锁： mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; 等待
	其他session也对该记录进行更新操作，则会导致死锁退出： mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
获得锁后，可以成功更新： mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1 Changed: 1 Warnings: 0