Practical PostgreSQL

Transactions and Cursors

PostgreSQL uses a multi-version approach to transactions within the database. A transaction is a formal term for a SQL statement’s effects being synchronized with the “current” data in the database. This doesn’t necessarily mean that the data is written to disk, but it becomes part of the “current” set of information stored in the database. When a statement’s results have effectively been processed in the current state of the database, the transaction is considered to be committed.

The issue of two users attempting to commit changes to the same database object is obviously a potential concern, as their modifications may be exclusive to one another. Some database systems rely on locking to prevent such conflicts.

Locking is a mechanism that disallows selecting from a database object while it is being modified, and vice versa. Locking presents several obvious performance concerns. For example, data which is being updated will not be selectable until the update transaction has completed.

PostgreSQL’s Multi-Version Concurrency Control (MVCC), however, allows for SQL statements to be performed within transaction-deferred blocks. This means that each connection to PostgreSQL essentially maintains a temporary snapshot of the database for objects modified within a transaction block, before the modifications are committed.

Without explicitly opening a transaction block, all SQL statements issued to PostgreSQL are auto-committed, meaning that the database ...