节
春
度
欢

MySQL锁

MySQL锁

目录

按照锁思想分类

乐观锁

需要应用程序实现，MySQL自身并未实现。

悲观锁

按照锁类型分类

读锁（共享锁、S锁）

  1. 加了读锁的事务，允许该事务进行读，不允许其他事务进行修改
  2. 同一行数据，可以被多个事务获得读锁
  3. 其他事务不能再获取写锁，只能等获得读锁的所有事务释放后，才能加写锁

写锁（排他锁、X锁）

  1. 一个事务获取了写锁，那么该事务可以进行数据的查看及修改
  2. 在该事务提交（释放锁）之前，不允许其他事务对该数据进行读操作或者写操作，会被阻塞住
  3. 即添加了写锁之后，只允许拥有该锁的事务进行读、写

意向锁

  1. 意向锁的主要用途是显示某人正在锁定一行，或者将要锁定表中的一行
  2. 想获取读锁或者写锁，必须先获取意向锁
  3. 申请意向锁的动作是数据库自动完成的

意向共享锁（IS Lock）

意向排他锁（IX Lock）

按照锁级别分类

全局锁（数据库级别）

FTWRL主要包括3个步骤：

  1. 上全局读锁(lock_global_read_lock)
  2. 清理表缓存(close_cached_tables)
  3. 上全局COMMIT锁(make_global_read_lock_block_commit)

注意点

  1. 上全局读锁会导致所有更新操作都会被堵塞；关闭表过程中，如果有大查询导致关闭表等待，那么所有访问这个表的查询和更新都需要等待；

  2. 上全局COMMIT锁时，会堵塞活跃事务提交。

示例

// 加锁：flush tables with read lock
// 执行后整个数据库将处于只读状态
// 解锁： unlock tables

表级锁

说明

表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的。

ps: 仅当autocommit=0、InnoDB_table_locks = 1（默认设置）时，InnoDB层才能知道MySQL加的表锁，

MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。

在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；
事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；
COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

意向锁

  1. 意向锁的主要用途是显示某人正在锁定一行，或者将要锁定表中的一行
  2. 想获取读锁或者写锁，必须先获取意向锁
  3. 申请意向锁的动作是数据库自动完成的

自增锁

Innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡。

该变量有三个可选值：0、1、2。

传统模式（0）

  1. 所有的insert 都会获得一个自增锁

  2. insert执行结束后就立即释放自增锁，不必等待事务结束

连续模式（1）

  1. 像执行INSERT INTO这样的语句时，可以提前知道要插入的数量

  2. 使用mutex轻量级锁，分配完毕就释放

交叉模式（2）
  1. 执行 INSERT 和INSERT INTO ... SELECT无法预知插入的数量的情况
  2. 使用较为轻量的 mutex 锁
  3. 主从复制时， binlog必须为Row模式，如果是statement模式的话，可能会出现数据不一致【待考证，有另一种说法见深入剖析 MySQL 自增锁评论区】
注意点

5.1.22版本及之前， 默认为 --> 传统模式

MySQL 8.0 之前，InnoDB 锁模式默认 --> 连续模式

MySQL 8.0 之后，默认模式 --> 交叉模式

扩展（MyISAM表锁）

MyISAM存储引擎只支持表锁，同时MyISAM不会出现死锁（下面会解释）。

查询MyISAM表级锁争用情况

通过show status like 'table%';， 查看table_locks_waited、table_locks_immediate状态变量，若table_locks_waited变量的值较高，则说明存在较严重的表级锁争抢情况。

DeadLock Free

MyISAM在执行lock tables时，必须同时取得所有涉及表的表锁，并且MySQL不支持锁升级；所以MyISAM中不会出现死锁。

MyISAM并发插入

MyISAM表不支持MVCC。

MyISAM表的读操作与写操作，以及写操作之间是串行的。

concurrent_insert变量。

当concurrent_insert=0时，不允许并发插入功能。

当concurrent_insert=1时，允许对没有空洞的表使用并发插入，新数据位于数据文件结尾(默认)。

当concurrent_insert=2时，不管表有没有空洞，都允许在数据文件结尾并发插入。

空洞：表的中间有被删除的行，空出来的那一行。

其他

MyISAM表的读操作与写操作，以及写操作之间是串行的。

并且默认情况下写操作命令的执行优先于读操作执行，即使读请求早于写请求到达，写锁也会插队到读请求前面，因为MySQL认为写请求一般比读请求要重要。

这也导致了有大量的并发更新操作时，读请求很有可能被阻塞。

可以通过这些方法解决：

  1. 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

  2. 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

  3. 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

行锁

InnoDB行锁是通过给索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

排他锁、共享锁

在获取排他锁之前，InnoDB会自动申请该表的意向排他锁，不需要用户手动处理。
对于update、delete和insert语句，InnoDB会自动给涉及到的数据行加排他锁；
对于普通的select语句，InnoDB不会加任何锁。
事务可以通过以下语句显式给数据行加共享锁或排他锁。

MySQL5.7

共享锁：select * from table_name where ... lock in share mode
排他锁：select * from table_name where ... for update

MySQL8.0

共享锁：select * from table_name where ... for share
排他锁：select * from table_name where ... for update [nowait | skip locked]

MySQL8.0向下兼容，支持MySQL5.7的语法。
MySQL5.7中的排他锁，如果遇到锁等待，那么session默认会等待50s，在高并发系统中，一旦出现对热点行的争用，
将会造成连接数的快速增加，甚至超过最大连接数。
所以在MySQL8.0中，增加了两个可选参数nowait、skip locked。

nowait: 发现有锁等待后，会立即返回错误，不用等到锁超时后报错
skip locked: 跳过被锁定的行，直接更新其他行，这样可能会造成更新结果不符合预期

InnoDB行锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB这种行锁的实现特点导致了， 如果在加锁的时候不通过索引条件查询数据，那么InnoDB将对表中所有的记录加行锁，实际效果和表锁一样。

InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是针对记录加的锁，所以虽然是访问不同行的记录，如果索引项的值是相同的话，也是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来给数据加锁。

Record Lock（记录锁）

数据行上加锁。

Gap Lock（间隙锁）、

  1. 只有在可重复读、串行化隔离级别才有该锁
  2. 锁定范围空间的数据（范围条件、相等条件查询不存在的记录时）

举例：

假设id有3,4,5，锁定id>3的数据，是指的4，5及后面的数字都会被锁定

对于没有索引的列，当前读操作时，会加全表间隙锁。

非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁

举例：

某表数据如下，非唯一索引2,6,9,11,15。
执行delete from table where t_id = 9;
gap锁将会锁定的列是(6,11]，该区间内无法插入数据。

Next Key Lock（临键锁）

Record Lock + Gap Lock

Insert Intention Lock（插入意向锁）

是间隙锁的一种。

插入意向锁不会阻止任何锁，对于插入的记录会持有一个Record Lock

假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，
获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

页锁

比表锁并发高，比行锁并发低。

BDB存储引擎支持，5.1版本之后就被弃用。

按照加锁方式分类

自动加锁（隐式加锁）

DML语句

自动获得所需要的表级锁和行级锁

DDL语句

排他锁

创建、修改、删除一个数据库对象的 DDL 语句获得操作对象的 排它锁

共享锁

需在数据库对象之间建立相互依赖关系的 DDL 语句通常需共享获得 DDL锁

显式加锁

LOCK TABLES
LOCK TABLES  tbl_name  read|write, tbl_name read|write, ...  # 手动加表锁

UNLOCK TABLES  # 解开该事务全部的锁，后面不跟表名，必须在事务提交之后再执行，此操作会隐式的提交事务
FLUSH TABLES
FLUSH TABLES tbl_name,... [WITH READ LOCK];

Two-Phase Locking(2PL，两阶段锁定)

加锁阶段

事务开始时处于加锁阶段，此时只允许加锁，不允许解锁；并且所有加锁操作必须在解锁操作之前。

解锁阶段

事务commit或者rollback时，进入解锁阶段，此阶段只允许解锁，不允许加锁。

分类

Basic 2PL(基本2PL): 在事务过程中，分为获得锁和释放锁两个阶段

Strict 2PL(严格2PL): 直到事务结束为止，都不释放获得的锁

Static 2PL(静态2PL): 在事务开始前，获得所需的全部锁

总结

  1. 加锁和解锁操作不能交叉执行（同一个事务内）
  2. 加锁总是优先于解锁
  3. 2PL并不能够避免死锁，只是用来防止并发事务对数据的干扰

参考：数据库系统 · 事物并发控制 · Two-phase Lock Protocol

其他

MySQL 在开始某个事务的时候，会隐式提交上一个事务。所以 MySQL 本身是不支持事务嵌套的。

但 MySQL 也给我们提供了一个 SAVEPOINT 来做出类似事务嵌套的动作。

参考：MySQL事务嵌套

死锁

关于死锁

MyISAM是DeadLock Free的，因为它总是一次获取整个事务需要的资源，要么全部满足，要么等待。

在InnoDB中，除了单个SQL组成的事务外，锁是逐步获取的，所以会出现死锁。

解决

一般情况下，发生死锁后，InnoDB都能够检测到死锁，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

但在涉及外部锁或者涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这个时候，可以设置锁等待超时参数innodb_lock_wait_timeout，降低锁等待时间，尽快失败，避免高并发时数据库性能问题。

预防死锁

  • 合理划分事务大小
  • 在代码中理清表之间的资源关系，避免形成互相等待资源的局面
  • 在事务中，如果要更新记录，应直接申请足够级别的锁，而不是先申请共享锁，等到更新时再申请排他锁

wait-for graph（死锁检测机制）

死锁的必要条件

  1. 多个并发事务（2个或者以上）；
  2. 每个事务都持有锁（或者是已经在等待锁）；
  3. 每个事务都需要再继续持有锁（为了完成事务逻辑，还必须更新更多的行）；
  4. 事务之间产生加锁的循环等待，形成死锁。

wait-for graph 算法

InnoDB将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后行成一个有向图。

我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph 算法。

posted @ 2022-10-27 23:27  ws_hawk  阅读(63)  评论(0)  编辑  收藏  举报