websql

24 Managing Transaction Processing

Learning Objectives

:

Describe why transaction processing is needed to maintain database integrity.

Define the terms transaction, commit, rollback, and savepoint.

Use BEGIN and COMMIT to control a transaction block in PostgreSQL.

Use ROLLBACK to undo an uncommitted transaction.

Use SAVEPOINT and ROLLBACK TO SAVEPOINT to undo part of a transaction.

24.1 Understanding Transaction Processing

Transaction processing is used to maintain database integrity by ensuring that batches of SQL operations execute completely or not at all.

As explained back in the lesson on joining tables, relational databases are designed so that data is stored in multiple tables to facilitate easier data manipulation, management, and reuse. Without going in to the hows and whys of relational database design, take it as a given that well-designed database schemas are relational to some degree.

The artist, work, and subject tables that you've been using throughout this book are a good example of this. A cataloguing operation touches all three: artist stores the artists themselves, work stores the individual works of art, and subject stores the individual subjects depicted in each work. These tables are related to each other using unique IDs called primary keys (as discussed in the lesson on understanding SQL).

The process of cataloguing a new work is as follows:

Check if the artist is already in the database. If not, add them.
Retrieve the artist's ID.
Add a row to the work table associating it with the artist's ID (and the appropriate museum ID).
Retrieve the new work ID assigned in the work table.
Add one row to the subject table for each subject depicted in the work, associating it with the work table by the retrieved work ID.

Now imagine that some database failure (for example, out of disk space, security restrictions, table locks) prevents this entire sequence from completing. What would happen to your data?

Well, if the failure occurred after the artist was added and before the work row was added, there is no real problem. It is perfectly valid to have artists without any catalogued works. When you run the sequence again, the inserted artist record will be retrieved and used. You can effectively pick up where you left off.

But what if the failure occurred after the work row was added but before the subject rows were added? Now you'd have an unclassified work sitting in your database.

Worse, what if the system failed during adding the subject rows? Now you'd end up with a partially catalogued work in your database, but you wouldn't know it.

How do you solve this problem? That's where transaction processing comes in. Transaction processing is a mechanism used to manage sets of SQL operations that must be executed in batches so as to ensure that databases never contain the results of partial operations. With transaction processing, you can ensure that sets of operations are not aborted mid-processing — they either execute in their entirety or not at all (unless explicitly instructed otherwise). If no error occurs, the entire set of statements is committed (written) to the database tables. If an error does occur, then a rollback (undo) can occur to restore the database to a known and safe state.

So, if we look at the same example, this is how the process would work:

Check if the artist is already in the database; if not, add them.
Commit the artist information.
Retrieve the artist's ID.
Add a row to the work table.
If a failure occurs while adding the row to work, roll back.
Retrieve the new work ID assigned in the work table.
Add one row to the subject table for each subject depicted.
If a failure occurs while adding rows to subject, roll back all the subject rows added and the work row.

When you're working with transactions and transaction processing, a few keywords will keep reappearing. Here are the terms you need to know:

Transaction — A block of SQL statements
Rollback — The process of undoing specified SQL statements
Commit — Writing unsaved SQL statements to the database tables
Savepoint — A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)

Which Statements Can You Roll Back? Transaction processing is used to manage INSERT, UPDATE, and DELETE statements — these can always be rolled back. Unlike many other DBMSs, PostgreSQL also supports fully transactional DDL: statements like CREATE TABLE, ALTER TABLE, and DROP TABLE can be rolled back too, as long as they occur within an explicit transaction block. The one real exception is SELECT — there is nothing to roll back, since a SELECT never changes any data in the first place.

24.2 Controlling Transactions

Now that you know what transaction processing is, let's look at what is involved in managing transactions.

The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not.

PostgreSQL requires that you explicitly mark the start of a transaction block using BEGIN (the ANSI-standard START TRANSACTION also works, if you prefer that spelling):

BEGIN;
...
COMMIT;

In this example, any SQL between the BEGIN and COMMIT statements must be executed entirely or not at all. Notice that there isn't an explicit "end of transaction" keyword the way there is a BEGIN — the transaction simply continues until something terminates it, usually a COMMIT to save changes or a ROLLBACK to undo them, as will be explained next.

Autocommit Mode. Outside of an explicit BEGIN...COMMIT block, PostgreSQL runs each individual statement as its own transaction, automatically committed the moment it succeeds. This is important to keep in mind: if you don't wrap a set of related statements in BEGIN, each one is saved permanently as soon as it runs, whether or not the statements that follow it succeed.

24.3 Using ROLLBACK

The SQL ROLLBACK command is used to roll back (undo) SQL statements, as seen in this next example:

BEGIN;
DELETE FROM work;
ROLLBACK;

In this example, a DELETE operation is performed and then undone using a ROLLBACK statement. Notice the explicit BEGIN — without it, the DELETE would run in autocommit mode and be permanently saved the instant it executed, leaving nothing for the ROLLBACK to undo. Although not the most useful example, it does demonstrate that, within a transaction block, DELETE operations (like INSERT and UPDATE operations) are never final until committed.

24.4 Using COMMIT

Outside of an explicit transaction, PostgreSQL statements are executed and written directly to the database tables the moment they succeed. This is known as an implicit commit — the commit (write or save) operation happens automatically.

Within a transaction block, however, nothing is written to the database tables until an explicit COMMIT is issued (or the transaction is rolled back instead).

To force an explicit commit, you use the COMMIT statement. The following example deletes the work with work_id 210 entirely from the system:

BEGIN;
DELETE FROM subject WHERE work_id = 210;
DELETE FROM work WHERE work_id = 210;
COMMIT;

In this example, work number 210 is deleted entirely from the system. Because this involves updating two database tables, work and subject, a transaction block is used to ensure that the work is not partially deleted. The final COMMIT statement writes the changes only if no error occurred. If the first DELETE worked but the second failed, neither DELETE would be committed — PostgreSQL would instead leave the transaction in a failed state, waiting for you to issue a ROLLBACK.

24.5 Using Savepoints

Simple ROLLBACK and COMMIT statements enable you to write or undo an entire transaction. Although this approach works for simple transactions, more complex transactions might require partial commits or rollbacks.

For example, the process of cataloguing a new work described previously is a single transaction. If an error occurs while adding the work or its subjects, you only want to roll back to the point before the work row was added. You do not want to roll back the addition to the artist table (if there was one).

To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders.

In PostgreSQL, these placeholders are called savepoints, created with the SAVEPOINT statement:

SAVEPOINT start_work;

Each savepoint takes a unique name that identifies it so that, when you roll back, the DBMS knows where you are rolling back to. To roll back to this savepoint, you do the following:

ROLLBACK TO SAVEPOINT start_work;

The following is a complete example:

BEGIN;

INSERT INTO artist (artist_id, full_name, first_name, last_name, nationality)
VALUES (502, 'Mira Petrova', 'Mira', 'Petrova', 'Bulgarian');

SAVEPOINT start_work;

INSERT INTO work (work_id, artist_id, museum_id, name, style)
VALUES (301, 502, 3, 'Winter Market', 'Realist');

INSERT INTO subject (work_id, subject)
VALUES (301, 'Marketplace');

INSERT INTO subject (work_id, subject)
VALUES (301, 'Winter Landscape');

COMMIT;

Here four INSERT statements are enclosed within a transaction block. A savepoint is defined after the first INSERT so that, if any of the subsequent INSERT operations fail, the transaction can be rolled back only that far, rather than losing the artist row too.

Error Handling Is Stricter in PostgreSQL. Unlike some other DBMSs, plain PostgreSQL SQL has no equivalent of an inline @@ERROR-style variable that lets you check the outcome of the previous statement and decide whether to continue. Instead, PostgreSQL takes a stricter approach: the moment any statement inside a transaction fails, the entire transaction is marked as aborted, and every subsequent statement is rejected until you issue either ROLLBACK (undoing everything back to BEGIN) or ROLLBACK TO SAVEPOINT (undoing everything back to a named savepoint). In practice, this means that if, say, the second INSERT INTO subject above were to fail, you would recover with:
ROLLBACK TO SAVEPOINT start_work;
This would undo the failed work and both subject inserts, while leaving the artist row (added before the savepoint) intact and still pending inside the open transaction. You could then correct the problem and try the work and subject inserts again, or simply COMMIT with just the artist row saved.

The More Savepoints the Better. You can have as many savepoints as you'd like within your SQL code, and the more the better. Why? Because the more savepoints you have, the more flexibility you have in managing rollbacks exactly as you need them.

24.6 Summary

In this lesson, you learned that transactions are blocks of SQL statements that must be executed as a batch. You learned that COMMIT and ROLLBACK statements are used to explicitly manage when data is written and when it is undone. You also learned that savepoints provide a greater level of control over rollback operations, and that PostgreSQL's stricter transaction-abort behavior means you must roll back (fully or to a savepoint) before a failed transaction can continue. Transaction processing is a really important topic, and one that is far beyond the scope of one lesson. Refer to the PostgreSQL documentation for further details.