The labs on this site need JavaScript to run. The same content is written out below.

Learn › Spring › Data access and transactions

Transaction basics

What if the withdrawal went through but the deposit did not?

A transfer that takes 1,000 won out of account A and adds 1,000 won to account B is two SQL statements. If an error occurs or the server crashes between the two statements, money can disappear. A transaction groups several statements so that either all of them take effect or none of them do. This lab compares the result when the two statements are not grouped with the result when they are.

Checked against real execution · All 12 combinations were run on SQLite with two connections

Concepts in this lab

Transaction

A way to group several data changes into a single unit so that either all of them are applied (commit) or all of them are canceled (rollback). In a bank transfer, it prevents an intermediate state where only one of the withdrawal and the deposit is applied. It is the most basic means a database has for keeping data consistent.

ACID

The four properties that a transaction must guarantee. Atomicity means everything succeeds or everything fails. Consistency means the data satisfies the defined rules before and after the transaction. Isolation means transactions that run at the same time do not interfere with each other. Durability means committed results are kept even if a failure occurs.

Undo log and redo log

An undo log records data as it was before a change. It is used to roll back a transaction, and in InnoDB it is also used to show past versions to other transactions (MVCC). A redo log records the changes themselves. If a failure occurs before committed changes are fully written to the data files, the changes are applied again at restart to recover them.

@Transactional

This annotation is placed on a method or a class so that its scope runs as one transaction. The proxy starts a transaction before the method runs, commits if the method ends normally, and rolls back according to the rules if an exception occurs. You do not have to write the code that starts and ends the transaction yourself, so this is called a declarative transaction.

Try these

• Default settings — An error partway through, without a transaction. 1,000 won disappears.
• Grouping the statements in a transaction — With the same error, the withdrawal is undone too.
• A server crash with a transaction — Changes that were not committed do not remain.
• When nothing goes wrong — The result is the same either way.
• When someone else queries partway through — Without a transaction: a moment when the total is 1,000 won short is visible.
• A query partway through a transaction — Changes that are not committed yet are not visible to others.

Running with the default settings

1. Account A has 5,000 won, and account B has 1,000 won. The total is 6,000 won. 1,000 won is transferred from A to B.
2. Withdrawal: A is changed to 4,000 won. There is no transaction grouping several statements, so this single statement is made permanent (committed) as soon as it finishes. It cannot be undone.
3. An error occurred in the application, so the deposit statement could not run. The withdrawal is already permanent. 1,000 won was taken out of A but did not go into B. 1,000 won has disappeared.

Result of this run

• Several statements must be "all or nothing", but only some of them were applied. A transaction is the mechanism that guarantees this (atomicity).
• The four properties of a transaction (ACID): all or nothing (atomicity), the data stays in a state that follows the rules (consistency), changes in progress are not visible to each other (isolation), and what is committed remains (durability). The @Transactional of Spring sends START TRANSACTION at the start of the method, COMMIT when it ends normally, and ROLLBACK on a runtime exception (on a checked exception, COMMIT by default), on your behalf.

Code

-- Autocommit: each statement is made permanent as soon as it finishes
UPDATE account SET balance = balance - 1000 WHERE id = 'A';   -- withdrawal
-- (an application error occurs here)
-- UPDATE account SET balance = balance + 1000 WHERE id = 'B';   -- deposit: never runs

Assumptions of this model

The final balances and the results of the query partway through were confirmed for all 12 combinations by running them on a real SQL engine (SQLite) with two connections. "The server crashes" was reproduced by closing the connection without committing. That uncommitted values are not visible to the query partway through is the behavior under the default settings of most DBs, and at the lowest isolation level they can be visible.

Previous lab: AOP: advice and pointcuts · Next lab: Transaction propagation