Firebird update statement freezes

Question

After over one day of finding & trying to solve issue its time to ask here.

We use Firebird over years (around 6) and with current version 2.5.2 updated long time ago we met the problem with unable to UPDATE statement.

SQL statement is OK, even "where" condition is used with primary key.

Problem: update is going to stuck after click on: execute
a) from php script it return Internal server error 500
b) directly from Flamerobin or IBQ its freezes and not responding at all

hint: SQL which wasnt working is working right after clean firebird (stop & start) but after a while its going to stuck again
hint: selects are or, problem is only with UPDATE
hint: I did all described here https://www.ibphoenix.com/resources/documents/how_to/doc_5 (redump of database file which was giving me errors on gfix -v -full) after redump there was no error but problem occurs
hint: We have more servers running the same configuration with more database files but this happen only for one table in one DB file on one server.

After some investigation I finally got this from fbtrace:

2016-11-28T14:25:28.4410 (9473:0x7f1489cb1f08) PREPARE_STATEMENT
    phones.fdb (ATT_273856, VILAS:NONE, UTF8, TCPv4:10.1.1.195)
    /usr/bin/flamerobin:10868
        (TRA_78838, CONCURRENCY | WAIT | READ_WRITE)

Statement 422749:
-------------------------------------------------------------------------------
UPDATE TELEFONI_CISLA SET DATUM_PRIDANI = '2011-7-3' WHERE ID = '17274'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TELEFONI_CISLA INDEX (RDB$PRIMARY4))
      0 ms

2016-11-28T14:25:28.4780 (9473:0x7f1489cb1f08) EXECUTE_STATEMENT_START
    phones.fdb (ATT_273856, VILAS:NONE, UTF8, TCPv4:10.1.1.195)
    /usr/bin/flamerobin:10868
        (TRA_78838, CONCURRENCY | WAIT | READ_WRITE)

Statement 422749:
-------------------------------------------------------------------------------
UPDATE TELEFONI_CISLA SET DATUM_PRIDANI = '2011-7-3' WHERE ID = '17274'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (TELEFONI_CISLA INDEX (RDB$PRIMARY4))

Im out of ideas what to check, almost lost. Just want let you know that we didnt change anything in FB or server configuration.

Thanks for any usefull help.

Firebird 2.5.2 is not a recent version, I suggest you upgrade to at least 2.5.6. However, your problem could also be due to a lock by a concurrent transaction and your use of a WAIT transaction. Either use a no wait transaction, or use a wait transaction with a timeout. — Mark Rotteveel
EDIT: Now its running 2.5.5 and I will try to specify timeout: DeadlockTimeout to 10 — Zbyněk Kočí

Zbyněk Kočí Zbyněk Kočí · Accepted Answer · 2016-12-02T11:24:17

Everything was caused by one of many script running in background (from cron) taking open transaction without committing for few hours. Its OK back again.

Firebird update statement freezes

1 Answers