Mysql 锁 笔记

·  阅读 840

锁是保证数据并发访问的一致性、有效性。根据加锁的范围,MySQL 中的锁可分为三类:

  • 全局锁
  • 表级锁
  • 行锁

全局锁

MySQL 全局锁会关闭所有打开的表,并使用全局读锁锁定所有表。其命令为:

FLUSH TABLES WITH READ LOCK;
复制代码

简称:FTWRL,可以使用下面命令解锁:

UNLOCK TABLES;
复制代码

当执行 FTWRL 后,所有的表都变成只读状态,数据更新或者字段更新将会被阻塞。

使用场景

全局锁一般用在整个库(包含非事务引擎表)做备份(mysqldump 或者 xtrabackup)时。也就是说,在整个备份过程中,整个库都是只读的,其实这样风险挺大的。如果是在主库备份,会导致业务不能修改数据;而如果是在从库备份,就会导致主从延迟。

好在 mysqldump 包含一个参数 --single-transaction,可以在一个事务中创建一致性快照,然后进行所有表的备份。因此增加这个参数的情况下,备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。

而对于 xtrabackup,可以分开备份 InnoDB 和 MyISAM,或者不执行 --master-data,可以避免使用全局锁。

表级锁

表级锁有两种:表锁和元数据锁。

表锁

表锁使用场景:

  • 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁,不仅事务执行效率低,而且可能造成其它事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高事务执行速度;

  • 事务涉及多个表,比较复杂,可能会引起死锁,导致大量事务回滚,可以考虑表锁避免死锁。

其中表锁又分为表读锁和表写锁,命令分别是:

表读锁:

lock tables table_name read;
/*本线程和其他线程可以读,本线程写会报错,其他线程写会等待*/
复制代码

表写锁:

lock tables table_name  write;
/*本线程可以读写,其他线程读写都会阻塞*/
复制代码

元数据锁

MDL 锁的出现解决了同一张表上事务和 DDL(Data Define Language) 并行执行时可能导致数据不一致的问题。

因此对于开发来说,在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等,当然对于 DBA 来说,也应该尽量避免在业务高峰执行 DDL 操作。

行锁

InnoDB 目前主流的重要原因是:

  • InnoDB 支持事务:适合在并发条件下要求数据一致的场景。
  • InnoDB 支持行锁:有效降低由于删除或者更新导致的锁定。

两阶段锁

两阶段锁原则:锁操作分为两个阶段,加锁阶段和解锁阶段,并且保证加锁阶段和解锁阶段不相交。

序号MySQL 操作解释锁阶段
1begin;事务开始
2insert into …;加 insert 对应的锁加锁阶段
3update table …;加 update 对应的锁加锁阶段
4delete from …;加 delete 对应的锁加锁阶段
5commit;事务结束,同时释放 2、3、4 步骤中加的锁解锁阶段

InnoDB 行锁模式

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

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

对于普通 select 语句,InnoDB 不会加任何锁,事务可以通过以下语句显式给记录集加共享锁或排他锁:

  • 共享锁(S):select * from table_name where … lock in share mode;

  • 排他锁(X):select * from table_name where … for update。

    补充:**为什么要用 for update ? **

    我们常使用的查询语句,比如 select * from t16 where b=1 属于快照读,是不会看到别的事务插入的数据的。

    而在查询语句后面加了 for update 显式给记录集加了排他锁,也就让查询变成了当前读。插入、更新、删除操作,都属于当前读。其实也就可以理解 select … for update 是为了让普通查询获得插入、更新、删除操作时所获得的锁。

InnoDB 行锁算法

InnoDB 行锁的三种算法:

  • Record Lock:单个记录上的索引加锁。
  • Gap Lock:间隙锁,对索引项之间的间隙加锁,但不包括记录本身。
  • Next-Key Lock:Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身。

InnoDB 行锁实现特点意味着:如果不通过索引条件检索数据,那么 InnoDB 将对表中所有记录加锁,实际效果跟表锁一样。

事物隔离级别

不同事务隔离级别对应的行锁是不同,讲解行锁的锁定范围之前,必须先明确事务的隔离级别。

MySQL 的 4 种隔离级别:

  • Read Uncommitted(读未提交):在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。可能会出现脏读。
  • Read Committed (读已提交,简称 RC):一个事务只能看见已经提交事务所做的改变。因为同一事务的其他实例在该实例处理期间可能会有新的 commit, 所以可能出现幻读。
  • Repeatable Read(可重复读,简称 RR):这是 MySQL 的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。消除了脏读、不可重复读,默认也不会出现幻读。
  • Serializable (串行):这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。

脏读和幻读:

  • 脏读:读取未提交的事务。
  • 幻读:一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的数据。

不同事务隔离级别下的行锁情况

RC 隔离级别

  • 通过非索引字段查询:由于没有索引,因此只能走聚簇索引,进行全表扫描,聚簇索引上的所有记录,都被加上了锁。因为如果一个记录无法通过索引快速过滤,那么存储引起层面就会讲所有记录加锁后返回,然后由 server 层进行过滤,过滤掉不满足条件的数据后,会把不满足条件的记录放锁。因此把所有记录的锁上。

    总结:没有索引的情况下,InnoDB 的当前读会对所有记录都加锁。所以在工作中应该特别注意 InnoDB 这一特性,否则可能会产生大量的锁冲突。

  • 通过唯一索引查询

    由于 a 是唯一索引,因此 select * from t16 where a=1 for update;(后面称为 SQL2) 语句会选择走 a 列的索引进行条件过滤,在找到 a=1 的记录后,会将唯一索引上 a=1 索引记录上加 X 锁,同时,会根据读取到的 id 列,回到聚簇索引,然后将 id=1 对应的聚簇索引项加 X 锁。

    总结:如果查询的条件是唯一索引,那么 SQL 需要在满足条件的唯一索引上加锁,并且会在对应的聚簇索引上加锁。

  • 通过非唯一索引查询

    总结:如果查询的条件是非唯一索引,那么 SQL 需要在满足条件的非唯一索引上都加上锁,并且会在它们对应的聚簇索引上加锁。

  • RC 隔离级别幻读

    RC 隔离级别下,只锁住了满足 c=3 的当前行,而不会对后面的位置(或者说间隙)加锁,因此导致 其他会话 的写入语句能正常执行并提交。

    由于行锁只能锁住当前行,但是新插入的记录,是在被锁住记录之前的间隙。因此,为了解决幻读问题,InnoDB 在 RR 隔离级别下配置了间隙锁(Gap Lock)。

RR 隔离级别

  • RR 隔离级别下的非唯一索引查询:

    与 RC 隔离级别下的图相似,但是有个比较大的区别是:RR 隔离级别多了个 GAP 锁。

    如上图,首先需要考虑哪些位置可以插入新的满足条件 c=4 的项:

    • 由于 B+ 树索引是有序的,因此 [2,2](代表 c 和 id 的值,后面就不一一说明了)前面的记录,不可能插入 c=4 的记录了;
    • [2,2] 与 [4,4] 之间可以插入 [4,3];
    • [4,4] 与 [4,6] 之间可以插入 [4,5];
    • [4,6] 之后,可以插入的值就很多了:[4,n](其中 n>6);

    为了保证这几个区间不会插入新的满足条件 c=4 的记录,MySQL RR 隔离级别选择了 GAP 锁,将这几个区间锁起来。

  • RR 隔离级别下的非索引字段查询:

    如图,所有记录都有 X 锁,除此之外,每个 GAP 也被加上了 GAP 锁。因此这张表在执行完 select * from t17 where b=1 for update; 到 commit 之前,除了不加锁的快照读,其它任何加锁的 SQL,都会等待,如果这是线上业务表,那就是件非常恐怖的事情了。

    总结:RR 隔离级别下,非索引字段做条件的当前读不但会把每条记录都加上 X 锁,还会把每个 GAP 加上 GAP 锁。再次说明,条件字段加索引的重要性。

  • RR 隔离级别下的唯一索引当前读是否会用到 GAP 锁

    GAP 锁的目的是:为了防止同一事务两次当前读,出现幻读的情况。如果能确保索引字段唯一,那其实一个等值查询,最多就返回一条记录,而且相同索引记录的值,一定不会再新增,因此不会出现 GAP 锁。

    因此以唯一索引为条件的当前读,不会有 GAP 锁。所以 RR 隔离级别下的唯一索引当前读加锁情况与 RC 隔离级别下的唯一索引当前读加锁情况一致。

死锁

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。

InnoDB 中解决死锁问题有两种方式:

  1. 检测到死锁的循环依赖,立即返回一个错误(这个报错内容请看下面的实验),将参数 innodb_deadlock_detect 设置为 on 表示开启这个逻辑;
  2. 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是 50 秒。

一般线上业务都建议使用的第 1 种策略,因为第 2 种策略锁等待时间是 50 秒,对于高并发的线上业务是不能接受的。但是第 1 种策略,也会有死锁检测时的额外 CPU 开销的。

出现死锁的情况

本节聊了死锁相关的内容。通过具体实验列举了几种出现死锁的情况:

  • 不同线程并发访问同一张表的多行数据,未按顺序访问导致死锁;
  • 不同线程并发访问多个表时,未按顺序访问导致死锁;
  • RR 隔离级别下,由于间隙锁导致死锁。

降低死锁的概率

那么应该怎样降低出现死锁的概率呢?这里总结了如下一些经验:

  1. 更新 SQL 的 where 条件尽量用索引;
  2. 基于 primary 或 unique key 更新数据;
  3. 减少范围更新,尤其非主键、非唯一索引上的范围更新;
  4. 加锁顺序一致,尽可能一次性锁定所有需要行;
  5. 将 RR 隔离级别调整为 RC 隔离级别。

分析死锁的方式

InnoDB 中,可以使用 SHOW INNODB STATUS 命令来查看最后一个死锁的信息。我们可以尝试用下这个命令获取一些死锁信息,如下:

show engine innodb status
复制代码
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改