MySQL中的锁

概述

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

MyISAM表级锁

锁模式

show status like ‘table%’ 可以分析锁争夺情况

表级锁分为两种模式：共享读锁，独占写锁

读读共享，读写互斥，写写互斥，写读互斥(比如写读互斥，当一个用户对user表进行写的时候，会阻塞其他用户的写操作和读操作)

加表锁

在select的时候自动加读锁，在update delete insert时，自动加写锁

一般不需要用户用lock table 命令直接显式加锁

显示加表锁时

session1显示给user表加锁

lock table user read;

session1此时不能读取未锁定的class表，所以lock table时要一次性锁定要用到的表

session1此时不能进行更新和插入，只能进行读取

其他session可以读取user表，但是更新会阻塞，而插入不会阻塞(可以通过设置concurrent_insert 系统变量来控制并发插入行为)

锁调度

当一个进程请求user表的读锁，一个进程请求user表的写锁，即使读请求先到，也是请求写锁成先成功

所以，MyISAM不适合大量的更新，因为大量更新会导致查操作很难获得读锁

InnoDB行级锁

行锁

通过 show status like ‘InnoDB_row_lock’ 来分析行锁争夺情况

共享锁S：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

排他锁X：允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁

意向锁

所谓意向锁就是意图拿到锁，所以要获得共享锁时，需要先获得意向共享锁

意向共享所IS，一个事务给一个数据行加共享锁时，必须先获得表的IS锁

意向排它锁IX，一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

间隙锁

范围查询时，比如select * from user where id>50 ，这个时候即使最大id为51，不仅会给符合条件的行加锁，也会给id大于51(不存在的记录)的加锁，这就是间隙锁

间隙锁解决了事务并发的幻读问题

兼容情况

锁

S

X

IS

IX

S

Yes

No

Yes

No

X

No

No

No

No

IS

Yes

No

Yes

Yes

IX

No

No

Yes

Yes

加锁

意向锁是InnoDB自动加的

对于update insert delete 会自动加排它锁

对于select不会自动加锁

Select * from user LOCK IN SHARE MODE 手动添加共享锁

select * from user For update 手动添加排它锁

行锁使用方式

通过索引条件检索数据时，才使用行锁，否则用表锁

也就是如果select的where列不是索引列，那么此时，实际上是给整个表加锁

检索条件是索引时，又分索引是聚集索引还是非聚集索引，如果是聚集索引(索引上叶节点存放的整条记录)，在聚集索引上的加上行锁，而如果是非聚集索引，要在非聚集索引记录上加行锁，又要在聚集索引上加行锁

什么时候使用表锁

事务需要更新表的大部分数据。如果每一行都加上排他锁，锁冲突严重，那么此时执行效率低

事务涉及多个表，比较复杂

死锁

MyISAM

由于加的是表锁，一次性锁定要需要的表，所以不会出现死锁

InnoDB

死锁一般是事务相互等待对方资源

发生死锁后，会自动检测，并让一个事务释放锁并回退

session1

session2

update user set username=’jiajun’ where id=1

update user set username=’jiajun’ where id=2

update user set username=’jiajun’ where id=2

update user set username=’jiajun’ where id=1

上面就是死锁的列子

乐观锁与悲观锁

乐观锁和悲观锁是一种思想，不是上面提到的数据库提供的锁，是并发控制的一种手段

有这样的场景，两个事务对商品(id=666)进行下单

乐观锁

有一个statu字段，未下单为0，下单为1

先查statu字段，下单进行更新， 再查询一次statu看是否与之前查的一样，一样的话commit，不一样的话rollback

实现方式：加一个版本号或者时间戳，提交前进行一次比较

悲观锁

直接对id为666的行加排他锁

实现方式：通过数据库的锁机制进行加锁

对比

悲观锁比较谨慎，采用加锁机制，一定程度下降低了并发性

乐观锁认为一般情况下不会发生冲突，并发性较好

从冲突量和重试代价来衡量，如果冲突很少，或者重试代价不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性，而且更容易实现。但是，如果冲突的代价严重，那么就需要使用悲观策略

两段锁原则

两段锁

是一个加锁原则，一种协议

锁操作分为两个阶段，加锁阶段只加锁不放锁，解锁阶段只放锁不加锁

比如一个事务中两个update语句，此时执行完第一个update不会立刻放锁，等加锁阶段完成后再进行放锁，也就是加锁和解锁不相交

事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。

我觉得分享是一种精神，分享是我的乐趣所在，不是说我觉得我讲得一定是对的，我讲得可能很多是不对的，但是我希望我讲的东西是我人生的体验和思考，是给很多人反思，也许给你一秒钟、半秒钟，哪怕说一句话有点道理，引发自己内心的感触，这就是我最大的价值。(这是我喜欢的一句话，也是我写博客的初衷)