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 is an alias of START TRANSACTION) and explicitly end the transaction by 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 | Description |
|---|---|
| START TRANSACTION [READ ONLY | READ WRITE] | Starts the 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. READ ONLY indicates that the transaction is started in read-only mode and modifications to the transaction are not allowed. READ WRITE indicates that the transaction is started in read and write mode, which is the default mode. |
| BEGIN | BEGIN and BEGIN WORK are used as the alias 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
Assume that you have table
awith the following data in your database.id name num sell_date 1 a 100 2013-06-21 10:06:43 2 b 200 2013-06-21 13:07:21 3 a 50 2013-06-21 13:08:15
Execute the following statements in sequence to start the transaction, change the
namevalue of the record whoseidis3toc, and insert a row for the record of sellinga.obclient> START TRANSACTION; Query OK, 0 rows affected obclient> UPDATE a SET name = 'c' WHERE id = 3; Query OK, 1 rows affected obclient> INSERT INTO a VALUES (4, 'a', 30, '2013-06-21 16:09:13'); Query OK, 1 rows affected obclient> COMMIT; Query OK, 0 rows affectedCommit the transaction and execute the following statement to query the information of table
a:SELECT * FROM a;Return result:
id name num sell_date 1 a 100 2013-06-21 10:06:43 2 b 200 2013-06-21 13:07:21 3 c 50 2013-06-21 13:08:15 4 a 30 2013-06-21 16:09:13
Notice
- Before a transaction is committed, you can view whether the operation in the transaction has taken effect.
For example, you can execute theSELECT * FROM astatement before you commit a transaction. - You can access the sessions of the current transaction to obtain the latest result. Access to sessions beyond the current transaction does not take effect. Before you
COMMITthe transaction, your operations are visible only to the current transaction session. - To roll back a transaction, replace
COMMITwithROLLBACK.