Oninit
Talk to an expert →

Informix Error -589: Cannot update multiple sites within a single transaction.

Cause and resolution

Cannot update multiple sites within a single transaction.

This database server supports only single-site update. The operations within one transaction can modify data at only one site in the network. Some preceding statement within this transaction has already modified data at one site; the current statement would modify data at a second site. The statement is not executed. Roll back the current transaction. Examine the application in the light of this restriction. Check the names of all tables that UPDATE, INSERT, and DELETE statements affect to make sure they are all in the same database or in databases that the same database server holds. (Check the definition of any synonyms. Synonyms can make tables in external databases appear to be in the current database.)

Database servers after Version 5.01 do not use this error message.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-589 is documented as obsolete — database servers after Version 5.01 don't produce it (later distributed-transaction support removed this single-site-update restriction). It's documented here for older platforms/archival reference: on the versions where it applied, the server supported only single-site update — every INSERT/UPDATE/DELETE within one transaction had to modify data at only one network site (one database server instance), and a transaction that had already modified data at one site couldn't then modify data at a second.

  1. A transaction's statements spanning tables in databases held by two different database server instances, per the official guidance — the direct, only cause on affected versions.
  2. Synonyms masking a cross-site reference, per the official guidance's specific caution — a synonym can make a table in an external database (potentially on a different site) appear to be local, so a statement that looks single-site might not actually be.
Solutions / Resolution
  1. Roll back the current transaction, per the official guidance.
  2. Check the names of all tables affected by UPDATE/INSERT/DELETE statements within the transaction, per the official guidance, confirming they're all in the same database or in databases the same database server instance holds.
  3. Check the definition of any synonyms involved, per the official guidance, since a synonym can make an external-database (potentially cross-site) table appear local.
  4. On a current-version server, this specific restriction no longer applies, per the official guidance — cross-site updates within a transaction are handled differently on supported versions.
Examples
Reviewing table sites via synonym definitions
SELECT T.tabname synonym, S.* FROM systables T, syssyntable S
WHERE T.tabname = 'possibly_remote_table' AND T.tabid = S.tabid;

per -557's discussion of this same systables/syssyntable pattern, this reveals whether a name actually resolves to a local table or an external (potentially different-site) one.

Diagnostic Checks
  1. List every table touched by modifying statements in the transaction, per the official guidance, and confirm they share a single database server instance.
  2. Resolve any synonyms among those tables to their real, possibly-external targets before concluding the transaction is genuinely single-site.
Related Errors / Related Topics
  • -557 — "Cannot locate table that is external to the current database after level-count levels of synonym mapping." Shares the same systables/syssyntable diagnostic pattern for resolving what a synonym actually points to.

Obsolete since Version 5.01 — documented here for older platforms/archival reference; check for synonyms masking a cross-site reference on affected versions.