Purpose
You can use this statement to start a transaction.
A database transaction is a series of operations performed as a logical unit of work. A transaction can be used to maintain the integrity of the database by ensuring that SQL operations in a batch are either all executed or none of them are executed.
Explicit transactions are user-defined or user-specified transactions. You can explicitly start a transaction by using the BEGIN or BEGIN WORK statement (which are aliases of START TRANSACTION) and explicitly end the transaction by using the COMMIT or ROLLBACK statement.
Note
- Before you commit a transaction, you can execute a
SELECTstatement to check whether the operations in the transaction have taken effect. - Before a transaction is committed, the operations in this transaction are invisible to sessions other than the session of the current transaction. In other words, before a transaction is committed, only the session of the current transaction can read the latest results.
- To roll back the transaction, directly execute the
ROLLBACKstatement.
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. After a transaction is started, the subsequent SQL data operation statements (such as INSERT, UPDATE, and DELETE) do not take effect until it is explicitly committed.
|
| BEGIN | BEGIN and BEGIN WORK are used as aliases of START TRANSACTION. |
| COMMIT | Commits the current transaction. |
| ROLLBACK | Rolls back the current transaction. |
| SET TRANSACTION {READ ONLY | READ WRITE} | Sets the current transaction to the READ ONLY or READ WRITE mode. |
Examples
Create a table named tbl1.
obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 2 | b | 200 |
| 3 | a | 50 |
+------+------+------+
3 rows in set
Start a transaction, change the name to
cfor the row whereidis3, and insert one row. Commit the transaction and query the information about thetbl1table.obclient> START TRANSACTION; Query OK, 0 rows affected obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 obclient> INSERT INTO tbl1 VALUES (4, 'a', 30); Query OK, 1 row affected obclient> COMMIT; Query OK, 0 rows affected obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | c | 50 | | 4 | a | 30 | +------+------+------+ 4 rows in setStart a transaction, change the name to
cfor the row whereidis3, insert one row, and query the information about thetbl1table. Then roll back the transaction and query the information about thetbl1table again.obclient> BEGIN; Query OK, 0 rows affected obclient> UPDATE tbl1 SET name = 'c' WHERE id = 3; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 obclient> INSERT INTO tbl1 VALUES (4, 'd', 300); Query OK, 1 row affected obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | c | 50 | | 4 | d | 300 | +------+------+------+ 4 rows in set obclient> ROLLBACK ; Query OK, 0 rows affected obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set