Start a transaction

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

Start a transaction

The transaction control statements in the Oracle compatible mode of OceanBase Cloud are compatible with those in Oracle. In the Oracle compatible mode of OceanBase Cloud, you can start a transaction 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.

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

  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 Cloud assigns an ID to uniquely identify the transaction.

In scenarios with multiple concurrent connections, 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.

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 V$OB_TRANSACTION_PARTICIPANTS view for information about the participants of active transactions.

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