Mysql中事务操作
mysql中事务默认是隐式事务,执行insert、update、delete操作的时候,数据库自动开启事务、提交或回滚事务。
是否开启隐式事务是由变量autocommit
控制的。
所以事务分为隐式事务和显式事务。
隐式事务
事务自动开启、提交或回滚,比如insert、update、delete语句,事务的开启、提交或回滚由mysql内部自动控制的。
查看变量autocommit
是否开启了自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
autocommit
为ON表示开启了自动提交。
显式事务
事务需要手动开启、提交或回滚,由开发者自己控制。
2种方式手动控制事务:
方式1:
语法:
//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;
示例1:提交事务操作,如下:
mysql> create table test1 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
Empty set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
示例2:回滚事务操作,如下:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
可以看到上面数据回滚了。
我们把autocommit
还原回去:
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
方式2:
语法:
start transaction;//开启事务
//执行事务操作
commit|rollback;
示例1:提交事务操作,如下:
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
上面成功插入了2条数据。
示例2:回滚事务操作,如下:
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
上面事务中我们删除了
test1
的数据,显示删除了3行,最后回滚了事务。
savepoint关键字
在事务中我们执行了一大批操作,可能我们只想回滚部分数据,怎么做呢?
我们可以将一大批操作分为几个部分,然后指定回滚某个部分。可以使用savepoint
来实现,效果如下:
先清除test1
表数据:
mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from test1;
Empty set (0.00 sec)
演示savepoint
效果,认真看:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)
mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
从上面可以看出,执行了2次插入操作,最后只插入了1条数据。
savepoint
需要结合rollback to sp1
一起使用,可以将保存点sp1
到rollback to
之间的操作回滚掉。
只读事务
表示在事务中执行的是一些只读操作,如查询,但是不会做insert、update、delete操作,数据库内部对只读事务可能会有一些性能上的优化。
用法如下:
start transaction read only;
示例:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql> delete from test1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+------+
| a |
+------+
| 1 |
| 1 |
+------+
2 rows in set (0.00 sec)
只读事务中执行delete会报错。