TRANSACTION statements are used to manage database transactions.
A database transaction is a sequence of operations executed as a single logical unit of work. Transaction processing helps maintain database integrity by ensuring that a batch of SQL operations are either all executed or none at all.
User transactions are explicitly started by users. They are explicitly started using the BEGIN or BEGIN WORK statement (which is an alias for START TRANSACTION) and explicitly ended using 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 |
Note |
|---|---|
| START TRANSACTION [READ ONLY \ | READ WRITE] | A transaction statement. Once a transaction is started, subsequent SQL data manipulation statements (that is, INSERT, UPDATE, DELETE will take effect until an explicit commit. READ ONLY Indicates that the transaction is started in read-only mode, and no modification operations are allowed within the transaction. READ WRITE indicates that the transaction is read-write enabled, which is the default mode. |
| BEGIN [WORK] | BEGIN and BEGIN WORK Used as START TRANSACTION is an alias for. |
| COMMIT [WORK] | Commits the current transaction, making all modifications within the transaction permanent. |
| ROLLBACK [WORK] | Roll back the current transaction and undo all modifications made by it. |
| SET TRANSACTION {READ ONLY \ | READ WRITE} | Set the current transaction to READ ONLY or READ WRITE mode. |
Examples
Use START TRANSACTION to start a transaction and commit it.
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 a transaction and roll back it.
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 the rollback, query the table data to confirm that the modification has 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 a transaction is
COMMIT, you can check whether the operations in the transaction have taken effect. For example, you can execute the SQL statementSELECT * FROM order_table;beforeCOMMIT. - Accesses from the current transaction session can read the latest results. Accesses outside the current transaction session will definitely not see the updated results. Before a transaction is
COMMIT, operations performed before the current transaction connection are invisible to other connections. - To roll back the transaction, simply use
ROLLBACKinstead ofCOMMIT.