mysql数据库死锁

转载

下面是查看正在运行时锁相关sql语句

下面三个一起运行

1. 查询当前事务
select * from information_schema.innodb_trx;

2. 查询当锁
select * from information_schema.innodb_locks;

3. 查询当前锁等待情况
select * from information_schema.innodb_lock_waits;

4. 导出刚发生的死锁日志
show engine innodb status;

锁介绍

• 行锁（Record Locks）
• 间隙锁（Gap Locks）
• 临键锁（Next-key Locks）
• 共享锁/排他锁（Shared and Exclusive Locks）
• 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
• 插入意向锁（Insert Intention Locks）
• 自增锁（Auto-inc Locks

1、行锁

记录（行）锁是索引记录上的锁。记录锁总是锁定索引记录，即使定义的表没有索引。在这种情况下，InnoDB会创建一个隐藏的聚集索引，并将该索引用于记录锁定。

这句话说明行锁一定是作用在索引上的。

2、间隙锁\临健锁

在MySQL的官方文档中有以下描述：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。
间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。
关于间隙锁的加锁规则和范围可参考

3.共享锁S/排他锁X

共享锁/排他锁都只是行锁，与间隙锁无关；其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select … in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select … for update。
不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。
一条记录的共享锁可多个事务同时持有；
一个事务要想持有排它锁X，则必须让其他事务都释放共享锁S，才能申请到排它锁X；不同事务中，共享锁和排它锁是互斥的，即针对同一条唯一索引记录，A事务拥有S锁，B事务拥有S锁，当A事务申请X锁时，必须要等待B事务释放S锁后才能申请到，所以当A、B两个事务又都同时申请X锁时，就是进入到了死锁。

4.意向共享锁IS/意向排他锁IX

意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。
共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

5.插入意向锁IIX

插入意向锁是mysql为了提高并发能力而支持的；
插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：

1. 插入意向锁和间隙锁互斥，当A事务拥有（a,b）间的间隙锁时，B事务此时想申请(a，b)之间的插入意向锁则会阻塞；
2. 当同时插入的数据记录（唯一索引）位置不冲突，那么就不会彼此阻塞；
3. 当出现同一个位置时，其中一个事务进行锁等待；

6.主键自增锁

innodb_autoinc_lock_mode锁行为机制
主键自增锁可看转载
个人认为自增主键基本不可能造成死锁，除非代码方面特意指定了主键的值。建议使用twitter-的snowflake算法（雪花id）

说明

• 读取时有S锁；更新/删除时有X锁；插入时便有插入意向锁IIX；

• for update加的什么锁？
  mysql进行row lock还是table lock只取决于是否能使用索引（例如主键，unique字段），能则为行锁，否则为表锁；

• 加共享锁
  select * from user where id = 154 lock in share mode;

• 加排它锁
  select * from user where id = 154 for update;