MySQL事务（一）MySQL事务隔离级别、锁机制

前言

数据库通常会同时执行多个事务，这些事务可能同时对同一批数据进行增删改查操作，可能会导致脏写、脏读、不可重复读和幻读等问题。

这些问题的根本是数据库的多事务并发性问题。为了解决多事务并发问题，数据库引入了事务隔离机制、锁机制和 MVCC 多版本并发控制隔离机制等一系列机制。接下来，小鱼将深入探讨这些机制，帮助各位 uu 们更好地理解数据库内部的执行原理。

MySQL 事务

MySQL 事务是由一组 SQL 语句组成的逻辑处理单元，具有以下 4 个属性，通常称为 ACID 属性。

• 原子性（Atomicity）：事务是一个原子操作单元，对数据的修改要么全部执行，要么全部不执行。事务中某一操作失败，之前已经执行的操作会被回退。
• 一致性（Consistent）：事务在开始和完成时，数据必须保持一致状态。这意味着所有相关的数据规则必须适用于事务的修改，以确保数据完整性。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，确保事务在独立环境中执行，不受外部并发操作影响。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成后，对数据的修改是永久的，即使系统发生故障也能保持。

在前言中提到，并发操作可能带来的脏写、脏读、不可重复读和幻读等问题。

• 更新丢失（Lost Update）或脏写：当多个事务选择同一行并尝试更新该行时，由于各事务不知道其他事务的存在，可能导致最后的更新覆盖了其他事务的更新，造成更新丢失问题。
  • 事务 A 执行a+1 操作，尚未提交；同时，事务 B 执行a+1 操作，并提交事务，事务 A 随后也提交事务。最终数据结果为a+1，而非a+2。
• 脏读（Dirty Reads）：指一个事务在对记录进行修改时，另一个事务读取到了未提交的数据，导致数据处于不一致状态。如果基于这些"脏"数据进行进一步处理，可能导致未提交的数据依赖关系。这种情况被称为脏读。
  • 事务 A 读取到了事务 B 已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果事务 B 失败，执行回滚，则事务 A 读取的数据无效，不符合一致性要求。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后，在稍后的时间再次读取相同数据，却发现数据已经发生改变或某些记录已被删除。这种现象称为不可重复读，违反了隔离性要求。
  • 事务 A 内部的两次相同查询语句在不同时刻读出的结果不一致，不符合隔离性。
• 幻读（Phantom Reads）：指一个事务按照相同的查询条件重新读取之前检索过的数据时，却发现其他事务插入了满足查询条件的新数据。这种情况被称为幻读，也违反了隔离性。
  • 事务 A 内部的两次相同查询语句在不同时刻读出的数据集行数不一致，不符合隔离性。

这些问题需要通过数据库的隔离机制解决。

事务的隔离级别

在MySQL 不同的隔离级别下，脏写、脏读、不可重复读和幻读等问题发生的可能性如下表所示。

数据库的事务隔离级别从上到下越严格，且隔离级别越严格，并发带来的副作用越小，相应需要付出的代价就越大。

因为事务隔离机制实质上使事务在一定程度上“串行化”进行，这是与“并发”逻辑是相互矛盾的。

让并发逻辑实现一定程度的串行化，则需要锁机制实现。

MySQL锁

在数据库中，锁是一种机制，用于协调多个并发事务对数据资源的访问。除了传统的计算资源（CPU、RAM、IO 等资源）竞争外，数据也是一种需要共享并发访问的资源。

为了确保数据访问的一致性和有效性，数据库必须解决并发访问的问题，而锁冲突则是影响数据库并发访问性能的重要因素。

通过使用锁机制，数据库可以确保在任何给定时刻只有一个事务可以访问或修改特定的数据，从而避免数据冲突和保证数据的完整性。

锁分类

1. 从性能上分
   • 乐观锁：通过版本对比来实现并发控制，假设事务之间不会发生冲突，直到提交操作时才会检查是否有冲突。适用于并发冲突较少的场景。
   • 悲观锁：假设事务之间会发生冲突，因此在访问数据之前就会加锁，保证同一时间只有一个事务能够访问数据。适用于并发冲突较多的场景。
2. 从对数据操作的粒度分
   • 表锁：锁定整个表，在事务操作时会锁定整张表，影响表中所有数据。
     • 锁的开销小，加锁快；不会出现死锁；同时锁定粒度大，容易发生锁冲突，并发度最低；适用于整张表数据迁移的场景。
   • 行锁：只锁定某行数据，使其他事务无法修改该行数据，但不影响表中其他数据的访问。
     • 每次操作只锁住表中一行数据。加锁的开销大，加锁慢；可能会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
3. 从对数据库操作的类型分
   • 读锁（共享锁，S锁 Shared）：在事务读取数据时加锁，其他事务可以读取同一数据，但不能进行写操作。即针对同一份数据，多个读操作不会受影响。可以通过 SELECT * FROM tablename WHERE id=1 LOCK IN SHARE MODE 进行加锁。
   • 写锁（排它锁，X锁 eXclusive）：在事务对数据进行写入或修改时加锁，阻止其他事务对该数据的读写操作。MySQL 数据修改时都会进行加写锁，查询时可以通过 SELECT * FROM tablename WHERE id=1 FOR UPDATE 加锁。
   • 意向锁（I 锁 Intention Lock）：针对于表锁，是MySQL 数据库本身加的。在获取表中某行的锁之前，首先会获取表的意向锁（设置一个标识），表示该事务将对表中的行进行操作，其他事务想要对表加锁时，可以知道这个意图而采取相应操作（不必再逐行判断是否存在锁）。
     • 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
     • 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

MySQL锁实践

表锁

先建立示例表 mylock，并向示例表插入一些数据。

--建表SQL
CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

--插入数据
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

• 创建名为 mylock 的表，表中包含两列 id、name，并且表的存储引擎为 MyISAM，字符集为 utf 8。

锁操作示例

-- 手动增加表锁
 lock table 表名称1 [read|write], 表名称2 [read|write];

-- 查看表上加过的锁
show open tables;

-- 删除表锁
unlock tables;

• 我们给示例表加上表锁 （读锁）lock table mylock read;

Pasted image 20240507170541.png

当前 session 对 mylock 表加上读锁后，当前session 和其他session 都可以进行读取数据，但是当前session 对 mylock 表插入数据或更新数据时会报错，其他session 插入数据或更新数据则需要等待读锁释放。

unlock tables; 删除读锁后，给示例表加上表锁 （写锁）lock table mylock write;

当前 session 对 mylock 表加上写锁后，当前session 可以对mylock 表进行增删改查操作，其他 session 对该表的所有操作都会被阻塞。

行锁

表锁我们是在 MyISAM 存储引擎上演示的，该存储引擎是不支持行锁的。接下来行数演示示例小鱼将创建一个InnoDB 存储引擎的表，基于这张表来展开演示。

• InnoDB支持事务（TRANSACTION）
• InnoDB支持行级锁

CREATE TABLE `userlock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` varchar (255) DEFAULT NULL,
	`balance` int (11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO `wedemo`.`userlock` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `wedemo`.`userlock` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `wedemo`.`userlock` (`name`, `balance`) VALUES ('lucy', '2400');

• 创建名为 userlock 的表，表中包含两列 id、name 、balance，并且表的存储引擎为 InnoDB，字符集为 utf 8。

读未提交案例

客户端A：打开一个MySQL session，并设置当前事务模式为未提交读（read uncommitted）

set session transaction isolation level read uncommitted;
set tx_isolation='read-uncommitted';

start transaction;
select * from userlock;

客户端B：在客户端A 事务提交之前，我们再打开一个MySQL session 去更新userlock 表，并查询结果，可以看到客户端B 中可以查到更新的数据。

set session transaction isolation level read uncommitted;
start transaction;
update userlock set balance = balance-50 where id=1;
select * from userlock;

客户端A：客户端B 事务没有提交，此时通过客户端A 再次查询结果，可以看到客户端B更新后的结果已经可以被客户端A 查询到。

客户端B：此时，客户端B 事务发生异常，进行回退了，其操作的所有数据被撤销。

客户端A：而客户端A 事务查询到的数据就是错误的，是脏数据。若是要执行更新操作会导致数据不一致。如下列操作，理想得到结果为 350，实际得到结果为 400。

update userlock set balance = balance-50 where id=1;
select * from userlock;

解决这个问题则需要采用读已提交的隔离级别。

读已提交案例

客户端A：打开一个 MySQL session，并设置当前事务模式为读已提交（read committed），再查询表的所有记录。

set session transaction isolation level read committed;
set tx_isolation='read-committed';

start transaction;
select * from userlock;

客户端B：在客户端A 事务未提交前，同样我们再打开一个session 去更新表。

set session transaction isolation level read committed;
start transaction;
update userlock set balance = balance-50 where id=1;
select * from userlock;

客户端A：客户端A 再去查询表记录，发现事务A 不能查询到事务B 未提交的数据。这样就解决了脏读的问题。

客户端B：客户端B 事务进行提交。

commit;

客户端A：此时，客户端A 再读一次表记录，会发现什么情况？

select * from userlock;

此时，发现两次读取的结果又不一样了，又产生了不可重复读的问题。

解决不可重复读问题需要采用可重复读的隔离级别。

可重复读案例

客户端A：打开一个 MySQL session，并设置当前事务模式为可重复读（repeatable read），再查询表的所有记录。

set tx_isolation='repeatable-read';
set session transaction isolation level repeatable read;

start transaction;
select * from userlock;

客户端B：在客户端 A 事务未提交前，同样我们再打开一个 session 去更新表，并提交事务。

set session transaction isolation level repeatable read;

start transaction;
update userlock set balance = balance-50 where id=1;
select * from userlock;
commit;

客户端A：在客户端A 中查询表记录，我们会发现表记录并没有发生改变。这样就不会出现不可重复读的问题。

我们在客户端A 中再执行一次更新操作，看看最后结果如何。

结果为 300，并没有变成 350，因为事务B 已经提交，这里的计算结果以 350 来计算的，所以数据一致性没有被破坏。

update userlock set balance = balance-50 where id=1;

可重复读的隔离级别采用了 MVCC(multi-version concurrency control) 机制。

• select 操作不会更新版本号，是快照读（读取的是历史版本）；
• insert、update和delete会更新版本号，是当前读（当前版本）。

客户端B：再次打开客户端B，插入一条数据，并提交。

begin；
insert into userlock values(4,'ber','700');
select * from userlock;
commit;

客户端A：在客户端A 中查询表记录，我们会发现并没有查询到新纪录，此时我们没有发现幻读的问题。

客户端A：当我们在客户端A 中执行更新操作，再查询表记录，此时会发现事务B 新增的数据，出现幻读问题。（MVCC 机制中，查询操作为可照读，修改操作为当前读）

update userlock set balance=888 where id = 4;
select * from userlock;

解决幻读问题则需要采用串行化的隔离级别。

串行化实例

客户端 A：打开一个 MySQL session，并设置当前事务模式为串行化（serializable），再查询表id=1 的记录。

set tx_isolation='serializable';
set session transaction isolation level serializable;

begin;
select * from userlock where id=1;

客户端B：在客户端 A 事务未提交前，同样我们再打开一个 session 去更新id=1 的数据，并尝试更新id=2 的数据。

begin;
update userlock set balance=450 where id=1;
update userlock set balance=450 where id=2;

我们会发现更新id=1 的数据时，会被阻塞，直到等待超时报错。而更新id=2 的数据是没有问题的。

由此我们知道当隔离级别被设为串行化时，查询事务也是会被上锁的。

同样，如果客户端A 执行的是范围查询，那么查询范围的数据（包括行间隙范围，不能在范围内插入数据--间隙锁）都会被加上锁。此时客户端B 在这个查询范围之中增加数据，也是会被阻塞的，这样就避免了幻读问题。

实际上，串行化隔离级别的并行化程度最低，很少被应用。

间隙锁实例

间隙锁（Gap Lock）是一种特殊类型的锁，在数据库中用于控制范围查询的并发访问。间隙锁通常用于防止其他事务在范围查询的结果集中插入新记录或更新已存在记录，从而确保范围查询的一致性。

客户端A：

set session transaction isolation level repeatable read;

begin;
select * from userlock;
update userlock set balance = 100 where id > 8 and id <18;

对于上述数据而言，数据间隙有 (4,10)，(10,100)，(100, ∞) 两个区间。 在客户端A下面执行范围更新 update userlock set balance = 100 where id > 8 and id <18;。

客户端 B：执行插入和修改数据操作，我们会发现 (4,100]区间都无法插入数据。

set session transaction isolation level repeatable read;

begin;
insert into userlock values(5,'duo','1700');
insert into userlock values(11,'duo','1700');
update userlock set balance = 100 where id=100;

同样，其他客户端没法在这个范围所包含的所有行记录（包括间隙行记录）以及行记录所在的间隙里插入或修改任何数据，即id在 (4,100] 区间都无法修改数据。

注意：

• 最后的 100 也是包含在内的。
• 间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)实例

Next-Key Locks 是行锁和间隙锁的结合。例如，对于 (4,100]这个区间范围，我们称之为临键锁。

对于没有索引的字段时，行锁会升级为表锁（RR (Read-Read) 级别会升级为表锁，RC(Read-Commit )级别不会升级为表锁）。主要是针对索引进行加锁，如果更新非索引字段，行锁可能会升级为表锁。

客户端 A ：执行：update userlock set balance = 800 where name = 'lilei'; 这时，客户端 B 对该表的任何行操作都会被阻塞。

set session transaction isolation level repeatable read;

start transaction;
update userlock set balance = 800 where name = 'lilei';

客户端B：

set session transaction isolation level repeatable read;

begin;
insert into userlock values(5,'duo','1700');
insert into userlock values(11,'duo','1700');
update userlock set balance = 100 where id=100;
insert into userlock values(111,'duo','1700');

在 InnoDB 中，行锁是针对索引而不是记录加的锁。而且该索引不能失效，否则会导致行锁升级为表锁。

另外，可以通过使用共享锁 lock in share mode 和排他锁 for update 来锁定某一行。

例如：select * from userlock where id = 2 for update; 这样其他 Session 只能读取这行数据，进行修改操作时会被阻塞，直到锁定行的 Session 提交。

行锁分析

我们可以通过 InnoDB_row_lock 这个状态变量来分析系统行锁情况（资源争夺等）。

show status like 'innodb_row_lock%';

• Innodb_row_lock_current_waits：当前正在等待锁定的数量
• Innodb_row_lock_time： 从系统启动到现在锁定总时间长度
• Innodb_row_lock_time_avg： 每次等待所花平均时间
• Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
• Innodb_row_lock_waits： 系统启动后到现在总共等待的次数

当存在等待次数较高、等待时长较高的时候，我们需要对锁进行分析，并制定相关的优化。

INFORMATION_SCHEMA 系统库中与锁有关的数据表

1. INNODB_LOCKS: 包含当前被锁定的 InnoDB 资源信息。
2. INNODB_LOCK_WAITS: 包含当前正在等待锁的事务和锁定资源的信息。
3. INNODB_TRX: 包含当前活动的事务信息，包括事务 ID、状态和锁定状态等。
4. INNODB_LOCKS_METADATA: 包含 InnoDB 锁定信息的元数据，例如锁定 ID 和锁定模式。
5. TABLE_LOCK_WAITS: 包含等待表级锁定的线程信息。

通过查询这些数据表，可以了解当前系统中的锁定情况，帮助诊断和优化锁定相关的性能问题。

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status\G; 

死锁实例

客户端A：

start transaction;
select * from userlock where id=1 for update;

客户端B：

start transaction;
select * from userlock where id=2 for update;

客户端A：

select * from userlock where id=2 for update;

客户端B：

select * from userlock where id=1 for update;

当出现死锁的时候，MySQL 自动检测到了，并且回滚了发生死锁的事务，所以客户端A 在客户端B 回滚后成功查询到数据。

-- 查看近期死锁日志信息
show engine innodb status\G; 

虽然说 MySQL 具有自动检测死锁的能力，但也有一些死锁 MySQL 无法检测到。

锁小结

1. 对MyISAM表的读操作(加读锁) ，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2. 对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
3. InnoDB 存储引擎实现了行锁，在锁定机制实现上带来的性能损耗会比表锁大一些，但在并发处理能力上要远优于MyISAM 存储引擎的表锁。
4. InnoDB 适合并发量高的场景，但如果使用不当可能会带来较差的效果，甚至不如MyISAM 存储引擎。
5. 锁优化总结
   1. 行锁需要索引完成，数据检索尽可能使用索引，避免行锁升级为表锁。
   2. 合理设计索引，尽可能缩小锁的范围。
   3. 尽可能缩小检索的数据范围，避免产生过多的间隙锁。
   4. 合理设计事务大小，减少锁占用的资源和时长，尽量将锁操作放在事务最后执行。