InnoDB锁总结

1. InnoDB锁

InnoDB采用行锁，根据事务级别分别采用不同的锁。一般情况下，如果二级索引加锁时，会同时去找对应的聚集索引加锁。如果无索引时，读时逐行扫描锁全表。

(1) S锁,共享锁；X锁，排他锁。S锁和S锁可以共存，S锁和X锁互斥。

(2) 意向锁【表级锁，多粒度锁支持表锁和行锁共存】

InnoDB允许表锁和行锁共存，意向锁是数据库表级别的锁，指出事务稍后对表中的行需要哪种类型的锁（X锁或者S锁）。意向锁只会阻塞锁全表，它只是表明某个事务已经获取或想要获取某行的锁。

# select..lock in share mode IS锁，在事务获取行的S锁前，该事务必须获取表的IS锁或者更强的锁；

# select..for update IX锁，在事务获取行的X锁之前，该事务必须获取表的IX锁；

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	共享	互斥	共享
S	互斥	互斥	共享	共享
IS	互斥	共享	共享	共享

# 总结

X锁和任何锁都互斥，S锁和S锁，IS共存；IX锁和IX，IS锁共存；IS锁和除X锁外共存

(3) 行记录锁【锁索引】

记录锁是指记录索引上的锁，记录锁总是第一时间去锁索引，若表上没有定义索引，则锁隐藏的聚集索引。

(4) gap间隙锁【区间】

间隙锁是指索引上的间隙锁，或对第一个索引记录之前或最后一个索引记录之后的间隙的锁。只阻塞其他事务在间隙区间内插入新记录，对于其他事务获取相同的间隙锁不阻塞。column=x时，该列有唯一索引不会触发gap锁，只会触发行锁，其他情况下会触发gap锁，锁(0,x]；触发当事务级别为read committed或者innodb_locks_unsafe_for_binlog开启时，间隙锁被禁用。

# select c from t where c between 10 and 20; 区间范围为10-20，则区间范围内不允许插入任何行。

(5) Next-key

记录锁和gap锁的结合，index行锁+index之前的gap锁，不允许index前insert任何数据。

默认REPEATABLE READ隔离级别下，InnoDB采用next-key，为扫描搜索表索引时遇上的行添加上S锁或者X锁。当一个事务拥有一行的记录锁时，阻塞其他事务在该行的索引顺序之前的间隙中插入新行，避免幻读。

(6) Insert意向锁

由insert操作发起，是一种gap锁。若多个事务在相同的间隙范围内插入行的位置不同，该锁不会阻塞对方。

(7) Auto-inc锁

表级锁，由insert操作发起，若有自增的列。阻塞其他事务。

(7) Predicate锁

表空间索引锁，包含最小边界矩阵，阻塞其他事务insert和修改。

2. 事务级别

read uncommited	会读到其他事务未提交的数据，脏读，无锁
read commited	# 同一个事务内的的select都获取当前最新版本数据 # gap锁禁用 # 对于加锁读,update,delete语句，只锁索引记录，允许insert到行锁的临近位置，gap锁只在外键时采用；会读到其他事务在当前事务提交前提交的数据； # update或delete加锁时，先锁住扫描时遇到的行，一行行处理时，经过评估释放不满足where条件的行锁，一直持有满足条件的行锁 # update语句，如果一行已经锁住了，InnoDB采用半一致读将最新提交的版本返回给mysql以判断这行是否满足where条件，若满足mysql锁住该行不满足释放锁。 #无索引时，锁全表
repeatable read	# InnoDB默认隔离级别，事务内一致读，每次都读第一次读时的产生的快照版本 # 对于加锁读，uodate,delete等，若表中有唯一索引且搜索采用唯一索引时，只锁满足条件的行，其他情况，一直锁住扫描的索引区间，采用gap锁或者next-key阻塞其他事务insert到相同区间 # 先锁住扫描时遇到的行，直到执行结束释放锁 # 无索引时，锁全表 # 一致读时，InnoDB为事务设置了一个时间点，这个时间点之后的修改或插入数据，事务内是不能读到的。但是，该snapshot对dml操作却没有作用，该事务可能会修改其他事务已经提交的修改。避免这种操作的方式就是select后提交事务，重新起另一个事务select后操作。 # 当事务A修改
serializable	gap锁或者next-key autocommit禁用时，隐式地将select语句转换为加IS锁模式

一致读在ddl时不起作用：

(1) drop table；Mysql不能使用已经删除的表

(2) alter table；该语句创建了一个临时表拷贝了原表数据，然后删除原表

3. 加锁sql

(1) select..lock in share mode

采用next-key，为读到的行加S锁，其他事务只能读不能修改这些行，如果该事务之前有其他事务正在修改这些行，那么该查询等待其他事务结束，并读到最新数据。

(2) select..for update

采用next-key，为扫描遇到的行加X锁，并且锁住任何索引入口。

(3) update..where

采用next-key加X锁，更新聚集索引时为二级索引加S锁，插入新二级索引时校验重复key。

(4) delete..where

采用next-key加X锁

(5) insert

采用索引记录锁加X锁，执行insert先设置一个意向gap锁，同区间内不同位置不会阻塞。

若重复key错误发生时重复记录设置一个S锁，可能引起死锁：

事务A inset 1，获取X锁；事务B insert 1, 等待；事务C insert 1, 等待；若事务A回滚释放X锁，则BC死锁。

事务A delete 1，获取X锁；事务B insert 1, 等待；事务C insert 1, 等待；若事务A提交释放X锁，则BC死锁。

(6) insert...on duplicate key update

若重复key错误发生时，重复主键设置一个X锁（索引记录锁），重复唯一键设置X锁（next-key）不会发生死锁。

(7) replace

采用next-key加锁，在没有冲突时和insert一致，否则加X锁

(8) insert into T select..from S where

T表加X锁（索引记录锁），若事务级别为read committed或者innodb_lock_unsafe_for_binlog启用，S表无锁，其他情况下S表加S锁（next-key）

(9) create table..select...

与8一致

4. 避免死锁

(1) 更新数据尽量采用小事务，避免占用锁过长时间；

(2) 更新时顺序一致；

(3) 设置innodb_lock_wait_timeout

(4) insert, update, delete操作尽量放一起；

(5) 合理的添加索引；

5.事务举例 [read-commit事务级别]

本地事务中注意控制幂等性，单纯的记录锁不能保证数据操作安全

时间	T1  order(id, price)	T2  order(id,version, price)
1	begin;	begin;
2	update order set price=1 where id=1 and price=0 获取id=1行X锁，执行成功影响1行
3		update order set price=2 where id=1 and price=0 等待id=1行X锁，该行后若有sql则阻塞等待
4	commit; （释放锁，持久化）	update order set price=3 where id=1
5		获取行锁，第一条执行成功影响0行，因为price已经不等于0了 第二条执行成功覆盖T1值 commit; （释放锁，持久化）
6