Contents / Previous / Next

Transactions, Isolation and Locks

A DB allows users to view and modify information simultaneously and ensure data integrity.

Every SQL query is executed in a transaction. Transactions give databases an all-or-nothing capability when making modifications.

Commands controling transaction and database access are the subset of SQL called the Data Control Language (DCL).

Example: Suppose you were executing a query to add $500 to everyone's salary and accidentally kicked the power cord out of the wall during the update procedure. Without transactions, the query may have updated half the salaries, but not the rest. It would be difficult to know where the UPDATE stopped.
With transactions, you can check to see if any of the rows were updated. If one was updated, then all were updated. If not, you can simply re-execute the query.


Multi-statement Transactions

With BEGIN...COMMIT you can bind multiple statements into a single transaction. Either all statements are executed to completion or none has any effect.

Example:
Suppose you have a table of bank account balances, and you wish to transfer $100 from one account to another account:

BEGIN work; UPDATE bankacct SET balance = balance - 100 WHERE acctno = '82021'; UPDATE bankacct SET balance = balance + 100 WHERE acctno = '96814'; COMMIT work; The multi-statement transaction guarantee that the UPDATEs are either both completed or have no effect.

A group of statements surrounded by BEGIN and COMMIT is called a transaction block.

atomic updates: Being atomic is an important property of updates, each update is implicity inclosed into a transaction.


ROLLBACK

When you begin a transaction with BEGIN, you do not have to commit it using COMMIT. Instead, you can close the transaction with ROLLBACK and the transaction will be discarded. The database is left as though the transaction had never been executed. Example: BEGIN work ; DELETE FROM assign WHERE stud=1; ROLLBACK work ; The "work" transaction is rolled back, causing the DELETE to have no effect.

Likewise, if any query inside a multistatement transaction cannot be executed due to an error, the entire transaction is automatically rolled back.


Visibility of Transactions

When multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others.
Per default only committed transactions are visible to users. Although the current user sees his changes, other users do not see them until the transaction is committed.


Transaction Isolation

Each transaction is isolated from other active transactions. But there are different levels of isolation.

The isolation level discussed so far is called SERIALIZABLE isolation.
SERIALIZABLE isolation forces the database to execute all transactions as though they were run serially (one after another), even if they are run concurrently.
SERIALIZABLE isolation provides a stable view of the database for SELECT transactions.
For transactions containing UPDATE and DELETE queries, if two concurrent transactions attempt to UPDATE the same row, serializability is impossible. In such a case, PostgreSQL forces one transaction to roll back.

ANSI/ISO SQL standard levels of transaction isolation:

SERIALIZABLE
The current transaction can only see rows committed before first query or data-modification statement was executed in this transaction.

Repeatable read
It is not possible that a transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

READ COMMITTED
A transaction re-reads data that has been modified and commited by another transaction.

Read Uncommitted (dirty reads are possible)
A transaction reads data written by concurrent uncommitted transaction.
PostgreSQL offers the READ COMMITTED and SERIALIZABLE isolation levels.


Locks

Exclusive locks prevent other users from modifying a row or an entire table until the current transaction is either committed or rolled back.

Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction.

You can use SELECT...FOR UPDATE to lock selected rows (on which you want to base an update) until the end of the current transaction. Example:

BEGIN ; SELECT * FROM stud WHERE name='john' FOR UPDATE ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 4 | john | 3 | phy | m (1 row) UPDATE stud SET name='john-boy' WHERE name='john' ; COMMIT ; Another user cannot modify the John's row between the SELECT...FOR UPDATE and UPDATE. In fact, the row remains locked until the transaction ends.


LOCK -- explicitly lock a table

You can also manually lock tables until the end of a transaction: LOCK [ TABLE ] name [, ...] LOCK [ TABLE ] name [, ...] IN lockmode MODE where lockmode is one of: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE SHARE MODE protects a table against concurrent data updates.

Example:

BEGIN WORK; LOCK TABLE stud IN SHARE MODE; . . COMMIT WORK;