一次死锁测试：
mysql> show create table t4;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
|
session 1： |
session 2： |
|
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | qqq | | 3 | www | +----+------+ 2 rows in set (0.00 sec) mysql> insert into t4 values (5,'qwer'); Query OK, 1 row affected (0.00 sec) mysql> select * from t4 where id = 1 for update; +----+------+ | id | name | +----+------+ | 1 | qqq | +----+------+ 1 row in set (0.00 sec) |
|
|
|
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | qqq | | 3 | www | +----+------+ 2 rows in set (0.00 sec) mysql> insert into t4 values (7,'tttt'); Query OK, 1 row affected (0.00 sec) mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | qqq | | 7 | tttt | | 3 | www | +----+------+ 3 rows in set (0.00 sec) mysql> delete from t4 where id=3; Query OK, 1 row affected (0.00 sec) |
|
mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | qqq | | 5 | qwer | | 3 | www | +----+------+ 3 rows in set (0.00 sec) mysql> update t4 set name='zzz' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update t4 set name='zzz' where id=3; |
|
|
|
mysql> delete from t4 where id=1; Query OK, 1 row affected (0.00 sec) |
|
产生死锁，整个事务回滚 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql> select * from t4; +----+------+ | id | name | +----+------+ | 1 | qqq | | 3 | www | +----+------+ |
|
怎样降低 innodb 死锁几率？
死锁在行锁及事务场景下很难完全消除，但可以通过表设计和SQL调整等措施减少锁冲突和死锁，包括：
1、设计好索引，并尽量使用索引访问数据；
2、尽量使用小事务，小事务发生锁冲突的几率也更小；
3、不同的程序访问一组表时，应尽量约定以相同的顺序访问各表；
