mysql InnoDB引擎与锁机制

1 共享锁与排它锁

共享锁(S锁):指读锁
排它锁(X锁):写锁,更新或删除锁

共享锁与共享锁兼容与排它锁互斥,排它锁与排它锁/共享锁都互斥
事务A对记录加共享锁,事务B对相同数据可以加共享锁,但是不能加排它锁
事务A对记录加排它锁,事务B对相同数据既不能加共享锁也不能加排它锁.
select 时也可以显示加共享锁:在语句最后加lock in share mode;
select 时也可以显示加排他锁:在语句最后加for update;

2表级锁行级锁

lock TABLES 表名 read  锁表(读)  
lock TABLES 表名 write 锁表(写)
unlock tables          解锁
select *  from 表名 where xx lock in share mode 锁行(select)
select *  from 表名 where xx for update         锁行(update delete)

3 意向锁

意向共享锁(IS):表共享锁  
意向排它锁(IX):表排他锁 

意向锁是表级锁,意向锁与意向锁之间不冲突,与行级的共享锁/排它锁也不冲突,但是意向排它锁与表级排它锁/表级共享锁冲突,意向共享锁与表级排他锁冲突.
意向锁的作用:当一个事务A获取表中的一条或者几条记录的排他锁时,如果其他事务要获取表级锁,需要先遍历所有行,看是否有其他事务的行级锁,来确定是否可以加表级锁,这样做效率太慢了.当一个事务要获取排它锁/共享锁之前,就必须获取意向排它锁/意向共享锁,只要根据表上是否有意向排他锁/意向共享锁,其他事务就可以直接判断是够否能够加表级排他锁/表级意向锁,从而节约时间.

4 间隙锁(gap锁)

间隙锁与间隙锁不互斥,是防止insert,行锁防止并发update跟delete
间隙锁只作用在Repeatable Read事务隔离级别下
对于唯一索引确定唯一记录情况下不适用gap锁,其他情况都是用gap锁(主键唯一,并且确定唯一记录情况下,本身就是不能在当前主键插入数据,所以也不需要gap锁),当使用非唯一索引时候,也会锁定范围为索引值,不允许插入相同索引值.
如果没有通过索引检索时,会锁定所有间隙,既其他事务什么也插入不了.
gap范围,在next-key锁中详细展示范围.

5 next-key锁

next-key锁:行锁+gap锁,repeatable read 事务隔离级别下才有
如果有数据:

id	name
10	li
20	zhang
30	zhao

如果没有使用索引:

select * from test where xxx for update;  无论什么条件都是表锁

如果使用唯一索引并且确定记录next-key锁下降为行级锁:

select * from test  where id = 10 for update;   id为唯一索引/主键,锁定10行,并且主键唯一,不能再插入id=10的数据 

使用普通索引并且确定唯一记录:

select * from test  where id = 10 for update;   id为普通索引
锁定范围 (+∞,20)
同理
id = 20 [10,30)
id = 30 [20,+∞)

使用普通索引锁定区间无记录:

select * from test where id = 11 for update;(表中没有id=11的记录)
锁定范围为[10,20) 
同理
id = 9  则(-∞,10)
id = 21 则[20,30)
id = 31 则[30,+∞)

唯一索引(主键)锁定区间无记录:gap锁

id = 9  则(-∞,10)
id = 15 则(10,20)
id = 21 则(20,30)
id = 31 则(30,+∞)

6 插入意向锁

插入意向锁本质也是gap锁,但是插入意向锁与真正的gap锁是互斥的,查阅了一下,包括官网说的都是模棱两可的,本人理解是:锁跟锁是要互斥才能保证并发的顺序,插入记录时候,因为此时判断是否能够插入的是gap锁,但是如果insert不获取锁的话,就没有办法使插入操作进行线程等待,而插入的记录获取的是gap锁的话,起不到作用,因为gap锁不互斥,而gap锁与排它锁又完全不是一个层次的锁,gap锁作用于inset,排它锁作用于update跟delete,所以才设计一个意向插入锁,当事务获取了gap锁时,另外一个事务必须要获取插入意向锁,此时两个锁互斥,这样才能保证insert可以插入还是线程等待.

7 auto_inc 锁

这个锁作用于RR事务隔离级别下的有自增主键的表的插入.是表级锁.
test表有id自增主键,name字符串列,
insert into test(name) values(‘li’) (还有其他insert into语句)此时插入语句会去获取表级的auto_inc锁,其他插入的事务需要等待.
此锁主要是为了保证主键的自增,对数据锁定是针对的语句级,而不是事务级.分不同模式,通过innodb_autoinc_lock_mode参数指定,详细内容就不介绍了.

8 事务隔离级别与锁

read uncommitted 读不加锁 写加表或者行锁
read committed 读不加锁,使用mvcc,写加表锁或者行锁
repeatable read 读不加锁,使用mvcc,写加next-key锁
serializable 读加读锁,写加写锁,都是表级(不要用for update,for update会更改默认的表锁,所以测试会发现不一样)