MySQL死锁预防分析解决死锁

最新推荐文章于 2024-04-01 12:57:06 发布

Alan CGH

最新推荐文章于 2024-04-01 12:57:06 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签：数据库

本文链接：https://blog.csdn.net/AllenChan_/article/details/126287265

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本篇文章根据官网文档的例子，学习死锁是如何发生的，以及如何预防和解决死锁。

死锁发生的例子

MySQL的死锁检测

降低和处理死锁

死锁例子

死锁发生的4个必要条件：

资源占有是互斥的
不主动释放已占有资源
不能强制剥夺已占有资源
形成环形等待链

用MySQL官方给出的例子介绍下死锁。

The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.
First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S lock on the row by selecting it in share mode:
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)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+
Next, client B begins a transaction and attempts to delete the row from the table:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDB generates an error for one of the clients and releases its locks. The client returns this error:
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
At that point, the lock request for the other client can be granted and it deletes the row from the table.

这个例子说了，事务A&B，A持有S锁，B请求X锁，此时B被阻塞在排队等A释放S锁。此时A升级S锁到X锁，A也陷入了阻塞！

还以为A可以顺利升级X锁呢？！文章给出解释，因为B在之前就排队申请X锁，A排在B后面申请X锁，因此死锁产生，A持有S锁，想升级X锁，B申请X锁，等待A释放S锁。

结果就是MySQL自动检测到死锁产生，选择一个代价小的一方结束事务，释放被结束方持有的资源。

这里可以延伸思考为什么A持有S锁却不能升级为X锁？

很简单，因为同时可能其他事务也持有S锁，贸然给A升级X锁，可能造成并发读写！

死锁检测

再来看看死锁检测，通过innodb_deadlock_detect配置项可以选择开启关闭，默认是开启的。开启后可以自动检测死锁并选择代价小的一个事务回滚来打破死锁。

怎么定义代价小？通过事务需要变动的数据行数大小来决定。

最后提到在高并发系统中，有大量的等待同一个锁的线程存在，死锁检测工作量会变大降低MySQL性能。建议关闭死锁检测，仅依靠 innodb_lock_wait_timeout 参数来回滚那些超时等待锁的事务。

如何降低和处理死锁

怎么降低死锁发生？

缩小写数据的范围，创建索引，即降低锁粒度
缩短持有锁的时间
在相同的事务中，用相同的操作顺序给资源加锁（死锁的发生条件之一就是两个事务用相反的顺序给资源加锁）

还有降低事务隔离级别，例如RC下就比RR 死锁概率低，因为RC下锁粒度会更小。

如果死锁已经发生了，在默认配置的MySQL下引擎会帮我们自动解决。一个事务因获取锁阻塞的时间超过了innodb_lock_wait_timeout阈值，就会被引擎强制释放已占有的资源并回滚事务，返回错误信息给客户端。

但问题本质没有解决，如果系统发生死锁概率比较高会严重影响性能和吞吐量。这个时候需要排查分析死锁发生的原因。通常有3种手段定位问题：1.查看正在活动的事务，找到当前等待的事务。2.找到这个事务正在等待的锁。3.找到是哪个事务持有这个锁。

查看Innodb中正在活动的事务

information_schema.innodb_trx 表记录了当前正在活动的事务信息

select * from information_schema.innodb_trx

然后会看到一部分信息，其中有事务ID，事务锁住的数据行数。

查看正在活动的锁情况

根据上面的语句只能看到大致的事务信息，怎么看具体事务给哪些数据行上了锁，上了什么锁。就需要看performance_schema.data_locks表。注意 MySQL 8以前的版本 5.7 5.6这些是information_schema.INNODB_LOCKS表。

可以看到事务id、上锁的数据库、表名、锁的级别、锁的模式、获取锁的情况、锁的索引数据值

SELECT
	engine_transaction_id,
	object_schema,
	object_name,
	index_name,
	lock_type,
	lock_mode,
	lock_status,
	lock_data 
FROM
	`performance_schema`.data_locks

通过以上信息，如果事务数少的情况下，可能可以比较容易看出是哪个事务阻塞了另外的事务。如果事务数多的情况，就不直观。

查看正在等待锁的事务情况

我们直接查看 performance_schema.data_lock_waits表，分析事务被哪个事务和锁阻塞了。

select * from `performance_schema`.data_lock_waits

重要的是可以显示出当前被阻塞的事务ID，它自己已获取的锁情况。它被另外一个事务ID阻塞，它正在等待的锁ID。

data_lock_waits表模型数据字段解释

ENGINE

The storage engine that requested the lock.

REQUESTING_ENGINE_LOCK_ID

The ID of the lock requested by the storage engine. To obtain details about the lock, join this column with the ENGINE_LOCK_ID column of the data_locks table.

REQUESTING_ENGINE_TRANSACTION_ID

The storage engine internal ID of the transaction that requested the lock.

REQUESTING_THREAD_ID

The thread ID of the session that requested the lock.

REQUESTING_EVENT_ID

The Performance Schema event that caused the lock request in the session that requested the lock.

REQUESTING_OBJECT_INSTANCE_BEGIN

The address in memory of the requested lock.

BLOCKING_ENGINE_LOCK_ID

The ID of the blocking lock. To obtain details about the lock, join this column with the ENGINE_LOCK_ID column of the data_locks table.

BLOCKING_ENGINE_TRANSACTION_ID

The storage engine internal ID of the transaction that holds the blocking lock.

BLOCKING_THREAD_ID

The thread ID of the session that holds the blocking lock.

BLOCKING_EVENT_ID

The Performance Schema event that caused the blocking lock in the session that holds it.

BLOCKING_OBJECT_INSTANCE_BEGIN

The address in memory of the blocking lock.