mysql有那些锁
在mysql 里面有三类锁,他们锁的粒度是不通的,分为全局锁,表级锁和行级锁。
全局锁
全局锁的命令是:
flush table with read lock
执行完后,整个数据库就都处于只读状态了,这时其他现在一下的操作都会阻塞:
- 对数据的增删改操作,比如 insert、delete、update等语句;
- 对表结构的更改操作,比如 alter table、drop table 等语句。
那么我们该如何释放他呢?
unlock tables
使用场景:
全局锁主要应用于做全库逻辑备份,这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。
加全局锁会带来什么缺点?
如果数据库里有很多数据,备份就会花费很多的时间,关键是备份期间,业务只能读数据,而不能更新数据,这样会造成业务停滞。
如何避免呢
如果数据库引擎支持可重复读的隔离级别,那么在备份数据库之前先开启事务,会先创建read view,然后整个事务执行期间都在用这个read view,而且由于mvcc的支持,备份期间业务依然可以对数据进行更新操作。
表级锁
- 表锁:
# 表级别的共享锁,也就是读锁
lock tables table_name read;
# 表级别的独占锁(排他锁),也就是写锁
lock tables table_name write;
排他锁:也就是说一个事务对表加了写入锁后,其他事务不能读取也不能写入表,其它事务的读写操作都会被阻塞。
共享锁:一个事务对表加入了读取锁以后,其他事务可以读取,但是不能写入,这个保证了读取操作的并发性,但是写入操作会阻塞。
如何释放表锁:
unlock tables
- 元数据锁
元数据锁是由系统自动管理的,用户无法直接设置和释放,当你执行一个操作的时候,mysql也会自动获取必要的元数据锁,以确保数据的一致性和完整性。- 对一张表进行CURD的时候,mysql给数据加的是MDL的读锁
- 对一张表的表结构进行修改的时候,mysql加的是MDL的写锁
注意:
MDL是在事务提交之后才会释放,这意味着事务执行期间,MDL是被一直持有的状态。
如果MDL被一直持有不释放,那么当有人做表结构修改的时候,这时候做alter操作就会进入阻塞状态,写入操作阻塞以后,那么后续的CURL操作也会阻塞。这时候除非一开始的事务将mdl锁释放,不然后续就会造成死锁。
- 意向锁
- 在使用innodb引擎的表里对某些记录加上【共享锁】之前,需要在表级别加上一个【意向共享锁】;
- 在使用innodb引擎的表里对某些记录加上【独占锁】之前,需要在表级别加上一个【意向独占锁】;
//先在表上加上意向共享锁,然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向锁,然后对读取的记录加独占锁
select ... for update;
意向共享锁和意向独占锁是表级别的锁,不会和行级共享锁和独占锁发生冲突,而且意向锁之间不会发生冲突,只会和共享表锁的(共享锁和排他锁)发生冲突。
- auto-inc 锁
auto-inc锁是一个特殊的锁,是用于保护auto_incrment列的并发插入操作。
当一个事务尝试插入一行,并且需要生成一个新的auto_incrment的值的时候,innodb存储引擎会自动获取auto_inc锁。
auto-inc锁的主要目的是为了保证auto_incrment的值的连续性和唯一性。但是,这种锁机制可能会降低并发插入的性能。
innodb下对auto_inc锁提供了3种模式:
- 0/TRADITIONAL:默认模式,这种模式也是最安全的模式,innodb会在插入语句开始的时候获取aoto_inc锁,并在插入完成后释放。这是为了保证auto_incrment的连续性。
- 1/CONSECUTIVE:这种模式下,innodb只在生成auto_incrment值的时候短暂的获取auto_inc锁,然后立即释放,这可以保证插入的高并发性能,但可能会导致auto_incment的值的不连续性。(这个模式在插入的时候,只会获取主键自增)
- 2/INTERLEAVED:这是高性能模式,但也是最不安全的模式,这种模式下innodb不会获取auto_inc锁,而且允许每个事务自己生成auto_incrment的值。这也是进一步的提高了并发插入的能力。但会导致auto_incrment值的严重不连续或重复。
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode'; -- 查看当前值
SET GLOBAL innodb_autoinc_lock_mode = 1; -- 设置新值
行级锁
innodb引擎是支持行级锁的,MyISAM引擎是不支持行级锁的。
普通select是不会对记录加锁的,因为他属于快照读,如果我们想对查询记录加行锁,可以使用一下两种形式
// 对读取记录加共享锁
select ... lock in share mode;
// 对读取记录加排他锁
select ... for update;
共享锁(S锁)满足读读共享,读写互斥。独占锁(X锁)满足写写互斥、读写互斥。
共享锁(s):也被称为读锁,多个事务可以在同时在同一行数据加共享锁,它们可以读取该行数据,但不能修改。如果一个事务对一行数据加了共享锁,其他事务就不能对该行数据加排他锁
排他锁(x):也被称为写锁,如果一个事务对一行数据加了排他锁,其他事务不能对该行数据加任何锁,它既不能读也不能写。
Record Lock 记录锁
Record Lock被称为记录锁,锁住一条记录。
共享锁(s):当一个事务对一条记录加了共享锁,其他事务也可以继续对该条记录加共享锁,但是不可以对该记录加排他锁(X)。
排他锁(x):当一个事务对一条记录加了排他锁,其他事务即不可以加共享锁,也不可以加排他锁。
mysql > begin;
mysql > select * from t_test where id = 1 for update;
我们对当前数据加里排他锁,这时候我们就不可以对这条数据进行修改。
Gap Lock 间隙锁
间隙锁,只存在与可重复读的隔离级别,目的是为了解决可重复度隔离级别下的幻读。
间隙锁锁的是一个范围数据但不包括它本身,使用了间隙锁以后无法在该区间内插入数据。
表中有一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了,这样就有效的防止幻读现象的发生。
间隙锁存在共享锁和排他锁但是没有什么区别,间隙锁之间是兼容的,即两个事务可以同事持有包含共同间隙范围的间隙锁,并不互斥,因为间隙锁的目的是防止插入幻影记录而提出的。
SELECT * FROM table WHERE index BETWEEN 1 AND 4 FOR UPDATE;
Next-Key Lock 临键锁
Next-Key Lock是 Record lock (记录锁)+ Gap lock (间隙锁)的组合,,锁定一个范围,并且锁定记录本身。
假设,表中有一个范围 id 为(3,5] 的 next-key lock,那么其他事务即不能插入 id = 4 记录,也不能修改 id = 5 这条记录。
所以 next key lock (临键锁)既能保护记录,又能防止其他事务将记录插入到被保护记录前面的间隙中。
next key lock 是包含间隙锁+记录锁的,如果一个事务获取的是共享型(x)的next key lock,那么另外一个事务在获取相同范围的 共享型(x)的next key lock时,也会被阻塞。
插入意向锁:
一个事务在插入一条记录的时候,需要判断插入位置是否已经被其它事务加了间隙锁(next key lock)。
如果有,插入就会阻塞,直到拥有间隙锁的哪个事务提交为止(释放间隙锁的时刻),在此期间会生成一个插入意向锁,表明有事务想在某个区间插入新记录,但是现在处于等待状态。
举个例子,假设事务 A 已经对表加了一个范围 id 为(3,5)间隙锁。
当事务 A 还没提交的时候,事务 B 向该表插入一条 id = 4 的新记录,这时会判断到插入的位置已经被事务 A 加了间隙锁,于是事物 B 会生成一个插入意向锁,然后将锁的状态设置为等待状态,此时事务 B 就会发生阻塞,直到事务 A 提交了事务。
插入意向锁名字虽然有意向锁,但是它并不是意向锁,它是一种特殊的间隙锁,属于行级别锁。
间隙锁锁的是一个区间,那么插入意向锁,锁的就是一个点。因而从这个角度来说,插入意向锁确实是一种特殊的间隙锁。