Title: Hard-committed transaction causes false "update conflicts" on SuperClassic (5.0.3 and 5.0.4)

[Peter-1-1]

Title: Hard-committed transaction causes false "update conflicts" on SuperClassic (5.0.3 and 5.0.4)

Overview

A transaction that has been fully committed via COMMIT_TRANSACTION (hard commit) continues to be seen as active/concurrent by new attachments for up to 10+ minutes after the commit. This causes false deadlock / update conflicts with concurrent update errors referencing the already-committed transaction number.

The bug is confirmed on Firebird 5.0.3 (build 1683) and Firebird 5.0.4 (build 1812) under SuperClassic architecture.

This appears related to #8541 (fix-version 5.0.4), but the fix did not resolve this scenario.

Affected Versions

• affect-version: 5.0.3, 5.0.4
• Architecture: SuperClassic
• OS: Ubuntu Linux x64
• Hardware: 124GB RAM, 32-core AMD EPYC, NVMe RAID10 (dedicated DB server)

Steps to Reproduce

The issue occurs under concurrent load with multiple attachments from different source IPs modifying the same table. It has been observed with two different tables (T_URGENT_OPERS and REPL$LOG) and three different client applications (php-fpm7.2, php8.3, IBReplicator), confirming it is not application-specific.

Evidence: Firebird 5.0.4 (2026-05-01)

Transaction TRA_327149913 — committed successfully

2026-05-01T05:59:07.3460 START_TRANSACTION
    /data/INOUT-FB.FDB (ATT_190168728, REPL_R4:NONE, NONE, TCPv4:77.78.30.33/44328)
    /opt/IBReplicator/replserver:1575986
        (TRA_327149913, CONCURRENCY | WAIT | READ_WRITE)

[... executes SELECTs and DELETE FROM REPL$LOG WHERE SEQNO<=1011222617 ...]

2026-05-01T05:59:08.5220 COMMIT_TRANSACTION
    /data/INOUT-FB.FDB (ATT_190168728, REPL_R4:NONE, NONE, TCPv4:77.78.30.33/44328)
    /opt/IBReplicator/replserver:1575986
        (TRA_327149913, CONCURRENCY | WAIT | READ_WRITE)
      0 ms, 1 read(s), 16 write(s), 1 fetch(es), 1 mark(s)

Hard commit at 05:59:08.522, no errors.

False deadlock errors referencing TRA_327149913 AFTER commit

New IBReplicator attachments attempt DELETE FROM REPL$LOG WHERE SEQNO<=1011222525 (a subset of already-deleted-and-committed rows):

2026-05-01T06:07:36.7340 ERROR AT JStatement::execute
    /data/INOUT-FB.FDB (ATT_190196062, REPL_R4:NONE, NONE, TCPv4:77.78.30.33/54960)
335544336 : deadlock
335544451 : update conflicts with concurrent update
335544878 : concurrent transaction number is 327149913

2026-05-01T06:08:39.1270 ERROR AT JStatement::execute
    /data/INOUT-FB.FDB (ATT_190198069, REPL_R4:NONE, NONE, TCPv4:77.78.30.33/53506)
335544336 : deadlock
335544451 : update conflicts with concurrent update
335544878 : concurrent transaction number is 327149913

2026-05-01T06:09:45.1120 ERROR AT JStatement::execute
    /data/INOUT-FB.FDB (ATT_190201072, REPL_R4:NONE, NONE, TCPv4:77.78.30.33/58926)
335544336 : deadlock
335544451 : update conflicts with concurrent update
335544878 : concurrent transaction number is 327149913

• Error 1: 8 minutes 28 seconds after commit
• Error 2: 9 minutes 31 seconds after commit
• Error 3: 10 minutes 37 seconds after commit

All three are from different attachments (ATT_190196062, ATT_190198069, ATT_190201072) — these are new connections, not long-lived ones with stale snapshots.

Evidence: Firebird 5.0.3 (2026-03-20) — same bug, different table

Transaction TRA_5791473 — committed successfully

2026-03-20T11:57:37.1090 START_TRANSACTION
    /data/TIP-TOP.FDB (ATT_4424969, SYSDBA:NONE, UTF8, TCPv4:172.16.3.85/49054)
    /usr/sbin/php-fpm7.2:3112708
        (TRA_5791473, CONCURRENCY | NOWAIT | READ_WRITE)

[... UPDATE T_URGENT_OPERS, INSERT T_WAYBILL_STATUSES_HISTORY ...]

2026-03-20T11:57:37.1230 COMMIT_TRANSACTION
    /data/TIP-TOP.FDB (ATT_4424969, SYSDBA:NONE, UTF8, TCPv4:172.16.3.85/49054)
        (TRA_5791473, CONCURRENCY | NOWAIT | READ_WRITE)
      0 ms, 1 read(s), 18 write(s), 1 fetch(es), 1 mark(s)

False deadlock errors referencing TRA_5791473 AFTER commit

2026-03-20T11:58:01.9540 ERROR (ATT_4425687, TCPv4:172.16.3.90)
    concurrent transaction number is 5791473
    At procedure 'P_SET_AWB_STATUS' line: 10
    At procedure 'P_SET_WH_SOFIA_STATUS' line: 22

2026-03-20T11:59:02.2300 ERROR (ATT_4427481, TCPv4:172.16.3.90)
    concurrent transaction number is 5791473

2026-03-20T12:00:01.5340 ERROR (ATT_4429365, TCPv4:172.16.3.90)
    concurrent transaction number is 5791473

• Error 1: 24 seconds after commit
• Error 2: 85 seconds after commit
• Error 3: 144 seconds after commit

Different attachments, different source IP (172.16.3.90 vs 172.16.3.85), different application (php8.3 vs php-fpm7.2).

Impact

1. False update conflicts: Transactions that should succeed are rejected.
2. Stale reads (silent data inconsistency): If an attachment sees a committed transaction as active, it reads the OLD record version instead of the new one. Unlike update conflicts, stale reads produce NO error — the application silently works with outdated data.
3. ACID violation: The Isolation guarantee of MVCC is broken — two concurrent connections can read different versions of the same committed data.

Analysis

The common factor across both cases:

• SuperClassic architecture: each attachment has its own page cache
• Hard COMMIT_TRANSACTION: not COMMIT_RETAINING
• New attachments: the failing connections are brand-new (different ATT numbers, different source ports), not long-lived connections with old snapshots
• TIP page propagation: after commit, the TIP (Transaction Inventory Page) is updated on disk, but other attachments' cached TIP copies are not invalidated/refreshed, so they continue to see the transaction as active

This is NOT #8541 (which was fixed in 5.0.4 for a replica journal scenario). The pattern here occurs on a primary (non-replica) server with regular application workload.

Relationship to Other Issues

• Deadlock update conflict on replica server? #8541 — Similar symptoms on replica server, fixed in 5.0.4, but fix does not cover this primary-server scenario
• Races between transactions on the primary side may cause update conflicts while applying journals to the replica #7052 — Describes the theoretical gap between TIP update and journal write in replication context
• Committed transaction could be marked as dead in some very special and rare case. #6984 — "Committed transaction could be marked as dead", CS/SC only, SS not affected (different bug but same architectural vulnerability)

Configuration

ServerMode = SuperClassic
DefaultDbCachePages = 8192
TempCacheLimit = 536870912          # 512MB
ReadConsistency = 1
GCPolicy = cooperative
ConnectionIdleTimeout = 60
MaxStatementCacheSize = 0           # disabled
MaxParallelWorkers = 32
ParallelWorkers = 20
DataTypeCompatibility = 2.5
AuthServer = Legacy_Auth, Srp, WinSspi
WireCrypt = Disabled
UserManager = Legacy_UserManager

Note: ReadConsistency = 1 is enabled — this uses the ReadConsistency snapshot isolation mode introduced in Firebird 4. This may be relevant as it changes how transaction snapshots and record version visibility are managed internally.

Server: 124GB RAM, 32-core AMD EPYC 4585PX, NVMe RAID10 (dedicated database server, no other services).
Concurrent sources: php-fpm7.2 (172.16.3.85, 172.16.3.87), php8.3 (172.16.3.90), IBReplicator (77.78.30.33). Approximately 100-200 concurrent connections during the observed incidents.

Trace Logs

Full fbtracemgr trace logs for both incidents are available upon request.

fb_debug_log.txt

[hvlad]

Full fbtracemgr trace logs for both incidents are available upon request.

Please, provide

[Peter-1-1]

This is a full log for the time period. Short extract only for transaction 327149913 is in fb_debug_log.txt

[hvlad]

Thanks.

I can make a patch with enhanced tracing to gather more information about the issue.
Is it OK for you to try such a build ?

[Peter-1-1]

Yes I'm ok with the trace build

[BFuerchau]

The main problem here is often the wrong handling of open ressources.
I could see, that in MON$STATEMENTS often are statements visible, which are minutes old before they disappear.
In the .Net-Client each object like Connection/Command/Reader has an Dispose() function to remove handles and clear internal ressources. I allways don't use the connection pool so the connection close really and no ressource is still available.
So if you issue a commit you schould be shure, that you don't have any ressource open except the connection, you can than dispose and close.
To prevent datatabse cache unload i have still an unsed connection open, that do every 30 seconds a single request to rdb$database within 1 transaction.

No software is errorfree, so statements, transactions and connection are leave active if an application crashes, exception are thrown without dispose of resources or something else.
So every database action should be within try/finally-Block to clear ressources.
Sometimes it needs days, i don't know why, that holds transactions open and the database growths on high transaction load.
Often only save/restore activities solve than the problem, which we do most 1 times á week.
This i do since firebird 1.5;-).

[aafemt]

The main problem here is often the wrong handling of open ressources.

Attached trace clearly show that this is not the case.

JFYI: Prepared statements are bound to attachment so it is normal if they live longer than transaction.

[BFuerchau]

In MON$STATEMENTS i have often statements with state 0 (no more active, the process doesn't exists) but have also attached transaction, so the oldest interested transaction never increase and garbage not happens.
With many transction ETL in the same table, the queries runs slower and slower because old data must be ignored.
So we have attached a watcher process which try to delete these statements, older than 60 minutes, so transaction and connection can be closed from fbserver. But sometimes, these statements can not be deleted and the save/restore is needed.

[hvlad]

Yes I'm ok with the trace build

Please, use the appropriate build artifacts from this link
Test in lab first!

[Peter-1-1]

Hi Vlad,

Thank you for the build!

As a temporary workaround we switched to SuperServer — the issue has
not appeared there so far.

We plan to deploy the instrumented build this weekend (May 9-10),
switch back to Classic to reproduce the bug, and report back with
the trace logs.

Best
Peter