Categories:
Transactions

BEGIN

Begins a transaction in the current session.

START TRANSACTION is a synonym for BEGIN.

See also:
COMMIT , ROLLBACK , SHOW TRANSACTIONS

Syntax

BEGIN [ WORK | TRANSACTION ] [ NAME <name> ]

START TRANSACTION [ NAME <name> ]

Parameters

WORK | TRANSACTION
Optional keywords that provide compatibility with other database systems.
NAME name
Optional string that assigns a name to the transaction. A name helps identify a transaction, but is not required and does not need to be unique.

Usage Notes

  • All transactions have a system-generated internal ID, which is the timestamp (in milliseconds) when the transaction was started.
  • If you specify a name for a transaction, the NAME keyword is required.
  • If a name is not specified, a system-generated name is assigned to the transaction.
  • To complete a transaction, a COMMIT or ROLLBACK command must be explicitly executed. Until one of these commands is executed, the transaction remains open.

Examples

Note

These examples do not include the necessary commands for completing the transactions. For examples of complete transactions, see COMMIT or ROLLBACK.

Begin a transaction:

BEGIN;

SHOW TRANSACTIONS;

+---------------+--------+--------------+--------------------------------------+-------------------------------+---------+
|            id | user   |      session | name                                 | started_on                    | state   |
|---------------+--------+--------------+--------------------------------------+-------------------------------+---------|
| 1530042321085 | USER1  | 223347060798 | 56cb9163-77a3-4223-b3e0-aa24a20540a3 | 2018-06-26 12:45:21.085 -0700 | running |
+---------------+--------+--------------+--------------------------------------+-------------------------------+---------+

SELECT CURRENT_TRANSACTION()

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1530042321085         |
+-----------------------+

Note the system-assigned name, 56cb9163-77a3-4223-b3e0-aa24a20540a3, for the transaction.

Begin a transaction with a specified name:

BEGIN NAME T1;

SHOW TRANSACTIONS;

+---------------+--------+--------------+------+-------------------------------+---------+
|            id | user   |      session | name | started_on                    | state   |
|---------------+--------+--------------+------+-------------------------------+---------|
| 1530042377426 | USER1  | 223347060798 | T1   | 2018-06-26 12:46:17.426 -0700 | running |
+---------------+--------+--------------+------+-------------------------------+---------+

SELECT CURRENT_TRANSACTION();

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1530042377426         |
+-----------------------+

Same as previous example, but using START TRANSACTION instead of BEGIN:

START TRANSACTION NAME T2;

SHOW TRANSACTIONS;

+---------------+--------+--------------+------+-------------------------------+---------+
|            id | user   |      session | name | started_on                    | state   |
|---------------+--------+--------------+------+-------------------------------+---------|
| 1530042467963 | USER1  | 223347060798 | T2   | 2018-06-26 12:47:47.963 -0700 | running |
+---------------+--------+--------------+------+-------------------------------+---------+

SELECT CURRENT_TRANSACTION();

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1530042467963         |
+-----------------------+

In all three examples, note that the CURRENT_TRANSACTION function returns the transaction ID (i.e. the start time of the transaction in milliseconds).

Any functions executed on the ID/timestamp must specify the appropriate scale. For example:

SELECT CURRENT_TRANSACTION();

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1530042467963         |
+-----------------------+

SELECT TO_TIMESTAMP_LTZ(1530042467963, 3) AS transaction_timestamp;

+-------------------------------+
| TRANSACTION_TIMESTAMP         |
|-------------------------------|
| 2018-06-26 12:47:47.963 -0700 |
+-------------------------------+