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 relational database systems rely on automatic
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 is synchronized with the results of the statement immediately
upon execution. When a transaction block is used, however, changes made to the database will not be visible to other users
until the block is committed. This allows for several changes to various objects within a database to be made tentatively.
They can then be either committed all at once, or rolled back.
Rolling back a transaction returns the state of any affected objects to the condition they were in before the
transaction block began. This can be useful when recovering from a partially failed operation, in
that any modifications made part-way into a process can be undone. Rolled back transactions are never actually
committed; while the process appears to undo modifications to the user who performed the rollback, other users connected to
the same database never know the difference.
PostgreSQL also supports
cursors
, which are flexible references to fully executed SQL queries. A
cursor is able to traverse up and down a result set, and only retrieve those rows which are explicitly requested. Used
properly, a cursor can aid an application in efficiently use a static result set. A cursor may only be executed within a
transaction block.
The following sections cover the basic use of transactions and cursors. They show how to begin, commit, and roll
back transactions, and also how to declare, move, and fetch data from a cursor.
