BEGIN

Synopsis

Use the BEGIN statement to start a new transaction with the default (or given) isolation level.

Syntax

  1. begin ::= BEGIN [ TRANSACTION | WORK ] [ transaction_mode [ ... ] ]

begin

BEGIN - 图1

Semantics

begin

  1. BEGIN [ TRANSACTION | WORK ] [ transaction_mode [ ... ] ]

WORK

Add optional keyword — has no effect.

TRANSACTION

Add optional keyword — has no effect.

transaction_mode

Supports both Serializable and Snapshot Isolation using the PostgreSQL isolation level syntax of SERIALIZABLE and REPEATABLE READS respectively. Even READ COMMITTED and READ UNCOMMITTED isolation levels are mapped to Snapshot Isolation.

Note that the Serializable isolation level support was added in v1.2.6. The examples on this page have not been updated to reflect this recent addition.

Examples

Create a sample table.

  1. CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

Begin a transaction and insert some rows.

  1. BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
  1. INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (1, 3.0, 4, 'b');

Start a new shell with ysqlsh and begin another transaction to insert some more rows.

  1. BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
  1. INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3, 'a'), (2, 3.0, 4, 'b');

In each shell, check the only the rows from the current transaction are visible.

1st shell.

  1. yugabyte=# SELECT * FROM sample; -- run in first shell
  1.  k1 | k2 | v1 | v2
  2. ----+----+----+----
  3.   1 |  2 |  3 | a
  4.   1 |  3 |  4 | b
  5. (2 rows)

2nd shell

  1. yugabyte=# SELECT * FROM sample; -- run in second shell
  1.  k1 | k2 | v1 | v2
  2. ----+----+----+----
  3.   2 |  2 |  3 | a
  4.   2 |  3 |  4 | b
  5. (2 rows)

Commit the first transaction and abort the second one.

  1. COMMIT TRANSACTION; -- run in first shell.

Abort the current transaction (from the first shell).

  1. ABORT TRANSACTION; -- run second shell.

In each shell check that only the rows from the committed transaction are visible.

  1. yugabyte=# SELECT * FROM sample; -- run in first shell.
  1.  k1 | k2 | v1 | v2
  2. ----+----+----+----
  3.   1 |  2 |  3 | a
  4.   1 |  3 |  4 | b
  5. (2 rows)
  1. yugabyte=# SELECT * FROM sample; -- run in second shell.
  1.  k1 | k2 | v1 | v2
  2. ----+----+----+----
  3.   1 |  2 |  3 | a
  4.   1 |  3 |  4 | b
  5. (2 rows)

See also