一、LOCK TABLE 和 UNLOCK TABLE
lock tables 锁定当前线性表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
unlock tables 释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时， 或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁
一个获得表锁和释放表锁的简单例子
| session_1 | session_2 |
|
获得表film_text的Read锁定 lock table film_text read |
|
|
当前session可以查询该表纪录 select id,title from film_text where id = 1; +-------+----------- id. title +--------+---------- 1 标签 |
其他session也可以查询该表的纪录 select id,title from film_text where id = 1; +-------+----------- id. title +--------+---------- 1 标签
|
|
其他session更新锁定表会等待获得锁 update film_text set title='lalamei' where id=1; ......等待 |
|
| 释放锁：unlock tables |
......等待 |
|
session 获得锁，更新操作完成 完成 |
二、事务控制
默认情况下，MySQL 是自动提交(Autocommit)的，如果需要通过明确的 Commit 和 Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务 。
* start transaction 或 begin语句可以开始一项新的事务
* commit 和 rollback 用来提交或者回滚事务
* chain 和 release 字句分别用来定义在事务提交或者回滚之后的操作，chain会立即启动一个新事物，并且和刚才的事物具有相同的隔离级别，release则会断开和客户端连接
* set autocommit 可以修改当前连接的提交方式，如果设置了set autocommit=0,则设置之后的所有事务都需要通过明确的命令进行提交或者回滚
演示使用start transaction 开始的事务在提交后自动回到自动提交的方式；如果在提交的时候使用commit and chain，那么会在提交后立即开启一个新的事务。
start transaction和commit and chain 的使用例子
| session_1 | session_2 |
|
从表actor中查询actor_id=201的记录，结果为空： mysql> select * from actor where actor_id = 201; Empty set (0.00 sec) |
从表actor中查询actor_id=201的记录，结果为空： mysql> select * from actor where actor_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(actor_id,first_name,last_name) values(201,'lisa','tom'); Query OK, 1 row affected (0.00 sec) |
查询表actor，结果仍然为空： mysql> select * from actor where actor_id=201; Empty set (0.00 sec)
|
|
执行提交： mysql> commit; Query OK, 0 rows affected (0.00 sec) |
|
|
再次查询表actor，可以查询到结果： mysql> select * from actor where actor_id=201; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 201 | lisa | tom | +----------+------------+-----------+ 1 row in set (0.00 sec) |
|
|
这个事务是按照自动提交执行的： mysql> insert into actor(actor_id,first_name,last_name) values(202,'lisa','lan'); Query OK, 1 row affected (0.01 sec) |
|
|
可以从actor表中查询到session1刚刚插入的数据。 mysql> select * from actor where actor_id in (201,202); +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 201 | lisa | tom | | 202 | lisa | lan | +----------+------------+-----------+ 2 rows in set (0.00 sec) |
|
|
重新用start transaction 启动一个事务： mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
往表actor中插入一条纪录： mysql> insert into actor(actor_id,first_name,last_name) values(203,'lisa','tt'); Query OK, 1 row affected (0.00 sec)
用commit and chain命令提交： mysql> commit and chain; Query OK, 0 rows affected (0.00 sec)
此时自动开始一个新的事务： mysql> insert into actor (actor_id,first_name,last_name) values(204,'Lisa','Mou'); Query OK, 1 row affected (0.00 sec) |
|
|
session1刚插入的纪录无法查看： mysql> select * from actor where first_name = 'lisa'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 201 | lisa | tom | | 202 | lisa | lan | | 203 | lisa | tt | +----------+------------+-----------+ 3 rows in set (0.00 sec) |
|
|
用commit命令提交： mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
|
session1插入的新记录可以看到： mysql> select * from actor where first_name = 'lisa'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 201 | lisa | tom | | 202 | lisa | lan | | 203 | lisa | tt | | 204 | lisa | mou | +----------+------------+-----------+ 4 rows in set (0.00 sec) |
如果在锁表期间，用 start sransaction 命令开始一个新事务，会造成一个隐含的unlock tables 被执行
start transaction 导致的 unlock tables
| session_1 | session_2 |
|
从表actor中查询actor_id=301的记录，结果为空： mysql> select * from actor where actor_id = 301; Empty set (0.00 sec) |
从表actor中查询actor_id=301的记录，结果为空： mysql> select * from actor where actor_id = 301; 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 actor_id=301; ....等待 |
|
|
插入一条记录 mysql> insert into actor(actor_id,first_name,last_name) values(301,'Lisa','Tom'); Query OK, 1 row affected (0.00 sec) |
....等待 |
|
回滚刚才的纪录： mysql> rollback; Query OK, 0 rows affected (0.00 sec) |
....等待 |
|
用start transaction 命令重新开始一个事务： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) |
....等待 |
|
session1 开始一个事务时，表锁被释放，可以查询： mysql> select * from actor where actor_id=301; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 301 | Lisa | Tom | +----------+------------+-----------+ 1 row in set (14 min 58.07 sec) 对lock方式加的表锁，不能通过rollback进行回滚。 |
/*
* 1、commit、rollback只能对事务类型的表进行提交和回滚
* 2、通常对提交的事务纪录到二进制文件中
* 3、所有的DDL语句是不能回滚的，部分DDL语句会造成隐式提交
*/
在事务中可以通过定义 SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务 的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚 不同的 SAVEPOINT。需要注意的是，如果定义了相同名字的 SAVEPOINT，则后面定义的 SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT，删除后的 SAVEPOINT，不能再执行 ROLLBACK TO SAVEPOINT 命令。
模拟回滚事务
| session_1 | session_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(actor_id,first_name,last_name) values(501,'Simon','Tom'); Query OK, 1 row affected (0.00 sec) |
|
|
可以查询到刚刚插入的纪录： mysql> select * from actor where first_name = 'Simon'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 501 | Simon | Tom | +----------+------------+-----------+ 1 row in set (0.00 sec)
|
无法从actor表中查询到session1刚插入的纪录： 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(actor_id,first_name,last_name) values(502,'Simon','Cof'); Query OK, 1 row affected (0.00 sec) |
|
|
可以查询到两条纪录 mysql> select * from actor where first_name = 'Simon'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 501 | Simon | Tom | | 502 | Simon | Cof | +----------+------------+-----------+ 2 rows in set (0.00 sec) |
仍然无法查询到结果： mysql> select * from actor where first_name = 'Simon'; Empty set (0.00 sec)
|
|
回滚到刚才定义的savepoint： mysql> rollback to savepoint test; Query OK, 0 rows affected (0.00 sec) |
|
|
只能从表 actor 中查询到第一条记录，因为第二条记录已经回滚： mysql> select * from actor where first_name = 'Simon'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 501 | Simon | Tom | +----------+------------+-----------+ 1 row in set (0.00 sec) |
仍然无法查询到结果： mysql> select * from actor where first_name = 'Simon'; Empty set (0.00 sec) |
|
用commit命令提交： mysql> commit; Query OK, 0 rows affected (0.00 sec) |
|
|
只能从表 actor 中查询到第一条记录： mysql> select * from actor where first_name = 'Simon'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 501 | Simon | Tom | +----------+------------+-----------+ 1 row in set (0.00 sec) |
只能从表 actor 中查询到 session1 插入的第一条记录： mysql> select * from actor where first_name = 'Simon'; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 501 | Simon | Tom | +----------+------------+-----------+ 1 row in set (0.00 sec) |
三、分布式事务的使用
简介
1、分布式事务只支持innoDB存储引擎
2、一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚
分布式事务的原理
分布式事务语法
XA {START|BEGIN} xid [JOIN|RESUMF]
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 PREPARE xid
使事务进入 PREPARE 状态，也就是两阶段提交的第一个提交阶段
这两个命令用来提交或者回滚具体的分支事务。也就是两阶段提交的第二个提交阶段， 分支事务被实际的提交或者回滚。
XA RECOVER
XA RECOVER返回当前数据库中处于PREPARE 状态的分支事务的详细信息。
分布式的关键在于？
如何确保分布式事务的完整性，
在某个分支出现问题时的故障解决。
演示了一个简单的分布式事务的执行，事务的内容是在 DB1 中插入一条记录， 同时在 DB2 中更新一条记录，两个操作作为同一事务提交或者回滚。
分布式事务例子
| session_1 in DB1 | session_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(actor_id,first_name,last_name) values(601,'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 中更新了21条记录： mysql> update film_actor set last_update=now() where actor_id=178; Query OK, 21 rows affected (0.00 sec) Rows matched: 21 Changed: 21 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; *************************** *************************** formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb1 1 rows in set (0.00 sec) |
用 xa recover 命令查看当前分支事务状态： mysql> xa recover \G; *************************** *************************** formatID: 1 gtrid_length: 4 bqual_length: 3 data: testdb2 1 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.00 sec)
|
| 两个事务都到达准备提交阶段后，一旦开始进行提交操作，就需要确保全部的分支都提交成功。 | |
存在的问题1
条件：
分支事务在达到prepare状态时，数据库异常重新启动，重启以后可以继续执行事务进行提交回滚的操作。
问题：
提交事务没有写binlog，存在隐患，可能导致使用binlog恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。
存在问题二
如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果 此时分支事务已经执行到 prepare 状态，那么这个分布式事务的其他分支可能已经成功提交， 如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。