【!MyISAM不支持行级锁】
4.1 锁的分类
- 全局锁
- 表级锁
- 表锁
- 元数据锁
- 意向锁
- AUTO-INC锁
- 行级锁
- Record Lock
- Gap Lock
- Next-Key Lock
- 插入意向锁
4.2 MySQL是怎么加锁的?
4.2.1 什么SQL语句会加行级锁
普通的select语句是不会对记录加锁的(除了串行化隔离级别),因为都属于快照读,是通过MVCC实现的。
如果要在查询记录时加行级锁,可以使用:
# S型锁
SELECT ... LOCK IN SHARE MODE;
# X型锁
SELECT ... FOR UPDATE;
上面这两句必须在一个事务中,因为当事务提交了,锁就会释放,所以使用这两条语句时,要加上begin或者start transaction。
除上面这两句锁定读语句会加行级锁之外,update和delete操作都会加行级锁,并且所得类型都是独占锁(X型)。
UPDATE TABLE ... WHERE ...;
DELETE FROM TABLE WHERE ...
共享锁之间读读共享,读写互斥。独占锁满足写写互斥,读写互斥。
4.2.2 行级锁有哪些种类
在读已提交隔离级别下,行级锁的种类只有记录锁。
在可重复读隔离级别下,行级锁除了有记录锁,还有间隙锁(避免幻读)、临键锁。
- Record Lock,记录锁,仅对一条记录上锁;
- Gap Lock,间隙锁,锁定一个范围,但是不包括记录本身;
- Next-Key Lock,Record Lock + Gap Lock,锁定一恶搞范围,并且锁定记录本身。
4.2.2.1 Record Lock
记录锁有X锁和S锁之分。
4.2.2.2 Gap Lock
只存在于可重复度隔离级别,目的是为了解决可重复度隔离级别下幻读的现象。
间隙锁存在X锁和S锁之分,但是没有什么区别,间隙锁之间是兼容的,即两个事务可以同时包含共同间隙范围的间隙锁,并不存在互斥关系,因为间隙锁的目的是防止插入幻影记录而提出的。
4.2.2.3 Next-Key Lock
临键锁,锁定一个范围,并且锁定记录本身。
临键锁也有X锁和S锁之分,由于记录锁之间的X和S锁互不兼容,所以当一个事务获取了X型的临键锁时,另一个事务在获取相同范围的X型临键锁时就会被阻塞。
4.2.3 MySQL时怎么加行级锁的
加锁的对象是索引,加锁的基本单位是next-key lock,是一个前开后闭的开区间。
但是临键锁在一定场景下会退化为记录锁或者间隙锁。(在仅使用记录锁或者间隙锁就可以避免幻读的场景下,就会退化)。
使用唯一索引进行等值查询时,「存在」退化为记录锁,「不存在」找到第一条大于该记录的记录后,退化为间隙锁。
使用唯一索引进行范围查询时,会对每一个扫描到的索引加next-key锁,
- 当「大于等于」范围查询时,如果等值查询的记录存在表中,那么该记录的索引中的next-key锁会退化为记录锁。
- 当「小于或小于等于」时,如果条件值的记录不存在表中,扫描到终止范围查询的记录时,该记录的临键锁退化为间隙锁,其他记录都是加临键锁。如果条件值的记录在表中,针对「小于」终止范围的记录的临键锁会退化为间隙锁,而「小于等于」不会退化。
使用非唯一索引等值查询
因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有满足查询条件的记录才会对它们的主键索引加锁。
- 当查询记录「存在」时,由于不是唯一索引,所以肯定存在索引值相同的记录,于是非唯一索引等值查询的过程是一个扫描的过程,直到扫描到第一个不符合二级索引记录就停止扫描,然后在扫描过程中,对扫描到的二级索引加的是临键锁,对于第一个不符合条件的二级索引记录,会退化为间隙锁,同时在符合查询条件的记录的主键索引上加上记录锁。
- 当查询记录「不存在」时,扫描到第一条不符合条件的二级索引记录时,该二级索引的临键锁会退化为间隙锁,引文i不存在满足查询条件的记录,所以不会对主键索引加锁。
使用非唯一索引范围查询:非唯一索引范围查询,索引的 next-key lock 不会有退化为间隙锁和记录锁的情况。
没有加索引的查询:
- 如果锁定读查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞。
- 在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。
4.3 update没加索引会锁全表吗?
在 update 语句的 where 条件没有使用索引,就会全表扫描,于是就会对所有记录加上 next-key 锁(记录锁 + 间隙锁),相当于把整个表锁住了。
我们可以将 MySQL 里的 sql_safe_updates
参数设置为 1,开启安全更新模式。
update 语句必须满足如下条件之一才能执行成功:
- 使用 where,并且 where 条件中必须有索引列;
- 使用 limit;
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
delete 语句必须满足以下条件能执行成功:
- 同时使用 where 和 limit,此时 where 条件中可以没有索引列;
如果 where 条件带上了索引列,但是优化器最终扫描选择的是全表,而不是索引的话,我们可以使用 force index([index_name])
可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。
4.4 MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗?
可以