MySQL事务控制和锁机制

Ramya ·

更新时间:2024-11-13

· 904 次阅读

摘要

本文基于MySQL5.7为基础，讨论与数据库事务和锁的相关内容。

锁机制

根据加锁的范围，MySQL里面的锁可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁能够对整个库实例进行加锁。

加锁的语法：

FLUSH TABLES WITH READ LOCK;

解锁的语法：

UNLOCK TABLES;

全局锁的典型使用场景是，做全库逻辑备份。应用全局锁做逻辑备份有以下问题：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

MySQL官方逻辑备份工具mysqldump，以下参数代表不通的实现方式：

--lock-all-tables，在整个转储期间获取全局锁定来实现的。适合全库逻辑备份。

--lock-tables，在转储之前锁定所有要转储的表。适合多表逻辑备份。

--single-transaction，导数据之前就会启动一个事务，来确保拿到一致性视图，因此需要将事务隔离模式设置为REPEATABLE READ。适合REPEATABLE READ的隔离级别，并且表支持事务。

表锁

对一个或者多个表进行加锁。

加锁的语法：

LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

解锁的语法：

UNLOCK TABLES;

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证安全性。

行锁

行锁，是现阶段InnoDB引擎支持的锁，因此，常常伴随着事务一同出现。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

	读锁	写锁
读锁	兼容	冲突
写锁	冲突	冲突

事务控制 事务特性

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成。 一致性：事务必须始终保持系统处于一致的状态。 隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。 持久性：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 隔离级别 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。 读已提交是指，一个事务提交之后，它做的变更才会被其他事务看到。 **可重复读（默认）**是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。 事务应用

MySQL通过SET AUTOCOMMIT, START TRANSACTION, COMMIT和ROLLBACK等语句支持本地事务。

语法：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

默认情况下，mysql是autocommit的，如果需要通过明确的commit和rollback来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务。
START TRANSACTION或BEGIN语句可以开始一项新的事务。
COMMIT和ROLLBACK用来提交或者回滚事务。

手动提交与自动提交

如果我们只是对某些语句需要进行事务控制，则使用START TRANSACTION开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果我们希望我们所有的事务都不是自动提交的，那么通过修改AUTOCOMMIT来控制事务比较方便，这样不用在每个事务开始的时候再执行START TRANSACTION。

time	session_1	session_2
–	mysql> SELECT * FROM t1; Empty set	mysql> SELECT * FROM t1; Empty set
–	mysql> start transaction; Query OK, 0 rows affected mysql> insert into t1 values(‘1’,1); Query OK, 1 row affected
–		mysql> SELECT * FROM t1; Empty set
–	mysql> commit; Query OK, 0 rows affected 手动提交	mysql> SELECT * FROM t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 1 ｜ 1 ｜ ±----±----+ 1 row in set
–	mysql> insert into t1 values(‘2’,2); Query OK, 1 row affected 自动提交
–		mysql> SELECT * FROM t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 1 ｜ 1 ｜｜ 2 ｜ 2 ｜ ±----±----+ 2 row in set

开始一个事务，会造成一个隐含的unlock tables被执行：

time	session_1	session_2
–	mysql> select * from t1; Empty set	mysql> select * from t1; Empty set
–	mysql> lock table t1 write; Query OK, 0 rows affected
–		mysql> select * from t1; waiting…
–	mysql> insert into t1 values(‘1’,1); Query OK, 1 row affected	waiting…
–	mysql> rollback; Query OK, 0 rows affected	waiting…
–	mysql> start transaction; Query OK, 0 rows affected 开始一个事务时，表锁被释放。	waiting…
–		Waiting ending mysql> select * from t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 1 ｜ 1 ｜ ±----±----+ 1 row in set 对于已经提交的事务（rollback之前是自动提交），不能通过 rollback进行回滚。

可重复读

读已提交与可重复读是目前在生产环境上应用比较多的隔离级别。我们先来看看InnoDB的可重复读是如何实现的。

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。可能你会觉得有的不可思议，如果整个库的数据是G级别，甚至是T级别的话，要拷贝这么多数据岂不是很慢，可是平常执行事务的速度感觉很快啊，这是怎么回事呢？

实际是InnoDB实现快照的方式所致，看看快照”在MVCC里是怎么工作的。

InnoDB每一个事务都有一个唯一的id，叫做transaction id，它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

在这里插入图片描述

图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id 为25的事务更新的，因此它的row trx_id也是25。

图中虚线框里是同一行数据的4个版本，当前最新版本是V4，k的值是22，它是被transaction id 为25的事务更新的，因此它的row trx_id也是25。实际上，图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

在这里插入图片描述

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

如果trx_id属于已提交事务，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据可见；如果trx_id属于未开始事务，表示这个版本是由将来启动的事务生成的，是肯定不可见的；如果trx_id属于未提交事务集合，那就包括两种情况: 若trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；若trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

分析下事务A的语句返回的结果，为什么是k=1。

在这里插入图片描述

现在事务A要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务A查询语句的读数据流程是这样的：

找到(1,3)的时候，判断出row trx_id=101，比高水位大，处于红色区域，不可见；

接着，找到上一个历史版本，一看row trx_id=102，比高水位大，处于红色区域，不可见；

再往前找，终于找到了（1,1)，它的row trx_id=90，比低水位小，处于绿色区域，可见。

更新逻辑

事务B的update语句，如果按照一致性读，好像结果不对哦？

事务B的视图数组是先生成的，之后事务C才提交，不是应该看不见(1,2)吗，怎么能算出(1,3)？

在这里插入图片描述

这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

其实，除了update语句外，select语句如果加锁(加上lock in share mode 或 for update)，也是当前读。

所以，能算出(1,3)。

读已提交

下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化，就是图中的read view框。

在这里插入图片描述

在读已提交的隔离级别下，所有已提交的版本的数据都能被读到，不会提供一致性读。read view是在get的时候才被创建的，所以能都到所有已提交的数据。

行锁

如果两个事务对同一行数据进行更新，由于InnoDB会对该行数据进行加锁，所以只能一个事务获得锁，另一个事务必须等待。

time	session_1	session_2
–	mysql> select * from t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 1 ｜ 1 ｜ ±----±----+ 1 row in set	mysql> select * from t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 1 ｜ 1 ｜ ±----±----+ 1 row in set
	update t1 set a = a+1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0
		update t1 set a = a+1; waiting…
	commit; Query OK, 0 rows affected
		waiting ending update t1 set a = a+1; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0
	mysql> select * from t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 2 ｜ 1 ｜ ±----±----+ 1 row in set
		mysql> select * from t1; ±----±----+ ｜ a ｜ b ｜ ±----±----+ ｜ 3 ｜ 1 ｜ ±----±----+ 1 row in set