序

  • 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只能对事务类型的表进行提交和回滚。