The COMMIT [WORK] statement is used to mark the end of a transaction, while preserving any changes made to the database within that transaction.
Required Privileges
No special privileges are required to execute the COMMIT [WORK]
command.
Syntax
Description
A transaction
begins with either a data definition (DDL)
, data manipulation (DML), or SET
TRANSACTION statement. A transaction is terminated by a COMMIT [WORK]
or ROLLBACK [WORK] command. A SET TRANSACTION
statement can also be used to both end a transaction with an implicit ROLLBACK
and start a new one. In SAND CDBMS, Data Definition statements and Data Manipulation
statements can be part of the same transaction.
Only the results of SQL commands executed within a single, uncommitted transaction are affected by the COMMIT command. When the COMMIT command is issued, all database changes caused by CREATE, ALTER TABLE, DROP, GRANT, REVOKE, INSERT, UPDATE, and DELETE commands in the transaction are made permanent, and can no longer be rolled back. However, if the transaction had been set to read-only through a SET TRANSACTION statement, the COMMIT will fail.
Note that COMMIT and COMMIT WORK perform identically.
Important!
Using the .EXIT system command to end an nisqlm session will automatically ROLLBACK any changes made to the database since the session began or since the last COMMIT/ROLLBACK command. If the user wishes to make the changes permanent, the COMMIT command should be used prior to the .EXIT. (Refer to the SAND CDBMS Interactive SQL Utility (nisqlm) chapter in the SAND CDBMS Tools Reference Guide for more information about the .EXIT system command.)
Example
INSERT INTO products VALUES (282, 175.82, 'foo bar');
COMMIT;
In this example, a row is inserted into the products table, followed by a COMMIT. The COMMIT command effectively makes permanent the immediately preceding INSERT and any other uncommitted database-altering statements in the transaction.