Start a transaction

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

Start a transaction

OceanBase Database in Oracle mode is compatible with the transaction control statements in Oracle Database. You can start a transaction in OceanBase Database in the following ways.

• Execute a BEGIN statement.

  obclient [SYS]> BEGIN;      //Start a transaction
  obclient [SYS]> INSERT INTO table1 VALUES(1,1);  
  obclient [SYS]> COMMIT;
  

• Execute a START TRANSACTION statement.

  obclient [SYS]> START TRANSACTION; //Start a transaction
  obclient [SYS]> INSERT INTO table1 VALUES(1,1);  
  obclient [SYS]> COMMIT;
  

  Notice

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

• When autocommit is set to 0 to disable the autocommit mode, execute the INSERT, UPDATE, DELETE, or SELECT FOR UPDATE statement to start a new transaction.

  obclient [SYS]> SET AUTOCOMMIT=0;
  obclient [SYS]> INSERT INTO table1 VALUES(1,1);  //Start a transaction
  obclient [SYS]> COMMIT;
  
  obclient [SYS]> SET AUTOCOMMIT=0;
  obclient [SYS]> UPDATE table1 SET id = 2 WHERE id = 1;  //Start a transaction
  obclient [SYS]> COMMIT;
  
  obclient [SYS]> SET AUTOCOMMIT=0;
  obclient [SYS]> DELETE FROM table1 WHERE id = 2;  //Start a transaction
  obclient [SYS]> COMMIT;
  
  obclient [SYS]> SET AUTOCOMMIT=0;
  obclient [SYS]> SELECT id FROM table1 WHERE id = 1 FOR UPDATE;  //Start a transaction
  obclient [SYS]> 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 the SELECT FOR UPDATE statement, see Use the SELECT FOR UPDATE statement to lock query results.

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

obclient [SYS]> CREATE TABLE ordr(
id NUMBER NOT NULL PRIMARY KEY,
name VARCHAR2(10) NOT NULL,
value NUMBER,
gmt_create DATE NOT NULL DEFAULT sysdate );
Query OK, 0 rows affected

obclient [SYS]>  INSERT INTO ordr(id, name, value)
VALUES (1,'CN',10001),(2,'US', 10002),(3,'EN', 10003);
Query OK, 3 rows affected
 Records: 3  Duplicates: 0  Warnings: 0

obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-19 14:51:12 |
|  2 | US   | 10002 | 2022-10-19 14:51:12 |
|  3 | EN   | 10003 | 2022-10-19 14:51:12 |
+----+------+-------+---------------------+
2 rows in set

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

obclient [SYS]> UPDATE ordr SET id=4 WHERE name='US';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

You can query the status of a transaction in the V$OB_TRANSACTION_PARTICIPANTS view.

obclient [SYS]> SELECT * FROM V$OB_TRANSACTION_PARTICIPANTS;
*************************** 1. row ***************************
       TENANT_ID: 1004
          SVR_IP: XX.XX.XX.223
        SVR_PORT: 2882
      SESSION_ID: 3221487658
  SCHEDULER_ADDR: "XX.XX.XX.223:2882"
         TX_TYPE: UNDECIDED
           TX_ID: 77130
           LS_ID: 1001
    PARTICIPANTS: NULL
 CTX_CREATE_TIME: 02-NOV-22 02.58.12.850332 PM
 TX_EXPIRED_TIME: 03-NOV-22 02.58.12.850332 PM
           STATE: ACTIVE
          ACTION: START
PENDING_LOG_SIZE: 48
FLUSHED_LOG_SIZE: 0
            ROLE: LEADER
1 row in set

For more information about fields in the $OB_TRANSACTION_PARTICIPANTS view, see $OB_TRANSACTION_PARTICIPANTS.

More information

• Mark a savepoint

• Commit a transaction

• Roll back a transaction