事务控制和锁定语句

     mysql 支持对myisam和memory存储引擎的表进行表级锁定，对bdb存储引擎的表进行行页级锁定，对innodb存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令，但是在有的情况下，需要明确的进行锁表或者进行事务的控制，以确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

   一，lock table 和unlock table

    lock tables 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，知道可以获取到所有锁定为止。

    unlock table可以释放当前线程获得的任何锁定。当前线程执行另一个lock tables 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含的解锁。

   

    下面我们演示一个例子：表t16获得read锁的情况，其他session更新该表记录会等待锁，t16表释放锁以后，其他session可以进行更新操作，其中session1和session2表示两个同时打开的session，表格中的每一行表示同一时刻两个session的运行状况。

mysql> create table t16(id int(11),name varchar(63),age int(3));
Query OK, 0 rows affected (0.31 sec)

mysql> desc t16;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(63) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t16(id,name,age)values(1,'fzy1',1),(1,'fzy1',1),(3,'fzy3',3);
Query OK, 3 row affected (0.13 sec)
 

  

获得表锁和释放标锁事例

session_1	session_2
获得表t16的read锁定 mysql> lock table t16 read; Query OK, 0 rows affected (0.00 sec)
当session可以查询该表记录 mysql> select * from t16 where id=1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | fzy1 | 1 | +------+------+------+ 1 row in set (0.00 sec)	其他session也可以查询该表的记录 mysql> select * from t16 where id=1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | fzy1 | 1 | +------+------+------+ 1 row in set (0.00 sec)
	其他session更新锁定的表会等待获得锁 Database changed mysql> update t16 set age=3 where id=1;   （一直处于光标闪烁，即等待状态）
释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)	等待
	session 获得锁，更新操作完成 Query OK, 1 row affected (1 min 38.38 sec) Rows matched: 1 Changed: 1 Warnings: 0

二，事务控制

   mysql 通过set autocommit ,start transaction ,commit ,rollback等语句支持本地事务。默认情况下，mysql是自动提交(autocommit)的，如果需要明确的commit和rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，这是和oracle的事务管理明显不同的地方。

    （1）start transaction 或 begin语句可以开始一项新的事务

    （2）commit 和rollback用来提交或者回滚事务。

     （3）chain和release字句分别用来定义在事务提交或回滚之后的操作，chain会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接。

    （4）set autocommit可以修改当前连接的提交方式，如果设置了set autocommit=0，则设置之后的所有事务读需要通过明确的命令进行提交或者回滚。

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

   

     下面，我们演示一个使用start transcation开始事务在提交后自动回到自动提交的方式。如果在提交的时候使用commit and chain那么在提交后立即开始一个新的事务。

start transation和commit and chain事例

session_1	session_2
从表t16中查询id=4，结果为空 mysql> select * from t16 where id=4; Empty set (0.00 sec)	从表t16中查询id=4，结果为空 mysql> select * from t16 where id=4; Empty set (0.00 sec)
用start rransaction命令启动一个事务，往表t16中插入一条记录，没有commit mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t16(id,name,age)values(4,'fzy4',4); Query OK, 1 row affected (0.01 sec)
查询id=4,有结果 mysql> select * from t16 where id=4; +------+------+------+ | id | name | age | +------+------+------+ | 4 | fzy4 | 4 | +------+------+------+ 1 row in set (0.00 sec)	查询表t16 id=4,结果仍为空 mysql> select * from t16 where id=4; Empty set (0.00 sec)
执行提交事务 mysql> commit; Query OK, 0 rows affected (0.04 sec)
	再次查询 mysql> select * from t16 where id=4; +------+------+------+ | id | name | age | +------+------+------+ | 4 | fzy4 | 4 | +------+------+------+ 1 row in set (0.00 sec)
这个事务是按照自动提交执行的 mysql> insert into t16(id,name,age)values(5,'fzy5',5); Query OK, 1 row affected (0.04 sec)
	查询插入的记录 mysql> select * from t16 where id=5; +------+------+------+ | id | name | age | +------+------+------+ | 5 | fzy5 | 5 | +------+------+------+ 1 row in set (0.01 sec)
重新用start transaction启动一个事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) ##插入语句 mysql> insert into t16(id,name,age)values(6,'fzy6',6); Query OK, 1 row affected (0.00 sec) ##提交并立即开启一个新事务（和刚才具有相同的隔离级别） mysql> commit and chain; Query OK, 0 rows affected (0.04 sec) ##插入数据 mysql> insert into t16(id,name,age)values(7,'fzy7',7); Query OK, 1 row affected (0.00 sec)
	查询 mysql> select * from t16; +------+------+------+ | id | name | age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | fzy2 | 2 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | | 5 | fzy5 | 5 | | 6 | fzy6 | 6 | +------+------+------+ 6 rows in set (0.00 sec) 发现id=7并没有这条记录
再提交事务 mysql> commit; Query OK, 0 rows affected (0.05 sec)
	查询 mysql> select * from t16; +------+------+------+ | id | name | age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | fzy2 | 2 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | | 5 | fzy5 | 5 | | 6 | fzy6 | 6 | | 7 | fzy7 | 7 | +------+------+------+ 7 rows in set (0.01 sec) id=7这条记录已经插入

    如果在锁表期间，用start transaction命令开始一个新事务，会造成一个隐含的unlock tables被执行

  

start transaction导致的unlock tables

session_1	session_2
从表t16中查询id=9的记录，结果为空 mysql> select * from t16 where id=9; Empty set (0.00 sec)	从表t16中查询id=9的记录，结果为空 mysql> select * from t16 where id=9; Empty set (0.00 sec)
对表t16加写锁 mysql> lock table t16 write; Query OK, 0 rows affected (0.00 sec)
	对表t16的读操作被阻塞 mysql> select * from t16; 等待
插入一条记录 mysql> insert into t16(id,name,age)values(9,'fzy9',9); Query OK, 1 row affected (0.06 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 t16; +------+------+------+ | id | name | age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | fzy2 | 2 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | | 5 | fzy5 | 5 | | 6 | fzy6 | 6 | | 7 | fzy7 | 7 | | 8 | fzy8 | 8 | | 9 | fzy9 | 9 | +------+------+------+ 9 rows in set (0.00 sec) 对lock方式加的表锁，不能通过rollback进行回滚

   

   因此，在同一个事务中，最好不使用不同的存储引擎，否则rollback时需要对非事务类型的表进行特别的处理。因为，commit ,rollback 只能对事务类型的表进行提交和回滚。

   通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型表的更新可以被复制到从数据库(slave)中。

  注：所有的ddl语句是不能回滚的，并且部分ddl语句会造成隐式的提交。

     在事务中可以通过定义savepoint，指定回滚事务部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的savepoint,满足不同的条件时，回滚不同的savepoint,需要注意的是，如果定义了相同名字的savepoint，则后面定义的savepoint会覆盖之前的定义。对于不需要使用的savepoint，可以通过release savepoint命令删除。

    下面我们模拟回滚事务：

模拟回滚事务

session_1	session_2
从表t16中查询id=10,为空 mysql> select * from t16 where id=10; Empty set (0.00 sec)	从表t16中查询id=10，为空 mysql> select * from t16 where id=10; Empty set (0.00 sec)
启动一个事务，往表里插入数据id=10 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t16(id,name,age)values(10,'fzy10',10); Query OK, 1 row affected (0.00 sec)
可以查到插入的数据 mysql> select * from t16 where id=10; +------+-------+------+ | id | name | age | +------+-------+------+ | 10 | fzy10 | 10 | +------+-------+------+ 1 row in set (0.00 sec)	不能查到插入的数据 mysql> select * from t16 where id=10; Empty set (0.00 sec)
定义savepoint ,名称为test_savepoint1，接着再插入一条数据 mysql> savepoint test_savepoint; Query OK, 0 rows affected (0.00 sec) mysql> insert into t16(id,name,age)values(11,'fzy11',11); Query OK, 1 row affected (0.00 sec)
可以查询到插入的2条记录 mysql> select * from t16 where id=11; +------+-------+------+ | id | name | age | +------+-------+------+ | 11 | fzy11 | 11 | +------+-------+------+ 1 row in set (0.00 sec	仍不能查询到插入的2条记录 mysql> select * from t16 where id=10 or id=11; Empty set (0.00 sec)
回滚刚才定义的savepoint mysql> rollback to savepoint test_savepoint; Query OK, 0 rows affected (0.00 sec)
只能从表中查询到第一条记录 mysql> select * from t16 where id=11 or id=10; +------+-------+------+ | id | name | age | +------+-------+------+ | 10 | fzy10 | 10 | +------+-------+------+ 1 row in set (0.00 sec)	仍不能查询到刚插入的记录 mysql> select * from t16 where id=10 or id=11; Empty set (0.00 sec)
提交 mysql> commit; Query OK, 0 rows affected (0.05 sec)
只能查到一条记录 mysql> select * from t16 where id=11 or id=10; +------+-------+------+ | id | name | age | +------+-------+------+ | 10 | fzy10 | 10 | +------+-------+------+ 1 row in set (0.00 sec)	能查到一条记录 mysql> select * from t16 where id=11 or id=10; +------+-------+------+ | id | name | age | +------+-------+------+ | 10 | fzy10 | 10 | +------+-------+------+ 1 row in set (0.00 sec)

 三，分布式事务（xa）的使用

       当前分布式事务只支持innodb存储引擎，一个分布式事务会涉及多个行动，这些行动本身是事务性的，所有行动都必须一起成功完成，或者一起被回滚。

  1，分布式事务原理

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

     （1）资源管理器(rm)用于提供通向事务资源的途径。数据库服务器是一种资源管理器，该管理器必须可以提交或回滚由rm管理的事务。如：多台mysql数据库作为多台资源管理器或者几台mysql服务器和几台oracle服务器作为资源管理器。

     （2）事务管理器（tm）用于协调作为一个分布式事务一部分的事务。tm与管理每个事务的rm s进行通信。在分布式事务中，各个单个事务均是分布式事务的“分支事务”。分布式事务和各个分支通过一种命名方法进行标示。

     mysql 执行xa时，mysql服务器相当于一个用于管理分布式事务中的xa事务的资源管理器。与mysql服务器连接的客户端相当于事务管理器

    要执行一个分布式事务，必须知道这个分布式事务涉及了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚。根据每个资源管理器报告的有关执行情况的内同，这些分支事务，必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务，必须要考虑任何组件或连接网络可能会出现故障。

    用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。

    （1）在第一阶段，所有的分支被预备好，即他们被tm告知要准备提交。通常，这意味着用于管理分支的每个rm会记录对于被稳定保存的分支的行动。分支指示是否他们可以这么做。这些结果被用于第二阶段。

      （2）在第二阶段，tm告知rm s是否要提交或回滚，如果在预备分支时，所有的分支指示他们将能够提交，则所有的分支被告知要提交。如果在预备是，有任何分支指示他将不能提交，则所有分支被告知回滚。