MySQL阻塞与死锁

MySQL阻塞与死锁

阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。

# 查看等待时间
show variables like 'innodb_lock_wait_timeout';
SET@@innodb_lock_wait_timeout=60;
# 是否在等待超时时对进行中的事务进行回滚操作
show variables like 'innodb_rollback_on_timeout';

#设置等待时间  默认50秒
SET@@innodb_lock_wait_timeout=60;  
#设置是否在等待超时时对进行中的事务进行回滚操作  默认是OFF 代表不回滚
SET@@innodb_rollback_on_timeout=on;
1
2
3
4
5
6
7
8
9
10

查询：

设置值：

注意：参数innodb_lock_wait_timeout 参数是动态的，在mysql运行时可进行调整，innodb_rollback_on_timeout参数是静态的，不可在运行时进行修改，否则会报错。

需要特别注意：在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。（其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚。）

异常实例演示：

左边为会话A，右边为会话B。初始状态数据库表film中有3条数据，ID，分别为3，5，6；

首先会话A 开启了事务A，并且在Next-Key Lock算法下锁定了小与5包含5的记录。事务B正常插入记录ID:7，当插入ID:4 时就进人阻塞状态了

当事务B达到事务超时间时间时，报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

这时事务B在此查询会发现，**ID:7 这条记录依然存在，这是因为事务B虽然抛出了异常，但是既没有进行commit 操作也没有进行 rollback操作。**这是非常危险的，因为此时数据库一致性特性被打破了。因此此时用户必须判断是否需要COMMIT还是ROLLBACK，之后再进行下一步的操作。

一般情况这时事务B需要进行 rollback操作，具体情况具体分析。

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

当发生死锁的时候，若无外力作用事务都将无法进行下去。

死锁产生的条件：

• 互斥条件
  临界资源是独占资源，进程应互斥且排他的使用这些资源。
• 占有和等待条件
  进程在请求资源得不到满足而等待时，不释放已占有资源。
• 不剥夺条件
  又称不可抢占，已获资源只能由进程自愿释放，不允许被其他进程剥夺。
• 循环等待条件
  又称环路条件，存在循环等待链，其中，每个进程都在等待链中等待下一个进程所持有的资源，造成这组进程处于永远等待状态。

如何解决死锁问题：

1.超时机制：当两个事务互相等待时，如果等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在InnoDB存储引擎中，innodb_lock_wait_timeout设置超时的时间。

我们知道，一条记录是有很多undo log的或者undo 版本链有很多版本的，如果一个事务操作更新了很多行，这时候如果要进行回滚所占用的时间可能就会很多。

2.使用wai t-for graph （等待图）进行死锁检测，数据库需要保存锁的信息链表和事务等待链表。我们通过锁的信息链表和事务等待链表就可以构造出一张图，如果图中存在回路那就说明出现了死锁。

​ 事务和锁状态图

上图中，transaction wait list 中有四个事务，事务t2对row1加了x锁，事务t1对row1加了s锁，并且事务t1需要等待事务t2中的row1资源，因此wait-for graph图中有一条边冲节点t1指向t2。 row2记录的情况同理。

​ wait-for graph

观察发现，t1 和 t2 存在回路，因此存在死锁。

阅读到这里，我们需要意识到因为MySQL数据库是一个并发的程序，所以才存在死锁。因为如果程序是串行的，那么也就不会发生死锁了。

死锁示例：

有两个事务A，B： 事务A当前读查询记录ID:5 ,事务B当前读查询ID:6。

接着交换一下：

事务A当前读查询记录ID:6 ,事务B当前读查询ID:5

会发现两个事务中的事务B立马就报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

通常来说InnoDB存储引擎选择回滚undo量最小的事务。