START TRANSACTION

Starts a transaction block.

Syntax

START TRANSACTION [ isolation_level ]

where isolation_level is one of:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }READ { ONLY | WRITE }

Parameters

Isolation level, described in the following table, determines what data the transaction can access when other transactions are running concurrently. The isolation level cannot be changed after the first query (SELECT) or DML statement (INSERT, DELETE, UPDATE) has run. A transaction retains its isolation level until it completes, even if the session's isolation level changes during the transaction. Vertica internal processes (such as the Tuple Mover and refresh operations) and DDL operations always run at the SERIALIZABLE isolation level to ensure consistency. 

WORK | TRANSACTION

Have no effect; they are optional keywords for readability.

 
ISOLATION LEVEL { 
   SERIALIZABLE |
   REPEATABLE READ |
   READ COMMITTED |
   READ UNCOMMITTED 
}
  • SERIALIZABLE—Sets the strictest level of SQL transaction isolation. This level emulates transactions serially, rather than concurrently. It holds locks and blocks write operations until the transaction completes. Not recommended for normal query operations.
  • REPEATABLE READ—Automatically converted to SERIALIZABLE by Vertica.
  • READ COMMITTED (Default)—Allows concurrent transactions. Use READ COMMITTED isolation for normal query operations, but be aware that there is a subtle difference between them. SeeTransactionsfor more information.
  • READ UNCOMMITTED—Automatically converted to READ COMMITTED by Vertica.
 
READ {WRITE | ONLY}

Determines whether the transaction is read/write or read-only. Read/write is the default.

Setting the transaction session mode to read-only disallows the following SQL commands, but does not prevent all disk write operations:

  • INSERT, UPDATE, DELETE, and COPY if the table they would write to is not a temporary table
  • All CREATE, ALTER, and DROP commands
  • GRANT, REVOKE, and EXPLAIN if the command it would run is among those listed.

Privileges

None

Notes

BEGIN performs the same function as START TRANSACTION.

Examples

This example shows how to start a transaction.

= > START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
START TRANSACTION
=> CREATE TABLE sample_table (a INT);
CREATE TABLE
=> INSERT INTO sample_table (a) VALUES (1);
OUTPUT
--------
1
(1 row)

See Also