SQL 事务控制和锁定语句文档格式.docx

SQL 事务控制和锁定语句文档格式.docx

《SQL 事务控制和锁定语句文档格式.docx》由会员分享，可在线阅读，更多相关《SQL 事务控制和锁定语句文档格式.docx（21页珍藏版）》请在冰豆网上搜索。

表14-1一个获得表锁和释放表锁的简单例子

session_1

session_2

获得表film_text的READ锁定

mysql>

locktablefilm_textread;

QueryOK,0rowsaffected（0.00sec）

当前session可以查询该表记录

selectfilm_id,titlefromfilm_textwherefilm_id=1001;

+---------+------------------+

|film_id|title|

|1001|ACADEMYDINOSAUR|

1rowinset（0.00sec）

其他session也可以查询该表的记录

其他session更新锁定表会等待获得锁：

updatefilm_textsettitle='

Test'

wherefilm_id=1001;

等待

释放锁

unlocktables;

Session获得锁，更新操作完成：

QueryOK,1rowaffected（1min0.71sec）

Rowsmatched:

1Changed:

1Warnings:

0

有关表锁的使用，将在后面的章节中进行介绍，读者可以参见“20.2Myisam表锁”一节以获得更详细的信息。

事务控制

MySQL通过SETAUTOCOMMIT、STARTTRANSACTION、COMMIT和ROLLBACK等语句支持本地事务，具体语法如下。

STARTTRANSACTION|BEGIN[WORK]

COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]

ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]

SETAUTOCOMMIT={0|1}

默认情况下，MySQL是自动提交（Autocommit）的，如果需要通过明确的Commit和Rollback来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理明显不同的地方。

如果应用是从Oracle数据库迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理。

STARTTRANSACTION或BEGIN语句可以开始一项新的事务。

COMMIT和ROLLBACK用来提交或者回滚事务。

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

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

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

如表14-2所示的例子演示了使用STARTTRANSACTION开始的事务在提交后自动回到自动提交的方式；

如果在提交的时候使用COMMITANDCHAIN，那么会在提交后立即开始一个新的事务。

表14-2STARTTRANSACTION和COMMITANDCHAIN的使用例子

从表actor中查询actor_id=201的记录，结果为空：

select*fromactorwhereactor_id=201;

Emptyset（0.00sec）

用starttransaction命令启动一个事务，往表actor中插入一条记录，没有commit：

starttransaction;

insertintoactor（actor_id,first_name,last_name）values（201,'

Lisa'

'

Tom'

）;

QueryOK,1rowaffected（0.00sec）

查询表actor，结果仍然为空：

执行提交：

commit;

QueryOK,0rowsaffected（0.04sec）

再次查询表actor，可以查询到结果：

selectactor_id,last_namefromactorwhereactor_idin（201,202）;

+----------+-----------+

|actor_id|last_name|

|201|Tom|

这个事务是按照自动提交执行的：

insertintoactor（actor_id,first_name,last_name）values（202,'

Lan'

QueryOK,1rowaffected（0.04sec）

可以从actor表中查询到session1刚刚插入的数据。

|202|Lan|

2rowsinset（0.00sec）

重新用starttransaction启动一个事务：

往表actor中插入一条记录：

insertintoactor（actor_id,first_name,last_name）values（203,'

TT'

用commitandchain命令提交：

commitandchain;

QueryOK,0rowsaffected（0.03sec）

此时自动开始一个新的事务：

insertintoactor（actor_id,first_name,last_name）values（204,'

Mou'

session1刚插入的记录无法看到：

selectactor_id,last_namefromactorwherefirst_name='

;

|178|MONROET|

|203|TT|

4rowsinset（0.00sec）

用commit命令提交：

QueryOK,0rowsaffected（0.06sec）

session1插入的新记录可以看到：

|204|Mou|

5rowsinset（0.00sec）

如果在锁表期间，用starttransaction命令开始一个新事务，会造成一个隐含的unlocktables被执行，如表14-3所示。

表14-3starttransaction导致的unlocktables

对表actor加写锁：

locktableactorwrite;

对表actor的读操作被阻塞：

selectactor_id,last_namefromactorwhereactor_id=201;

插入一条记录

回滚刚才的记录：

rollback;

用starttransaction命令重新开始一个事务：

session1开始一个事务时，表锁被释放，可以查询：

1rowinset（17.78sec）

对lock方式加的表锁，不能通过rollback进行回滚。

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

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

和Oracle的事务管理相同，所有的DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交。

在事务中可以通过定义SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。

对于复杂的应用，可以定义多个不同的SAVEPOINT，满足不同的条件时，回滚不同的SAVEPOINT。

需要注意的是，如果定义了相同名字的SAVEPOINT，则后面定义的SAVEPOINT会覆盖之前的定义。

对于不再需要使用的SAVEPOINT，可以通过RELEASESAVEPOINT命令删除SAVEPOINT，删除后的SAVEPOINT，不能再执行ROLLBACKTOSAVEPOINT命令。

如表14-4所示的例子就是模拟回滚事务的一个部分，通过定义SAVEPOINT来指定需要回滚的事务的位置。

表14-4模拟回滚事务

从表actor中查询first_name=’Simon’的记录，结果为空：

select*fromactorwherefirst_name='

Simon'

启动一个事务，往表actor中插入一条记录：

QueryOK,0rowsaffected（0.02sec）

insertintoactor（actor_id,first_name,last_name）values（301,'

可以查询到刚插入的记录：

|301|Tom|

无法从actor表中查到session1刚插入的记录：

定义savepoint，名称为test：

savepointtest;

继续插入一条记录：

insertintoactor（actor_id,first_name,last_name）values（302,'

Cof'

可以查询到两条记录：

|302|Cof|

仍然无法查询到结果：

回滚到刚才定义的savepoint：

rollbacktosavepointtest;

只能从表actor中查询到第一条记录，因为第二条已经被回滚：

QueryOK,0rowsaffected（0.05sec）

只能从actor表中查询到第一条记录：

只能从actor表中查询到session1插入的第一条记录：

分布式事务的使用

MySQL从5.0.3开始支持分布式事务，当前分布式事务只支持InnoDB存储引擎。

一个分布式事务会涉及多个行动，这些行动本身是事务性的。

所有行动都必须一起成功完成，或者一起被回滚。

一十四.1.1分布式事务的原理

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

·

资源管理器（RM）用于提供通向事务资源的途径。

数据库服务器是一种资源管理器。

该管理器必须可以提交或回滚由RM管理的事务。

例如，多台MySQL数据库作为多台资源管理器或者几台Mysql服务器和几台Oracle服务器作为资源管理器。

事务管理器（TM）用于协调作为一个分布式事务一部分的事务。

TM与管理每个事务的RMs进行通讯。

一个分布式事务中各个单个事务均是分布式事务的“分支事务”。

分布式事务和各分支通过一种命名方法进行标识。

MySQL执行XAMySQL时，MySQL服务器相当于一个用于管理分布式事务中的XA事务的资源管理器。

与MySQL服务器连接的客户端相当于事务管理器。

要执行一个分布式事务，必须知道这个分布式事务涉及到了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。

根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或回滚。

要管理一个分布式事务，必须要考虑任何组件或连接网络可能会故障。

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

在第一阶段，所有的分支被预备好。

即它们被TM告知要准备提交。

通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。

分支指示是否它们可以这么做。

这些结果被用于第二阶段。

在第二阶段，TM告知RMs是否要提交或回滚。

如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被