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 (which are aliases of START TRANSACTION) and explicitly end the transaction by using the COMMIT or ROLLBACK statement.

Syntax

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

Parameters

Examples

This section provides examples of using transaction-related statements.

1. Create a sample table employees and insert data:

   obclient> CREATE TABLE employees (
     emp_id INT,
     emp_name VARCHAR(10),
     salary INT
   );
   
   obclient> INSERT INTO employees VALUES 
     (1, 'Alice', 5000),
     (2, 'Bob', 6000),
     (3, 'Charlie', 4500);
   

   View the initial data:

   obclient> SELECT * FROM employees;
   

   Result:

   +--------+---------+--------+
   | EMP_ID | EMP_NAME| SALARY |
   +--------+---------+--------+
   |      1 | Alice   |   5000 |
   |      2 | Bob     |   6000 |
   |      3 | Charlie |   4500 |
   +--------+---------+--------+
   

2. Example of committing a transaction: start a transaction, update employee information, and add a new employee.

   obclient> START TRANSACTION;
   
   obclient> UPDATE employees SET emp_name = 'Charles' WHERE emp_id = 3;
   obclient> INSERT INTO employees VALUES (4, 'David', 5500);
   
   obclient> COMMIT;
   

   View the updated data:

   obclient> SELECT * FROM employees;
   

   Result:

   +--------+---------+--------+
   | EMP_ID | EMP_NAME| SALARY |
   +--------+---------+--------+
   |      1 | Alice   |   5000 |
   |      2 | Bob     |   6000 |
   |      3 | Charles |   4500 |
   |      4 | David   |   5500 |
   +--------+---------+--------+
   

3. Example of rolling back a transaction: start a transaction, make changes, then roll back.

   obclient> BEGIN;
   
   obclient> UPDATE employees SET emp_name = 'Eve' WHERE emp_id = 3;
   obclient> INSERT INTO employees VALUES (5, 'Frank', 7000);
   
   -- View changes within the transaction
   obclient> SELECT * FROM employees;
   

   Result:

   +--------+---------+--------+
   | EMP_ID | EMP_NAME| SALARY |
   +--------+---------+--------+
   |      1 | Alice   |   5000 |
   |      2 | Bob     |   6000 |
   |      3 | Eve     |   4500 |
   |      4 | David   |   5500 |
   |      5 | Frank   |   7000 |
   +--------+---------+--------+
   

   Perform a rollback:

   obclient> ROLLBACK;
   

   View the data after rollback:

   obclient> SELECT * FROM employees;
   

   Result:

   +--------+---------+--------+
   | EMP_ID | EMP_NAME| SALARY |
   +--------+---------+--------+
   |      1 | Alice   |   5000 |
   |      2 | Bob     |   6000 |
   |      3 | Charles |   4500 |
   |      4 | David   |   5500 |
   +--------+---------+--------+