TRANSACTION related statements are used to manage database transactions.
A database transaction is a series of operations that are executed as a single logical unit. Transaction processing can be used to maintain database integrity and ensure that all SQL operations in a batch are either executed or not executed.
Explicit transactions are user-defined or user-specified transactions. An explicit transaction starts with the BEGIN or BEGIN WORK statement (which is an alias for START TRANSACTION) and ends with the COMMIT or ROLLBACK statement.
Syntax
transaction_stmt:
START TRANSACTION [READ ONLY | READ WRITE]
| BEGIN [WORK]
| COMMIT [WORK]
| ROLLBACK [WORK]
| SET TRANSACTION {READ ONLY | READ WRITE}
Parameters
Parameter |
Description |
|---|---|
| START TRANSACTION [READ ONLY | READ WRITE] | Starts a transaction. Once a transaction is started, subsequent SQL data manipulation statements (such as INSERT, UPDATE, and DELETE) will only take effect until explicitly committed. READ ONLY indicates that the transaction is started in read-only mode, and no modification operations can be executed within the transaction. READ WRITE indicates that the transaction is started in read-write mode, which is the default mode. |
| BEGIN [WORK] | BEGIN and BEGIN WORK are aliases for START TRANSACTION. |
| COMMIT [WORK] | Commits the current transaction, making all modifications in the transaction permanent. |
| ROLLBACK [WORK] | Rolls back the current transaction, undoing all modifications in the transaction. |
| SET TRANSACTION {READ ONLY | READ WRITE} | Sets the current transaction to READ ONLY or READ WRITE mode. |
Examples
Use START TRANSACTION to start and commit a transaction.
obclient> CREATE TABLE order_table(id INT PRIMARY KEY, order_name VARCHAR(50), amount DECIMAL(10,2));
obclient> START TRANSACTION;
obclient> INSERT INTO order_table VALUES (1, 'Order001', 100.50);
obclient> UPDATE order_table SET amount = 150.00 WHERE id = 1;
obclient> COMMIT;
Use BEGIN to start and roll back a transaction.
obclient> BEGIN;
obclient> INSERT INTO order_table VALUES (2, 'Order002', 200.00);
obclient> UPDATE order_table SET amount = 250.00 WHERE id = 2;
obclient> ROLLBACK;
After rolling back, query the table data to confirm that the modifications have been undone.
obclient> SELECT * FROM order_table;
The query result is as follows:
+----+-----------+--------+
| id | order_name| amount |
+----+-----------+--------+
| 1 | Order001 | 150.00 |
+----+-----------+--------+
1 row in set
Use SET TRANSACTION to set the transaction to read-only mode.
obclient> SET TRANSACTION READ ONLY;
obclient> START TRANSACTION;
obclient> SELECT * FROM order_table;
Notice
- Before
COMMITis executed, you can check whether the operations in the current transaction have taken effect. For example, you can execute the SQL statementSELECT * FROM order_table;beforeCOMMITis executed. - Within the current transaction session, the latest results can be accessed, but outside the transaction session, the results are not effective. Before
COMMITis executed, the operations performed are only visible within the current transaction session. - To roll back the transaction, use
ROLLBACKinstead ofCOMMIT.