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 and BEGIN WORK statements (used as the alias of START TRANSACTION) and explicitly end the transaction by using the COMMIT or ROLLBACK statement.

Syntax

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

Parameters

Examples

• Assume that you have table a with the following data in your database.

  id	name	num	sell_date
  1	a	100	2013-06-21 10:06:43
  2	b	200	2013-06-21 13:07:21
  3	a	50	2013-06-21 13:08:15

1. Execute the following statements in sequence to start the transaction, change the name value of the record whose id is 3 to c, and insert a row for the record of selling a.

   obclient> START TRANSACTION;
   Query OK, 0 rows affected 
   
   obclient> UPDATE a SET name = 'c' WHERE id = 3;
   Query OK, 1 rows affected  
   
   obclient> INSERT INTO a VALUES (4, 'a', 30, '2013-06-21 16:09:13');
   Query OK, 1 rows affected  
   
   obclient> COMMIT;
   Query OK, 0 rows affected
   

2. Commit the transaction and execute the following statement to query the information of table a:

   SELECT * FROM a;
   

   Result:

   id	name	num	sell_date
   1	a	100	2013-06-21 10:06:43
   2	b	200	2013-06-21 13:07:21
   3	c	50	2013-06-21 13:08:15
   4	a	30	2013-06-21 16:09:13

   Notice

   • Before a transaction is committed, you can view whether the operation in the transaction has taken effect. For example, you can execute the SELECT * FROM a statement before you commit a transaction.
   • You can access the sessions of the current transaction to obtain the latest result. Access to sessions beyond the current transaction does not take effect. Before you COMMIT the transaction, your operations are visible only to the current transaction session.
   • To roll back a transaction, replace COMMIT with ROLLBACK.