Informix Guide to SQL: Syntax SQL Statements

Use the COMMIT WORK statement to commit all modifications made to the database from the beginning of a transaction. This statement informs the database server that you reached the end of a series of statements that must succeed as a single unit. The database server takes the required steps to make sure that all modifications made by the transaction are completed correctly and committed to disk.

Use the COMMIT WORK statement when you are sure you want to keep changes that are made to the database from the beginning of a transaction. Use the COMMIT WORK statement only at the end of a multistatement operation.

The COMMIT WORK statement releases all row and table locks.

In ESQL/C, the COMMIT WORK statement closes all open cursors except those declared with hold.

The WORK keyword is optional in a COMMIT WORK statement. The following two statements are equivalent:

COMMIT;



COMMIT WORK;

The following example shows a transaction bounded by BEGIN WORK and COMMIT WORK statements. In this example, the user first deletes the row from the call_type table where the value of the call_code column is O. The user then inserts a new row in the call_type table where the value of the call_code column is S. The database server guarantees that both operations succeed or else neither succeeds.

BEGIN WORK;

	DELETE FROM call_type WHERE call_code = 'O';

	INSERT INTO call_type VALUES ('S', 'order status');

COMMIT WORK;

In a database that is not ANSI compliant, if you initiate a transaction with a BEGIN WORK statement, you must issue a COMMIT WORK statement at the end of the transaction. If you fail to issue a COMMIT WORK statement in this case, the database server rolls back the modifications to the database that the transaction made.

However, if you do not issue a BEGIN WORK statement, the database server executes each statement within its own transaction. These single-statement transactions do not require either a BEGIN WORK statement or a COMMIT WORK statement.

In an ANSI-compliant database, you do not need to mark the beginning of a transaction. You only need to mark the end of each transaction. An implicit transaction is always in effect. A new transaction starts automatically after each COMMIT WORK or ROLLBACK WORK statement.

You must issue an explicit COMMIT WORK statement to mark the end of each transaction. If you fail to do so, the database server rolls back the modifications to the database that the transaction made.

Related statements: BEGIN WORK, ROLLBACK WORK, and DECLARE

For a discussion of concepts related to transactions, see the Informix Guide to SQL: Tutorial.