MySQL中的各种锁

MySQL中的各种锁

MySQL的锁管理机制

相对其他数据库而言,MySQL 的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL锁管理机制

  • 元数据锁(MDL,MetaData Lock): 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。

    因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL读锁;当要对表做结构变更操作的时候,加 MDL 写锁

  • 存储引擎特有锁:InnoDB-行级锁、间隙锁;BDB-页级锁

  • 表级锁:

  • 全局读锁:对整个数据库实例加锁,命令是Flash tables with read lock(FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

各种锁解析

下面我们一起看一下MySQL中的各种锁。

元数据锁(MDL,Meta Data Lock)

元数据锁是一个读写锁,因此,可以多线程的对一张表,进行增删改查操作,因为它此时加的是MDL读锁;但当要对表做结构变更操作的时候,就会加上 MDL 写锁。

事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

MDL锁是没有超时时间的,因此,只要事务还没有提交,这个MDL锁就会一直锁着,在下面这种场景下就会x造成线程阻塞、业务中断。

1
2
3
4
5
6
7
8
9
10
11
12
13
1. sessionA:
begin;
select * from t limit 1;
 
2. sessionB:
select * from t limit 1;
 
3. sessionC:
alter table t add f int;
#会mdl锁住
 
4. sessionD:
select * from t limit 1;

如上例子,会话A申请了MDL读锁,但是没有提交;会话B也申请了读锁,正常执行(读锁不排他);这时候会话C想要修改表字段,那么它就要获取这个表的写锁。但是因为会话A的读锁还没有释放,因此会话C阻塞,从而也造成后面的会话D阻塞。

解决办法就是找到这个长时间未执行完的事务,提交或者回滚即可。

行级锁

  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    • 最大程度的支持并发,同时也带来了最大的锁开销。
      • 在 InnoDB 中,除单个 SQL 组成的事务外,
        锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。
      • 行级锁只在存储引擎层实现,而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统

InnoDB 实现了以下两种类型的行锁

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

锁模式的兼容情况:

锁模式的兼容状况

如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务; 反之, 如果两者不兼容,该事务就要等待锁释放。

InnoDB行锁实现方式

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的,这一点 MySQL 与 Oracle 不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁!
  • 不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引,才能使用行锁:即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL 认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时,
    别忘了检查 SQL 的执行计划(可以通过 explain 检查 SQL 的执行计划),以确认是否真正使用了索引。
  • 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然多个session是访问不同行的记录, 但是如果是使用相同的索引键, 是会出现锁冲突的(后使用这些索引的session需要等待先使用索引的session释放锁后,才能获取锁)。 应用设计的时候要注意这一点。

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

使用间隙锁的目的

使用间隙锁有以下目的:

  1. 防止幻读,以满足相关隔离级别的要求;
  2. 满足恢复和复制的需要

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句,并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点:

  1. MySQL 的恢复是 SQL 语句级的,也就是重新执行 BINLOG 中的 SQL 语句。

  2. MySQL 的 Binlog 是按照事务提交的先后顺序记录的, 恢复也是按这个顺序进行的。

由此可见,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。

表级锁

表级锁的语法是

LOCK TABLES table_name READ/WRITE

例如:LOCK TABLES t1 READ, t2 WRITE

如果表被其他线程锁定了,当前线程会等待,直到获取了锁为止。

可以执行以下命令来释放锁

UNLOCK TABLES

UNLOCK TABLES会释放当前线程获得的所有锁。另外,当前线程执行另一个LOCK TABLES或者与服务器连接被关闭时,当前线程获得的所有锁将会被隐式释放。

注意:

  1. 在用LOCK TABLES对InnoDB表加锁时,需要将AutoCommit设置为0,否则MySQL不会给表加锁
  2. 事务结束前,不要用UNLOCK TABLES 释放表锁,因为UNLOCK TABLES会隐式提交当前事务
  3. COMMIT或者ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁
  4. lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

在上面的例子中,如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

全局读锁

全局锁的典型使用场景是,做全库逻辑备份(mysqldump)。重新做主从时候
也就是把整库每个表都 select 出来存成文本。

数据库只读状态的危险性:

  • 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就能停止。
  • 如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。

全局锁两种方法

  1. FLUSH TABLES WRITE READ LOCK
  2. set global readonly=true

既然要全库只读,为什么不使用 set global readonly=true 的方式呢?确实 readonly 方式也可以让全库进入只读状态,建议用 FTWRL 方式,主要有几个原因:

  1. 在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大.
  2. 在异常处理机制上有差异。如果执行FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。
  3. readonly 对super用户权限无效

InnoDB加锁方法

  • 意向锁是InnoDB自动加的,不需要用户干预;
  • 对于UPDATE、INSERT和DELETE语句,InnoDB会自动给涉及的数据集加排他锁(X)
  • 对于普通SELECT语句,InnoDB不会加锁
  • 事务可以通过以下语句显示地给数据集加共享锁或排他锁
    • 共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。其他事务仍可以查询记录并对当前数据集加共享锁。但如果当前事务需要对该数据集进行更新操作,则很有可能造成死锁
    • 排他锁(X):SELECT * FROM table_name WHERE … FOR UPDATE.其他事务可以查询当前数据集,但是不能对该数据集加共享锁或排他锁。

InnoDB在事务执行的识货使用两阶段锁协议:

  1. 随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁;
  2. 锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。

死锁处理

当不同的事务或线程出现循环资源依赖的时候,就会出现死锁。

比如,事务A获取了记录R1的写锁,修改成功之后,准备获取记录R2的写锁;而事务B先获取了记录R2的写锁,修改成功之后,准备获取R1的写锁。此时,事务AB等在等对方释放锁,就会产生死循环,造成死锁。

死锁

死锁对策

  1. 主动等待超时,由参数 innodb_lock_wait_timeout 设置,但是业务无法等待;
  2. 主动死锁检测(innodb_deadlock_detect=on

发生死锁后,InnoDB 一般都可以检测到,并使一个事务释放锁回退,另一个则可以获取锁完成事务。

另外,我们可以采取以下方式避免死锁:

  • 通过表级锁来减少死锁产生的概率;
  • 多个程序尽量约定以相同的顺序访问表(这也是解决并发理论中哲学家就餐问题的一种思路);
  • 同一个事务尽可能做到一次锁定所需要的所有资源。

另外,死锁检测也非常耗费资源,判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是 O(n) 的操作。

参考资料

MySQL实战 | 06/07 简单说说MySQL中的锁

史上最详细MySQL全局锁和表锁

MySQL锁总结

0%