事务
事务通常包含一系列更新操作,这些更新操作是一个不可分割的逻辑工作单元。如果事务成功执行,那么该事务中所有的更新操作都会成功执行,并将执行结果提交到数据库文件中,成为数据库永久的组成部分。如果事务中某个更新操作执行失败,那么事务中的所有更新操作均被撤销。
简言之:事务的更新操作要么都执行,要么都不执行,这个特征叫做事务的原子性。
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)