START TRANSACTION

START TRANSACTION — starts a transaction and lets you specify a transaction isolation level.

Syntax

START TRANSACTION
   [   transaction_qualifier
     | transaction_isolation_level
     | transaction_qualifier, transaction_isolation_level
     | transaction_isolation_level, transaction_qualifier
   ]

where transaction_qualifier is:

READ { ONLY | WRITE }

where transaction_isolation_level is:

ISOLATION LEVEL
          {   CONSISTENT READ
            | READ COMMITTED
            | REPEATABLE READ
            | SERIALIZABLE
          }

Description

The START TRANSACTION statement explicitly starts a transaction. The benefit of the START TRANSACTION statement is that you can override About Default Transaction Behavior.
By default, NuoDB enables autocommit behavior. This means that NuoDB starts a transaction to execute each statement. After running a statement, NuoDB commits the transaction.
Transactions can contain one or more statements, including those defined in functions, triggers and stored procedures. During a transaction opened by START TRANSACTION, autocommit behavior is suspended for statements.
When there is no START TRANSACTION statement then execution of a statement implicitly starts a transaction. Such a transaction is always a read-write transaction that uses the default transaction isolation level and the default autocommit setting.
To end a transaction, specify either a COMMIT or ROLLBACK statement (See COMMIT, ROLLBACK).
In a transaction started with START TRANSACTION, NuoDB executes any SET AUTOCOMMIT statement (See SET) and suspends the specified autocommit behavior until after the transaction ends. For example, specification of SET AUTOCOMMIT ON inside an explicitly started transaction does not affect that transaction.

Parameters

READ ONLY | READ WRITE: Defines whether the transaction can only read the data, or can also update the data. READ WRITE is the default.
ISOLATION LEVEL: If you specify an ISOLATION LEVEL then it defines how this transaction can view DML and DDL updates performed by itself and other, concurrent transactions. The ISOLATION LEVEL for a transaction remains in effect until either a COMMIT or a ROLLBACK is issued. See Transactions and Isolation Levels for a detailed description of each transaction isolation level that NuoDB supports.

Example

The following are examples of the transaction qualifiers that NuoDB supports. See Transactions and Isolation Levels for examples of the isolation levels that NuoDB supports.

START TRANSACTION READ ONLY;
CREATE TABLE t1 (n1 integer);
   /* ERROR: Read only transactions cannot create tables. */
ROLLBACK;

START TRANSACTION READ WRITE;
CREATE TABLE t1 (n1 integer);
INSERT INTO  t1 (n1) VALUES (1), (2), (3);
SELECT * FROM t1;
 N1
 ---
  1
  2
  3
COMMIT;
START TRANSACTION READ ONLY;
UPDATE t1 SET n1=n1+10;
     /* ERROR: Read only transactions cannot change data. */
ROLLBACK;
START TRANSACTION READ WRITE;
UPDATE t1 SET n1=n1+10;
SELECT * FROM t1;
 N1
 ---
 11
 12
 13
COMMIT;