一、rollback保存点的锁释放
在保存点后如果做了操作造成了行锁,是否rollback回来后还会保留操作时引起的行锁?
设计:在保存点后进行delete操作,查看rollback之前和之后的事务锁变化。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t5; ###保存点状态
+------+------+
| id | num |
+------+------+
| 2 | aa |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
mysql> delete from t5 where id=2; ###更改操作
Query OK, 1 row affected (0.00 sec)
mysql> rollback to a; ####回滚到保存点时
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t5; ###rollback后数据还原到事务保存点状态,即撤销delete操作
+------+------+
| id | num |
+------+------+
| 2 | aa |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
show engine innodb status;
rollback之前
------------
TRANSACTIONS
------------
Trx id counter 18044
Purge done for trx's n:o < 18041 undon:o < 0 state: running but idle
History list length 566
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 18037, not started
MySQL thread id 272, OS thread handle0x7f61312b0700, query id 79768 localhost root init
show engine innodb status
---TRANSACTION 18039, ACTIVE 17 sec
2 lock struct(s), heap size 360, 3 rowlock(s), undo log entries 1 ###delete造成锁形成
MySQL thread id 274, OS thread handle0x7f60bd82d700, query id 79767 localhost root cleaning up
Trx read view will not see trx with id>= 18040, sees < 18040
Rollback之后
------------
TRANSACTIONS
------------
Trx id counter 18046
Purge done for trx's n:o < 18045 undon:o < 0 state: running but idle
History list length 567
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 18037, not started
MySQL thread id 272, OS thread handle0x7f61312b0700, query id 79771 localhost root init
show engine innodb status
---TRANSACTION 18045, ACTIVE 5 sec
MySQL thread id 274, OS thread handle0x7f60bd82d700, query id 79770 localhost root cleaning up
Trx read view will not see trx with id>= 18046, sees < 18046
###锁已经被释放
结论:
Rollback释放了savepoint后delete操作的行锁。
二、回滚保存点只撤销本事务保存点后的操作
回滚保存点后,是否撤销了保存点后其他的事务对数据进行修改?
设计:设置保存点,事务二对no_index表进行update操作,然后回滚保存点,比较回滚前后数据变化。
set tx_isolation='read_committed';
事务一
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint modi;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from no_index; ###事务二对no_index表操作前
+------+------+------+
| id | fir | sec |
+------+------+------+
| 1 | aa | bb |
| 3 | b | c |
| 4 | c | d |
| 5 | e | f |
| 7 | e | q |
| 8 | cd | qq |
| 11 | a | b |
+------+------+------+
7 rows in set (0.00 sec)
mysql> select * from no_index; ###事务二对no_index表操作后
+------+------+------+
| id | fir | sec |
+------+------+------+
| 1 | aa | b |
| 3 | b | c |
| 4 | c | d |
| 5 | e | f |
| 7 | e | q |
| 8 | cd | qq |
| 11 | a | b |
+------+------+------+
7 rows in set (0.00 sec)
mysql> rollback to modi;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from no_index; ####回滚到保存点后数据没有发生变化
+------+------+------+
| id | fir | sec |
+------+------+------+
| 1 | aa | b |
| 3 | b | c |
| 4 | c | d |
| 5 | e | f |
| 7 | e | q |
| 8 | cd | qq |
| 11 | a | b |
+------+------+------+
7 rows in set (0.00 sec)
事务二
mysql> update no_index set sec='b' whereid='1'; ###对表进行update操作
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback to modi; ###可以看出保存点依附于事务中
ERROR 1305 (42000): SAVEPOINT modi does notexist
结论:
1.保存点回滚只是撤销本事务保存点后的操作,对保存点后其他事务的操作不凑效。
2.保存点的声明和定义只在本事务中凑效,而且当本事务commit或rollback后,保存点也不复存在。
转载于:https://blog.51cto.com/10574662/1688446