事务控制和锁定语句
事务控制和锁定语句
MySQL 支持对 MyISAM 和MEMORY 存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级锁定，对InnoDB存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。
LOCK TABLES 和 UNLOCK TABLES
LOCAK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
UNLOCK TABLES 可以释放当前线程获得的任何锁定，当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定地表被隐含地解锁，具体语法如下
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITY}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITY}]
UNLOCK TABLES
下表给出了一个获得表锁和释放表锁地例子，其中 film_text表示获得 read 锁地情况，其他session更新该表记录会等待锁，film_text表释放锁以后，其他session 可以进行更新操作。其中session1 和 session2表示两个同时打开地sess，表格中的每一行表示同一时刻两个session地运行情况，后面地例子也是同样地情况。
| session_1 | session_2 |
|---|---|
| 获得表 film_text 地read锁： mysql> lock table film_text read; Query OK, 0 rows affected (0.00 sec) |
|
| 当前session 可以查询该表地记录 mysql> select * from film_text; … 5 rows in set (0.00 sec) |
其他 session 也可以查询该表地记录： mysql> select * from film_text; … 5 rows in set (0.00 sec) |
| 其他session 更新锁定表会等待获得锁： mysql> update film_text set title=‘update title’; 等等 |
|
| 释放锁： mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) |
等待 |
| session获得锁，更新操作完成： mysql> update film_text set title=‘update title’; Query OK, 5 rows affected (54.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 |
注意：LOCK TABLES/UNLOCK TABLES 有时也写为 LOCK TABLE/UNLOCK TABLE,两种写法含义一致。
事务控制
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来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，这是和Oracle 的事务管理明显不同的地方。如果应用是从oracle迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理。
- START TRANSACTION 或 BEGIN 语句可以开始一项新的事务。
- COMMIT和ROLLBACK 用来提交或者回滚事务
- CHAIN 和 RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新的事务，并且和刚才事务具有相同的事务隔离级别，RELEASE则会断开和客户端的连接。
- SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0,则设置之后的所有事物都需要通过明确的命令进行提交或者回滚。
如果只是对某些语句需要进行事务控制，则使用 START TRANSACTION语句开始一个事务比较方便，这样，事务结束之后可以自动回到自动提交的方式。如果希望所有的事物都不是自动提交的，那么通过修改AUTOCOMMIT来控制事务比较方便，这样不用在每个事物开始的时再在执行START TRANSACTION语句。
下表演示了使用 START TRANSACTION 开始的事务再提交后自动回到自动提交的方式；如果在提交时使用COMMIT AND CHAIN，那么会再提交后立即开始一个新的事物。
| session_1 | session_2 |
|---|---|
| 从表actor中查询actor_id=201的记录，结果为空： mysql> select * from actor where id=201; Empty set (0.00 sec) |
从表actor中查询actor_id=201的记录，结果为空： mysql> select * from actor where id=201; Empty set (0.00 sec) |
| 用start transaction 命令启动一个事务，往表actor 中插入一条记录，没有commit： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into actor(id) values (201); Query OK, 1 row affected (0.00 sec) |
|
| 查询表actor，结果仍然为空： mysql> select * from actor where id=201; Empty set (0.00 sec) |
|
| 执行提交： mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
| 再次查询表actor，可以查询到结果： mysql> select * from actor where id=201; … 1 row in set (0.00 sec) |
|
| 这个事务是按照自动提交执行的： mysql> insert into actor(id) values (202); Query OK, 1 row affected (0.01 sec) |
|
| 可以从actor表中查询到session_1 刚插入的数据：： mysql> select * from actor where id=202; … 1 row in set (0.00 sec) |
|
| 重新用start transaction启动一个事务： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) 往表actor插入一条数据： mysql> insert into actor (id) values (203); Query OK, 1 row affected (0.00 sec) 用 commit and chain命令提交： mysql> commit and chain; Query OK, 0 rows affected (0.01 sec) 此时自动开始一个新的事务： mysql> insert into actor (id) values (204); Query OK, 1 row affected (0.00 sec) |
|
| session_1 刚刚插入的记录无法看到： mysql> select * from actor where id=203; 1 row in set (0.00 sec) m mysql> select * from actor where id=204; Empty set (0.00 sec) |
|
| 用 commit命令提交： mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
| session_1 插入的新纪录可以看到： mysql> select * from actor where id=204; 1 row in set (0.00 sec) |
如果在锁表期间，用 start transaction 命令开始一个新的事物，则会造成一个隐式的 UNLOCK TABLES 被执行，如下：
| sesstion_1 | sesstion_2 |
|---|---|
| 从表 actor 中查询 id = 201 的记录为空： mysql> select * from actor where id=201; Empty set (0.00 sec) |
从表 actor 中查询 id = 201 的记录为空： mysql> select * from actor where id=201; Empty set (0.00 sec) |
| 对表 actor 加写锁： mysql> lock table actor write; Query OK, 0 rows affected (0.00 sec) |
|
| 对表actor的读操作被阻塞： mysql> select * from actor where id=201; 等待 |
|
| 插入一条记录： mysql> insert into actor (id) values (201); Query OK, 1 row affected (0.01 sec) |
等待 |
| 回滚刚才的记录： mysql> rollback; Query OK, 0 rows affected (0.00 sec) |
等待 |
| 用start transaction 重新开始一个事务： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) |
等待 |
| session_1 开始一个事务的时候，表锁被释放所，可以查询。 对lock方式加的表锁，不能通过 rollback 进行回滚。 |
因此，在同一个事物中，最好使用相同存储引擎的表，否则 ROLLBACK时需要对非事务类型的表进行特别的处理，因为 COMMIT,ROLLBACK 只能对事物类型的表进行提交和回滚。
通常情况下，只对提交的事务记录到二进制日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型的表的更新可以被复制到数据库（slave）中。
和Oracle的事务管理相同，所有DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交。
在事务中可以通过定义 SAVEPOINT,指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的SAVEPOINT，满足不同的条件时，回滚不同的SAVEPOINT 。需要注意的是，如果定义了相同名字的SAVEPOINT,则后面定义的 SAVEPOINT 会覆盖之前的定义，对于不再需要使用的SAVEPOINT,可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT,删除后的SAVEPOINT 不能再执行 ROLLBACK TO SAVEPOINT 命令。
下标的例子就是模拟回滚事务的一个部分，通过定义SAVEPOINT 来指定需要回滚的事务的位置。
| sesstion_1 | sesstion_2 |
|---|---|
| 从表actor中查询first_name='Simon’的记录，结果为空： mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
从表actor中查询first_name='Simon’的记录，结果为空： mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
| 启动一个事务，往表actor中插入一条记录： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into actor (id, first_name) values(301, ‘Simon’); Query OK, 1 row affected (0.00 sec) |
|
| 可以查询到刚才插入的记录： mysql> select * from actor where first_name=‘Simon’; … 1 row in set (0.00 sec) |
无法从 actor 表中查到 session_1 刚插入的记录： mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
| 定义 savepoint ，名称为 test: mysql> savepoint test; Query OK, 0 rows affected (0.00 sec) 插入一条记录 mysql> insert into actor (id, first_name) values(302, ‘Simon’); Query OK, 1 row affected (0.00 sec) 可以查询到两条记录： mysql> select * from actor where first_name=‘Simon’; … 2 rows in set (0.00 sec) |
|
| 仍然无法查询到结果： mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
|
| 回滚到刚才定义的 savepoint： mysql> rollback to test; Query OK, 1 row affected (0.00 sec) 只能从表actor查询出一条记录，因为第二条已经被回滚： mysql> select * from actor where first_name=‘Simon’; … 1 rows in set (0.00 sec) |
|
| 用 commit 命令提交： mysql> commit; Query OK, 0 rows affected (0.01 sec) 只能从 actor中查询到一条记录： mysql> select * from actor where first_name=‘Simon’; … 1 rows in set (0.00 sec) |
只能从 actor中查询到一条记录： mysql> select * from actor where first_name=‘Simon’; … 1 rows in set (0.00 sec) |
分布式事务的使用
MySQL从5.0.3版本开始支持分布式事务，当前分布式事务只支持InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。
分布式事务的原理
在MySQL中，使用分布式事务的应用程序涉及到一个或多个资源管理器和一个事务管理器。
-
资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM 管理的事务 。例如，多台MySQL数据库作为多台资源管理器或者几台MySQL服务器和几台Oracle服务器作为资源管理器。
-
事务管理器（TM）用于协调作为一个分布式事务一部分的事物。TM与管理每个事务的RMs进行通讯。在一个分布式事务中，各个单个事务均是分布式事务的“分支事务”，分布式事务和各分支通过一种命名方法进行标识。
MySQL执行 XA MySQL 时，MySQL服务器相当于一个用于管理分布式事务中的XA事务的资源管理器。与MySQL服务器连接的客户端相当于事务管理器。
要执行一个分布式事务，必须知道这个分布式事务涉及哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务，必须考虑任何组件或连接网络可能会出现故障。
用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。
- 在第一阶段中，所有的分支被预备好，即他们被TM告知要准备提交。通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
- 在第二阶段中，TM告知RMs是否要提交或回滚。如果在预备分支时，所有的分支指示他们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。
在有些情况下，一个分布式事务可能会使用一阶段提交。例如当一个事务管理器发现，一个分布式事务只由一个事务资源组成（即单一分支），则该资源可以被告知同时进行预备和提交。
分布式事务的语法
分布式事务（XA 事务）的SQL语法如下：
XA {START | BEGIN} xid [JOIN | RESUME]
XA START xid 用于启动一个带给定xid值的 XA事务。每个XA事务必须有一个唯一的xid值，因此该值当前不能被其他的XA事务使用。xid是一个XA事务标识符，用来唯一标识一个分布式事务。xid值由客户端提供，或由MySQL服务器生成。xid值包含1~3个部分：
xid: gtrid [, bqual [, formatID]]
- gtrid:是一个分布式事务标识符，相同的分布式事务应该使用相同的gtrid，这样可以明确知道 XA事务属于哪个分布式事务。
- bqual：是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual值必须是唯一的。
- formatID 是一个数字，用于标识由gtrid 和 bqual 值使用的格式，默认值是1.
下面其他XA语法中用到的xid值都必须和START操作使用的xid相同，也就是表示对这个启动的XA事务进行操作。
XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid
使事务进入PREPARE 状态，也就是两阶段提交的第一个提交阶段。
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid
这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段：分支事务被实际提交或者回滚。
XA RECOVER
XA RECOVER 返回当前数据库中处于PREPARE 状态的分支事务的详细信息。
分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。XA的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分布式事务、使事务进入准备阶段以及事务的实际提交回滚操作等。
下面例子演示了一个简单的分布式事务的执行，事务的内容是在BD1中插入一条记录，同时在DB2中更新一条记录，两个操作作为同一事物提交或者回滚。
| sesstion_1 in DB1 | sesstion_2 in DB2 |
|---|---|
| 在数据库DB1中启动一个分布式事务的一个分支事务，xid的gtrid 为 “test” ， bqual 为 “db1”： mysql> xa start ‘test’,‘db1’; Query OK, 0 rows affected (0.00 sec) 分支事务1 在表actor 中插入一条记录： mysql> insert into actor values (301, ‘Simon’, ‘Tom’); Query OK, 1 row affected (0.00 sec) 对分支事务 1 进行第一阶段的提交，进入 prepare状态： mysql> xa end ‘test’,‘db1’; Query OK, 0 rows affected (0.00 sec) mysql> xa prepare ‘test’,‘db1’; Query OK, 0 rows affected (0.00 sec) |
在数据库DB2中启动分布式事务 “test” 的另一个分支事务，xid 的 gtrid 为 “test”，bqual 为 “db2”： mysql> XA start ‘test’,‘db2’; Query OK, 0 rows affected (0.00 sec) 分支事务 2 在表 film_actor 中更新记录。 mysql> update film_actor set last_update=now(); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 对分支事务 2 进行第一阶段提交，进入 PREPARE状态： mysql> xa end ‘test’,‘db2’; Query OK, 0 rows affected (0.00 sec) mysql> xa prepare ‘test’,‘db2’; Query OK, 0 rows affected (0.01 sec) |
| 用 xa recover 命令查看当前分支事务状态： mysql> xa recover \G; *************************** 1. row formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb1 2. row *************************** formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb2 2 rows in set (0.00 sec) |
用 xa recover 命令查看当前分支事务状态： mysql> xa recover \G; *************************** 1. row formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb1 2. row *************************** formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb2 2 rows in set (0.00 sec) |
| 两个事物都进入准备提交阶段，如果之前遇到任何错误，都应该回滚所有的分支，以确保分布式事务的正确。 | 两个事物都进入准备提交阶段，如果之前遇到任何错误，都应该回滚所有的分支，以确保分布式事务的正确。 |
| 提交分支事务 1 ： mysql> xa commit ‘test’,‘db1’; Query OK, 0 rows affected (0.00 sec) 两个事物都达到准备提交阶段后，一旦开始进行提交操作，就需要确保全部的分支都提交成功。 |
提交分支事务 2： mysql> xa commit ‘test’,‘db2’; Query OK, 0 rows affected (0.01 sec) |
存在的问题
虽然MySQL支持分布式事务，但是仍存在一些问题。
在MySQL 5.5 之前的版本，如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以选择对分支事务进行提交或者回滚操作，但是即使选择提交事务，该事务也不会被写入BINLOG。这就存在一定的隐患，可能导致使用BINLOG 恢复时丢失部分数据。如果存在复制从库，则有可能导致主从数据库的数据不一致。以下演示了这个过程。
（1）从表actor 中查询 first_name='Simon’的记录，显示有一条：
mysql> select * from actor where first_name='Simon';
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 301 | Simon | Tom |
+----------+------------+-----------+
1 row in set (0.00 sec)
（2）启动分布式事务“test”，删除刚才查询的记录。
mysql> xa start 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> delete from actor where actor_id=301;
Query OK, 1 row affected (0.00 sec)
mysql> select * from actor where first_name='Simon';
Empty set (0.00 sec)
（3）完成第一阶段的提交，进入PREPARE状态：
mysql> xa end 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare 'test';
Query OK, 0 rows affected (0.01 sec)
（4）此时数据库异常终止，查询出错。
（5）启动数据库后分支事务依然存在。
mysql> xa recover \G;
*************************** 1. row ***************************
formatID: 1
gtrid_length: 4
bqual_length: 0
data: test
1 row in set (0.00 sec)
（6）表中记录并没有被删除。
mysql> select * from actor where first_name='Simon';
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 301 | Simon | Tom |
+----------+------------+-----------+
1 row in set (0.01 sec)
（7）可以在MySQL的数据库日志中看到分布式事务的处理情况，数据库启动的时候返现有一个prepare状态的事务，提示需要进行处理：
（8）可以进行提交或者回滚：
mysql> xa commit 'test';
Query OK, 0 rows affected (0.05 sec)
mysql> select * from actor where first_name='Simon';
Empty set (0.01 sec)
提交后，使用mysqlbinglog 查看 BINLOG，可以确认最后提交的这个分支事务并没有记录到BINLOG中，因为复制和灾难恢复都是依赖于BINLOG的，所以BINLOG的缺失会导致复制环境的不同步，以及使用BINLOG 恢复丢失部分数据。由于这个BUG的存在，在MySQL 5.7 之前，对于数据库实例死机，官方建议的是选择回滚 prepare事务。
此外，如果分支事务的客户端连接异常终止，例如执行 prepare之后，退出连接，那么数据库会自动回滚未完成的分支事务，但是这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务。如下：
| sesstion_1 | sesstion_2 |
|---|---|
| 从表 actor 中查询first_name=‘Simon’ 的记录为空： mysql> select * from actor where first_name=‘Simon’; Empty set (0.01 sec) |
从表 actor 中查询first_name=‘Simon’ 的记录为空： mysql> select * from actor where first_name=‘Simon’; Empty set (0.01 sec) |
| 启动分布式事务 test: mysql> xa start ‘test’; Query OK, 0 rows affected (0.00 sec) 往表插入一条数据： mysql> insert into actor values (301, ‘Simon’, ‘Tom’); Query OK, 1 row affected (0.00 sec) 事务结束： mysql> xa end ‘test’; Query OK, 0 rows affected (0.00 sec) 查询刚才插入的记录，显示错误（mysql8.0.16）： mysql> select * from actor where first_name=‘Simon’; ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state |
|
| 查询刚才插入的记录，显示结果为空： mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
|
| 完成第一阶段的提交，进入prepare状态： mysql> xa prepare ‘test’; Query OK, 0 rows affected (0.01 sec) |
|
| 查询分布式事务 “test” 状态： mysql> xa recover \G; *************************** 1. row *************************** formatID: 1 gtrid_length: 4 bqual_length: 0 data: test 1 row in set (0.00 sec) |
|
| sesstion_1 异常终止 | |
| sesstion_1 被回滚 | sesstion_1 异常中止后，分布式事务被回滚，sesstion_2 中无法查询到sesstion_1 插入的记录，如果此时sesstion_2存在分支事务并且被成功提交，则会导致分布式事务不完整 mysql> select * from actor where first_name=‘Simon’; Empty set (0.00 sec) |
而上面也已经提到，当发现部分分支已经提交成功，需要使用备份和BINLOG 来恢复数据的时候，那些 在 prepare 状态的分支事务因为并没有记录到BINLOG ，所以不能通过BINLOG进行恢复，在数据恢复后，将丢失这部分的数据。
在MySQL5.7 中，已经解决了XA事务的严格持久化问题，在sesstion 断开和实例崩溃的情况下，事物都不会自动回滚，同时在 XA PREPARE时，之前的事务信息就会被写入BINLOG并同步到备库。最终再由用户决定将悬挂事务回滚或者是提交。下面测试一下 XA事务在MySQL5.7 中的改进。
首先开启一个事务：
mysql> xa start 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into actor values (301, 'Simon', 'Tom');
Query OK, 1 row affected (0.00 sec)
mysql> xa end 'test';
Query OK, 0 rows affected (0.00 sec)
在MySQL 5.7 中，XA 事务在结束之后，提交之前，不允许进行查询：
mysql> select * from actor;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state
mysql> xa prepare 'test';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from actor;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the PREPARED state
此时查看BINLOG，可以看到执行 XA PREPARE 后，BINLOG已经有相应的记录
断开重新连接MySQL，可以看到，事务没有被自动回滚，可以手动进行回滚或提交：
mysql> xa recover \G;
*************************** 1. row ***************************
formatID: 1
gtrid_length: 4
bqual_length: 0
data: test
1 row in set (0.00 sec)
总之，MySQL的分布式事务还存在一些问题，在数据库或者应用异常的情况下，可能会导致分布式事务的不完整或者需要人工介入处理。如果需要使用分布式事务，建议尽量采用MySQL 5.1.7 或者更新的版本。
小结
事务控制和锁定是MySQL重要特点之一。
上一篇： [NIO.2] 第二十二篇 创建硬链接
下一篇： 苹果开发者账号个人升级公司