This topic describes the commonly used statements for starting a transaction in OceanBase AI Database and provides examples.
Start a transaction
The transaction control statements in OceanBase AI Database are compatible with those in MySQL Database. You can start a transaction in OceanBase AI Database by using one of the following methods:
Execute the
BEGINstatement.obclient [test]> BEGIN; // Start a transaction. obclient [test]> INSERT INTO table1 VALUES(1,1); obclient [test]> COMMIT;Execute the
START TRANSACTIONstatement.obclient [test]> START TRANSACTION; // Start a transaction. obclient [test]> INSERT INTO table1 VALUES(1,1); obclient [test]> COMMIT;After you set
autocommit = 0to disable automatic commit, executing theINSERT,UPDATE,DELETE, orSELECT FOR UPDATEstatement starts a new transaction by default.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 AI Database assigns a transaction ID to it, which uniquely identifies the transaction.
In practice, when multiple connections concurrently operate on the same table, two transactions may modify the same row at the same time. For read operations, you can use the SELECT FOR UPDATE statement to lock the query results, preventing other DML statements from modifying the record concurrently. For more information about this statement, see Lock query results with SELECT FOR UPDATE.
The following example first disables automatic commit by using the SET autocommit=0 statement, and then starts a transaction by using an UPDATE statement.
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
After successful execution, you can use the oceanbase.V$OB_TRANSACTION_PARTICIPANTS view to query the participant information of active transactions.
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 the columns in the oceanbase.V$OB_TRANSACTION_PARTICIPANTS view, see V$OB_TRANSACTION_PARTICIPANTS.