MySQL锁详解

1 按性能划分

1.1 乐观锁

使用版本比对实现

update table set ... where version=1

1.2 悲观锁

不管三七二十一，操作前先加锁

2 按数据库操作类型划分

读锁和写锁均属于悲观锁

2.1 读锁

• 共享锁，S锁（Share）
• 针对同一份数据，多个读操作可以同时进行而不会受影响

2.2 写锁

• 排它锁，X锁(eXclusive)
• 当前写操作未完成前，会阻断其他写锁和读锁

2.3 意向锁

意向锁是表级锁

表明事务稍后对表中的行或者更细粒度的对象请求某种锁

意向锁帮助InnoDB快速检测锁冲突

2.3.1 意向共享锁（IS）

• 表示事务将在某些行上设置共享锁

• 当事务想要给表中某些行加S锁时
• 会首先在表级别获取一个IS锁
• IS并不直接阻止其他事务访问该表中的数据以及表级别的S锁
• 但其阻止其他事务获取表级的X锁

2.3.2 意向排它锁（IX）

• 表示事务将在某些行上设置排它锁

• 当事务想要修改（更新，删除，插入）表中某些行时
• 会首先在表级别获取一个IX锁
• IX并不直接阻止其他事务访问该表中的数据
• 但其阻止其他事务获取表级的S锁或者X锁

3 按数据库操作粒度划分

3.1 表锁

• 每次操作锁住整张表
• 开销小，加锁快
• 锁粒度大，发生锁冲突的概率高
• 并发度最低

• 加写锁

lock table aaa write;

• 加读锁

lock table bbb read;

• 查看表锁

SHOW OPEN TABLES WHERE In_use > 0;

• 解除当前session的所有表锁

unlock tables;

注意：

• 当同一session内多次执行lock table命令，会先释放前一次锁再进行加锁
• 读锁会阻塞写操作，不会阻塞读操作
• 写锁会把读和写操作都阻塞
• session内加了表锁之后，只能操作被加锁的表

• MyISAM和InnoDB均支持表锁
• MyISAM执行select前，会自动给表加读锁
• InnoDB设置隔离级别为串行化时，select前也会自动给表加读锁
• InnoDB设置隔离级别为其他时，select时会给所查询数据加行锁

3.2 行锁

• 每次操作锁住一行数据
• 开销大，加锁慢
• 锁粒度最小，发生锁冲突的概率最低
• 并发度最高
• 仅InnoDB支持行锁

• 加S锁

• 查看S锁

• update加X锁

• 查看X锁

• insert加X锁

• 查看X锁，此时仅有意向排他锁

• 另外开一个session给id为5的行加S锁时

• 然后再次查看X锁，行锁才会显示出来（有点奇怪，应该insert之后就有的）

• delete加X锁

• 查看X锁

• select加X锁

• 查看X锁

• 查看行锁争夺情况

show status like 'innodb_row_lock%';

字段	含义
Innodb_row_lock_current_waits	当前等待锁的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待锁花的平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数

• 分析行锁的系统表

performance_schema.data_locks
performance_schema.data_lock_waits

• 查看死锁以及锁等待的详细信息

show engine innodb status\G

3.3 间隙锁

• 隔离级别为可重复读时一个范围锁
• 可以解决幻读的问题

• 间隙锁分析

操作	事务A	事务B
设置隔离级别并开启事务
事务A加X锁	此时查询的范围是 3 < id < 18 但是加锁的范围为 3~20
事务B尝试插入id为4的数据		由于3~20间隙锁存在，超时报错
事务B插入id为19的数据		由于3~20间隙锁存在，超时报错
事务B插入id为22的数据		无22的间隙锁，插入成功

• 查看间隙锁

3.4 临键锁

• 间隙锁和行锁的组合
• 当mysql增加间隙锁时，发现间隙范围内存在数据，则会给每行数据加上一个行锁