A database transaction contains one or more DML statements and has clear start and end points.
Start a transaction
For example, a transaction is started when the database executes one of the following statements in MySQL mode:
begin
START TRANSACTION
insert ...
update ...
delete ...
select ... for update...
When a transaction is started, OceanBase Database assigns an ID to uniquely identify the transaction. You can use ocean.__all_virtual_trans_stat to query the status of a transaction. In the following example, the UPDATE statement starts a transaction.
session 1:
obclient> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
obclient> UPDATE t SET c="b" WHERE i=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 2:
obclient> SELECT trans_id FROM __all_virtual_trans_stat;
+-----------------------------------------------------------------------------------------+
| trans_id |
+-----------------------------------------------------------------------------------------+
| {hash:17242042390259891950, inc:98713, addr:"xx.xx.xx.xx:24974", t:1632636623536459} |
+-----------------------------------------------------------------------------------------+
Execute statements
During the statement execution, OceanBase Database creates a transaction context in every partition that is accessed by the statements. The transaction context records the version numbers of data snapshots and the changes made by the statements to the partition.
End a transaction
When a transaction ends, all the partitions affected by the transaction receive a transaction commit or rollback request, depending on the scenario. The commit (COMMIT) or rollback (ROLLBACK) of a transaction is triggered in any of the following scenarios:
You explicitly initiate a commit or rollback. If you initiate a commit, OceanBase Database persists the changes made by the transaction to the clog file.
You execute a DDL statement, such as
CREATE,DROP,RENAME, orALTER. In this scenario, OceanBase Database implicitly initiates a commit request. Statements after the DDL statement start a new transaction.The client is disconnected. If the client is disconnected during transaction execution, OceanBase Database implicitly initiates a rollback request to roll back the transaction.
After a transaction ends, subsequent requests start a new transaction. In the following example, a transaction is started by an UPDATE statement and ends after a ROLLBACK statement. Then, another UPDATE statement starts a new transaction.
session 1:
obclient> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
obclient> UPDATE t SET c="b" WHERE i=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 2:
obclient> SELECT trans_id FROM __all_virtual_trans_stat;
+-----------------------------------------------------------------------------------------+
| trans_id |
+-----------------------------------------------------------------------------------------+
| {hash:17242042390259891950, inc:98713, addr:"xx.xx.xx.xx:24974", t:1632636623536459} |
+-----------------------------------------------------------------------------------------+
session 1:
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> UPDATE t SET c="c" WHERE i=1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
session 2:
obclient> SELECT trans_id FROM __all_virtual_trans_stat;
+------------------------------------------------------------------------------------------+
| trans_id |
+------------------------------------------------------------------------------------------+
| {hash:11752179762787656100, inc:104885, addr:"xx.xx.xx.xx:24974", t:1632636737417119} |
+------------------------------------------------------------------------------------------+
1 row in set