MySQL笔记--事务控制和锁定语句

序

• MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级锁定，对InnoDB存储引擎的表进行行级锁定；
• 默认情况下，表锁和行锁都是自动获得的，不需要额外的命令；
• 但是在有的情况下，用户需要明确地进行锁表或者进行事物的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁表语句来完成。

LOCK TABLES 和 UNLOCK TABLES

• LOCK TABLES 可以锁定用于当前线程的表，如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止；
• UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁；
• LOCK TABLES/UNLOCK TABLES有时也写为LOCK TABLE/UNLOCK TABLE，两种写法含义一致；
• 语法：

LOCK TABLES
	tb1_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, 
	tb1_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

• 例子：

session_1	session_2
获得表film_text的READ锁定：mysql> lock table film_text read;
当前session可以查询表记录	其他session也可以查询该表的记录
	其他session更新锁定表会等待获得锁：mysql> update film_text set title='Test' where film_id=1001; 等待
释放锁：mysql> unlock tables;	等待
	session获得锁，更新操作完成：mysql> update film_text set title='Test' where film_id=1001;

事务控制

• MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务
• 语法：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

• 默认情况下，MySQL是自动提交的。如果需要明确的Commit和Rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理明显不同的地方；
• 如果应用是从Oracle数据库迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理。

1	START TRANSACTION或BEGIN语句可以开始一项新的事务
2	COMMIT和ROLLBACK用来提交或者回滚事务
3	CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接
4	SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

• 如果只是对某些语句需要进行事务控制，则使用START TRANSACTION语句开始一个事务比较方便，这样，事务结束之后可以自动回到自动提交的方式；
• 如果希望所有的事务都不是自动提交的，那么通过修改AUTOCOMMIT来控制事务比较方便，这样不用在每个事务开始的时候再执行START TANSACTION语句；
• 使用START TRANSACTION开始的事务在提交后自动回到自动提交的方式；
• 如果在提交时使用COMMIT AND CHAIN，那么会在提交后立即开始一个新的事务。
• START TRANSACTION和COMMIT AND CHAIN的使用例子：

session_1	session_2
从表中查询某个字段，结果为空	从表中查询某个字段，结果为空
用start transaction命令启动一个事务，往表中插入一条记录，没有commit
	查询表，结果仍然为空
执行提交commit
	再次查询表中字段，可以查询到结果
这个事务是按照自动提交执行的：插入一个字段
	可以从表中查询到session1刚刚插入的数据
重新用start transaction启动一个事务：mysql> start transaction;
往表中插入一条记录
用commit and chain命令提交：mysql> commit and chain
此时自动开始一个新的事务：mysql> insert into table_name() values()
	session1刚插入的记录无法看到
用commit命令提交
	session1插入的新记录可以看到

• 如果在锁表期间，用start transaction命令开始一个新事务，则会造成一个隐含的UNLOCK TABLES被执行，如下：

session1	session2
从表中查询一个字段的记录，结果为空	从表中查询一个字段的记录，结果为空
对表加写锁：mysql> lock table table_name write:
	对表的读操作被阻塞：mysql> select * from table_name where xx_id=xx;等待
插入一条记录	等待
回滚刚才的记录：mysql> rollback;	等待
用start transaction命令重新开始一个事务：mysql> start transaction;	等待
	session1开始一个事务时，表锁被释放，可以查询
	对lock方式加的表锁，不能通过rollback进行回滚

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