MySQL锁介绍

转载: https://mp.weixin.qq.com/s/yhyr6M0hVEgQPyER0XyXuQ

MySQL都有哪些锁呢? - 小林coding的回答 - 知乎 https://www.zhihu.com/question/422489306/answer/2062958661

在MySQL 里,根据加锁的范围,可以分为 全局锁表级锁行锁 三类。

MySQL锁介绍

 

 

全局锁

要使用全局锁,则要执行这条命令:

 

flush tables with read lock

 

执行后,整个数据库就处于只读状态了,这时其他线程执行一下操作,都会被阻塞:

  对数据的增删改操作,比如 insert、delete、update 等语句;

  对表结构的更改操作,比如 alter table、 drop table 等语句。

如果要释放全局锁,则要执行这条命令:

unlock tables

当然,会话断开了,全局锁会被自动释放。

全局锁住要应用于做 全库逻辑备份, 这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据和预期的不一样。

例如多表关联的业务,备份数据中的可能部分表数据是修改后的,导致数据发生错乱。

加上全局锁,意味着整个数据库都是只读状态,会造成业务停滞,如何解决呢?

  如果数据库的引擎支持的事务支持可重复读的隔离级别,那么在备份数据库之前开启事务,会创建Read View, 然后整个事务执行期间都在用这个 Read View, 而且由于 MVCC 的支持,备份期间业务仍然可以对数据库进行更新操作。

  因为在可重复读的隔离级别下,即使其他食物更新了 表数据, 也不会影响备份数据库时的 Read View,这样备份期间备份的数据一直是在开启事务时的数据。

  备份数据库的工具时 mysqldump, 在使用mysqldump 时加上 -single-transaction 参数的时候,就会在备份数据库之前开启事务,这回总给你发那个发只适用于支持 可重复读隔离级别事务 的存储引擎。

  InnoDB 支持可重复读,可以采用此方法。  MyISAM 不支持事务,所以备份数据需要使用全局锁。

 

表级锁

  表锁 ; 元数据锁(MDL); 意向锁; AUTO-INC 锁;

  表锁:表级别的共享锁,也就是读锁; 表级别的独占锁(排它锁), 也就是写锁

// 表级别的共享锁,也就是 读锁
lock tables t_student read;

//表级别的独占锁, 也就是写锁
lock tables t_student write;

   需要注意的是,表锁出了会限制别的线程的读写外,也会限制本线程接下来的读写操作。

也就是说如果本线程对表加了「共享表锁」,那么本线程接下来如果要对学生表执行写操作的语句,是会被阻塞的,当然其他线程对表的写操作也会被阻塞,直到锁被释放。

要释放表锁,可以使用下面这条命令,释放当前会话的所有表锁:

unlock tables

另外,当前会话推出后,也会释放所有表锁。

不过尽量避免在 InnoDB 引擎中使用表锁,因为表锁的颗粒度太大,会影响并发性能, InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁(MDL)

我们不需要显示的使用MDL, 当我们进行数据库表操作时,会自动给这个表加上MDL:

  对一张表进行CRUD 操作时,加的是 MDL读锁;

  对一张表做结构变更操作时,加的是 MDL 写锁。

MDL 是为了保证当用户对表进行CRUD 操作时,防止其他线程对这个表结构做了变更。

当有线程对表结构进行变更(加 MDL 写锁) 的期间,如果有其他线程执行了CRUD操作(申请 mDL读锁), 那么就会被阻塞,直到表结构完成变更(释放MDL 写锁)

MDL 是在事务提交后才会释放,这意味着事务执行期间, MDL 是一直持有的。

作者:小林coding
链接:https://www.zhihu.com/question/422489306/answer/2062958661
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

那如果数据库有一个长事务(所谓的长事务,就是开启了事务,但是一直还没提交),那在对表结构做变更操作的时候,可能会发生意想不到的事情,比如下面这个顺序的场景:

  1. 首先,线程 A 先启用了事务(但是一直不提交),然后执行一条 select 语句,此时就先对该表加上 MDL 读锁;
  2. 然后,线程 B 也执行了同样的 select 语句,此时并不会阻塞,因为「读读」并不冲突;
  3. 接着,线程 C 修改了表字段,此时由于线程 A 的事务并没有提交,也就是 MDL 读锁还在占用着,这时线程 C 就无法申请到 MDL 写锁,就会被阻塞,

那么在线程 C 阻塞后,后续有对该表的 select 语句,就都会被阻塞,如果此时有大量该表的 select 语句的请求到来,就会有大量的线程被阻塞住,这时数据库的线程很快就会爆满了。

为什么线程 C 因为申请不到 MDL 写锁,而导致后续的申请读锁的查询操作也会被阻塞?

这是因为申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁,一旦出现 MDL 写锁等待,会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。

 

意向锁 

在使用 InnoDB 引擎的表里对某些记录加上 「共享锁」 之前,需要先在表级别加上一个 「意向共享锁」;

在使用 InnoDB 引擎的表里对某些记录加上「独占锁」之前,需要先在表级别加上一个「意向独占锁」

也就是,当执行插入、更新、删除操作,需要先对表加上「意向独占锁」,然后对该记录加独占锁。

而普通的select 是不会加行级锁的, 普通的select 语句是利用 MVCC 实现一致性读,是无锁的。

不过,select 也是可以对记录加共享和独占锁的,具体方式如下:

// 先在表上加上意向共享锁, 然后对读取的记录加独占锁
select ... lock in share mode;
// 8.0 后增加 for share, 作用同上
select ... for share; // 现在表上加上意向独占锁, 然后对读区的记录加独占锁 select ... for update;

意向共享锁 和 意向独占锁 是表级锁, 不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突, 只会和 共享表锁(lock tables ... read) 和 独占表锁 (lock tables ... write) 发生冲突。

表锁和行锁是满足 读读共享、读写互斥、 写写互斥的。

如果没有「意向锁」,那么加「独占表锁」时,就需要遍历表里所有记录,查看是否有记录存在独占锁,这样效率会很慢。

那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。

所以,意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁    在为某个字段声明 AUTO_INCREMENT 属性时, 之后可以在插入数据时, 可以不置顶该字段的值,数据库会自动给该字段赋值递增的值, 这主要是通过 AUTO-INC 锁实现的。    AUTO-INC 锁是特殊的表锁机制, 锁不是在一个事务提交后才释放,而是执行完插入语句后就会立即释放。   在插入语句时, 会加一个表级别的 AUTO-INC 锁, 然后为被 AUTO-INCREMENT 修饰的字段赋值递增的值, 等插入语句执行完成后, 才会把 AUTO-INC 锁释放掉。   那么,一个事务持有 AUTO-INC 锁的过程中, 其他事务的如果要向该表插入语句都会被阻塞, 从而保证插入数据时, 被 AUTO_INCREMENT 修饰的字段的值是连续递增的。   但是, AUTO-INC 锁再对大量数据进行插入的时候, 会影响插入性能, 因为另一个事务中的插入会被阻塞。   因此, MySQL 5.1.22 版本开始,InnoDB 存储引擎提供了一种轻量级的锁来实现自增。   一样也是在插入数据的时候, 会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁, 然后给该字段赋值一个自增的值,就把这个轻量级锁释放了,而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量,是用来控制选择用 AUTO-INC 锁,还是轻量级的锁。

  • 当 innodb_autoinc_lock_mode = 0,就采用 AUTO-INC 锁;
  • 当 innodb_autoinc_lock_mode = 2,就采用轻量级锁;
  • 当 innodb_autoinc_lock_mode = 1,这个是默认值,两种锁混着用,如果能够确定插入记录的数量就采用轻量级锁,不确定时就采用 AUTO-INC 锁。

不过,当 innodb_autoinc_lock_mode = 2 是性能最高的方式,但是会带来一定的问题。因为并发插入的存在,在每次插入时,自增长的值可能不是连续的,这在有主从复制的场景中是不安全的

 

行级锁

InnoDB 引擎是支持行级锁的, MyISAM  引擎不支持行级锁。  Record Lock , 记录锁, 也就是仅仅把一条记录锁上;  Gap Lock, 间隙锁, 锁定一个范围, 但是不包含记录本身;  Next-Key Lock : Record Lock + Gap Lock 的组合,锁定一个范围, 并且锁定记录本身。  Insert Intention Locks: 插入意向锁 , 一种特殊的 gap lock.INSERT操作插入成功后,会在新插入的行上设置index record lock,但,在插入行之前,INSERT操作会首先在索引记录之间的间隙上设置insert intention lock,该锁的范围是(插入值, 向下的一个索引值)。有shard或exclusive两种模式,但,两种模式没有任何区别,二者等价。

  insert intention lock发出按此方式进行插入的意图:多个事务向同一个index gap并发进行插入时,多个事务无需相互等待。

  假设已存在值为4和7的索引记录,事务T1和T2各自尝试插入索引值5和6,在得到被插入行上的index record lock前,俩事务都首先设置insert intention lock,于是,T1 insert intention lock (5, 7),T2 insert intention lock (6, 7),尽管这两个insert intention lock重叠了,T1和T2并不互相阻塞。

  如果gap lock或next-key lock 与 insert intention lock 的范围重叠了,则gap lock或next-key lock会阻塞insert intention lock。隔离级别为RR时正是利用此特性来解决phantom row问题;尽管insert intention lock也是一种特殊的gap lock,但它和普通的gap lock不同,insert intention lock相互不会阻塞,这极大的提供了插入时的并发性。总结如下:

1. gap lock会阻塞insert intention lock。事实上,gap lock的存在只是为了阻塞insert intention lock 

2. gap lock相互不会阻塞

3. insert intention lock相互不会阻塞

4. insert intention lock也不会阻塞gap lock

  前面也提到,普通的 select 语句是不会对记录加锁的, 如果在查询时对记录加行锁, 可以使用下面这两个方式:
// 对读区的记录加共享锁
select ... lock in share mode;
// 8.0 后增加 for share, 作用同上
select ... for share;
// 对读区的记录加独占锁
select ... for update;

上面这两条语句必须在事务中, 当事务提交了,锁就会被释放, 因此在使用这两条语句的时候, 要加上begin、start transaction 或者 set autocommit = 0

 

MySQL5.7及之前,可以通过information_schema.innodb_locks查看事务的锁情况,但,只能看到阻塞事务的锁;如果事务并未被阻塞,则在该表中看不到该事务的锁情况。

 

MySQL8.0删除了information_schema.innodb_locks,添加了performance_schema.data_locks,可以通过performance_schema.data_locks查看事务的锁情况,和MySQL5.7及之前不同,performance_schema.data_locks不但可以看到阻塞该事务的锁,还可以看到该事务所持有的锁,也就是说即使事务并未被阻塞,依然可以看到事务所持有的锁(不过,正如文中最后一段所说,performance_schema.data_locks并不总是能看到全部的锁)。表名的变化其实还反映了8.0的performance_schema.data_locks更为通用了,即使你使用InnoDB之外的存储引擎,你依然可以从performance_schema.data_locks看到事务的锁情况。

performance_schema.data_locks的列LOCK_MODE表明了锁的类型,下面在介绍各种锁时,我们同时指出锁的LOCK_MODE。

 

上一篇:SpringBoot整合Redis


下一篇:复试准备数据库第3天