MySQL事务控制与锁定语句

MySQL事务控制与锁定语句

事务

事务通常包含一系列更新操作，这些更新操作是一个不可分割的逻辑工作单元。如果事务成功执行，那么该事务中所有的更新操作都会成功执行，并将执行结果提交到数据库文件中，成为数据库永久的组成部分。如果事务中某个更新操作执行失败，那么事务中的所有更新操作均被撤销。

简言之：事务的更新操作要么都执行，要么都不执行，这个特征叫做事务的原子性。

lock table和unlock table

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

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

Syntax:
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

===============session1===============
表student READ锁定<br
mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)                                                           
mysql> select * from student;
+------+-------+--------+
|id    |name   |  gender|
+------+-------+--------+
|2     | alice | F      |   
+------+-------+--------+ 

mysql> select * from student;
+------+-------+--------+
|id    |name   |  gender|
+------+-------+--------+
|2     | alice | F      |
+------+-------+--------+ 

==========打开新的会话session2==================
mysql> update student set name="Bob" where id=2;

中间会一直等待
==============session1==================
mysql> unlock tables;
Query OK, 0 rows affected (0.34 sec) |                                                  

=================session2==============
更新完成
mysql> update student set name="Bob" where id=2;
Query OK, 1 row affected (26.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务控制

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

Syntax:
START TRANSACTION [WITH CONSISTENT SNAPSHOT]
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

默认情况下，MySQL是自动提交(autocommit)的，如果需要通过明确的commit和rollback来提交和回滚事务，就需要通过明确事务控制命令来开始事务

• START TRANSACTION或BEGIN语句可以开始一项新的事务。

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

• CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事务。RELEASE则会断开和客户端的连接。

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

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

==================START TRANSACTION使用===============
mysql> select * from student;
+------+------+--------+
| id   | name | gender |
+------+------+--------+
|    2 | Bob  | F      |
+------+------+--------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(1,'Alice','M');
Query OK, 1 row affected (0.00 sec)

mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | Bob   | F      |
|    1 | Alice | M      |
+------+-------+--------+
2 rows in set (0.00 sec)
=================COMMIT AND CHAIN使用===============
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(3,'John','M');
Query OK, 1 row affected (0.00 sec)

mysql> commit and chain;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(4,'lili','M');
Query OK, 1 row affected (0.00 sec)

mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | Bob   | F      |
|    1 | Alice | M      |
|    3 | John  | M      |
|    4 | lili  | M      |
+------+-------+--------+
4 rows in set (0.00 sec)

SAVEPOINT回滚指定事务

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

#开始一个事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
#插入一条记录
mysql> insert into student values(5,'wang','F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | Bob   | F      |
|    1 | Alice | M      |
|    3 | John  | M      |
|    4 | lili  | M      |
|    5 | wang  | F      |
+------+-------+--------+
5 rows in set (0.00 sec)

#定义savepoint,名称为test
mysql> savepoint test;
Query OK, 0 rows affected (0.00 sec)
#继续插入一条记录
mysql> insert into student values(6,'xu','F');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | Bob   | F      |
|    1 | Alice | M      |
|    3 | John  | M      |
|    4 | lili  | M      |
|    5 | wang  | F      |
|    6 | xu    | F      |
+------+-------+--------+
6 rows in set (0.00 sec)
#回滚到刚才定义的savepoint
mysql> rollback to savepoint test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | Bob   | F      |
|    1 | Alice | M      |
|    3 | John  | M      |
|    4 | lili  | M      |
|    5 | wang  | F      |
+------+-------+--------+
5 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

START TRANSACTION导致的UNLOCK TABLES

session1:

mysql> create table test(id int);
Query OK, 0 rows affected (0.35 sec)

mysql> lock table test write;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

session2:
mysql> select * from test;
查询被阻塞.

开始一个新的事务
session1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1开始一个新的事务时,表锁被释放.
Session2:
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (1 min 15.37 sec)