mysql数据库AB-BA死锁问题
使用Innodb引擎
死锁概念
死锁是指两个或者两个以上事务在执行过程中,因争夺资源而造成一种相互等待的现象
准备
准备表k:
mysql> desc k;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.16 sec)
mysql> select * from k;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 4 | 5 |
| 5 | 4 |
+---+---+
3 rows in set (0.04 sec)
AB-BA问题
sesseionA: | sessionB:
mysql> start transaction; |
Query OK, 0 rows affected (0.00 sec) |
|
mysql> select * from k where a=1 for update; |
+---+---+ |
| a | b | |
+---+---+ |
| 1 | 2 | |
+---+---+ |
1 row in set (0.03 sec) |
| mysql> start transaction;
| Query OK, 0 rows affected (0.00 sec)
|
| mysql> select * from k where a=4 for update;
| +---+---+
| | a | b |
| +---+---+
| | 4 | 5 |
| +---+---+
| 1 row in set (0.02 sec)
mysql> select * from k where a=4 for update; |
| mysql> select * from k where a=1 for update;
| transaction1213 - Deadlock found
| when trying to get lock;
| try restarting transaction
可以发现已经产生了死锁
解决死锁
- 超时机制
- wait-for graph(等待图)
超时机制
当两个事务相互等待时,当一个等待时间超过设置的某一阈值时,其中一个事务进行回滚,另一个等待的事务就能继续进行。在InnoDB存储引擎中,可以通过设置innodb_lock_wait_timeout
参数,默认是50:
mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 50 |
+----------------------------+
1 row in set (0.11 sec)
wait-for graph机制
wait-for graph要求数据库保存以下信息:
- 锁的信息链表
- 事务等待链表
通过上述链表可以构造一张图,而这个图中若存在回路,就代表存在死锁