START TRANSACTION

Description

START TRANSACTION starts a transaction. If the isolation level and access mode are specified, the new transaction will have those characteristics. You can also specify them using SET TRANSACTION.

Syntax

Format 1: START TRANSACTION

START TRANSACTION
  [ 
    { 
       ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
       | { READ WRITE | READ ONLY } | WITH CONSISTENT SNAPSHOT
     } [ ...] 
  ];

Format 2: BEGIN

BEGIN [ WORK | TRANSACTION ]
  [ 
    { 
       ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
       | { READ WRITE | READ ONLY }
      } [ ...] 
  ];

Parameters

WORK | TRANSACTION
Specifies an optional keyword in the BEGIN format and does not have any actual effect.
ISOLATION LEVEL
Specifies the transaction isolation level, which determines the data that the transaction can view if there are other concurrent transactions.

The isolation level cannot be changed after data is modified using SELECT, INSERT, DELETE, UPDATE, or COPY in the current transaction.

Value range:
- READ COMMITTED: Only committed data can be read. This is the default.
- READ UNCOMMITTED: After this isolation level is set, its behavior is the same as that of READ COMMITTED.
- REPEATABLE READ: Only the data committed before the transaction starts can be read. Uncommitted data or data committed in other concurrent transactions cannot be read.
- SERIALIZABLE: Currently, this isolation level is not supported in an M-compatible database. It is equivalent to REPEATABLE READ.
READ WRITE | READ ONLY
Specifies the transaction access mode (read/write or read-only).
WITH CONSISTENT SNAPSHOT
Generates a snapshot when a transaction is started. It takes effect only at the repeatable read isolation level. At other levels, an alarm is generated.

If this parameter is disabled, at the repeatable read isolation level, a consistent read snapshot is generated for a transaction after the first data modification statement (DML, DDL, or DCL) in the transaction is executed. You can set this parameter multiple times.

Examples

-- Create a schema.
m_db=# CREATE SCHEMA tpcds;

-- Create a table named tpcds.reason.
m_db=# CREATE TABLE tpcds.reason (c1 int, c2 int);

-- Start a transaction in default mode.
m_db=# START TRANSACTION;
m_db=# SELECT * FROM tpcds.reason;
m_db=# COMMIT;

-- Start a transaction in default mode.
m_db=# BEGIN;
m_db=# SELECT * FROM tpcds.reason;
m_db=# COMMIT;

-- Start a transaction with the isolation level being READ COMMITTED and the access mode being READ WRITE:
m_db=# START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
m_db=# SELECT * FROM tpcds.reason;
m_db=# COMMIT;

-- At the repeatable read isolation level, a transaction is started with WITH CONSISTENT SNAPSHOT.
m_db=# START TRANSACTION WITH CONSISTENT SNAPSHOT ISOLATION LEVEL REPEATABLE READ;
m_db=# SELECT * FROM tpcds.reason;
m_db=# COMMIT;

-- At the non-repeatable read isolation level, a transaction is started with WITH CONSISTENT SNAPSHOT.
m_db=# START TRANSACTION WITH CONSISTENT SNAPSHOT ISOLATION LEVEL READ COMMITTED;
WARNING:  WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level.
m_db=# SELECT * FROM tpcds.reason;
m_db=# COMMIT;

-- Drop the tpcds.reason table.
m_db=# DROP TABLE tpcds.reason;

-- Drop the schema.
m_db=# DROP SCHEMA tpcds;