数据库通常会同时执行多个事务,这些事务可能同时对同一批数据进行增删改查操作,可能会导致脏写、脏读、不可重复读和幻读等问题。
这些问题的根本是数据库的多事务并发性问题。为了解决多事务并发问题,数据库引入了事务隔离机制、锁机制和 MVCC 多版本并发控制隔离机制等一系列机制。接下来,小鱼将深入探讨这些机制,帮助各位 uu 们更好地理解数据库内部的执行原理。
MySQL 事务是由一组 SQL 语句组成的逻辑处理单元,具有以下 4 个属性,通常称为 ACID 属性。
在前言中提到,并发操作可能带来的脏写、脏读、不可重复读和幻读等问题。
这些问题需要通过数据库的隔离机制解决。
在MySQL 不同的隔离级别下,脏写、脏读、不可重复读和幻读等问题发生的可能性如下表所示。
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
读未提交(Read uncommitted) | 可能 | 可能 | 可能 |
读已提交(Read committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatableread) | 不可能 | 不可能 | 可能 |
可串行化(Serializable) | 不可能 | 不可能 | 不可能 |
数据库的事务隔离级别从上到下越严格,且隔离级别越严格,并发带来的副作用越小,相应需要付出的代价就越大。
因为事务隔离机制实质上使事务在一定程度上“串行化”进行,这是与“并发”逻辑是相互矛盾的。
让并发逻辑实现一定程度的串行化,则需要锁机制实现。
在数据库中,锁是一种机制,用于协调多个并发事务对数据资源的访问。除了传统的计算资源(CPU、RAM、IO 等资源)竞争外,数据也是一种需要共享并发访问的资源。
为了确保数据访问的一致性和有效性,数据库必须解决并发访问的问题,而锁冲突则是影响数据库并发访问性能的重要因素。
通过使用锁机制,数据库可以确保在任何给定时刻只有一个事务可以访问或修改特定的数据,从而避免数据冲突和保证数据的完整性。
SELECT * FROM tablename WHERE id=1 LOCK IN SHARE MODE
进行加锁。SELECT * FROM tablename WHERE id=1 FOR UPDATE
加锁。先建立示例表 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;
当前 session 对 mylock
表加上读锁后,当前session 和其他session 都可以进行读取数据,但是当前session 对 mylock
表插入数据或更新数据时会报错,其他session 插入数据或更新数据则需要等待读锁释放。
unlock tables;
删除读锁后,给示例表加上表锁 (写锁)lock table mylock write;
当前 session 对 mylock
表加上写锁后,当前session 可以对mylock 表进行增删改查操作,其他 session 对该表的所有操作都会被阻塞。
表锁我们是在 MyISAM 存储引擎上演示的,该存储引擎是不支持行锁的。接下来行数演示示例小鱼将创建一个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) 机制。
客户端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] 区间都无法修改数据。
注意:
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%';
当存在等待次数较高、等待时长较高的时候,我们需要对锁进行分析,并制定相关的优化。
通过查询这些数据表,可以了解当前系统中的锁定情况,帮助诊断和优化锁定相关的性能问题。
-- 查看事务
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 无法检测到。