Start a transaction

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

Start a transaction

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

• Execute a BEGIN statement.

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

• Execute a START TRANSACTION statement.

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

• 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 [test]> SET AUTOCOMMIT=0;
  obclient [test]> INSERT INTO table1 VALUES(1,1);  // Start a transaction.
  obclient [test]> COMMIT;
  
  obclient [test]> SET AUTOCOMMIT=0;
  obclient [test]> UPDATE table1 SET id = 2 WHERE id = 1;  // Start a transaction.
  obclient [test]> COMMIT;
  
  obclient [test]> SET AUTOCOMMIT=0;
  obclient [test]> DELETE FROM table1 WHERE id = 2;  // Start a transaction.
  obclient [test]> COMMIT;
  
  obclient [test]> SET AUTOCOMMIT=0;
  obclient [test]> SELECT id FROM table1 WHERE id = 1 FOR UPDATE;  // Start a transaction.
  obclient [test]> 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.

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

obclient [test]> CREATE TABLE ordr(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
value INT,
gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );
Query OK, 0 rows affected

obclient [test]>  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 [test]> 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 [test]> SET autocommit=0;
Query OK, 0 rows affected

obclient [test]> 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 oceanbase.V$OB_TRANSACTION_PARTICIPANTS view.

obclient [test]> SELECT * FROM oceanbase.V$OB_TRANSACTION_PARTICIPANTS;
*************************** 1. row ***************************
       TENANT_ID: 1002
          SVR_IP: xx.xx.xx.223
        SVR_PORT: 2882
      SESSION_ID: 3221487660
  SCHEDULER_ADDR: "xx.xx.xx.223:2882"
         TX_TYPE: UNDECIDED
           TX_ID: 110352
           LS_ID: 1001
    PARTICIPANTS: NULL
 CTX_CREATE_TIME: 2022-10-19 14:55:23.763474
 TX_EXPIRED_TIME: 2022-10-19 14:55:23.763474
           STATE: ACTIVE
          ACTION: START
PENDING_LOG_SIZE: 116
FLUSHED_LOG_SIZE: 0
            ROLE: LEADER
1 row in set

For more information about columns in the oceanbase.V$OB_TRANSACTION_PARTICIPANTS view, see oceanbase.V$OB_TRANSACTION_PARTICIPANTS.

References

• Mark a savepoint

• Commit a transaction

• Roll back a transaction