TRANSACTION

Description

This statement is used to start a transaction.

A database transaction is a series of operations executed as a single logical unit. Transaction processing can be used to maintain database integrity, ensuring that all SQL operations in a batch are either all executed or none are executed.

Explicit transactions are user-defined or user-specified transactions. They are explicitly started with the BEGIN or BEGIN WORK statement (which is an alias for START TRANSACTION) and explicitly ended 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};

Parameter Description

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 take effect until the transaction is explicitly committed. READ ONLY indicates that the transaction is started in read-only mode, and no modification operations can be performed within the transaction. READ WRITE indicates that the transaction is started in read-write mode, which is the default mode.
BEGIN	BEGIN and BEGIN WORK are aliases for START TRANSACTION.
COMMIT	Commits the current transaction.
ROLLBACK	Rolls back the current transaction.
SET TRANSACTION {READ ONLY | READ WRITE}	Sets the current transaction to READ ONLY or READ WRITE mode.

Examples

• Assume that the a table is as follows:

  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

1. Execute the following commands to start a transaction, change the name of the record with id equal to 3 to c, and insert a new record indicating the current sale of a.

   START TRANSACTION;
   Query OK, 0 rows affectedaffected (0.011 sec)
   
   UPDATE a SET name = 'c' WHERE id = 3;
   Query OK, 1 rows affected (0.012 sec)
   
   INSERT INTO a VALUES (4, 'a', 30, '2013-06-21 16:09:13');
   Query OK, 1 rows affected (0.015 sec)
   
   COMMIT;
   Query OK, 0 rows affected (0.017 sec)

2. After the transaction is committed, execute the following command to view the information in the a table.

   SELECT * FROM a;

   The result is as follows:

   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

tip

• Before the transaction is committed, you can check whether the operations in the transaction have taken effect. For example, you can execute the SQL statement SELECT * FROM a; before COMMIT.
• The results of the current transaction session are the latest, but the results of other sessions are not. Before the transaction is committed, the operations performed in the transaction are not visible to other sessions.
• If you want to roll back the transaction, use ROLLBACK instead of COMMIT.