MySQL中开启一次事务，只能进行一次回滚操作

MySQL中开启一次事务，只能进行一次回滚操作

最近在学习数据库事务的时候，偶然间发现，在开启事务之后，发生误操作，进行了回滚，如果不提交并再次开启事务的话，假如再次误操作，将无法进行回滚操作。例如：

mysql> select * from student;
 +-----+--------+------+---------------------+-------+
 | sno | sname  | ssex | sbirthday           | class |
 +-----+--------+------+---------------------+-------+
 | 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
 +-----+--------+------+---------------------+-------+
 6 rows in set (0.00 sec)
 
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> delete from student where sname='李军';
 Query OK, 1 row affected (0.06 sec)
 
 mysql> select * from student;
 +-----+--------+------+---------------------+-------+
 | sno | sname  | ssex | sbirthday           | class |
 +-----+--------+------+---------------------+-------+
 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
 +-----+--------+------+---------------------+-------+
 5 rows in set (0.00 sec)
 
 mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from student;
 +-----+--------+------+---------------------+-------+
 | sno | sname  | ssex | sbirthday           | class |
 +-----+--------+------+---------------------+-------+
 | 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
 +-----+--------+------+---------------------+-------+
 6 rows in set (0.00 sec)
 
 mysql> delete from student where sno=101;
 Query OK, 1 row affected (0.01 sec)
 
 mysql> select * from student ;
 +-----+--------+------+---------------------+-------+
 | sno | sname  | ssex | sbirthday           | class |
 +-----+--------+------+---------------------+-------+
 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
 +-----+--------+------+---------------------+-------+
 5 rows in set (0.00 sec)
 
 mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from student; 
 +-----+--------+------+---------------------+-------+
 | sno | sname  | ssex | sbirthday           | class |
 +-----+--------+------+---------------------+-------+
 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
 +-----+--------+------+---------------------+-------+
 5 rows in set (0.00 sec)
 
 mysql> 

所以在开启一次事务后，如果误操作，进行回滚，必须要先提交事务，并且重新开启一个新的事务，再进行后续的操作。