【MySQL】锁

YFR718
35 阅读9分钟

锁

锁的分类

全局锁

-- 使用全局锁
flush tables with read lock
-- 释放全局锁，会话断开也会释放
unlock tables

整个数据库处于只读状态，以下操作都会被阻塞：

  • 对数据的增删改操作： insert、delete、update等语句；

  • 对表结构的更改操作： alter table、drop table 等语句。

应用场景

  • 全库逻辑备份

    • 避免全局锁：可重复读的隔离级别，备份前开启事务

      • 使用 mysqldump 时加上 –single-transaction 参数

表级锁

表锁

--表级别的共享锁，也就是读锁；
lock tables t_student read;
--表级别的独占锁，也就是写锁；
lock tables t_stuent write;
--释放锁，会话退出后，也会释放所有表锁。
unlock tables

  • 应避免在InnoDB 引擎使用表锁，锁粒度太大，影响并发性能

元数据锁

Why？

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

无需使用 MDL，对数据库表进行操作时自动加MDL锁，事务提交后才释放，事务执行期间MDL 是一直持有的。

  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；

  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

？？？是公平锁还是写优先？😡

使用的是读写公平锁，写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。：

  • 有长事务时，表修改操作会阻塞，后续读写操作也会阻塞

    • 这时数据库的线程很快就会爆满了。

  • 修改表结构时应该先Kill掉长事务。

意向锁

Why？

加表锁时，要遍历每一行看有没有锁，效率低

How？

加行锁时，同时加一个表级别的意向锁，相当于打了标签。意向锁的目的是为了快速判断表里是否有记录被加锁。

  • 加「行共享锁」之前，先在表级别加上一个「意向共享锁」；

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

  • 加「行独占锁」之前，先在表级别加上一个「意向独占锁」；

    • 普通的 select 是不会加行级锁的，利用 MVCC 实现一致性读，无锁的。下面是特殊的select：
    • --先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
-- 先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;
    • 上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

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

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

AUTO-INC锁

Why？

AUTO_INCREMENT 字段如何实现无冲突的递增？

How？

插入数据时，会加一个表级别的 AUTO-INC 锁，再执行完插入语句后就会立即释放。

  • AUTO-INC 锁对大量数据进行插入时性能低，其他事务中的插入会被阻塞。

    • MySQL 5.1.22 版本轻量级的锁：插入数据的前被 AUTO_INCREMENT 修饰的字段加上轻量级锁，给该字段赋值一个自增的值，就把这个轻量级锁释放了，不需要等待整个插入语句执行完后才释放锁。当搭配 binlog 的日志格式是 statement ，在「主从复制的场景」中会发生数据不一致的问题，binlog_format = row才可以。

    • innodb_autoinc_lock_mode 系统变量：

      • 0：采用 AUTO-INC 锁，语句执行结束后才释放锁；

      • 2：采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

      • 1：

        • 普通 insert 语句，自增锁在申请之后就马上释放；
        • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

行级锁

InnoDB 引擎支持行级锁，MyISAM 引擎不支持行级锁。

读已提交，行级锁的种类只有记录锁。

可重复读行级锁有三类：

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

Record Lock

记录锁，锁住的是一条记录。有 S 锁和 X 锁之分。

begin;
-- 对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁
select * from t_test where id = 1 for update;
commit；

Gap Lock

Gap Lock间隙锁：锁定一个范围，但是不包含记录本身（a,b），可重复读隔离级别，为了解决可重复读隔离级别下幻读的现象。

  • 间隙锁存在 X 型、S 型间隙锁，并没有什么区别，间隙锁之间是兼容的，两个事务可以同时持有包含共同间隙范围的间隙锁，为防止插入幻影记录。

Next-Key Lock

Next-Key Lock临键锁：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身,(a,b]。

  • next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

  • 如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

    • 记录锁是冲突的。

插入意向锁

一个事务在插入一条记录的时候，插入位置已被加间隙锁，会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态，阻塞直到释放间隙锁。

  • 插入意向锁不是意向锁，一种特殊的间隙锁，属于行级别锁。

  • 间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点

  • 「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，拥有向交的间隙锁和该间隙区间内的插入意向锁

加锁过程

什么 SQL 语句会加行级锁？

  • 普通select：不加，MVCC快照读

    • 串行化隔离级别：会加锁

  • 锁定读

    • S锁：select ... lock in share mode;
    • X锁：select ... for update;

  • update 和 delete：X锁

插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置的下一条记录的索引上有间隙锁，才会发生阻塞。

MySQL 是怎么加行级锁的？

  • 加锁的对象是索引，加锁的基本单位是 next-key lock

  • next-key lock 在一些场景下会退化成记录锁或间隙锁。

    • 在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成退化成记录锁或间隙锁。

查看加锁

-- 查看事务执行 SQL 过程中加了什么锁
select * from performance_schema.data_locks\G;

  • LOCK_TYPE：

    • TABLE：表级
    • RECORD：行级

  • LOCK_MODE：

    • IX：意向锁

    • X： next-key 锁；

    • X, REC_NOT_GAP：记录锁；

    • X, GAP：间隙锁；

唯一索引等值查询

  • 查询的记录「存在」：退化成「记录锁」。

    • 只需要避免这一条记录被删除即可

  • 查询的记录「不存在」：在索引树找到第一条大于该查询记录的记录，退化成「间隙锁」。

    • 锁范围是（a,b），a第一个小于要查询的值，b第一个大于的值

唯一索引范围查询

  • ：(a,b]对后面每一个间隙都加锁

  • =：等值为记录锁，右边其他的还是Next-Key Lock

  • <、<=：要看条件值的记录是否存在于表中：

    • 记录不在表中：左边的是Next-Key Lock， 扫描到终止范围查询的下一个记录时，退化成间隙锁。

    • 在表中：

      • <：扫描到终止范围查询的记录时，退化成间隙锁，其他加 next-key 锁；

      • <=：都是 Next-Key Lock

非唯一索引等值查询

存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

  • 记录存在：扫描直到扫描到第一个不符合条件的二级索引记录就停止扫描，二级索引记录加 next-key 锁，对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁在符合查询条件的记录的主键索引上加记录锁。

  • 记录不存在：扫描到第一条不符合条件的二级索引记录，加间隙锁。不会对主键索引加锁。

非唯一索引范围查询

非唯一索引范围查询，索引的 next-key lock 不会有退化为间隙锁和记录锁的情况

没有加索引的查询

一条语句干翻数据库。

定读查询语句、update、delete语句 where 条件没有使用索引，就会全表扫描，对所有记录加上 next-key lock，相当于把整个表锁住了。在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了

where 带上索引就能避免全表加锁？

不是的，在执行过程中，优化器最终选择的是索引扫描才可以。

避免这种事故的发生？

当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功：

  • 使用 where，并且 where 条件中必须有索引列；

  • 使用 limit；

  • 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

  • 同时使用 where 和 limit，where 条件中可以没有索引列；

  • 如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引，使用 force index([index_name]) 指定索引，以此避免有几率锁全表带来的隐患。

主键索引、非主键加锁的流程图：

avatar
文章
阅读
粉丝