Syntax

START TRANSACTION | BEGIN [WORK]

Remarks

• This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore Commands. Note that you must connect to the master aggregator when running this command on reference tables.

• When BEGIN is used to start a transaction, it overrides the behavior of autocommit engine variable for this transaction, and the transaction must be ended explicitly with COMMIT or ROLLBACK.

• If the transaction is successful, execute the COMMIT command to commit the changes; if the transaction is unsuccessful or needs to be reverted, then execute the ROLLBACK command to revert the changes.

• The maximum number of tables that can be written into a single transaction is 1024. If the transaction table writes exceed the maximum amount, an error will be generated. Execute the ROLLBACK command to revert any changes. Then split the transaction into smaller sections and execute again.

Example

For this example, consider the following Employee table:

BEGIN;

UPDATE Employee SET Name = "John" WHERE ID = 300;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Run the SELECT query to verify if the UPDATE is correct:

SELECT * FROM Employee;

+------+-------+
| ID   | Name  |
+------+-------+
|   30 | Jim   |
|   20 | Rob   |
|   40 | Rick  |
+------+-------+

Run ROLLBACK since there were no matching results for the ID and the UPDATE was not successful.

ROLLBACK;

Note: Before the user runs COMMIT or ROLLBACK, only that user can see the updates made after BEGIN was run.