BEGIN - Amazon Redshift

BEGIN

Starts a transaction. Synonymous with START TRANSACTION.

A transaction is a single, logical unit of work, whether it consists of one command or multiple commands. In general, all commands in a transaction run on a snapshot of the database whose starting time is determined by the value set for the transaction_snapshot_begin system configuration parameter.

By default, individual Amazon Redshift operations (queries, DDL statements, loads) are automatically committed to the database. If you want to suspend the commit for an operation until subsequent work is completed, you need to open a transaction with the BEGIN statement, then run the required commands, then close the transaction with a COMMIT or END statement. If necessary, you can use a ROLLBACK statement to stop a transaction that is in progress. An exception to this behavior is the TRUNCATE command, which commits the transaction in which it is run and can't be rolled back.

Syntax

BEGIN [ WORK | TRANSACTION ] [ ISOLATION LEVEL option ] [ READ WRITE | READ ONLY ] START TRANSACTION [ ISOLATION LEVEL option ] [ READ WRITE | READ ONLY ] Where option is SERIALIZABLE | READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ Note: READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ have no operational impact and map to SERIALIZABLE in Amazon Redshift. You can see database isolation levels on your cluster by querying the stv_db_isolation_level table.

Parameters

WORK

Optional keyword.

TRANSACTION

Optional keyword; WORK and TRANSACTION are synonyms.

ISOLATION LEVEL SERIALIZABLE

Serializable isolation is supported by default, so the behavior of the transaction is the same whether or not this syntax is included in the statement. For more information, see Managing concurrent write operations. No other isolation levels are supported.

Note

The SQL standard defines four levels of transaction isolation to prevent dirty reads (where a transaction reads data written by a concurrent uncommitted transaction), nonrepeatable reads (where a transaction re-reads data it read previously and finds that data was changed by another transaction that committed since the initial read), and phantom reads (where a transaction re-runs a query, returns a set of rows that satisfy a search condition, and then finds that the set of rows has changed because of another recently committed transaction):

  • Read uncommitted: Dirty reads, nonrepeatable reads, and phantom reads are possible.

  • Read committed: Nonrepeatable reads and phantom reads are possible.

  • Repeatable read: Phantom reads are possible.

  • Serializable: Prevents dirty reads, nonrepeatable reads, and phantom reads.

Though you can use any of the four transaction isolation levels, Amazon Redshift processes all isolation levels as serializable.

READ WRITE

Gives the transaction read and write permissions.

READ ONLY

Gives the transaction read-only permissions.

Examples

The following example starts a serializable transaction block:

begin;

The following example starts the transaction block with a serializable isolation level and read and write permissions:

begin read write;