Start a transaction

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

Syntax

Transaction control statements in OceanBase Database are compatible with those in MySQL. You can start a transaction using several methods.

Take OceanBase Database in MySQL mode as an example. The database will start a transaction when any of the following statements is executed:

• 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;
  

• 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 high concurrency scenarios, the same row of data may be operated by two transactions. 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****} |
+-----------------------------------------------------------------------------------------+