Mysql锁机制总结

按锁的粒度可以分为全局锁、表级锁、行锁、间隙锁、临键锁。

按锁的属性可以分为共享锁、排他锁

全局锁

• 加全局读锁的命令：Flush tables with read lock (FTWRL)
• 此命令会使整个库处于只读状态
• 使用场景：做全库逻辑备份

表级锁（MyISAM 默认）

• 表锁语法是lock tables xxx read/write，用
• 元数据锁（表的结构、字段、数据类型、索引等）（metadata lock，MDL)，由server层实现，不需要显示的加，增删改查会加mdl读锁，对表结构进行变更时会加mdl写锁，读读不互斥，读写、写写互斥。

行级锁（InnoDB 默认）

• 共享锁（Share Lock即S Lock，读锁、S锁）: 一个事务对一个数据对象加了S锁，可以对这个数据对象进 行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加S锁，不能加X锁。

SELECT * FROM t WHERE ... LOCK IN SHARE MODE 

• 排他锁（eXclusive Lock即X Lock，写锁，X锁）: 一个事务对一个数据对象加了X锁，可以对这个对象进行 读取和更新操作，加锁期间，其他事务不能对该数据对象进行加X锁或S锁。

SELECT * FROM t WHERE ... FOR UPDATE

默认情况下innodb用的是隐式加锁。Innodb存储引擎在执行update、delete、insert语句时会隐式加排它锁，而对于select不会加任何锁。

意向共享锁 IS/意向排他锁 IX：

• 意向共享锁 IS/意向排他锁 IX 属于表锁，取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

• InnoDB行锁是给索引项加锁来实现的。这样的实现方式意味着当一个事务对表的某一行加锁后，后面的每个需要对该表加持表锁的事务都需要遍历整个索引树才能知道自己是否能够进行加锁，这样就会很浪费时间和损耗数据库性能。

• 于是有了意向锁(Intention locks)的概念：如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向锁。

间隙锁和临键锁

间隙锁（Gap lock）：间隙锁是在事务加锁后其锁住的是表记录的某一个区间（开区间），当表的相邻ID之间出现空隙则会形成一个区间，比如表里面的数据id 为 1,7,10 ，那么会形成以下几个间隙区间，(负无穷，1)，(1，7)，(7，10)，(10，正无穷)。

• 作用：用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的，防止幻读问题。
• 触发条件： 查询条件必须命中索引，范围查询，或者等值查询未命中记录（若命中，则会升级为行锁）。间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。在RU和RC隔离级别下没有间隙锁。

临键锁（Next-Key Lock）：临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

• InnoDB在RR隔离级别下，如果你使用select … in share mode或者select … for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

数据库乐观锁和悲观锁

• 乐观锁实现：在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE … SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

• 悲观锁实现：

数据库死锁

数据库的死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方的资源。

场景：

• 事务1：UPDATE T SET xxx WHERE ID = 1; UPDATE T SET xxx WHERE ID = 2;
• 事务2：UPDATE T SET xxx WHERE ID = 2; UPDATE T SET xxx WHERE ID = 1;

预防死锁：

• 不同程序尽量约定以相同的顺序访问表
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源
• 在事务中，如果要更新记录，应该申请足够级别的锁，比如排他锁。

解决死锁：

• 一般InnoDB会自动检测到，使一个事务回滚，另一个事务继续；
• 设置超时等待参数innbdb_lock_wait_timeout