TRANSACTION

Purpose

This statement is used to start a transaction.

A database transaction is a series of operations that are executed as a single logical unit. Transaction processing can be used to maintain the integrity of the database, ensuring that a batch of SQL operations is either all executed or none of them are executed.

A displayed transaction is a user-defined or user-specified transaction. It is displayed to start with the BEGIN or BEGIN WORK statement (which is an alias for START TRANSACTION) and ends with 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, and 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 the changes in 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 |
   +--------+---------+--------+
   

   Execute the rollback:

   obclient> ROLLBACK;
   

   View the data after the rollback:

   obclient> SELECT * FROM employees;
   

   Result:

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