MySQL

70-MySQL-事务-表级锁

2023-01-06  本文已影响0人  紫荆秋雪_文

事务的隔离性是由锁来保证的

一、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。当多个线程并发访问某个数据的时候，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。在数据库中，除传统的计算资源（CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为了保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。

二、 MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为3种

2.1、读-读情况

读-读 情况，即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生

2.2 写-写情况

写-写 情况，即并发事务相继对相同的记录做出改动。

小结

2.3 读-写或写-读情况

读-写 或 写-读 ，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读 、 不可重复读 、 幻读 的问题。各个数据库厂商对 SQL标准 的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经解决了 幻读 问题

2.4 并发问题的解决方案

通过两种可选的解决方案来解决 脏读 、 不可重复读 、 幻读 这些问题

2.4.1、方案一：读操作利用多版本并发控制（MVCC），写操作进行 加锁 。

所谓MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由 undo日志构建）。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。

2.4.2、方案二：读、写操作都采用 加锁 的方式

如果一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。如在银行存款的业务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行

小结

三、锁的不同角度分类

image.png

1、从数据操作的类型划分：读锁、写锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读这些情况中的操作相互阻塞，所以 MySQL 实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁（Shared Lock，S Lock）和 排它锁（Exclusive Lock，X Lock）也叫读锁（ReadLock）和写锁（Write Lock）

对于行级读写锁来说：如果一个事务T1已经获得了某个行 r 的读锁，那么此时另外的一个事务T2是可以去获得这个行 r 的读锁，因为读取操作并没有改变行 r 的数据；但是，如果某个事务T3想获取行 r 的写锁，则它必须等待事务T1、T2释放掉行 r 上的读锁才行

读写锁总结.png

1.1、锁定读

在采用加锁方式解决脏读、不可重复读、幻读这些问题时，读取一条记录时需要获取记录的S锁，其实是不严谨的，有时候需要在读取记录时就获取记录的X锁来禁止别的事务读写该记录，为此MySQL提出了两种比较特殊的 SELECT语句格式

1.1.1、对读取的记录加S锁

在普通的 SELECT 语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S 锁（比方说别的事务也使用SELECT . . . LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X 锁（比如使用 SELECT . . . FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S 锁释放掉

SELECT . . . LOCK IN SHARE MODE;
#  或
SELECT . . . FOR SHARE;  #(8.0新增语法)
1.1.2、对读取的记录加X 锁

在普通的 SELECT 语句后边加 FOR UPDATE ，如果当前事务执行了该语句，那么它会为读取到的记录加X 锁，这样既不允许别的事务获取这些记录的S 锁（比方说别的事务使用 SELECT . . . LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X 锁（比如使用 SELECT . . . FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的 S 锁 或者 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X 锁释放掉

SELECT . . . FOR UPDATE;
1.1.3、MySQL8.0新特性

在 5.7及之前的版本，SELECT . . . FOR UPDATE，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。在8.0版本中，SELECT . . . FOR UPDATE，SELECT . . . FOR SHARE 添加NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定

1.1.4、实战
#  开启事务
begin;

#  获取  X 锁
select * from account where id = 2 for update;

select * from account where id = 2 for update;
一直等待.png 等待超时，报错.png 
select * from account where id = 2 for update nowait;
获取锁时使用 nowait.png 
select * from account where id = 2 for update skip locked;
获取锁时使用skip locked.png

1.2、写操作

写操作无非是DELETE、UPDATE、INSERT三种

2、从数据操作的粒度划分：表级锁、页级锁、行锁

为了尽可能提高数据库的并发能力，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很消耗资源的事情（涉及获取、检查、释放锁等动作）。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了锁粒度（Lock Granularity）的概念。对一条记录加锁影响的也只是这条记录而已，就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁

2.1、表锁（Table Lock）

表锁会锁定整张表，它是 MySQL 中最基本的锁策略，并不依赖于存储引擎（不管你是 MySQL 的什么存储引擎，对于表锁的策略都是一样的），并且表锁是开销最小的策略（因为粒度比较大）。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源竞争的概率升高，导致并发率大打折扣

2.1.1、表级别的S锁（共享锁）、X锁（排他锁）

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ，简称 MDL ）结构来实现的。一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写

尽量避免在使用InnoDB存储引擎的表上使 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的 行锁 ，关于InnoDB表级别的 S锁 和 X锁 了解一下就可以了。

CREATE TABLE mylock
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
) ENGINE myisam;

INSERT INTO mylock(name) VALUES ('a');
image.png 
SHOW OPEN TABLES ; #  主要关注 In_use 字段的值
或者
SHOW OPEN TABLES WHERE in_use > 0;

LOCK TABLES mylock READ ;  #  存储引擎会对表 mylock 加 表级别的共享锁
LOCK TABLES mylock WRITE ;  #  存储引擎会对表 mylock 加 表级别的排他锁

LOCK TABLES mylock WRITE ; 
SHOW OPEN TABLES WHERE in_use > 0;

UNLOCK TABLES ;
image.png
2.1.1.1、实战-加表读锁

为 mylock 表加 Read 锁，观察阻塞情况

LOCK TABLES mylock READ ;

SELECT *
FROM mylock;

SELECT *
FROM account;
当前 session1 不能查询其他没有锁定的表.png 
INSERT INTO mylock(name) VALUES ('b');
当前 session1 中插入或者更新锁定的表都会提示错误.png 
SELECT *
FROM mylock;

INSERT INTO account(id, balance) VALUES (5,500);
2.1.1.2、实战-加表写锁

为 mylock 表加 write 锁，观察阻塞的情况

LOCK TABLES mylock WRITE ;

SELECT *
FROM mylock;

UPDATE mylock
SET name = 'c'
WHERE id = 3;


INSERT INTO mylock(name) VALUES ('d');
2.1.1.3、小结

MyISAM在执行查询语句（SELECT）前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁或者写锁

image.png

2.1.2、意向锁 （intention lock）

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁
1、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁 与 行锁）的锁并存
2、意向锁是一种 不与行级锁冲突的表级锁
3、表明某个事务正在某些行持有了锁或该事务准备去持有锁

2.1.2.1、意向锁种类
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
2.1.2.2、意向锁要解决的问题

现在有两个事务，分别是 T1 和 T2，其中T2试图在该表级别上应用共享或排他锁，如果没有意向锁存在，那么T2就需要去检查各个页或行是否存在锁；如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。在数据表的场景中，如果我们给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排他锁了，这样当其他人想要获取数据表排他锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可

2.1.2.3、意向锁实战
CREATE TABLE teacher (
    id INT NOT NULL ,
    name VARCHAR(200) NOT NULL ,
    PRIMARY KEY (id)
);

INSERT INTO teacher (id, name)
VALUES (1, 'zs'),
       (2, 'ls'),
       (3, 'ww'),
       (4, 'zl');

BEGIN ;

SELECT *
FROM teacher
WHERE id = 3 FOR UPDATE ;

BEGIN ;

LOCK TABLES teacher READ ;
一直处于等待状态.png 意向锁之间的兼容互斥性.png
意向锁与普通锁互斥性.png

事务B检测 事务A持有 teacher 表的意向排他锁，就可以得知事务A 必然持有该表中某些数据行的排他锁，那么事务B 对 teacher 表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁

2.1.2.4、意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）

SELECT *
FROM teacher
WHERE id = 4 FOR UPDATE ;
2.1.2.5、结论

2.1.3、自增锁（AUTO-INC锁）

2.1.3.1、在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性
CREATE TABLE `teacher`
(
    `id`   INT          NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;
2.1.3.2、 Simple inserts （简单插入）

可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT...VALUES() 和 REPLACE 语句。

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');
2.1.3.3、Bulk inserts （批量插入）

事先不知道要插入的行数 （和所需自动递增值的数量）的语句。比如 INSERT ... SELECT， REPLACE ... SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列分配一个新值。

2.1.3.4、 Mixed-mode inserts （混合模式插入）

这些是 Simple inserts 语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的混合模式插入是 INSERT ... ON DUPLICATE KEY UPDATE 。对于上面数据插入的案例，MySQL中采用了自增锁的方式来实现，AUTO-INC锁是向使用含有 AUTO_INCREMENT 列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性不高，当我们向一个有 AUTO_INCREMENT 关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低的，所以InnoDB通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高 SQL 语句的可伸缩性和性能。innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式

2.1.4、元数据锁（MDL锁）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更结构操作的安全性，解决了 DML 和 DDL操作之间的一致性问题。无需显示使用，在访问一个表的时候会被自动加上。

2.1.4.1、会话A-从表中查询数据
BEGIN ;

SELECT *
FROM teacher;
2.1.4.2、会话B-修改表结构，增加新列
BEGIN ;

ALTER TABLE teacher ADD age INT NOT NULL ;
2.1.4.3、查看当前MySQL的进程
SHOW PROCESSLIST ;
image.png
2.1.4.4、小结

会话A会对表teacher 加一个MDL 读锁，之后会话B要加MDL 写锁会被 Blocked，因为 会话A的MDL 读锁还没有释放，而 会话C要在表teacher上申请 MDL 读锁的请求也会被会话 B阻塞。因为所有对表的增删改查操作都需要先申请 MDL 读锁，就都被 会话B 阻塞，等于这个时候完全不可读写了

image.png
上一篇 下一篇

猜你喜欢

热点阅读