[MySQL 之四] 事务控制与锁定语句

  MySQL 支持对 MyISAM 和 MEMORY 存储引擎的表进行表级锁定，对 BDB 存储引擎的表进行页级锁定，对 InnoDB 存储引擎的表进行行级锁定。

  默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但有些情况下，为了确保事务的完整性，需要显式锁表和控制事务。

1、表锁

  使用以下命令来锁定表：

LOCK TABLES    # 锁定用于当前线程的表
LOCK TABLE tablename {read|write}   # 显式指定要对哪个表加锁，以及是读锁还是写锁

UNLOCK TABLES  # 释放当前线程锁定的表
UNLOCK TABLE tablename  # 释放对某个表的锁定

当一个客户端会话 session1 对表加了读锁时，不影响另一个客户端会话 session2 的读，但是会阻塞写，除非 session1 释放锁，否则一致阻塞等待下去。

session1 释放锁之后，session2 的写得以顺利被执行

同样，当 session1 加了写锁时，session2 将无法读写，一直阻塞直到 session1 释放锁。

2、事务控制

  相关语句如下：

# 1、启动一个事务
START TRANSACTION | BEGIN

# 2、所有 SQL 执行成功，提交事务；中间有 SQL 执行失败，回滚事务
COMMIT | ROLLBACK

# 设置每执行一条 SQL 时是否自动提交，默认为是
SET AUTOCOMMIT={0|1}

（1）事务启动

【实例演示】

1、session1 启动了一个事务，并且插入了一条数据，但是尚未 commit；session2 查询不到尚未 commit 的数据

2、session1 提交事务；session2 再查询能查到数据

3、session1 再插入一条数据，由于未启动事务，且默认为自动提交，所以插入之后 session2 马上能查询到数据

4、有时不想每次 COMMIT 之后都要重新执行启动事务语句，则可以使用 COMMIT AND CHAIN

 **Session1** 提交了事务之后启动了一个新的事务，之后再次插入一条记录；**Session2** 只能看到最后一次事务提交后的记录，新的尚未提交的记录看不到

**Session1** 提交对 204 记录的插入；**Session2** 再次查询能看到记录了

（2）自动提交

关于设置 SQL 自动提交，按情况处理：

• 若只是对某些语句进行事务控制，则使用 START TRANSACTION 语句开始一个事务比较好，因为事务结束后可自动切换到自动提交模式
• 若希望所有事务都不是自动提交的，则通过修改 AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始时都要先执行 START TRANSACTION

自动提交选项默认为打开，如果关闭，并执行写操作，则表现跟启动事务再写相同

PS. 不建议将自动提交关闭，因为这样可能导致长事务，mysql 使用 MVVC 机制，即从事务启动时保存数据的不同视图来实现不同的隔离级别，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

（3）事务与表锁

  在表锁定期间，如果用 start transaction 开启一个事务，就是隐式地执行 unlock tables，即释放表锁。

  为了不影响使用表锁的引擎，在一个事务中，最好不要使用不同的存储引擎的表。

（4）savepoint

    有的时候希望在某些情况下回滚事务时只回滚一部分，而不是全部回滚，可以使用 savepoint（保存点），但是提交事务不允许只提交一部分（回滚到 savepoint 的那部分事务不在此范围内）。

1、session1 启动事务，插入两条记录，一条在保存点之前，一条在保存点之后；session2 两条记录都看不到

2、session1 回滚到保存点，在查询记录只有一条，然后提交事务；session2 在事务提交后查询到保存点之前的那条记录

3、分布式事务

  分布式事务只支持 InnoDB 引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的，要么全部成功完成，否则回滚。

（1）分布式事务的原理

  在 MySQL 中，使用分布式事务的应用程序设计一个或多个资源管理器和一个事务管理器。

• 资源管理器（RM）：用于提供通向事务资源的途径。数据库服务器是一种资源管理器，该管理器可以提交或回滚由 RM 管理的事务。

• 事务管理器（TM）：用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通信。

  分布式事务分为两阶段：

• 第一阶段（prepare）：所有的分支被预备好。即它们被 TM 告知要准备提交。通常，这意味着用于管理分支的每个 RM 会记录对于被稳定保存的分值的行动。分支只是它们是否可以这么做，这些结果被用于第二阶段。

• 第二阶段（commit/rollback）：TM 告知 RMs 是否要提交或回滚。如果在预备分支时，所有的分支指示它们能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

（2）语法

启动事务

xa {start|begin} xid

xid 的组成有三部分：

• gtrid：分布式事务标识符，相同的分布式事务使用相同的 gtrid。
• bqual：分支限定符，对每个分支事务来说，该值必须是唯一的。
• formatID：一个数字，标识由 gtrid 和 bqual 值使用的格式，默认是 1 。
  
  

第一阶段，完成后进入 PREPARE 状态

xa end xid
xa prepare xid

第二阶段

xa commit xid
xa rollback xid

提交和回滚具体的分支事务。

xa recover  # 返回当前数据库中处于 PREPARE 状态的分支事务的详细信息

【实例演示】

（3）缺陷

  MySQL 的分布式事务在正式 commit/rollback 之前，如果数据库发生异常需要重启服务器，则重启后事务可以继续 commit/rollback，但是这些事务不会写到 binlog 中，存在一定隐患，如果用这些 binlog 做数据恢复或主从同步，会导致数据不一致。

  默认情况下，binlog是不开启的

  可以通过 --log-bin[=file_name] 选项启动，或者在 my.cnf 中配置

  配置完重启才能生效，再查看系统参数，已经打开

  现在来模拟一下分布式事务在 commit/rollback 之前数据库异常重启，导致重启后的 commit/rollback 不记入 binlog 的情况。

  执行事务之前有一条数据，事务启动后，删除该条数据，并进入 prepare 状态

  这时数据库发生异常重启，客户端重连，显式分布式事务依然存在，事务第一阶段中删除的数据并没有被真正删除。

  客户端提交事务，数据被真正删除。

  用 mysqlbinlog 查看 binlog，发现该事务的提交并没有被记录到 binlog 中，因为复制和灾备恢复都是依赖于 binlog，所以 binlog 的缺失会导致环境的不同步，以及使用 binlog 恢复丢失部分数据。

  而正常情况下有记录事务 commit 的 binlog 如下：

  上述只是对只有一个分支事务的分布式事务而言，如果是有多个分支事务的分布式事务，当分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果此时分支事务以及执行到 prepare 状态，那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。

  如果分支事务在执行到 prepare 状态时，数据库异常，且不能再正常启动，需要使用备份和 binlog 来恢复数据，那么那些在 prepare 状态的分支事务因为并没有记录到 binlog，所以不能通过 binlog 进行恢复，在数据库恢复后，将丢失这部分的数据。