1 事务的ACID 特性
事务是一组 SQL 语句,它们是一个执行单位。InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。
1.1 原子性(Atomicity)与 持久性(Durability)
1.1.1 Undo Log
Undo :意为撤销或取消,以撤销操作为目的,返回某个指定的状态。Undo Log :数据库事务开始之前,会将要修改的记录存放到 Undo 日志里,当事务回滚时或者数据库崩溃时,可以利用 Undo 日志,撤销未提交事务对数据库产生的影响。Undo Log 产生和销毁: Undo Log 在事务开始前产生;事务在提交后,并不会立刻删除 U ndo Log,I nnodb 会将该事务对应的 U ndo Log 放入到删除列表中,后面会通过后台线程P urge Thread 进行回收处理。Undo Log 属于逻辑日志,记录一个变化过程。例如执行一个 delete ,Undo Log 会记录一个 insert ;执行一个 update ,Undo Log 会记录一个相反的 update 。
1.1.2 Redo Log
Redo:顾名思义就是重做。以恢复操作为目的,在数据库发生意外时重现操作。
Redo Log:事务中修改的任何数据,都会将最新的值备份存储到 Redo Log,在系统 carsh 重启后可通过 redo log 来恢复数据。
Redo Log 的生成和释放:随着事务操作的执行,就会生成 Redo Log ,在事务提交时会将产生 Redo Log 写入 Log Buffer ,并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后,Redo Log 的使命也就完成了, Redo Log 占用的空间就可以重用(被覆盖写入)。
1.1.3 图解
(1)流程图
(2)Redo Undo 写入过程
(3)数据持久化到磁盘
通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值:0、1、2,默认为1。这个变量只是控制 commit 动作是否刷新 Log Buffer 到磁盘
(1)0,事务提交后,每秒将 Redo buffffer 写入 OS cache, 再调用 fsync() 把 OS cache 写入磁盘;
(2)1,事务每次提交都会将 Redo buffffer 写入 OS cache, 再调用 fsync() 把 OS cache 写入磁盘;
(3)2,事务每次提交仅将 Redo buffffer 写入 OS cache,然后由后台 Master 线程每隔 1s 调用 fsync() 把 OS cache 写入磁盘。
一般建议选择取值2,因为 MySQL 挂了数据没有损失,整个服务器挂了才会损失1秒的事务提交数据。
1. 2 一致性(Consistency)
一致性是一种约束,是一种目的,在事务开始之前和事务结束之后,数据库的完整性不会被破坏,包括约束一致性和数据一致性。它是原子性,持久性,隔离性共同保证的结果。
1.3 隔离性(Isolation)
2 事务隔离级别
2.1 并发事务下可能会产生的问题
2.1.1 脏读(数据在修改)
某个事务所做的修改尚未提交时,其他事务就能读到这些修改。
2.1.2 幻读(数据在增加)
一个事务突然读到之前未见过的行。假设某个事务刚执行完一条 SELECT 语句,接着另一个事务插入了一条新的记录,此时第一个事务再执行一条 SELECT 语句时,就有可能看到这条新增的记录。
2.1.3 不可重复读
2.1.4 数据覆盖
提交覆盖:一个事务提交操作,把其他事务已提交的数据给覆盖了。
2.2 隔离级别
事务隔离级别是为了解决并发事务下产生的问题
性能从 1 到 4 逐渐降低
(1)READ UNCOMMITTED
允许某个事务看到其他事务尚未提交的行修改。
(2)READ COMMITTED
允许某个事务看到其他事务已经提交的行修改。
(3)REPEATABLE READ(MySQL默认隔离级别 )
同一个事务使用同一条 SELECT 在每次读取时得到的结果都一样,就算中间有其他事务插入或修改行。
公司的 DB 也是设置了可重复读。
(4)SERIALIZABLE
如果某个事务在读取某些行,那么在它完成之前,其他事务都无法对这些行进行修改。
查看事务隔离级别
show variables like 'transaction_isolation';
select @@transaction_isolation;
设置事务隔离级别
set transaction_isolation='READ-UNCOMMITTED';
set transaction_isolation='READ-COMMITTED';
set transaction_isolation='REPEATABLE-READ';
set transaction_isolation='SERIALIZABLE';
2.2.1 隔离级别的的底层实现
1)“可重复读”隔离级别
在可重复读隔离级别下,事务在启动的时候就会创建一个视图(read-view),类似于“拍了个快照”,记录当时事务的id,数值,整个事务存在期间都用这个视图。
每行数据也都是有多个版本的,就是数据库的多版本并发控制(MVCC:Multi Version Concurrency Control)。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个数据版本的事务 ID,记为 row trx_id。同时保留旧的数据版本,生成一条回滚日志。
假设一个值 k 从 1 被按顺序改成了 10、11、22,下图是该记录被多个事务连续更新后的状态:
InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间,当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。
数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位,就组成了当前事务的一致性视图(read-view)。
而数据版本的可见性规则,就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。
这样,对于当前事务的启动瞬间来说,一个数据版本的 row trx_id,有以下几种可能:
- 如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数据是可见的;
- 如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的;
- 如果落在黄色部分,那就包括两种情况
- 若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见;
- 若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见。
总结
InnoDB 的行数据有多个版本,每个数据版本有自己的 row trx_id,每个事务或者语句有自己的一致性视图。
普通查询语句是一致性读,一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
- 对于可重复读,查询只承认在事务启动前就已经提交完成的数据;
- 对于读提交,查询只承认在语句启动前就已经提交完成的数据;
更新逻辑
更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。除了 update 语句外,select 语句如果加锁,也是当前读。
// 拿到的k为当前值22
mysql> select k from t where id=1 lock in share mode;
// 或,拿到的k为当前值22
mysql> select k from t where id=1 for update;
什么时候删除回滚日志?
当没有事务再需要用到这些回滚日志时,即系统里没有比这个回滚日志更早的 read-view 的时候,回滚日志会被删除。
为什么建议尽量不要使用长事务?
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。
长事务还占用锁资源,也可能拖垮整个库。
系统里如何避免长事务?
- 使用 set autocommit=1, 通过显式语句的方式来启动事务;
- 在开发过程中,尽可能的减小事务范围;
- 在 information_schema 库的 innodb_trx 这个表中查询长事务,查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
2)“读提交”隔离级别下
读提交需要每次都读到最新已提交的数据,所以每次执行 SQL 都会创建一个全新的视图;
不创建视图行不行?不行,因为当前最新的数据有可能是未提交的,没有视图作为依据,无法找到已提交的数据。
3)“读未提交”隔离级别
不需要创建视图,直接返回记录上的最新值
4)“串行化”隔离级别
直接用加锁的方式来避免并行访问。
3 锁分类
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
- 根据操作粒度来分:全局锁,表锁和行锁
- 根据操作类型来分:读锁(共享锁)和写锁(排他锁)
-
根据操作性能来分:乐观锁和悲观锁
3.1 全局锁
Flush tables with read lock (FTWRL)
当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
- 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;
- 如果在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟。
3.2 表级锁
3.2.1 表锁
lock table 表名称 read|write, 表名称 2 read|write;
show open tables;
unlock tables;
3.2.2 MDL
3.2.3 实战场景
首先要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。
如果要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
3.3 行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的,但并不是所有的引擎都支持行锁,比如MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。
行锁就是针对数据表中行记录的锁。比如事务 A 更新了一行,而这时候事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放,这样锁住该记录的时间最少。
实战1:
假设你负责实现一个电影票在线交易业务,顾客 A 要在影院 B 购买电影票。涉及到以下操作:
- 从顾客 A 账户余额中扣除电影票价;
- 给影院 B 的账户余额增加这张电影票价;
- 记录一条交易日志。
要完成这个交易,需要 update 两条记录,并 insert 一条记录。当然,为了保证交易的原子性,我们要把这三个操作放在一个事务中。那么,你会怎样安排这三个语句在事务中的顺序呢?
如果同时有另外一个顾客 C 要在影院 B 买票,那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额,需要修改同一行数据。根据两阶段锁协议,不论你怎样安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才释放的。所以,如果你把语句 2 安排在最后,比如按照 3、1、2 这样的顺序,那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待,提升了并发度。
实战2:
如果要删除一个表里面的前 10000 行数据,有以下三种方法可以做到:
第一种,直接执行 delete from T limit 10000;
第二种,在一个连接中循环执行 20 次 delete from T limit 500;
第三种,在 20 个连接中同时执行 delete from T limit 500。
你会选择哪一种方法呢?为什么呢?
解析:
- 第二种相关好。将相对长的事务分成多次相对短的事务,则每次事务占用锁的时间相对较短,其他客户端在等待相应资源的时间也较短;
- 第一种 delete from T limit 10000; 单个语句占用时间长,锁的时间也比较长;而且大事务还会导致主从延迟;
- 第二种在 20 个连接中同时执行 delete from T limit 500,会人为造成锁冲突
3.4 悲观锁(Pessimistic Locking)
3.5 乐观锁
乐观锁不用借助数据库的锁机制,需要开发者自己去实现。在数据库操作时,想法很乐观,认为这次的操作不会导致冲突,因此在数据库操作时并不做任何的特殊处理,即不加锁,而是在进行事务提交时再去判断是否有冲突了。
3.5.1 乐观锁实现方式
- 使用版本字段(version)
`version` int(11) NOT NULL DEFAULT '1' COMMENT '版本号'
select (quantity,version) from goods where id=1;
update goods set quantity=quantity-1,version=version+1 where id=1 and version=#{version};
- 使用时间戳(Timestamp)
与使用 version 版本字段相似,同样需要给在数据表增加一个字段,字段类型使用 timestamp 时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则提交更新,否则就是版本冲突,取消操作。
3.5.2 乐观锁存在的问题
高并发场景下,只有一个线程可以修改成功,那么就会存在大量的失败。
4 死锁与解决方案
4.1 表锁死锁
产生原因
解决方案
这种死锁比较常见,是由于程序的 BUG 产生的,除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑,对于数据库的多表操作时,尽量按照相同的顺序进行处理,尽量避免同时锁定两个
4.2 行锁死锁
产生原因 1
如果在事务中执行了一条没有索引条件的查询,引发全表扫描,把行级锁上升为全表记录锁定(等价于表级锁),多个这样的事务执行后,就很容易产生死锁和阻塞,最终应用系统会越来越慢,发
解决方案 1
SQL 语句中不要使用太复杂的关联多表的查询;使用 explain 对 SQL 语句进行分析,对于有全表扫描和全表锁定的 SQL 语句,建立相应的索引进行优化。
产生原因 2
两个事务分别想拿到对方持有的锁,互相等待,于是产生死锁。
解决方案 2
在同一个事务中,尽可能做到一次锁定所需要的所有资源
4.3 死锁策略
- 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
- 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。
在 InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。
但是,我们又不可能直接把这个时间设置成一个很小的值,比如 1s。这样当出现死锁的时候,确实很快就可以解开,但如果不是死锁,而是简单的锁等待呢?所以,超时时间设置太短的话,会出现很多误伤。
所以,正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。
每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。
4.4 死锁检测
- 查看死锁日志
show engine innodb status\G 命令查看近期死锁日志信息。
- 查看锁状态变量
show status like'innodb_row_lock%‘ 命令检查状态变量,分析系统中的行锁的争夺情况
5 幻读
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?
begin;
select * from t where d=5 for update;
commit;
这个语句会命中 d=5 的这一行,对应的主键 id=5,因此在 select 语句执行完成后,id=5 这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行 commit 语句的时候释放。
由于字段 d 上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的 5 行记录上,会不会被加锁呢?
5.1 幻读是什么
如果只在 id=5 这一行加锁,而其他行的不加锁的话,会怎么样?
上述场景,session A 里执行了三次查询,分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同,都是 select * from t where d=5 for update,查所有 d=5 的行,使用的是当前读(能读到所有已经提交的记录的最新值),并且加上写锁。
幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
- 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。
- 上面 session B 的修改结果,被 session A 之后的 select 语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。
5.2 幻读有什么问题
首先是语义上的。
session A 在 T1 时刻就声明了,“我要把所有 d=5 的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。
其次,是数据一致性的问题。
锁的设计是为了保证数据的一致性。而这个一致性,不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。
- T1 时刻,id=5 这一行变成 (5,5,100),结果最终是在 T6 时刻正式提交的 ;
- 经过 T2 时刻,id=0 这一行变成 (0,5,5);
- 经过 T4 时刻,表里面多了一行 (1,5,5);
- 其他行跟这个执行序列无关,保持不变。
这样看,这些数据也没啥问题,但是我们再来看看这时候 binlog 里面的内容。
T2 时刻,session B 事务提交,写入了两条语句;
T4 时刻,session C 事务提交,写入了两条语句;T6 时刻,session A 事务提交,写入了 update t set d=100 where d=5 这条语句。
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/*所有d=5的行,d改成100*/
这个语句序列,不论是拿到备库去执行,还是以后用 binlog 来克隆一个库,这三行的结果,都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。也就是说,id=0 和 id=1 这两行,发生了数据不一致。
这个数据不一致到底是怎么引入的?
这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行,也就是 id=5 的这一行加锁”导致的。
那改,扫描过程中碰到的行,也都加上写锁。
即使把所有的记录都加上锁,还是阻止不了新插入的记录
5.3 如何解决幻读
产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。
间隙锁,锁的就是两个值之间的空隙。比如表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。
当执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还要同时加 7 个间隙锁,这样就确保了无法再插入新的记录。间隙锁和行锁合称 next-key lock。
间隙锁的引入,可能会导致同样的语句锁住更大的范围,引发死锁,影响并发度。
有没有更简单一点的处理方法呢?
间隙锁是在可重复读隔离级别下才会生效的。所以,如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row。
binlog 有两种常用的格式,一种是 statement(默认),一种是 row。statement 格式记录的是我们执行的 sql,而 row 格式记录的则是实际受影响的数据的变化前后值。