» Documentation » SQL Reference

Transactions

BEGIN TRANSACTION

Syntax

BEGIN {TRAN | TRANSACTION} [READ COMMITTED | READ REPEATABLE | SERIALIZABLE] 

Description

Start a (read committed by default) transaction. Changes within a transaction will either be entirely committed atomically, or aborted.

Specifying READ COMMITTED, READ REPEATABLE, or SERIALIZABLE sets the transaction isolation level.

COMMIT TRANSACTION

Syntax

COMMIT [TRAN | TRANSACTION] 

Description

Commits the currently in progress transaction.

ROLLBACK

Syntax

ROLLBACK [TRAN | TRANSACTION] 

Description

Aborts and reverts back any modifications since the beginning of the transaction.

GO

Syntax

GO 

Description

This statement will force all queries in the current transaction to be processed by the query optimizer. This is necessary to seperate DDL and DML queries. Without specifying GO, between DDL queries that define structures that subsequent DML queries rely on, the query optimizer will try and combine the queries into a single internal DQL transaction, but fail because not all databases, tables and column names have been defined yet.

GO does not affect the atomicity of any current transactions. That is, doing START TRANSACTION, CREATE TABLE, GO, INSERT, then ROLLBACK will still roll back creation of a table.

Examples

Simple GO example

CREATE DATABASE IF NOT EXISTS example;
DROP TABLE IF EXISTS example.table_go_test;
CREATE TABLE example.table_go_test
  (id INT NOT NULL PARTITION PRIMARY KEY);
GO;
INSERT INTO example.table_go_test VALUES (1);
INSERT INTO example.table_go_test VALUES (2);
SELECT * FROM example.table_go_test; 

Multi-table GO example

CREATE DATABASE IF NOT EXISTS example;
DROP TABLE IF EXISTS example.go1;
CREATE TABLE example.go1 (
    go1_id INT NOT NULL PARTITION,
    title VARCHAR(0),
    CONSTRAINT pkey PRIMARY KEY (id));
DROP TABLE IF EXISTS example.go2;
CREATE TABLE example.go2 (
    go2_id INT NOT NULL PARTITION,
    go1_id INT,
    CONSTRAINT pkey PRIMARY KEY (go2_id),
    CONSTRAINT go1_fkey FOREIGN KEY (link) REFERENCES example.go1 (go1_id));
GO;
INSERT INTO example.go1 VALUES (1,'First');
INSERT INTO example.go2 VALUES (1,1);
INSERT INTO example.go2 VALUES (2,1);
INSERT INTO example.go1 VALUES (2,'Second');
INSERT INTO example.go2 VALUES (3,2);
INSERT INTO example.go2 VALUES (4,2);
SELECT go1.go1_id, go1.title, go2.go2_id
    FROM example.go1 JOIN example.go2 ON go1.go1_id=go2.go1_id;
CREATE TABLE followed by ALTER TABLE:

CREATE TABLE test_table1 (
    col1 BIGINT NOT NULL PARTITION,
    CONSTRAINT pkey PRIMARY KEY (id));
GO;
ALTER TABLE test_table1 (ADD col2 BIGINT); 

SET FLUSH LOG

Turn off/on transaction's data flushing to disk before commit. By default, each transaction has flush log set to ON. The command will affect only current connection's transaction.

Syntax

SET FLUSH LOG {ON|OFF} 

Description

To speed up single update/insert/delete transaction, you may turn off log flushing per each transaction on commit. Instead, it will be flushed on checkpoint, or, if any other ACID transaction commits. NOTE: after crash or unexpected DB termination, the data modification after the last checkpoint (default, every 5 minutes) may be lost. Hence, turning off immediate logging will not corrupt database.