COMMIT
This statement commits a transaction inside of the TIDB server.
In the absence of a BEGIN or START TRANSACTION statement, the default behavior of TiDB is that every statement will be its own transaction and autocommit. This behavior ensures MySQL compatibility.
Synopsis
- CommitStmt
- CompletionTypeWithinTransaction
CommitStmt ::=
'COMMIT' CompletionTypeWithinTransaction?
CompletionTypeWithinTransaction ::=
'AND' ( 'CHAIN' ( 'NO' 'RELEASE' )? | 'NO' 'CHAIN' ( 'NO'? 'RELEASE' )? )
| 'NO'? 'RELEASE'
Examples
mysql> CREATE TABLE t1 (a int NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.12 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
MySQL compatibility
- Currently, TiDB use Metadata Locking (MDL) to prevent DDL statements from modifying tables used by transactions by default. The behavior of metadata lock is different between TiDB and MySQL. For more details, see Metadata Lock.
- By default, TiDB 3.0.8 and later versions use Pessimistic Locking. When using Optimistic Locking, it is important to consider that a
COMMITstatement might fail because rows have been modified by another transaction. - When Optimistic Locking is enabled,
UNIQUEandPRIMARY KEYconstraint checks are deferred until statement commit. This results in additional situations where aCOMMITstatement might fail. This behavior can be changed by settingtidb_constraint_check_in_place=ON. - TiDB parses but ignores the syntax
ROLLBACK AND [NO] RELEASE. This functionality is used in MySQL to disconnect the client session immediately after committing the transaction. In TiDB, it is recommended to instead use themysql_close()functionality of your client driver. - TiDB parses but ignores the syntax
ROLLBACK AND [NO] CHAIN. This functionality is used in MySQL to immediately start a new transaction with the same isolation level while the current transaction is being committed. In TiDB, it is recommended to instead start a new transaction.