为什么要显式地在sql中使用回滚?

I'm using PostgreSQL 9.3

我使用PostgreSQL 9.3

I have one misunderstanding about transactions and how they work. Suppose we wrapped some SQL operator within a transaction like the following:

我对交易及其运作方式有一个误解。假设我们在一个事务中封装了一些SQL操作符，如下所示:

BEGIN;
   insert into tbl (name, val) VALUES('John', 'Doe');
   insert into tbl (name, val) VALUES('John', 'Doee');
COMMIT;

If something goes wrong the transaction will automatically be rolled back. Taking that into account I can't get when should we use ROLLBACK explicitly? Could you get an example when it's necessary?

如果出现错误，事务将自动回滚。考虑到这一点，我不知道什么时候应该显式地使用回滚?有必要的话，你能举个例子吗?

2 个解决方案

#1

In PostgreSQL the transaction is not automatically rolled back on error.

在PostgreSQL中，事务不会在错误时自动回滚。

It is set to the aborted state, where further commands will fail with an error until you roll the transaction back.

它被设置为中止状态，在此状态下，进一步的命令将以错误失败而失败，直到您回滚事务。

Observe:

观察:

regress=> BEGIN;
BEGIN
regress=> LOCK TABLE nosuchtable;
ERROR:  relation "nosuchtable" does not exist
regress=> SELECT 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
regress=> ROLLBACK;
ROLLBACK

This is important, because it prevents you from accidentally executing half a transaction. Imagine if PostgreSQL automatically rolled back, allowing new implicit transactions to occur, and you tried to run the following sequence of statements:

这很重要，因为它可以防止您意外地执行半个事务。想象一下，如果PostgreSQL自动回滚，允许出现新的隐式事务，并试图运行以下语句序列:

BEGIN;
INSERT INTO archive_table SELECT * FROM current_tabble;
DELETE FROM current_table;
COMMIT;

PostgreSQL will abort the transaction when it sees the typo current_tabble. So the DELETE will never happen - all statements get ignored after the error, and the COMMIT is treated as a ROLLBACK for an aborted transaction:

PostgreSQL将在看到错误current_tabble时中止事务。因此，删除将永远不会发生——所有的语句都在错误之后被忽略，而提交被当作是中止事务的回滚:

regress=> BEGIN;
BEGIN
regress=> SELECT typo;
ERROR:  column "typo" does not exist
regress=> COMMIT;
ROLLBACK

If it instead automatically rolled the transaction back, it'd be like you ran:

如果它自动回滚事务，就像你在运行:

BEGIN;
INSERT INTO archive_table SELECT * FROM current_tabble;
ROLLBACK; -- automatic
BEGIN; -- automatic
DELETE FROM current_table;
COMMIT; -- automatic

... which, needless to say, would probably make you quite upset.

…不用说，这可能会让你很难过。

#2

Other uses for explicit ROLLBACK are manual modification and test cases:

显式回滚的其他用途是手工修改和测试用例:

Do some changes to the data (UPDATE, DELETE ...).
对数据做一些更改(更新、删除…)。
Run SELECT statements to check results of data modification.
运行SELECT语句检查数据修改的结果。
Do ROLLBACK if results are not as expected.
如果结果不是预期的，请回滚。

In Postgres DB you can do this even with DDL statements (CREATE TABLE, ...)

在Postgres DB中，即使使用DDL语句(CREATE TABLE，…)，也可以这样做。

#1