3.4. Transactions

3.4. Transactions

3.4 事务

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

对于所有的数据库系统概念来说，事务是基石。它将多个步骤绑定为一个操作，要么都执行，要么都不执行。步骤之间的中间状态对于其他事务不可见，而且如果在事务执行过程中失败，那么会回滚所有步骤。

 

For example, consider a bank database that contains balances for various customer accounts, as well as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from Alice's account to Bob's account. Simplifying outrageously, the SQL commands for this might look like:

例如，假设这里有一个包含各种账户余额的银行的数据库。假设我们想要记录从Alice账户支付$100.00给Bob账户的操作。简化点的步骤中，所执行的SQL语句可能如下所示：

 

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

UPDATE branches SET balance = balance - 100.00

WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');

UPDATE accounts SET balance = balance + 100.00

WHERE name = 'Bob';

UPDATE branches SET balance = balance + 100.00

WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

 

The details of these commands are not important here; the important point is that there are several separate updates involved to accomplish this rather simple operation. Our bank's officers will want to be assured that either all these updates happen, or none of them happen. It would certainly not do for a system failure to result in Bob receiving $100.00 that was not debited from Alice. Nor would Alice long remain a happy customer if she was debited without Bob being credited. We need a guarantee that if something goes wrong partway through the  operation, none of the steps executed so far will take effect. Grouping the updates into a transaction gives us this guarantee. A transaction is said to be atomic: from the point of view of  other transactions, it either happens completely or not at all.

在这里，命令的具体细节并不是重点，重点是，我们需要执行一系列的update操作去实现我们的目的。银行人员需要确认，这些update要么全部都执行了，要么全部都不执行。不能Bob收到钱了，却没有扣Alice的钱。我们需要能够保证在执行update过程中，万一出现问题，这些update都不要执行。而将这些update操作绑定到一个事务中提供了这个保证。也就是事务中的原子性：对于其他事务来说，事务的执行要么全部成功，要么都不执行。

 

We also want a guarantee that once a transaction is completed and acknowledged by the database system, it has indeed been permanently recorded and won't be lost even if a crash ensues shortly thereafter. For example, if we are recording a cash withdrawal by Bob, we do not want any chance that the debit to his account will disappear in a crash just after he walks out the bank door. A transactional database guarantees that all the updates made by a transaction are logged in permanent storage (i.e., on disk) before the transaction is reported complete.

当然也需要保证，在事务执行完成后，可以在数据库中永久的留存，即使在事务执行后发生了宕机，也不会丢掉事务执行的结果。例如，我们不希望，因为系统宕机，导致Bob的取款行为未做记录。而事务型数据库保证了事务在返回成功之前，事务所做的所有更改都已经永久的保存了下来（例如，存储到了磁盘上）。

 

Another important property of transactional databases is closely related to the notion of atomic updates: when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. For example, if one transaction is busy totalling all the branch balances, it would not do for it to include the debit from Alice's branch but not the credit to Bob's branch, nor vice versa. So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously.

事务型数据库另一个重要的特性，也有原子性有关：当多事务并行运行时，互相之间无法看到未完成的更改。例如，如果一个事务在统计所有账户余额，那么在这期间，对于Alice或者Bob账户的余额变化就不应统计在内。所以，事务的全部成功或根本不执行，不仅仅体现在留存的永久性上，也应体现在对其他事务的可见性上。一个事务的更改，只有在全部完成后（即事务完成），才可以对其他事务可见。

 

In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands. So our banking transaction would actually look like:

PostgreSQL中，通过在SQL命令中使用BEGIN和COMMIT命令设置。所以上例中的银行事务应该如下：

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

-- etc etc

COMMIT;

If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that Alice's balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled.

如果，在事务执行过程中我们又不想提交了（或许是因为我们刚刚指导Alice余额不足了），那么我们可以使用ROLLBACK来回滚操作，这样所有的update就会被取消。

 

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

实际上，PostgreSQL视每个SQL语句以事务方式执行。如果你未使用BEGIN命令，那么每个语句会有一个隐式的BEGIN和（如果执行成功）COMMIT。使用BEGIN开头和COMMIT结尾的语句，通常称为事务块。

 

Note

注：

Some client libraries issue BEGIN and COMMIT commands automatically, so that you get the effect of transaction blocks without asking. Check the documentation the interface you are using.

一些客户端会自动发出BEGIN和COMMIT命令，以使你默认获得事务块的效果。可通过查看你在用客户端的文档以确认。

 

It's possible to control the statements in a transaction in a more granular fashion through the use of savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the rest. After defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with ROLLBACK TO. All the transaction's database changes between defining the savepoint and rolling back to it are discarded, but changes earlier than the savepoint are kept.

可以通过使用保存点（savepoints）以更细的粒度控制事务中的语句执行。保存点（savepoints）使得在提交事务中部分语句的时候，可以忽略另一部分语句。在使用SAVEPOINT命令定义保存点之后，你可以使用ROLLBACK TO命令回滚到该保存点。介于保存点与回滚命令之间的语句将会忽略，而在保存点之前的变更会保留。

 

After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times. Conversely, if you are sure you won't need to roll back to a particular savepoint again, it can be released, so the system can free some resources. Keep in mind that either releasing or rolling back to a savepoint will automatically release all savepoints that were defined after it.

设置的保存点，可以多次回滚。当然，如果你确认不需要某个保存点了，也可以删除该保存点，以释放系统资源。但要注意，释放或者回滚到某一保存点，那么该保存点之后定义的保存点将同时被自动释放。

 

All this is happening within the transaction block, so none of it is visible to other database sessions. When and if you commit the transaction block, the committed actions become visible as a unit to other sessions, while the rolled-back actions never become visible at all.

这些都发生在事务块中，但对其他会话不可见。如果提交事务，则对所有会话可见，如果回滚了事务，则变更不可见。

 

Remembering the bank database, suppose we debit $100.00 from Alice's account, and credit Bob's account, only to find later that we should have credited Wally's account. We could do it using savepoints like this:

回到之前的银行数据库，假设我们从Alice的账户中扣除$100.00到Bob账户中，之后却发现其实应该将钱转到Wally账户中。我们可以通过保存点实现：

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice';

SAVEPOINT my_savepoint;

UPDATE accounts SET balance = balance + 100.00

WHERE name = 'Bob';

-- oops ... forget that and use Wally's account

ROLLBACK TO my_savepoint;

UPDATE accounts SET balance = balance + 100.00

WHERE name = 'Wally';

COMMIT;

This example is, of course, oversimplified, but there's a lot of control possible in a transaction block through the use of savepoints. Moreover, ROLLBACK TO is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely and starting again.

虽然这个示例比较简单，但是通过对保存点的使用，可以实现更多复杂的事务控制。此外，rollback to是唯一可以将因为错误而被系统置于中止状态的事务重新置于控制之下的方式，而不用将事务全部回退或重新开始。