Lesson 13 of 15 · 24 min
Transactions
By the end of this lesson you can wrap related changes in a transaction, commit or roll them back, recover from a failed statement, and use savepoints to undo part of a transaction.
Imagine moving SAR 100 of store credit from customer A to customer B. It takes two statements: subtract 100 from A, add 100 to B. If the server crashes, or the second statement fails, after the first one, SAR 100 has vanished. Nobody should ever be able to see, or be left with, that half-finished state.
A transaction is a group of statements that the database treats as one unit. PostgreSQL guarantees four properties, known as ACID:
- Atomicity: all the statements take effect, or none do.
- Consistency: constraints hold when the transaction ends; a transaction cannot leave a CHECK or foreign key violated.
- Isolation: other sessions do not see your uncommitted changes.
- Durability: once committed, the change survives a crash.
You have been using transactions all along without knowing it. Every statement you run on its own is a transaction of one (PostgreSQL's autocommit): it is committed automatically if it succeeds and undone if it fails. BEGIN lets you put several statements inside one transaction.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons