A3.3.6 · Database programming
A3.3.6 · 数据库编程
How transactions maintain data integrity
事务如何维护数据完整性
Booking a kiln firing takes two statements: deduct the credits, then record the booking. Everything on this page exists because of what happens if the second one never runs.
预订一次窑炉烧制需要两条语句:先扣除积分,再记录预订。本页的一切内容,都是围绕如果第二条语句始终没有运行会发生什么而展开的。
Explainer
讲解
A pottery studio charges members in kiln credits. When someone books a firing, two things have to happen: their credit balance goes down, and a row appears in the firing log. Those are two separate SQL statements, and between them sits a gap in which the power can fail, the network can drop, or the second statement can simply be invalid.
某陶艺工作室以窑炉积分向会员收费。当有人预订一次烧制时,必须发生两件事:扣减其积分余额,并在烧制记录中新增一行。这是两条独立的 SQL 语句,而在这两者之间存在一段空隙,期间可能断电、网络可能中断,或者第二条语句本身可能就是无效的。
If only the first one lands, the studio has taken credits for a firing it has no record of. If only the second lands, it has booked a firing nobody paid for. Neither state is wrong in the sense of breaking SQL. Both are wrong in the sense that the database now describes something that never happened.
如果只有第一条语句成功执行,工作室就收取了积分,却没有对应的烧制记录。如果只有第二条语句成功执行,它就预订了一次没有人付费的烧制。这两种状态都不违反 SQL 语法本身。但两者都是错误的,因为数据库现在描述的是一件从未真正发生过的事。
A transaction is a way of stapling several SQL statements together so the database treats them as one job, not several. It never stops partway through and leaves some of them done and others not: either the whole staple goes through, or none of it does.
一个事务(transaction)是将若干条 SQL 语句"钉"在一起的一种方式,使数据库把它们当作一项工作来处理,而不是多项独立的工作。它绝不会在中途停止,让其中一些已完成、另一些未完成:要么整叠语句全部通过,要么一条也不通过。
The point is not that transactions make statements faster or safer individually. It is that they remove the in-between state entirely.
事务的意义并不在于让每条语句本身运行得更快或更安全,而在于它彻底消除了那种"介于两者之间"的中间状态。
The transaction control language commands
事务控制语言命令
TCL is the fourth family of SQL commands, alongside the DDL and DML of A3.3.1. Its job is managing transactions, and three commands do almost all of it.
TCL 是 SQL 命令的第四大家族,与 A3.3.1 中的 DDL 和 DML 并列。它的作用是管理事务,而几乎所有的工作都由三条命令完成。
| Command | 命令 | What it does | 作用 | Example | 示例 |
|---|---|---|---|---|---|
| BEGIN TRANSACTION | Starts a new transaction. Statements after it are held together as one unit rather than taking effect one by one. | 开启一个新事务。此后的语句会被作为一个整体一起处理,而不是逐条单独生效。 | BEGIN TRANSACTION; |
||
| COMMIT | Closes the current transaction and locks in everything it did. From this point on, every other user querying the database sees the change too, and nothing can pull it back. | 结束当前事务,并将它所做的一切锁定下来。从这一刻起,其他任何查询该数据库的用户也都能看到这项更改,并且没有任何操作能把它撤回。 | BEGIN TRANSACTION; UPDATE Member SET KilnCredits = KilnCredits - 10 WHERE MemberID = "M1"; COMMIT; |
||
| ROLLBACK | Throws away everything the current transaction has done and closes it, as if none of those statements had ever been typed. | 丢弃当前事务所做的一切,并将其关闭,就如同那些语句从未被输入过一样。 | BEGIN TRANSACTION; UPDATE Member SET KilnCredits = KilnCredits - 10 WHERE MemberID = "M1"; ROLLBACK; |
ROLLBACK only reaches back as far as the last BEGIN TRANSACTION. It undoes the current transaction, not the last thing you regret. Once COMMIT has run, that transaction is finished and permanent, and a later ROLLBACK has nothing of it left to undo.
ROLLBACK 只能回溯到最近一次 BEGIN TRANSACTION 为止。 它撤销的是当前事务,而不是你后悔的最后一件事。一旦 COMMIT 已经运行,该事务就已结束并永久生效,之后的 ROLLBACK 已经没有任何内容可以撤销。
Explainer
The four ACID properties
ACID names the four guarantees that make a transaction trustworthy. Each one is easiest to hold onto through the thing that goes wrong without it, so each card below names both.
Atomicity
There is no such thing as a transaction that is 80 percent finished. Every action inside it succeeds, or the whole set is torn up and the database looks exactly as it did before any of it started, one failed step is enough to cancel everything else too.
Without itInes is charged 10 credits for a firing that was never booked.
Consistency
Every constraint the database has, unique keys, checks, whatever else was set up, still has to hold once a transaction finishes. Atomicity alone can guarantee a transaction runs to completion; it takes consistency to guarantee that completed result is actually a legal one.
Without itRafi books a 15-credit firing with 12 credits and ends on a balance of minus 3.
Isolation
Two transactions running at the same moment should never be able to see or disturb each other's unfinished work. However many run in parallel, the end result has to look exactly like they happened one after another in some order, never a result that mixing them together could invent.
Without itTwo members book the last Friday kiln slot at the same moment, and both are told it is theirs.
Durability
COMMIT is not a polite suggestion to the database, it is the last word. From that instant on, the change survives a crash, a power cut, anything short of the storage itself being destroyed, because a committed record is one the database has promised will still be there later.
Without itThe studio loses power an hour after Ines books, and her confirmed firing is simply gone.
Atomicity and consistency are the pair most often mixed up. Atomicity is about how much of the transaction happens: all of it, or none of it. Consistency is about what the result is allowed to look like: the rules of the database still hold at the end. A transaction can be perfectly atomic and still be refused for breaking a constraint, which is consistency doing its job.
Worked example
A firing that goes wrong, then goes right
Two tables, MEMBER and KILN_LOG. Click through and watch the same booking fail, roll back, and then succeed.
Try it yourself
Run a real transaction
This console understands BEGIN TRANSACTION, COMMIT and ROLLBACK, and it runs several statements at once, so you can paste a whole transaction and watch it behave. Everything else A3.3 teaches works here too.
3 rows
2 rows
The chip worth running twice is "A booking that fails halfway". Its INSERT reuses LogID L1, which already exists, so the primary key refuses it. Run it and look at what the UPDATE did: the credits came off, the log row never appeared, and the transaction is still open. That half-finished state is the one atomicity exists to prevent, and it stays on screen until you either COMMIT it or ROLLBACK. Type ROLLBACK; and watch the credits come back.
Check your understanding
A transaction updates two rows and then runs COMMIT. A moment later the user runs ROLLBACK. What happens?
Common mistake
A3.3.6RecurringCandidates on this statement usually do understand the concepts. What costs the marks is imprecise language: the four ACID terms get defined in general words that could describe any of them, so a marker cannot tell which property is actually being described. Terminology has to be used precisely here, and each of the four needs the specific thing only it guarantees.
What a weak answer looks like: "Atomicity means the transaction is done properly and the data stays correct." That sentence would pass equally well as a description of consistency, or of durability, so it identifies nothing.
What it should say: "Atomicity means the transaction is indivisible: either every one of its operations completes, or none of them does. If any part fails the whole transaction is rolled back, so the database is never left in a half-finished state." Indivisible, all or none, rolled back on failure. Those three specifics belong to atomicity alone.
Revision questions
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
[2]Describe what is meant by a transaction in a database.
Marking points
评分要点
- A transaction is a group of database operations treated as a single unit of work.
- Either all of its operations take effect (commit) or none of them do (rollback).
- 事务是一组被当作单个工作单元处理的数据库操作。
- 要么它的所有操作都生效(提交),要么都不生效(回滚)。
[4]Describe the role of atomicity and durability in ensuring reliable processing of transactions.
Marking points
评分要点
- Atomicity: the transaction is all-or-nothing, so if any statement fails, every earlier statement is rolled back and the database is left as it was.
- Durability: once the transaction commits, its changes are written to stable storage and survive a crash or power loss.
- Together they mean a completed transaction is never partly done and never silently lost.
- 原子性:事务全有或全无,所以若任一语句失败,之前的每条语句都被回滚,数据库保持原样。
- 持久性:事务一旦提交,其改动被写入稳定存储,能在崩溃或断电后保留。
- 两者合起来意味着一个已完成的事务绝不会只做了一半,也绝不会悄然丢失。
[6]A studio deducts kiln credits and records a firing as two SQL statements. Describe how TCL commands would be used to protect the data, and explain what would happen if the second statement failed.
Marking points
评分要点
- Begin a transaction before the first statement (BEGIN or START TRANSACTION).
- Run both statements: deduct the kiln credits, then insert the firing record.
- If both succeed, COMMIT makes both permanent.
- If the second statement fails, ROLLBACK undoes the credit deduction so the student is not charged for a firing that was not recorded.
- Atomicity guarantees the database cannot be left with credits deducted but no firing logged.
- 在第一条语句前开始一个事务(BEGIN 或 START TRANSACTION)。
- 执行两条语句:扣除烧窑积分,然后插入烧窑记录。
- 若两条都成功,COMMIT 使两者永久生效。
- 若第二条失败,ROLLBACK 撤销扣分,使学生不会为一次未被记录的烧窑而被扣费。
- 原子性保证数据库不会停留在扣了积分却没有记录烧窑的状态。
Flashcards
Active recall for this statement's key terms.
Keep this statement's practice with you
Free account: unlock every revision question and flashcard for A3.3.6, and every other IB CS 2027 statement.