14.事务控制和锁定
存储引擎和锁： MyISAM和MEMORY存储引擎的表支持表级锁； BDB存储引擎的表支持页级锁； InnoDB存储引擎的表支持行级锁。默认情况下，表锁和行锁都是根据执行的语句自动获得和释放，不需要额外处理。用户也可根据业务需要来手动添加和释放锁，以保证事务的完整性。14.1 Lock table和Unlock table
Lock table可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所需的锁定为止。Unlock table可以释放当前线程获得的任何锁定。当前线程执行另一个lock tables时或与线程到服务器的连接被关闭时，被线程锁定的表将被解锁。语法：LOCK TABLES table_name [as alias] {read [local]|[low_priority] write};UNLOCK TABLES;例子：打开两个session,验证锁和所等待-- 在session1中添加read锁lock table emp read;-- 查询语句不需要锁，所以在session1中可以查询表,在session2中也可以查询表select * from emp;-- 修改语句需要行级排他锁，在session2中执行修改语句会产生所等待update emp set sal=3000 where empno=7788; -- Waiting for table metadata-- 在session1中释放锁,session2修改语句执行完成unlock tables;14.2 事务控制
MySQL事务控制语句包括：set autocommit,start transaction,commit,rollback等。语法：start transaction|begin [work] 开启一个新事务commit [work] [and [no] chain] [[no] release] 提交事务，chain事务提交后开启一个新事务，release事务提交后断开数据库连接rollback [work] [and [no] chain] [[no] release] 回退事务，chain事务回退后开启一个新事务，release事务回退后断开数据库连接set autocommit={0|1} 1设置自动提交，0不设置自动提交-- 查看autocommit参数select @@autocommit; -- 1 设置自动提交MySQL默认设置为自动提交(Oracle事务需要手动提交或回退)，不需要用户手动提交事务，同时也不能回退事务，所以修改和删除操作也特别当心。将参数autocommit设置为0，则所有的insert\update\delete操作完成后，再确认无误时手动或程序执行commit,确认有误时手动或程序执行rollback。MySQL函数和过程因为有begin，所以在一个事务中，不受autocommit参数限制，用户可在函数和过程中自行控制事务。在set autocommit=1自动提交模式下，可以使用start transaction来手动开启事务，在事务内insert\update\delete操作不会自动提交，用commit提交事务或rollback回退事务后，事务关闭，又回到自动提交模式，再进行insert\update\delete操作时将被自动提交。例子：打开两个session,验证手动开启事务与自动提交的关系-- 在Session1和Session2中同时查看一行记录select * from emp where empno=7788;-- 在session1中手动开启事务,并修改数据start transaction;update emp set sal=3500 where empno=7788;-- 在session1中查看数据，看到的是修改后的数据select * from emp where empno=7788;-- 在session2中查看数据，看到的仍是修改前的数据select * from emp where empno=7788;这是因为在session1中手动开启事务，事务内DML语句不会自动提交(即修改语句未提交)，系统设置的隔离级别设置为读已提交，所以在session2中看到的仍是修改前的数据。-- 在session1中手动提交事务commit;-- 在session2中查看数据，看到的是修改后的数据select * from emp where empno=7788;在锁表期间，开启事务将隐含执行锁释放操作unlock tables;
例子：-- 在session1中增加一个写锁lock table emp write;-- 在session2中查询数据将产生所等待select * from emp;-- 在session1中开启事务或提交事务、回退事务，session2中查询等待消失，查询成功start transaction;commit;rollback;在锁表期间，提交事务、回退事务不会释放锁。例子：-- 在session1中增加一个写锁lock table emp write;-- 在session2中查询数据将产生所等待select * from emp;-- 在session1中提交事务、回退事务，session2中查询仍旧等待commit;rollback;在MySQL中提交和回退只能处理支持事务的类型的表(InnoDB表)，同一个事务中尽量不要使用不同储存引擎的表。一般情况下，只对提交的事务记录二进制日志，如果一个事务中包含非事务类型的表，那么回滚操作也会被记录二进制日志。DDL语句支持隐式自动提交，不需要手动提交，也不能手动回滚。在事务内可以设置保存点savepoint，用于回滚事务内的一部分操作(rollback to savepoint)，提交操作不能使用保存点，即不能提交一部分操作；保存点必须设置名字或标签，多个保存点的名字或标签可以相同，相同名字或标签的保存点会进行覆盖，即只有最后一个相同名字或标签的保存点有效，所以尽量使用不同名称来命名保存点。例子：打开两个session,验证事务与保存点的关系-- 在Session1中开启事务，并进行修改操作start transaction;update emp set sal=4000 where empno=7788;-- 在Session1中设置保存点a1,并再进行修改操作savepoint a1;update emp set sal=sal+100 where empno=7369;-- 在Session2查看数据，发现两次更新的数据都看不到（因为没提交）select * from emp;-- 在Session1中回退事务到保存点a1并查看数据，即第二次的修改操作被回退，第一次的修改操作暂时保留rollback to savepoint a1;select * from emp;-- 在Session2查看数据，发现两次更新的数据都看不到（因为没提交）select * from emp;-- 在Session1中提交事务,即第一次的修改操作被永久写入库中commit;-- 在Session2查看数据，发现第一次的修改操作生效select * from emp;14.3 分布式事务的使用
InnoDB支持分布式事务，一个分布式事务会涉及多个行动，行动本身是事务性的，所有行动都必须一起成功，或者一起被回滚。 14.3.1 分布式事务的原理 分布式事务是一个事务的集合，即将多个事务组合成一个更大的事务，更大的事务满足事务的ACID特性，每个事务称为更大事务的分支事务。 分布式事务需要一个或多个资源管理器和一个事务管理器。 资源管理器(RM)用于提供通向事务资源的途径。数据库服务器是一种资源管理器，该管理器可以提交或回退由RM管理的事务。 事务管理器(TM)用于协调作为一个分布式事务一部分的事务。TM与管理每个事务的RMs进行通信，分布式事务和各分支通过一种命名方法进行标识。 在执行分布式事务时，MySQL服务器作为资源管理器，客户端作为事务管理器。 分布式事务的两阶段提交： 所有的分支被预备好，资源管理器(RM)告知事务管理器(TM)预备好了； 事务管理器(TM)告知资源管理器(RM)是否要提交或回退。 14.3.2 分布式事务的语法 开启分布式事务：XA {START|BEGIN} xid [JOIN|RESUME] xid：gtrid[,bqual[,formatID]] 说明：gtrid是一个分布式事务的标识符，每个分布式事务的所有分支事务有相同gtrid。 bqual是一个分支限定符，用于区分一个分布式事务内的多个分支事务，每个分支有不同的bqual。 formatID是一个数字，用于表示由gtrid+bqual生成的分支事务序号，默认值为1. 例子： xa start 'test','db1'; xa start 'test','db2'; 启动分布式事务，分布式事务名'test'，有2个分支事务，分别为'db1'和'db2'。 结束分支事务：XA END xid [[SUSPEND] FOR MIGRATE] 分支事务进入prepare状态： XA PREPARE xid 提交分支事务：XA COMMIT xid [ONE PHASE] 回退分支事务：XA ROLLBACK xid 返回当前数据库中处于prepare状态的分支事务的详细信息：XA RECOVER 14.3.3 存在的问题 如果分支事务在达到prepare状态时，数据库异常重启，分支事务提交没有写binlog，可能导致数据丢失，如果有主从库时，可能导致主从库不一致； 如果分支事务在prepare状态时,数据库异常，且不能启动，需要用备份和binlog来恢复数据，在prepare状态的分支事务未记录binlog，将导致部分数据丢失； 如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，其它分支可能已提交成功，不能保证事务的原子性。14.4 小结