Oninit
Talk to an expert →

Informix Error -876: Cannot issue SET TRANSACTION once a transaction has started.

Cause and resolution

Cannot issue SET TRANSACTION once a transaction has started.

When a transaction is active, do not issue a SET TRANSACTION statement. A transaction becomes active when a DDL or a DML statement is issued. The only statements that you can place between the BEGIN WORK and the SET TRANSACTION statements are SET statements such as SET EXPLAIN, SET CONSTRAINT, SET DATASKIP, and so on.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-876 fires when SET TRANSACTION is issued after the transaction has already become active — per the official guidance, a transaction becomes active as soon as a DDL or DML statement runs, and SET TRANSACTION must come before that.

  1. A DDL/DML statement issued between BEGIN WORK and SET TRANSACTION, per the official guidance — this activates the transaction, closing the window SET TRANSACTION needed.
  2. A misunderstanding of what's allowed between BEGIN WORK and SET TRANSACTION, per the official guidance — only other SET statements (SET EXPLAIN, SET CONSTRAINT, SET DATASKIP, and similar) are permitted there, not DDL/DML.
Solutions / Resolution
  1. Issue SET TRANSACTION immediately after BEGIN WORK, per the official guidance, before any DDL/DML statement.
  2. Move any DDL/DML statements to after SET TRANSACTION, per the official guidance, reordering the transaction's statements if needed.
  3. Only use other SET statements between BEGIN WORK and SET TRANSACTION, per the official guidance, if additional session configuration is needed at that point.
Examples
The disallowed order
BEGIN WORK;
UPDATE orders SET status = 'shipped' WHERE order_id = 42;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- -876: the UPDATE already activated the transaction
Corrected
BEGIN WORK;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE orders SET status = 'shipped' WHERE order_id = 42;
Diagnostic Checks
  1. Check for any DDL/DML statement between BEGIN WORK and SET TRANSACTION, and move SET TRANSACTION to immediately follow BEGIN WORK.
Related Errors / Related Topics
  • -877 — "Isolation Level previously set by 'Set Transaction'." A related SET TRANSACTION error, about a conflicting subsequent SET ISOLATION rather than statement ordering.

SET TRANSACTION must come immediately after BEGIN WORK — only other SET statements are allowed between them.