Repository navigation
Hard-committed transaction causes false "update conflicts" on SuperClassic (5.0.3 and 5.0.4) #9017
Description
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
- False update conflicts: Transactions that should succeed are rejected.
- 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.
- 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_UserManagerNote: 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.
Activity
hvlad commentedon May 1, 2026
Please, provide
Peter-1-1 commentedon May 1, 2026
This is a full log for the time period. Short extract only for transaction 327149913 is in fb_debug_log.txt
hvlad commentedon May 1, 2026
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 commentedon May 2, 2026
Yes I'm ok with the trace build
BFuerchau commentedon May 3, 2026
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 commentedon May 3, 2026
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 commentedon May 3, 2026
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 commentedon May 3, 2026
Please, use the appropriate build artifacts from this link
Test in lab first!
Peter-1-1 commentedon May 4, 2026
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
Peter-1-1 commentedon May 11, 2026
Hi Vlad,
I've sent you the full trace log to your email.
Best
Peter
hvlad commentedon May 11, 2026
Thanks, looking into it
hvlad commentedon May 13, 2026
Would you like to test new build ?
Peter-1-1 commentedon May 13, 2026
Yes, is the build a fix or does it add additional debug data?
hvlad commentedon May 13, 2026
Additional debug\tracing.
BTW, any interesting messages in firebird.log ?
Also, what settings is changed in firebird.conf ?
Peter-1-1 commentedon May 13, 2026
firebird.conf:
DataTypeCompatibility = 2.5
MaxParallelWorkers = 32
ParallelWorkers = 20
ServerMode = SuperClassic -- tested and with Classic - bug exist
AuthServer = Legacy_Auth, Srp, WinSspi
AuthClient = Legacy_Auth, Srp, WinSspi
WireCrypt = Disabled
UserManager = Legacy_UserManager
ReadConsistency = 1 -- tested and with ReadConsistency = 0 - bug exist
ConnectionIdleTimeout = 60
MaxStatementCacheSize = 0
DefaultDbCachePages = 8192
TempCacheLimit = 536870912
GCPolicy = cooperative
There is no errors in firebird.log
The first errors appear after 12 to 48 hours of operation - then they become more frequent
19 remaining items
hvlad commentedon Jul 31, 2026
New build for testing
hvlad commentedon Aug 14, 2026
Peter, are you plan to test the new build?
Or should I close the issue and be satisfied with the current patch?
Just let me know, please.
Peter-1-1 commentedon Aug 14, 2026
Hi,
Due to the holiday period, I would be able to test the new build on September 4 at the earliest.
So, if possible, please keep the issue open until then, and I’ll test the new build and provide feedback.
Thanks for your patience.
Best regards,
Peter
hvlad commentedon Aug 14, 2026
Sure, thanks
hvlad commentedon Oct 21, 2026
Reminder...
pcisar commentedon Oct 22, 2026
I suppose the fix was tested internally? The severity calls for a permanent test scenario in Firebird QA suite. As embedded is always SuperClassic, the blast radius is not small. I really hope for fix in 5.0.5, otherwise we'll have to live with it for another year.
Peter-1-1 commentedon Oct 22, 2026
Sorry, there are too many issues at the moment. I'll run the test again this Friday and provide a final answer on Monday.
Best
Peter Minev
Peter-1-1 commentedon Oct 26, 2026
Hi Vlad,
Quick status update: we are testing your latest commit (a108a04) on our production server in Classic mode since October 24, with transaction/error tracing enabled.
After 48 hours there has been no reproduction of the bug:
The plan is to keep the test running for one week. I'll report back with the final result.
Best regards,
Peter
hvlad commentedon Oct 26, 2026
Thanks!
Peter-1-1 commentedon Nov 2, 2026
Hi Vlad,
Final report on testing a108a04.
Result: the bug did not reproduce.
No "TPC:" messages in firebird.log, and no crashes or restarts of the custom build during the whole week.
No false update conflicts: we checked every update conflict in the trace against the blocker's Commit number. All were legitimate (blocker committed after the victim's snapshot, or still active at the time of the error); the concurrent CN in the error always matched the Commit number in the trace.
TPC shared memory was fully re-created 6 times during the test (mostly at ~04:30, when all attachments were gone), with no issues after any of them.
Thanks for the fix.
Best regards,
Peter
hvlad commentedon Nov 2, 2026
Thanks!