Start a transaction

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

Start a transaction

The transaction control statements in the Oracle mode of OceanBase Database are compatible with those in Oracle Database. In the Oracle mode of OceanBase Database, you can start a transaction in the following ways:

• Execute the BEGIN statement.

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

• Execute the START TRANSACTION statement.

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

  Notice

  Here, 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 more 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

Query the transaction status 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 the fields in the V$OB_TRANSACTION_PARTICIPANTS view, see V$OB_TRANSACTION_PARTICIPANTS.

References

• Mark a savepoint

• Commit a transaction

• Roll back a transaction