Work with Transactions in Cloudberry Database

Transactions allow you to bundle multiple SQL statements in one all-or-nothing operation.

The following are the Cloudberry Database SQL transaction commands:

• BEGIN or START TRANSACTION starts a transaction block.
• END or COMMIT commits the results of a transaction.
• ROLLBACK abandons a transaction without making any changes.
• SAVEPOINT marks a place in a transaction and enables partial rollback. You can roll back commands run after a savepoint while maintaining commands run before the savepoint.
• ROLLBACK TO SAVEPOINT rolls back a transaction to a savepoint.
• RELEASE SAVEPOINT destroys a savepoint within a transaction.

Transaction isolation levels

Cloudberry Database accepts the standard SQL transaction levels as follows:

• READ UNCOMMITTED and READ COMMITTED behave like the standard READ COMMITTED.
• REPEATABLE READ and SERIALIZABLE behave like REPEATABLE READ.

The following information describes the behavior of the Cloudberry Database transaction levels.

Read uncommitted and read committed

Cloudberry Database does not allow any command to see an uncommitted update in another concurrent transaction, so READ UNCOMMITTED behaves the same as READ COMMITTED. READ COMMITTED provides fast, simple, partial transaction isolation. SELECT, UPDATE, and DELETE commands operate on a snapshot of the database taken when the query started.

A SELECT query:

• Sees data committed before the query starts.
• Sees updates run within the transaction.
• Does not see uncommitted data outside the transaction.
• Can possibly see changes that concurrent transactions made if the concurrent transaction is committed after the initial read in its own transaction.

Successive SELECT queries in the same transaction can see different data if other concurrent transactions commit changes between the successive queries. UPDATE and DELETE commands find only rows committed before the commands started.

READ COMMITTED transaction isolation allows concurrent transactions to modify or lock a row before UPDATE or DELETE find the row. READ COMMITTED transaction isolation might be inadequate for applications that perform complex queries and updates and require a consistent view of the database.

Repeatable read and serializable

SERIALIZABLE transaction isolation, as defined by the SQL standard, ensures that transactions that run concurrently produce the same results as if they were run one after another. If you specify SERIALIZABLE Cloudberry Database falls back to REPEATABLE READ. REPEATABLE READ transactions prevent dirty reads, non-repeatable reads, and phantom reads without expensive locking, but Cloudberry Database does not detect all serializability interactions that can occur during concurrent transaction execution. Concurrent transactions should be examined to identify interactions that are not prevented by disallowing concurrent updates of the same data. You can prevent these interactions by using explicit table locks or by requiring the conflicting transactions to update a dummy row introduced to represent the conflict.

With REPEATABLE READ transactions, a SELECT query:

• Sees a snapshot of the data as of the start of the transaction (not as of the start of the current query within the transaction).
• Sees only data committed before the query starts.
• Sees updates run within the transaction.
• Does not see uncommitted data outside the transaction.
• Does not see changes that concurrent transactions make.
• Successive SELECT commands within a single transaction always see the same data.
• UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands find only rows committed before the command started. If a concurrent transaction has updated, deleted, or locked a target row, the REPEATABLE READ transaction waits for the concurrent transaction to commit or roll back the change. If the concurrent transaction commits the change, the REPEATABLE READ transaction rolls back. If the concurrent transaction rolls back its change, the REPEATABLE READ transaction can commit its changes.

The default transaction isolation level in Cloudberry Database is READ COMMITTED. To change the isolation level for a transaction, declare the isolation level when you BEGIN the transaction or use the SET TRANSACTION command after the transaction starts.

See also

• Transactional Concurrency Control
• Insert, Update, and Delete Rows