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 
            | WRITE COMMITTED
            | 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. A START TRANSACTION statement suspends autocommit behavior except for Data Definition Language (DDL) statements. A DDL statement in a transaction is automatically committed and it cannot be rolled back. This is true of any DDL statement, inside or outside a transaction, regardless of the autocommit setting.
Transactions can contain one or more Data Manipulation Language (DML) statements, including those defined in functions, triggers and stored procedures. During a transaction opened by START TRANSACTION, autocommit behavior is suspended for DML 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

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;