Discuss / SQL / 事务SQL语句执行失败,但是没有回滚

事务SQL语句执行失败,但是没有回滚

Topic source

九月-柚

#1 Created at ... [Delete] [Delete and Lock User]

廖老师,您好!

关于COMMIT,我故意将一条语句中的字段名写错,可还是没有回滚。

下面是我的测试代码,麻烦您帮我看一下:

mysql> SELECT * FROM account;
+----+---------+
| id | balance |
+----+---------+
|  1 |     499 |
|  2 |     501 |
+----+---------+
2 rows in set (0.00 sec)

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

mysql> UPDATE account SET balance = balance + 1 WHERE id = 1;       
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET bal = balance - 1 WHERE id = 1;       //故意将字段balance写成bal,有报错
ERROR 1054 (42S22): Unknown column 'bal' in 'field list'

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM account;
+----+---------+
| id | balance |
+----+---------+
|  1 |     500 |                  //但是第一条update语句还是执行了,没有回滚
|  2 |     501 |
+----+---------+
2 rows in set (0.00 sec)

mysql> BEGIN;UPDATE account SET balance = balance + 1 WHERE id = 1;UPDATE account SET bal = balance - 1 WHERE id = 2;COMMIT;   //将所有语句写在一起,结果依旧
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0
ERROR 1054 (42S22): Unknown column 'bal' in 'field list'

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM account;
+----+---------+
| id | balance |
+----+---------+
|  1 |     501 |
|  2 |     501 |
+----+---------+
2 rows in set (0.00 sec)

廖雪峰

#2 Created at ... [Delete] [Delete and Lock User]

回滚是rollback不是commit

九月-柚

#3 Created at ... [Delete] [Delete and Lock User]

抱歉,是我表述出错。

您文中有写到:

‘数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败,那么效果就和没有执行这些SQL一样,不会对数据库数据有任何改动。’

我在一个事务中写了两条update语句,第一条update语句执行后,第二条update语句执行报错。但是,第一条update语句执行后的结果仍然被写进了数据库。

廖雪峰

#4 Created at ... [Delete] [Delete and Lock User]

事务失败是程序检测到错误主动调用rollback

你忽略了错误继续调用commit那成功执行的sql就保存了

java里会失败是因为错误的sql会抛出异常,异常导致:

try {
    execute("insert ...");
    execute("insert ...");
    commit();
} catch (Exception e) {
    rollback();
}

PBC

#5 Created at ... [Delete] [Delete and Lock User]

请问楼主知道怎么写了吗?

不是java代码层面的

而是纯sql语句

PBC

#6 Created at ... [Delete] [Delete and Lock User]

https://www.runoob.com/mysql/mysql-transaction.html

这里有demo,大致可以意会到,

程序中会因为失败自动调用 rollback

而在sql中,需要认为手动 rollback 才行,操作者自己选择回滚,而不是看到错误,还继续commit,期待数据库系统给我们rollback


  • 1

Reply