Skip to content

事务

分组多个操作到 atomic 单元使用 BEGIN, COMMIT, and ROLLBACK.

你将学到

  • 什么事务是 and 为什么它 matters
  • BEGIN, COMMIT, and ROLLBACK 语法
  • 如何事务 ensure atomicity and consistency
  • Savepoints for 部分 rollbacks
  • 常见事务 isolation issues

概念讲解

什么是事务?

transaction 是一种 sequence of database operations that are executed as a single unit — either all of them succeed, or none of them do. The classic example is a bank transfer: deducting $100 from account A and adding $100 to account B must both succeed. If the system crashes after the debit but before the credit, the money is lost. A transaction prevents this: the debit and credit are committed together or rolled back together.

BEGIN, COMMIT, ROLLBACK

BEGIN (or START TRANSACTION) starts a transaction. All subsequent statements are part of it until you COMMIT (make the changes permanent) or ROLLBACK (undo all changes since BEGIN). If the connection drops or the database crashes before COMMIT, the transaction is automatically rolled back.

Savepoints

A savepoint is a named marker within a transaction. You can roll back to a savepoint without rolling back the entire transaction. This is useful when you want to try an operation that might fail, and continue with other work if it does. SAVEPOINT sp1 ... ROLLBACK TO sp1.

Why Transactions Matter

Without transactions, a partial failure leaves the database in an inconsistent state. With transactions, the database guarantees that either all operations in the group are applied or none are — this is the atomicity property, the A in ACID. Transactions also provide isolation (concurrent transactions do not interfere) and durability (committed data survives crashes).

Autocommit

By default, most database drivers run in autocommit mode — every statement is its own transaction, committed immediately. To use explicit transactions, you either turn off autocommit or wrap statements in BEGIN ... COMMIT. In application code, use the driver's transaction API rather than sending raw BEGIN/COMMIT strings.

示例代码

              -- A bank transfer as a transaction
BEGIN;

  -- Deduct from source account
  UPDATE accounts
    SET balance = balance - 100.00
    WHERE id = 1;

  -- Credit to destination account
  UPDATE accounts
    SET balance = balance + 100.00
    WHERE id = 2;

  -- Verify both accounts exist and source has sufficient funds
  -- (application code checks row count and balance here)

COMMIT;
-- If anything went wrong, run ROLLBACK instead

-- Using savepoints for partial recovery
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 50.00);
  SAVEPOINT after_order;

  INSERT INTO order_items (order_id, product_id, quantity)
    VALUES (currval('orders_id_seq'), 100, 2);
  -- If this fails, roll back to savepoint but keep the order

  -- ROLLBACK TO after_order;  -- uncomment if needed
COMMIT;
            

Two 模式: bank transfer wrapped in BEGIN/COMMIT, and 事务使用 savepoint for 部分 rollback.

动手试一试

常见错误

错误写法

Forgetting 到 COMMIT or ROLLBACK, leaving 事务打开

正确做法

总是关闭事务使用 COMMIT or ROLLBACK. 打开事务持有 locks and prevents other 事务从 proceeding. Use try/最后 in application code.

错误写法

运行长 事务那个 locks 许多行

正确做法

Keep 事务短. 长-运行事务块 other 操作 and 能 cause deadlocks. 做 heavy computation 之外事务.

练习 事务

8 个练习

练习 事务

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误