For five modules we have looked at BiblioRed's schema the way you look at a blueprint: flat on the table, still, with time to argue about whether that column is redundant or that foreign key is missing. The blueprint is fine. The tables are normalized, the constraints written and the four denormalizations justified in writing.

This morning the blueprint turned into a building. The system went live in Vallmar's four branches and, with it, the real world came in: there are two people at the Central branch front desk registering loans at the same time, there is a member paying a fine by card while the server decides to shut down, and there is a query that took 30 milliseconds on the development laptop and takes fourteen seconds here.

This module is about all of that. And it starts with the piece everything else rests on: the transaction.

A transaction is the databases' answer to an uncomfortable question: what happens if an operation is interrupted halfway through? It is not a theoretical question. The server shuts down, the process dies, the network drops, the application throws an exception, the operator closes the window. The only relevant question is not whether it will happen, but what is left in the database when it does. And the answer a transactional database gives is as simple as it is radical: everything is left, or nothing is.

In this lesson we will see what a transaction is and why it exists, how it is controlled from SQL, how it behaves when something fails, and what the four letters of ACID really mean —with particular attention to the D for durability and to the mechanism that makes it possible, the write-ahead log or WAL, which is the same mechanism that will reappear in lesson 06-04 when we talk about backups.

1. The problem: registering a loan is three operations
2. What a transaction is
3. Transaction control in SQL: BEGIN, COMMIT, ROLLBACK
4. Autocommit mode: every standalone statement is already a transaction
5. Savepoints: SAVEPOINT, ROLLBACK TO and RELEASE
6. The life cycle of a transaction
7. Atomicity: all or nothing
8. Consistency: from one valid state to another valid state
9. Isolation: stated here, developed in 06-02
10. Durability and the write-ahead log (WAL)
11. Checkpoints and recovery after a crash
12. The cost of durability and the parameters that relax it
13. Errors inside a transaction: PostgreSQL's behavior
14. Transactions and DDL
15. Good practices when writing transactions
16. Outside PostgreSQL: SQLite and MongoDB

Let us start with BiblioRed's most common case. Marta Alsina (member 14) walks up to the Central branch front desk with copy EJ-3081 of "The Map of Time". She had a pending reservation on that material. The person at the desk clicks "Lend".

What the application has to do in the database is three separate operations:

Three statements. Each one, on its own, is correct. And yet the set is a bomb, because there are two moments at which the world can stop:

None of these intermediate states is a valid state of the library. There is no library in which a book is on loan and available at the same time. It exists in the database because we have written three statements where the business has a single fact: "Marta has taken copy EJ-3081".

Notice that no constraint from module 4 saves us from this. A CHECK checks one row; a foreign key checks one reference. Neither of them can express "these three statements go together". We need another tool, of a different nature: one that talks not about data, but about time.

Definition. A transaction is a sequence of operations on the database that the management system treats as a single indivisible unit of work: either all of its operations are applied, or none is.

Three consequences worth being clear about from the outset:

• A transaction is a logical unit, not a technical one. Its size is decided by the business, not by the engine. "Registering a loan" is a transaction because in the library it is a single act. Whether it is three UPDATEs or seven is irrelevant.
• The boundary is drawn by whoever writes the code. The management system cannot guess that those three UPDATEs belong together. Somebody has to tell it, and that somebody is you.
• A transaction is not just "a group of statements". It is a group of statements with four associated guarantees —the ACID properties— that the management system undertakes to honor even if the power goes out.

The acronym ACID was coined by Theo Härder and Andreas Reuter in 1983, formalizing ideas Jim Gray had been developing since the seventies at IBM. You will remember from the historical tour in 01-03 that this is exactly the period in which relational databases went from laboratory prototype to banking production system: without reliable transactions, that leap would not have been possible.

The answer to all four, in a transactional management system, is no. We will see them one by one from section 7 onwards. First we have to know how to write them.

The vocabulary is short and has not changed in forty years:

START TRANSACTION is the SQL standard form; BEGIN is the short form PostgreSQL accepts and the one used in practice. They are equivalent.

Marta's loan, written correctly:

Expected result:

And if something goes wrong midway —the application detects that the copy was already on loan, or that the member has been deregistered— it is enough to change the last line:

After the ROLLBACK the database is exactly as it was before the BEGIN. There is no loan, the copy is still available and the reservation is still active. Nothing has to be "undone by hand": undoing is the management system's responsibility, and it does it well.

This is the first of several demonstrations that you must reproduce by opening two terminals with psql connected to the same database. We will call each one Session A and Session B. Run the statements in the order of the "step" column: the order matters, and that is where the whole lesson lies.

Two things to learn here:

1. Inside the transaction, A sees its own changes (t3). That is coherent: A is working.
2. Outside the transaction, B sees nothing (t4) until A commits. This is the isolation property peeking out. What exactly each session sees, at what moment and under what rules, is the entire content of lesson 06-02; here we are only interested in establishing that uncommitted work is invisible to everyone else.

A reasonable question: if transactions are opened with BEGIN, what about the hundreds of standalone INSERTs we wrote in modules 2 and 3, with no BEGIN in sight? Were they unprotected?

No. They were inside a transaction, just an implicit one.

Autocommit. When the client has not opened an explicit transaction, the management system wraps each individual statement in its own transaction, which is committed automatically if the statement succeeds and rolled back if it fails.

That is, this:

behaves internally like this:

And it has a very useful consequence that usually goes unnoticed: a single SQL statement is already atomic. If that UPDATE affects 9,400 copies and fails on the 9,399th because one violates a CHECK, you are not left with 9,398 modified rows: you are left with none. The standard requires exactly this, and every serious management system honors it.

That last point has a concrete measurement. Loading 200,000 rows into loans row by row in autocommit can take several minutes; the same 200,000 rows inside a single BEGIN ... COMMIT take a few seconds. The difference is not in the INSERT, it is in the COMMIT: every commit forces the log to be synced to disk, and we will look at that in section 10.

Not all clients behave the same, and this is an inexhaustible source of surprises:

The classic mistake with psycopg is to write an INSERT, never call commit(), close the program and not find the row. It has not been lost: it has been rolled back, which is exactly what should happen to a transaction that was never committed.

A ROLLBACK is a blunt instrument: it undoes the entire transaction. Sometimes you need something finer, and that is what savepoints are for.

Savepoint. A named mark inside an open transaction that allows the work done after that mark to be undone without aborting the whole transaction.

The three statements:

Documentation usually presents them with artificial examples. The two real uses are these:

Use 1: optional operations inside a mandatory operation.

When registering the return of an overdue loan, BiblioRed tries to issue the corresponding fine. If the fine calculation fails —because the fine type is not configured for that material, for instance—, the return must be recorded anyway: it is intolerable for a member to be unable to return a book because the fines system is misconfigured.

Expected result on the happy path:

And on the failing path, after the ROLLBACK TO SAVEPOINT before_fine, the final COMMIT commits the return without the fine. Which is exactly what the library wants.

Use 2: recovering from an error without losing the work.

This is the decisive use in PostgreSQL, and it is best understood in section 13: when a statement fails inside a transaction, PostgreSQL aborts the whole transaction and rejects everything that comes after it. A savepoint is the only way to survive an error and carry on. In fact, when a driver offers to "retry this statement", it is almost always placing an implicit SAVEPOINT before each statement.

Savepoints are not free: each one consumes internal resources in the management system. Placing one before every statement in a loop of 100,000 iterations degrades performance noticeably. Use them where there is a real decision to make, not as a matter of routine.

The behavior we have seen follows a very simple automaton, present in any textbook and in the implementation of any management system:

The five states, with their practical meaning:

There are two details that are usually overlooked and that matter a great deal here.

The first: "partially committed" is not a technicality. It is the critical instant. The application has asked for COMMIT, the management system has applied the changes in memory, but it has not yet received confirmation from the disk that the log is safe. If the machine goes down in that microsecond, the transaction has not been committed and will be rolled back at startup. That is why the management system does not reply "COMMIT" to the client until it is sure: the reply to the client is the promise of durability.

The second: there is no way out of "committed". There is no such thing as "un-committing". A ROLLBACK after a COMMIT undoes nothing —it opens an empty transaction and rolls it back—. If you need to revert something already committed, you have to write the inverse operation, or restore from a backup (lesson 06-04). This irreversibility is a feature, not a defect: it is what allows you to build on top.

Atomicity. A transaction is indivisible: either all of its operations are applied or none is. There is no observable or persistent intermediate state.

What it guarantees. That the three statements of Marta's loan behave as one. That there is never a row in loans without its corresponding copies.status = 'on_loan'.

What fails if it is missing. Exactly the table of intermediate states in section 1: phantom loans, copies on loan that show as available, orphaned reservations. And the worst part is that these failures are silent. There is no error, no trace, no exception. Just a library that one day discovers its inventory does not add up and does not know since when.

How the management system implements it. With the information needed to undo. Before modifying a piece of data, the management system records somewhere enough to go back:

• PostgreSQL does not overwrite rows: every UPDATE creates a new version of the row and marks the old one as obsolete from that transaction onwards. Undoing is as simple as marking the transaction as aborted: the new versions stop being visible to everyone and the old ones are still there. This mechanism is MVCC, and its full treatment belongs to 06-02. The interesting consequence is that in PostgreSQL a ROLLBACK is cheaper than a COMMIT, unlike in other management systems.
• Oracle and MySQL/InnoDB use an undo segment: they store the previous image of each modified row and, when undoing, put it back.

Both paths arrive at the same place. The practical difference is that PostgreSQL pays afterwards, cleaning up dead tuples with VACUUM (06-02), and the others pay during, maintaining the undo segment.

Let us trigger a failure on purpose in the middle of a transaction. We are going to try to lend a copy to a member who does not exist (9999), with the foreign key from 02-06 doing its job:

Expected result:

The UPDATE had worked. Atomicity wiped it off the map. The copy is still available, as it should be.

Consistency. A transaction takes the database from one valid state to another valid state. If the database satisfied all its rules before the transaction, it satisfies them afterwards.

What it guarantees. That on commit, no foreign key points to nothing, no CHECK is violated, no UNIQUE is duplicated and no NOT NULL is empty. The whole catalog of constraints from 04-04 and the referential integrity from 02-06 are still standing on the far side of the COMMIT.

What fails if it is missing. Data that contradicts the business rules: fines assigned to non-existent loans, registrations for deleted events, copies in branches that closed.

How the management system implements it. By checking the constraints. Most are checked as each statement runs; some can be deferred to the COMMIT if they were declared DEFERRABLE, which is essential when two tables reference each other:

If some deferred constraint is not satisfied when the COMMIT arrives, the COMMIT fails and the whole transaction is rolled back. That is consistency doing its job at the last second.

It is worth saying, because any student who reads about the subject will run into it: there is broad consensus that the "C" in ACID is not on the same level as the other three.

The reasons are these:

If fines.amount can be negative because nobody wrote the CHECK, the database will accept -€50.00 without protest and will have been perfectly "consistent": it has not violated any rule, because that rule did not exist. The management system's consistency is consistency with respect to the declared constraints, not with respect to common sense.

Besides, atomicity and isolation already imply much of what the C promises. That is why some people say, with reason, that ACID is "three properties and a letter that looked good in the acronym". The useful stance for a professional is somewhere in between: the C is a reminder that the business rules must be declared in the schema so that the transaction can protect them. That is exactly the argument of section 21 of lesson 04-04 about which rules belong in the database and which in the application.

Isolation. Each transaction runs as if it were the only one in the system. The results of a concurrent transaction do not interfere with those of another.

What it guarantees. That you can reason about your transaction without thinking about the other eleven running at the same time.

What fails if it is missing. The two front desks at Central lend the same copy EJ-3081 at the same time. Two members take the same last seat in the reading club. A report adds up figures from one instant and figures from another, and matches nothing.

How the management system implements it. With locks, with multiversion concurrency control (MVCC), or with a combination of both.

And here we deliberately stop. Isolation is by far the most complex of the four properties: it is the only one that admits degrees —the SQL standard defines four levels and each one permits some phenomena and forbids others—, it is the only one in which the management system lets you choose how much guarantee you want in exchange for how much performance, and it is the source of the hardest-to-reproduce bugs in this whole profession.

All of that is the entire content of lesson 06-02: the concurrency phenomena one by one with two reproducible sessions, the four isolation levels and their canonical table, shared and exclusive locks, PostgreSQL's MVCC, deadlocks and optimistic versus pessimistic locking. Here it is enough to have the definition and to know that isolation exists, that it has levels, and that PostgreSQL's default level —READ COMMITTED— is not the strictest one.

Durability. Once the management system has replied COMMIT, the changes survive any later failure: power cut, process kill, operating system crash.

What it guarantees. That when the front desk sees "Loan registered", the loan exists. Even if the building loses power half a second later.

What fails if it is missing. That the library believes it has collected a fine that is not on record. And, above all, that nobody knows which of the last hour's operations survived and which did not.

How it is implemented is the interesting part, and it is worth understanding because it explains a lot about PostgreSQL's behavior, including performance.

Remember the buffer manager from lesson 01-04. The database does not read or write directly to disk: it keeps a page cache in memory (in PostgreSQL, shared_buffers). When an UPDATE modifies a row, what is modified is the page in memory. That page is marked as dirty (modified and not written to disk) and will be written "later".

This is essential for performance: memory is several orders of magnitude faster than disk, and grouping writes avoids thousands of input/output operations.

But it creates an obvious problem: if the COMMIT only modifies memory, a power cut takes everything committed with it.

The naive solution would be to write all modified pages to disk on every COMMIT. It is correct, and it is unacceptably slow: the modified pages are scattered across the data file, and writing them forces head seeks (on a mechanical disk) or rewriting whole blocks (on an SSD). A transaction that touches three tables would write to three distant places on disk.

Write-Ahead Log (WAL). Before modifying a data page, the management system writes into a sequential log file an entry describing the change. The log is synced to disk before the transaction is committed; the data pages can wait.

The rule, stated canonically, is total simplicity itself:

A change is never written to the data files before the log entry describing it has been written to disk.

Why is it faster? Because the log is sequential. All the entries of all the transactions are appended to the end of the same file, one after another. Writing 4 KB at the end of a sequential file is the cheapest operation there is on any storage system. Writing 4 KB in eight different places on disk is not.

This is the real sequence of a COMMIT:

Notice the order: the application receives the "COMMIT" as soon as the log is safe, not when the data is written. The data may take minutes to reach its final place. It does not matter: the information needed to rebuild it is already somewhere safe.

In PostgreSQL the WAL lives in the pg_wal/ directory, in 16 MB files by default. You can see it:

And query the log's current position (the LSN, Log Sequence Number, which is the address of a byte inside the log):

That number advances with every write. It is durability's internal clock, and it will appear again in 06-04 when we talk about point-in-time recovery.

If the log grew indefinitely and you had to reread it in full to recover, starting up a two-year-old database would take days. That is why checkpoints exist.

Checkpoint. A periodic operation in which the management system writes to disk all the dirty pages in memory and records in the log that, up to that point, the data files are up to date.

Consequence: to recover from a crash you only have to read the log from the last checkpoint. Everything before that is already in the data files.

The parameters that govern it in PostgreSQL:

There is a clear trade-off: frequent checkpoints make recovery fast but load the disk during normal operation; spaced-out checkpoints are gentler while running but lengthen startup after a crash.

Suppose that at 12:41 the power goes out in Vallmar's data center. The last checkpoint was at 12:37. Between 12:37 and 12:41 there were 214 transactions: 209 committed and 5 open at the moment of the cut.

On startup, PostgreSQL detects that the shutdown was not clean and runs recovery, in two phases:

Phase 1 — Redo. It reads the log from the last checkpoint and reapplies all the recorded changes, both those of committed transactions and those of transactions that were not. It sounds odd, and it is deliberate: it is faster to reapply everything and clean up afterwards than to decide case by case.

Phase 2 — Undo. The effects of transactions that never committed are discarded. In PostgreSQL this phase is almost free thanks to MVCC: the 5 open transactions simply never show as committed in the transaction status map, so their row versions are invisible to everyone and will be cleaned up by VACUUM. In a management system with an undo segment, this phase does involve real restoration work.

The result after recovery is exact: the 209 committed ones are there; the 5 open ones left no trace. Not one of them half done.

In the server log it looks like this:

That "invalid record length" is not an error: it is the management system finding the end of the valid log, that is, the exact instant of the cut. Read it as "this is as far as the power got".

This mechanism —the log, the checkpoints, redo and undo— is what in 01-04 we called the transaction and recovery manager, working shoulder to shoulder with the buffer manager. Now you know exactly what those two boxes in the diagram do.

The fsync() from section 10 —the system call that forces the disk to confirm that it really has written— is the most expensive operation in the whole cycle. On a decent server SSD it is around 0.1-1 ms; on a mechanical disk, between 5 and 15 ms. That time is a hard ceiling: a database cannot commit more transactions per second than its disk can sync.

PostgreSQL offers a parameter to relax it, and you have to understand exactly what you buy and what you pay:

The gain is real and sometimes spectacular: on workloads with many small transactions, off can multiply the number of transactions per second. But the small print has to be stated in full:

With synchronous_commit = off you can lose transactions the management system has already confirmed to you. The database is not left corrupted —atomicity and consistency hold: the lost transactions are lost whole—, but operations the user saw as completed disappear.

Is that acceptable? It depends on the table:

Practical rule: if losing the last few seconds means having to phone somebody, do not turn it off.

Additional note: there is also the fsync = off parameter. That one does turn off the root protection and can leave the database corrupted and unrecoverable after a power cut. Its only legitimate use is a disposable test database that can be regenerated with a script. Never in production, under no circumstances and for no reason.

This section explains one of PostgreSQL's most frequent —and most poorly understood— error messages.

When a statement fails inside an explicit transaction, PostgreSQL aborts the whole transaction. Not the statement: the transaction. From that moment on, any statement is rejected until ROLLBACK (or ROLLBACK TO SAVEPOINT) is run.

Two notable things:

1. The SELECT —which is harmless— is rejected too. The transaction is poisoned.
2. The final COMMIT replied ROLLBACK. PostgreSQL does not commit an aborted transaction: it rolls it back. It is safe behavior and at the same time treacherous, because an application that only checks "did I get a reply to the COMMIT?" will believe everything went well.

PostgreSQL's stance is the strictest, and it is defensible: if a statement in your unit of work has failed, your unit of work most likely no longer makes sense. But it forces you to write the code differently.

If a specific error is expected and you want to survive it, wrap it in a savepoint:

Nuria's enrollment has been kept. Iván's has not. The transaction reached the COMMIT alive.

A PostgreSQL feature that surprises people coming from other management systems: DDL is transactional. CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX can go inside a transaction and be undone with ROLLBACK.

Neither the column nor the table exists. This has an enormous operational consequence: a schema migration in PostgreSQL can be atomic. If step 7 of 9 fails, the schema comes back intact to its initial state instead of being left half migrated, which is the worst possible situation in the small hours of a deployment.

The exceptions in PostgreSQL, worth knowing:

• CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE cannot go inside a transaction.
• CREATE INDEX CONCURRENTLY —the one that does not lock the table— cannot either: that is precisely how it avoids locking. It will come back in 06-03.
• VACUUM cannot either.

And an important warning: DDL being transactional does not mean it is free. An ALTER TABLE takes a strong lock on the table, and while the transaction is open nobody else will be able to use it. Locks are the subject of 06-02.

Four rules that prevent most production problems. The first three boil down to one idea: an open transaction is an expensive resource that somebody else is waiting for.

An open transaction holds locks, prevents VACUUM from cleaning up dead tuples and consumes a connection slot. The longer it lasts, the more of a nuisance it is.

A DELETE of nine million rows in batches:

Each run is its own transaction (autocommit), short, interruptible and not inflating the log with nine million entries in one go.

It is the classic front-desk system mistake:

That FOR UPDATE keeps the copy's row locked for forty minutes, and any other front desk that tries to touch it is left waiting. The correct pattern is to read without a transaction, show the dialog, and open the transaction after the human has decided —checking at that point that nothing has changed—. It is exactly optimistic locking, which is covered in 06-02.

As a safety net, PostgreSQL lets you cut off the careless:

Any session that stays more than 30 seconds with an open transaction doing nothing will be disconnected. In production it is highly advisable to set a sensible value.

Two problems of a different nature:

• Performance: the transaction lasts as long as the network does.
• Correctness, and this is the serious one: the external call is not undone by ROLLBACK. If the COMMIT fails after you have charged, you have charged and it is not on record. A database transaction cannot undo the outside world.

The correct pattern separates the two things: one transaction records the intent (payments with status initiated), the external call is made outside any transaction, and a second transaction records the outcome. With an idempotent reference so you can retry without charging twice —that is what the payments.reference column is for.

A transaction can fail for transient reasons: a deadlock, a serialization failure, a momentary disconnection. These errors do not mean the code is wrong; they mean you have to try again. A serious application wraps its transactions in a retry with increasing backoff and a maximum number of attempts. The specific mechanics are covered in 06-02, where the errors that should be retried and those that should not are laid out.

SQLite is fully ACID, which surprises anyone who takes it for "a toy database". It is not: it is the most widely deployed database in the world, and it is genuinely transactional.

Its differences come from its embedded nature (01-04):

Turning on WAL mode, which is the first thing to do in any serious use of SQLite:

The concept is the same as in PostgreSQL —write the log first—, applied to a local file. The decisive difference remains the lock granularity: PostgreSQL locks rows; SQLite locks the whole file in order to write. For BiblioRed, with four front desks writing at the same time, SQLite would be a bad choice; for the inventory application a librarian carries on a tablet and syncs at the end of the day, it would be perfect.

Picking up what we saw in 03-04:

Document-level atomicity explains why the document modeling of 03-03 pushes you to group in one document what has to change together. If the loan and the copy's status live in the same document, the operation is atomic with no transaction at all.

With separate documents an explicit transaction is indeed needed:

It works, and it is correct. But in MongoDB a multi-document transaction has a notably higher cost than in PostgreSQL, and the community treats it as the exception, not as the everyday tool. The criterion from 03-04 still stands: if your domain needs multi-document transactions all the time, that is a strong sign that the relational model fits your problem better.

Believing that ROLLBACK undoes what has been committed. It does not exist. Once the COMMIT has replied, the only way back is a compensating operation or a restore from backup (06-04). Write the code knowing that COMMIT is a point of no return.

Trusting the COMMIT without checking the reply. As we saw in section 13, a COMMIT on an aborted transaction replies ROLLBACK without raising an exception in some clients. Always check the result; do not assume.

Leaving the transaction open waiting for a human or for a network. It is the origin of 80% of locking problems in production. Configure idle_in_transaction_session_timeout and do not leave it to anybody's judgment.

Putting the whole nightly process in a single transaction. Nine hours of processing in one open transaction prevent dead tuples from being cleaned up, bloat the database and, if it fails in the eighth hour, everything is lost. Split it into batches with intermediate commits.

Forgetting commit() in the driver. With psycopg and with many ORMs, if you do not commit, the work is discarded when the connection closes. No error, no warning, no row.

Using synchronous_commit = off on tables that represent money or inventory. The performance gain is real and so is the potential loss. Decide it table by table, not globally, and write it down.

Touching fsync = off even once in production. There is no case for it. None.

Not placing a SAVEPOINT where there is an expected error. If your logic has a "this may fail and it does not matter", in PostgreSQL it needs a savepoint. Without one, the failure takes the whole transaction down with it.

Final tip: name your transactions in the code. A registerLoan() method that opens and closes the whole transaction, with the BEGIN and the COMMIT visible in the same block of code, can be read and audited. A BEGIN in one place and a COMMIT three layers down is an inexhaustible source of forgotten transactions.

In BiblioRed, returning a copy involves: (a) setting return_date in loans; (b) setting copies.status = 'available'; (c) if it is late, issuing a fine of €0.20 per day in fines. Issuing the fine is optional: if it fails, the return must be recorded anyway.

Write the complete transaction for loan 88214 of member 14 on copy 3081, whose due_date was 2026-07-15 and which is returned on 2026-08-02. Use a savepoint where appropriate and compute the amount with SQL, not by hand.

Given the following sequence, state which rows of members exist at the end and why. Assume that branch 77 does not exist and that the others do.

Vallmar's council department wants to reduce the front desk's response time. A technician proposes setting synchronous_commit = off in the server's configuration file, for the whole database. Argue with three concrete points why that decision, as formulated, is unacceptable, and propose an alternative that keeps part of the benefit.

Solution 1

Key points of the solution:

• The INSERT ... SELECT with the condition AND l.return_date > l.due_date makes the fine be issued only if the return is late, with no need for logic in the application. If it is not late, the reply is INSERT 0 0 and nothing happens.
• The amount is computed with the date subtraction (18 days × €0.20 = €3.60), reading from the loan itself already updated within the same transaction. That is correct because a transaction sees its own changes.
• The savepoint allows the application, if the INSERT fails —for example, because a CHECK on fines rejects an amount above the by-law's maximum—, to run ROLLBACK TO SAVEPOINT before_fine and commit the return anyway.
• The order matters: first loans, then copies, then fines. Always keeping the same table access order prevents deadlocks (06-02).

Solution 2

In the end none of the four rows exists. Step-by-step walkthrough:

The exercise's trap is twofold. First: ROLLBACK TO s2 does not abort the transaction, it rescues it —without it, everything afterwards would have failed with current transaction is aborted—. Second: ROLLBACK TO s1 discards B and D, which many people take as committed because "they had already gone through". A savepoint undoes everything after the mark, including the operations that succeeded.

Solution 3

Point 1 — The scope is global when the problem is not. Putting it in the configuration file applies it to every transaction, including those on payments and fines. The library would be accepting the loss of confirmed collections in exchange for a faster front desk. That is not a trade a public service can make, and it certainly cannot be decided by a technician on their own.

Point 2 — Nobody has measured where the problem is. There is no data saying that the front desk's response time is going into the fsync(). It is just as likely —more so, in fact— that it is going into a query with no index (06-03), into network time or into the interface itself. Changing a durability parameter before having measured is acting on an unverified hypothesis, and doing so with the safety guarantee as the stake.

Point 3 — The risk is neither bounded nor communicated. "The last transactions may be lost" is a statement the council department must know about and accept in writing, because it affects citizens' data and collections. A decision of this kind is not technical: it is operational risk, and it gets documented.

A reasonable alternative. Leave synchronous_commit = on as the global configuration and turn it off per transaction, only in the operations where losing a few seconds is tolerable:

With SET LOCAL the effect dies when the transaction ends, so it cannot escape into another operation by accident. And before that: measure with EXPLAIN ANALYZE (06-03) where the front desk's time really goes, which is almost never where people think.

This lesson has changed the object of study. Up to module 5 we were looking at structure: which tables, which columns, which constraints. From here on we look at behavior: what happens when the system is running and things go wrong.

The transaction is the unit you reason with about that behavior. We have seen that registering a loan in BiblioRed is three statements but a single fact, and that whoever decides where a unit of work begins and ends is not the management system but whoever writes the code. We have seen the complete vocabulary —BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO, RELEASE—, the autocommit mode that wraps every standalone statement in its own transaction, and the five-state cycle every transaction goes through, with that critical instant of "partially committed" in which the management system has not promised anything yet.

Of the four ACID properties we have developed three. Atomicity, which erases the intermediate states and makes a ROLLBACK in PostgreSQL cheaper than a COMMIT. Consistency, which is the halfway letter —the management system protects the rules you have declared to it, and only those, which turns every CHECK and every foreign key from module 4 into part of the transactional guarantee—. And durability, which we have opened right up: the write-ahead log that is synced before the data because writing sequentially is cheap and writing scattered is not; the checkpoint that bounds how much log has to be reread; the two-phase recovery —redo everything from the last checkpoint, then discard what was not committed— that returns the database exactly to the last COMMIT replied; and the price of all that, that fsync() which puts a hard ceiling on the number of transactions per second and which synchronous_commit lets you relax in exchange for accepting, in writing, what you are willing to lose.

We have also learned to live with PostgreSQL's strictness: a failed statement aborts the whole transaction, and the savepoint is the only way to survive an expected error. In exchange, PostgreSQL gives away something other management systems do not have: transactional DDL, and with it schema migrations that either apply in full or leave no trace.

One letter is left undeveloped, and it is the hardest of the four. Isolation we have stated —each transaction behaves as if it were alone— and we have seen its effect peek out in the two-terminal demonstration in section 3, where session B saw nothing of what session A was doing. But we have not said what happens when the two sessions touch the same row, nor what exactly each one sees, nor what happens if both decide at the same time that copy EJ-3081 is available and both lend it.

That is lesson 06-02, Concurrency and Isolation Levels: the four classic phenomena —lost update, dirty read, non-repeatable read, phantom read— triggered one by one with two psql terminals over BiblioRed's data; write skew, which surprises people even at high isolation levels; the four levels of the SQL standard with the table of what each one permits and what PostgreSQL really does; shared and exclusive locks with SELECT ... FOR UPDATE; the multiversion concurrency control that means readers never block writers in PostgreSQL, and the VACUUM that pays that bill; deadlocks, with two sessions waiting for each other forever until the management system kills one; and the final, complete solution to the problem already waiting for us in the North branch reading club's agenda: two members signing up in the same second for the last free seat.