Explain how commit and rollback differ, separating the case where processing succeeds from the case where it fails.
Tell apart the four ACID properties by what each one protects.
Distinguish exclusive control, which prevents conflicts when several processes access the same data at once, from deadlock, which arises when processes wait on each other's locks.
Explain, stage by stage, how two-phase commit keeps updates across several databases in step.
A transaction (トランザクション) is a unit of processing that groups several related updates and ends in one of two ways: all of them take effect, or none do.
Commit
Rollback
When it is used
When every grouped step finishes normally
When a grouped step fails partway
What happens to updates
Updates are made final and kept
Updates are undone, restoring the state before the start
The operation that makes updates final once every grouped step has finished normally is a commit (コミット), and undoing the updates after a failure partway through so the data returns to its starting state is a rollback (ロールバック). To be able to undo, the DBMS records the history of updates in a file called the log (ログ). When several users reach the same data at the same moment the results can conflict, so the DBMS applies exclusive control (排他制御): it places a lock (ロック) on data in use and makes other processes wait.
AnalogyIt is like a package holiday where the flight and the hotel can only be booked together. If the hotel is full, the flight is not booked either; the booking stands only once both are secured.
How to rememberIn everyday English a transaction is a deal, and a deal only counts when the goods change hands and the payment is made, both together.
Exam points
ExamA commit (コミット) makes the updates final at the point when every grouped step has finished normally. It works the opposite way to a rollback (ロールバック), which returns to the starting state on failure.
ExamExclusive control (排他制御) is the mechanism that prevents inconsistencies when several processes access the same data at once, and it works by locking (ロック) data in use and making other processes wait until the first one is done with it.
ExamIf two processes update in parallel without locks and both read the same value before writing in turn, only the later write survives and the earlier update is lost.
The ACID properties (ACID特性) are the four properties a transaction must satisfy to be processed correctly, named by the initials of their English names.
Initial
Property
What it protects
A (Atomicity)
Atomicity
All updates take effect, or none do
C (Consistency)
Consistency
Data meets its defined rules before and after processing
I (Isolation)
Isolation
Concurrent processes are unaffected by each other's partial results
D (Durability)
Durability
Committed results survive failures
Atomicity (原子性) means updates either all take effect or none do, and consistency (一貫性) means the data keeps meeting its defined rules before and after processing, such as a quantity never going negative. Isolation (独立性) means several transactions running at the same time are not swayed by each other's partial results, and durability (耐久性) means a committed result is not lost even if a failure occurs later.
How to rememberPut A, C, I and D together and you get the word acid. The first one, Atomicity, comes from atom, something that cannot be split, so a series of updates is never split to keep just part of it.
Exam points
ExamAtomicity (原子性) is the property that makes a series of updates take effect entirely or not at all, which is exactly what a transaction (トランザクション) does when it commits on success and reverts on failure.
ExamIsolation (独立性) is the property that concurrent processes are not swayed by each other's partial results, and it is maintained through exclusive control, which locks data in use and makes other processes wait until that process ends.
Mix-upAtomicity leaves no half-finished state before a commit, while durability (耐久性) protects the result after a commit from failures, so the two split on whether they guard the time before or after the commit.
A deadlock (デッドロック) is a state in which several processes each hold a resource while waiting for another to release the resource it holds, so none of them can move forward.
Process 1 locks resource X
Process 2 locks resource Y
Process 1 waits for Y to be released
Process 2 waits for X to be released
Process 2 also waits, for X. The waiting forms a ring and both stop.
4 / 4
Suppose process 1 locks resource X and process 2 locks resource Y, and then process 1 asks for Y while process 2 asks for X: each now waits for the other to unlock, forming a ring. This standoff cannot clear unless one side gives way, so the DBMS resolves it by, for instance, rolling back one process to free its locks.
AnalogyPicture two diners who each hold one chopstick and wait for the other's to free up, so neither can start eating.
How to rememberDead (not moving) plus lock: two parties lock each other out and freeze, just as the word says.
Exam points
ExamThe heart of a deadlock (デッドロック) is that each process holds a resource while waiting for another, and those waits link up into a ring.
ExamA lock goes on before the data is read and comes off only after the result has been written. This keeps other processes from slipping in between the read and the write.
Mix-upExclusive control is the mechanism meant to prevent conflicts from simultaneous access, whereas a deadlock is a stall that can occur when processes using locks wait on each other. One is the intended mechanism, the other a problem it can produce.
Two-phase commit (2相コミットメント) is a procedure that carries out a commit in two stages, prepare and commit, so that a transaction spanning several databases is either committed as a whole or cancelled as a whole.
Coordinator asks each DB to prepare
Each DB prepares and votes yes or no
Coordinator checks every vote
All DBs told to commit or cancel
Phase 2: if all said yes it orders a commit, and if even one said no it orders every database to cancel.
4 / 4
In phase 1 the coordinator (調整役) overseeing the whole transaction asks each database whether it is ready to commit, and each database brings its updates to a state where they can be committed at any moment before answering yes or no. Only if every answer is yes does it order a commit in phase 2, and if even one is no it tells all databases to roll back. This prevents a mismatch in which one site has committed while another has cancelled.
AnalogyIt is like a group purchase where everyone is charged at once only after every member confirms they can pay; if a single person cannot, nobody is charged.
How to rememberThe 相 in the Japanese name translates phase. Phase 1 asks and phase 2 decides, so pair each number with its action.
Exam points
Mix-upAn update confined to one database is made final by a single commit in that database, but two-phase commit (2相コミットメント) inserts a stage before committing that confirms every participating database is ready.
Mix-upA database that votes yes in phase 1 has still not committed at that point. If it receives a cancel order in phase 2, it rolls back as well.
A procedure for committing a unit that spans several databases
Key differenceHow it commits
Within a single database, one commit makes it final
Confirms every database is ready, then commits them all at once
On failure
Rolls back to the state before it began
If even one says no, every database rolls back
Key points
A transaction is a unit that makes a series of updates take effect entirely or not at all, ending with a commit on success and a rollback to the starting state on failure.
When several processes access the same data at once, conflicts can arise (a later write wiping out an earlier update, for example), and exclusive control, which uses locks to make other processes wait, prevents them.
The ACID properties are atomicity, consistency, isolation and durability; isolation, for example, means concurrent processes are not swayed by each other's partial results.
A deadlock is a ring of processes each holding a resource while waiting for another's, and it is resolved by, for instance, rolling back one of them to free its locks.
In two-phase commit, the coordinator collects a ready reply from every database before ordering all of them to commit or cancel at once.