MySQL InnoDB引擎锁

InnoDB引擎出现死锁的情况如下：

（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

InnoDB引擎中表顺序操作不一致造成死锁
session1	session2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where id = 1 for update; +----+----------+ | id | name     | +----+----------+ |  1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
	mysql> select * from t_temp_user where id = 1; +----+----------+----------+ | id | name     | password | +----+----------+----------+ |  1 | zhangsan | 123456   | +----+----------+----------+ 1 row in set (0.00 sec)
mysql> select * from t_temp_user where id = 1 for update; 等待
	mysql> select * from t_user where id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans action mysql>
mysql> select * from t_temp_user where id = 1 for update; +----+----------+----------+ | id | name     | password | +----+----------+----------+ |  1 | zhangsan | 123456   | +----+----------+----------+ 1 row in set (7.93 sec) mysql>

 

（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能

InnoDB引擎中表记录操作不一致造成死锁
session1	session2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where id = 1 for update; +----+----------+ | id | name     | +----+----------+ |  1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
	mysql> select * from t_user where id = 2 for update; +----+------+ | id | name | +----+------+ |  2 | lisi | +----+------+ 1 row in set (0.00 sec)
	mysql> select * from t_user where id = 1 for update; +----+----------+ | id | name     | +----+----------+ |  1 | zhangsan | +----+----------+ 1 row in set (6.16 sec) mysql>
mysql> select * from t_user where id = 2 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans action mysql>

 

（3）在事务中如果需要更新数据时，应该先申请排他锁，而不应该先共享锁，再申请排他锁。

InnoDB引擎中更新数据使用共享锁造成死锁
session1	session2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where id = 1 lock in share mode; +----+----------+ | id | name     | +----+----------+ |  1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
	mysql> select * from t_user where id = 1 lock in share mode; +----+----------+ | id | name     | +----+----------+ |  1 | zhangsan | +----+----------+ 1 row in set (0.00 sec)
mysql> update t_user set name = 'zhangsan' where id = 1;
	mysql> update t_user set name = 'zhangsan' where id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans action mysql>
mysql> update t_user set name = 'zhangsan' where id = 1; Query OK, 0 rows affected (32.54 sec) Rows matched: 1  Changed: 0  Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql>

 

（4）在repeatable-read隔离级别下，如果两个线程同时对相同条件记录用select * for update加排他锁，在没有符合记录条件的情况下，两个线程都会加锁成功
    如果此时，同时插入一条新的记录，会造成死锁，需要将隔离级别设置read committed

InnoDB引擎中隔离级别为repeatable-read造成死锁
session1	session2
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> select * from t_user where id =5; Empty set (0.00 sec) mysql> 查看当前数据库的隔离级别，及不存在id = 5的记录
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where id = 5 for update; Empty set (0.00 sec)
	mysql> select * from t_user where id = 5 for update; Empty set (0.00 sec)
mysql> insert into t_user(name) values('qianqi'); dengdai
	mysql> insert into t_user(name) values('qianqi'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans action mysql>
mysql> insert into t_user(name) values('qianqi'); Query OK, 1 row affected (36.04 sec) mysql>

 

 

对于InnoDB引擎，如果没有索引，则创建的行锁是表锁，具体示例如下

InnoDB引擎中表不存在索引，则建立的行锁，其实是表锁
session1	session2
mysql> show create table t_temp_user; +-------------+---------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------+ | Table       | Create Table                         | +-------------+---------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------+ | t_temp_user | CREATE TABLE `t_temp_user` (   `id` int(19) default NULL,   `name` varchar(30) default NULL,   `password` varchar(30) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------------+---------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------+ 1 row in set (0.00 sec) mysql>
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_temp_user where id = 1 for update; +------+----------+----------+ | id   | name     | password | +------+----------+----------+ |    1 | zhangsan | 123456   | +------+----------+----------+ 1 row in set (0.00 sec) mysql>	mysql> select * from t_temp_user where id = 2 for update; 出现等待
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql>
	mysql> select * from t_temp_user where id = 2 for update; +------+------+----------+ | id   | name | password | +------+------+----------+ |    2 | lisi | 123456   | +------+------+----------+ 1 row in set (7.07 sec) mysql>