Informix Guide to SQL: Syntax SQL Statements

Use the BEGIN WORK statement to start a transaction (a sequence of database operations that the COMMIT WORK or ROLLBACK WORK statement terminates). Use the BEGIN WORK WITHOUT REPLICATION statement to start a transaction that does not replicate to other database servers.

Each row that an UPDATE, DELETE, or INSERT statement affects during a transaction is locked and remains locked throughout the transaction. A transaction that contains many such statements or that contains statements affecting many rows can exceed the limits that your operating system or the database server configuration imposes on the maximum number of simultaneous locks. If no other user is accessing the table, you can avoid locking limits and reduce locking overhead by locking the table with the LOCK TABLE statement after you begin the transaction. Like other locks, this table lock is released when the transaction terminates. The example of a transaction on Example of BEGIN WORK includes a LOCK TABLE statement.

Important: You can issue the BEGIN WORK statement only if a transaction is not in progress. If you issue a BEGIN WORK statement while you are in a transaction, the database server returns an error.

In ESQL/C, if you use the BEGIN WORK statement within a UDR called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. These statements prevent the program from looping if the ROLLBACK WORK statement encounters an error or a warning.

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

BEGIN;



BEGIN WORK;

In an ANSI-compliant database, you do not need the BEGIN WORK statement because transactions are implicit. A warning is generated if you use a BEGIN WORK statement immediately after one of the following statements:

• DATABASE
• COMMIT WORK
• CREATE DATABASE
• ROLLBACK WORK

An error is generated if you use a BEGIN WORK statement after any other statement.

When you use Enterprise Replication for data replication, you can use the BEGIN WORK WITHOUT REPLICATION statement to start a transaction that does not replicate to other database servers.

You cannot execute the BEGIN WORK WITHOUT REPLICATION statement as a stand-alone embedded statement within an ESQL/C application. Instead you must execute this statement indirectly. You can use either of the following methods:

• You can use a combination of the PREPARE and EXECUTE statements to prepare and execute the BEGIN WORK WITHOUT REPLICATION statement.
• You can use the EXECUTE IMMEDIATE statement to prepare and execute the BEGIN WORK WITHOUT REPLICATION statement in a single step.

You cannot use the DECLARE cursor CURSOR WITH HOLD with the BEGIN WORK WITHOUT REPLICATION statement.

For more information about data replication, see the Guide to Informix Enterprise Replication.

The following code fragment shows how you might place statements within a transaction. The transaction is made up of the statements that occur between the BEGIN WORK and COMMIT WORK statements. The transaction locks the stock table (LOCK TABLE), updates rows in the stock table (UPDATE), deletes rows from the stock table (DELETE), and inserts a row into the manufact table (INSERT). The database server must perform this sequence of operations either completely or not at all. The database server guarantees that all the statements are completely and perfectly committed to disk, or the database is restored to the same state as before the transaction began.

BEGIN  WORK;

	LOCK  TABLE  stock;

	UPDATE  stock  SET  unit_price  =  unit_price  *  1.10

		WHERE  manu_code  =  'KAR';

	DELETE  FROM  stock  WHERE  description  =  'baseball  bat';

	INSERT  INTO  manufact  (manu_code,  manu_name,  lead_time)  

		VALUES  ('LYM',  'LYMAN',  14);

COMMIT  WORK;

Related statements: COMMIT WORK, ROLLBACK WORK

For discussions of transactions and locking, see the Informix Guide to SQL: Tutorial.