MySQL InnoDB下的死锁和锁等待超时的问题验证与梳理

MySQL数据库死锁问题

例子表
user{
id 主键
name
}


//PART01
备注：共享锁之间可以并发执行，排他锁需要等待其他共享锁、排他锁释放
共享锁（简单的select语句不会加锁）
select * from user where id='5' lock in share mode;
排他锁
update user set name='111' where id='5';


//PART02
表级锁（非索引作为条件）
update user set name='222' where name='111'
行级锁（索引作为条件）
update user set name='111' where id='5'


//PART03
备注：完整的事务命令
mysql>begin;
mysql>update user set name='111' where id='5';
mysql>commit;

问题：
锁等待超时Lock wait timeout exceeded;
事务1
mysql>begin;
mysql>update user set name='111' where id='5';//事务1占着本行行级锁，一直不执行commit，不释放行级锁
mysql>
事务2
mysql>begin;
mysql>update user set name='222' where id='5';//卡住了，等待事务1 commit后释放行级锁，直到超时


死锁Deadlock found when trying to get lock;
事务1
mysql>begin;
mysql>update user set name='111' where id='5';//事务1占有本行行级锁
mysql>update user set name='222' where name='111';//卡住了，事务1占有表级锁时候，需要等待事务2释放行级锁，进入 A等待B，B又等待A的死锁（此时mysql的内部机制会重启事务2，解决死锁问题，保证事务1可以正常进行）
事务2
mysql>begin;
mysql>update user set name='333' where id='5';//事务2要占有行级锁时卡住了，排队等待事务1释放本行行级锁（验证过，多个事务是有等待顺序的）

注，验证发现：

行级锁如果确定的行不同，不会等待对方，例如where id=1 和 where id=2不会让对方等待，只有同时where =1时，后开始的事务才会等待先开始的事务释放行级锁；

表级锁则始终要后事务要等待前一个事务释放表级锁，不论条件是where name='11'和where name='22'，还是where name='11'和where name='11'