Start a transaction

This topic describes how to start a transaction in OceanBase Database.

Syntax

Oracle Database is compatible with the transaction control statements in OceanBase Database. When you execute the following statements in OceanBase Database in Oracle mode, a transaction is started.

• BEGIN statement

  BEGIN;      // Start the transaction.
  INSERT INTO table1 VALUES(1,1);  
  COMMIT;
  

• START TRANSACTION statement

  START TRANSACTION; // Start the transaction.
  INSERT INTO table1 VALUES(1,1);  
  COMMIT;
  

  Notice

  BEGIN is the alias of START TRANSACTION and is used to start a transaction.

• INSERT, UPDATE, DELETE, or SELECT FOR UPDATE statement (when autocommit = 0 is configured to disable the autocommit mode)

  SET AUTOCOMMIT=0;
  INSERT INTO table1 VALUES(1,1);  // Start the transaction.
  COMMIT;
  
  SET AUTOCOMMIT=0;
  UPDATE table1 SET id = 2 WHERE id = 1;  // Start the transaction.
  COMMIT;
  
  SET AUTOCOMMIT=0;
  DELETE FROM table1 WHERE id = 2;  // Start the transaction.
  COMMIT;
  
  SET AUTOCOMMIT=0;
  SELECT id FROM table1 WHERE id = 1 FOR UPDATE;  // Start the transaction.
  COMMIT;
  

When a transaction is started, OceanBase Database assigns an ID to uniquely identify the transaction.

In scenarios with multiple concurrencies, the same row of data may be operated by two transactions. For query reads, you can use the SELECT FOR UPDATE statement to lock the query results to prevent other DML statements from modifying this record. For information about how to use this statement, see Use the SELECT FOR UPDATE statement to lock query results.

Example: Start a transaction

Use SET autocommit=0 to disable the autocommit mode and then use UPDATE to start a transaction.

obclient> SET autocommit=0;
Query OK, 0 rows affected

obclient> UPDATE t SET c="b" WHERE i=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Use oceanbase.__all_virtual_trans_stat to query the status of the transaction.

obclient> SELECT trans_id FROM oceanbase.__all_virtual_trans_stat;
+-----------------------------------------------------------------------------------------+
| trans_id                                                                                |
+-----------------------------------------------------------------------------------------+
| {hash:1724204239025989****, inc:9****, addr:"100.88.*.*:24974", t:163263662353****} |
+-----------------------------------------------------------------------------------------+