COMMIT
NAME
COMMIT: end a transaction, keeping its changes
SYNOPSIS
<commit statement> ::= COMMIT [ WORK ]
DESCRIPTION
COMMIT ends the session's transaction. Unless something prevents it, the transaction's changes to data and to definitions become visible to every transaction that begins after it, and survive a crash of a database kept in a directory.
COMMIT with no transaction active does nothing, and completes.
RULES
As COMMIT runs:
- Each cursor the session has open is closed.
- Each instance of a temporary table defined ON COMMIT DELETE ROWS is emptied, so no row of one breaks a constraint.
- Each constraint whose checking was deferred, by DEFERRABLE INITIALLY DEFERRED or SET CONSTRAINTS, is checked, as if SET CONSTRAINTS ALL IMMEDIATE ran.
- If a constraint is not satisfied, the transaction is rolled back instead: none of its changes is kept.
- If anything else prevents the commit, the transaction is likewise rolled back.
The transaction's locks are released once it ends, and the next statement that needs a transaction begins a new one.
COMMIT may be a statement of a compound statement, or of another SQL control statement, NOT ATOMIC and run directly. It ends the transaction there, and the statements after it run in a new one (see BEGIN). It may not be in an ATOMIC compound statement, a FOR, or the body of a routine or trigger.
PRIVILEGES
None.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2D000 | -917 | It runs in a handler's action while an ATOMIC compound statement runs |
| 40002 | -913 | A deferred constraint is not satisfied: the transaction was rolled back |
| 40001 | -911 | It conflicts with a transaction that committed first: it was rolled back, and may be run again |
| 40000 | -913 | An engine refused to commit, an earlier statement of the transaction could not be undone, or the commit record could not be written to the log, or a sync of the log failed earlier, so the database takes no change until it is opened again: the transaction was rolled back |
| 08007 | -922 | The commit record was written but could not be synced to disk: the transaction is in doubt. No session sees its changes; the database takes no change until it is opened again, and then the transaction is committed if its commit record reached the disk, else rolled back |
EXAMPLES
A constraint checked at commit:
CREATE TABLE ACCOUNTS (
ID INTEGER,
BALANCE DECIMAL(9,2) CONSTRAINT NOT_NEGATIVE CHECK (BALANCE >= 0)
DEFERRABLE INITIALLY DEFERRED);
COMMIT;
INSERT INTO ACCOUNTS VALUES (1, 100.00);
COMMIT WORK;
Each statement completes:
OK
OK
OK
OK
A change that breaks the constraint is accepted when it is made, and refused at COMMIT, the transaction rolled back:
UPDATE ACCOUNTS SET BALANCE = BALANCE - 150 WHERE ID = 1;
COMMIT;
SELECT ID, BALANCE FROM ACCOUNTS;
OK
turborel: SQLCODE -913 (SQLSTATE 40002): the transaction is rolled back: CHECK ANN.NOT_NEGATIVE of ANN.ACCOUNTS is false for a row [ISO/IEC 9075:1992 §11.6 GR3] [ISO/IEC 9075:1992 §14.3 GR5a]
ID | BALANCE
--------------+-------------
1 | 100.00
(1 row)
STANDARD
ISO/IEC 9075:1992 §14.3, and X3.135-1986 §8.2, which requires WORK. WORK may be left out, as SQL-92 allows. Cursors are closed [§14.3 GR2], temporary tables of ON COMMIT DELETE ROWS emptied [GR3], and then deferred constraints checked [GR4, GR5a]. Of the other failures that roll the transaction back [GR5b], SQLSTATE 40001 is the standard's serialization failure, and 40000, without a subclass, is the class alone. 08007 is the standard's transaction resolution unknown [§4.29]: whether the transaction committed cannot be verified until the database is opened again.
ISO/IEC 9075-4:2006 draft §4.10.4 makes a compound statement NOT ATOMIC a non-atomic execution context, where COMMIT may run, and ISO/IEC 9075-2:2023 §17.7 GR2 raises 2D000 in an atomic one.
SEE ALSO
BEGIN, ROLLBACK, SET CONSTRAINTS, SET TRANSACTION, Transactions