TRANSACTION

Purpose

You can use this statement to start a transaction.

A database transaction is a series of operations performed as a logical unit of work. A transaction can be used to maintain the integrity of the database by ensuring that SQL operations in a batch are either all executed or none of them are executed.

Explicit transactions are user-defined or user-specified transactions. You can explicitly start a transaction by using the BEGIN or BEGIN WORK statement (alias of START TRANSACTION) and explicitly end the transaction by using the COMMIT or ROLLBACK statement.

Note

• Before committing a transaction, you can execute a SELECT statement to check whether the operations in the transaction have taken effect.
• Before a transaction is committed, the operations in this transaction are invisible to other sessions except the session of the current transaction. In other words, before a transaction is committed, only the session of the current transaction can read the latest results.
• To roll back a transaction, replace COMMIT with ROLLBACK.

Syntax

transaction_stmt:
      START TRANSACTION [READ ONLY  READ WRITE];
     BEGIN [WORK];
     COMMIT [WORK];
     ROLLBACK [WORK];
     SET TRANSACTION {READ ONLY  READ WRITE};

Parameters

Examples

Create a table named tbl1.

obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
 ID    NAME  NUM  
+------+------+------+
    1  a      100 
    2  b      200 
    3  a       50 
+------+------+------+
3 rows in set

• Start the transaction, change name to c for the row where id is 3, and insert one row. Commit the transaction and query the information about the tbl1 table.

  obclient> START TRANSACTION;
  Query OK, 0 rows affected
  
  obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> INSERT INTO tbl1 VALUES (4, 'a', 30);
  Query OK, 1 row affected
  
  obclient> COMMIT;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
   ID    NAME  NUM  
  +------+------+------+
      1  a      100 
      2  b      200 
      3  c       50 
      4  a       30 
  +------+------+------+
  4 rows in set
  

• Start the transaction, change name to c for the row where id is 3, insert one row, and query the information about the tbl1 table. Roll back the transaction and query the information about the tbl1 table.

  obclient> BEGIN;
  Query OK, 0 rows affected
  
  obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> INSERT INTO tbl1 VALUES (4, 'd', 300);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
   ID    NAME  NUM  
  +------+------+------+
      1  a      100 
      2  b      200 
      3  c       50 
      4  d      300 
  +------+------+------+
  4 rows in set
  
  obclient> ROLLBACK ;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
   ID    NAME  NUM  
  +------+------+------+
      1  a      100 
      2  b      200 
      3  a       50 
  +------+------+------+
  3 rows in set