Subject RE: [firebird-support] RE: Firebird SuperClassic hangs
Author Paul Beach
> Deadlock scans: 289, Deadlocks: 1, Scan interval: 10

Is probably the culprit, 289 deadlock scans, one actual deadlock found....

"Deadlock scans. The number of times that the lock manager walked a chain of
locks and owners looking for deadlocks. The lock manager initiates a deadlock scan
when a process has been waiting 10 seconds for a lock."

"Deadlocks. The number of actual deadlocks found, A deadlock occurs when
Process A, wants a lock on Resource 1 which is held in an incompatible mode
by Process B and Process B wants a lock on some Resource 2 which is held in an
incompatible mode by Process A.

"Each owner stands around glowering at the other and neither will do anything
to improve the situation, so the lock manager returns a fatal error to one or the
other. Deadlocks can also occur with a single resource if both owners start with
read locks and request conversions to write locks. However, deadlocks always
involve two owners (or two separate transactions)

Errors that are returned as "lock conflict" from "no wait" lock requests will not
be recorded in the lock table as deadlocks because only one owner is waiting.

Errors returned as "deadlock" with a secondary message specifying "update conflicts
with concurrent update" are not actual deadlocks either. What has happened in those
cases is that one owner has modified (or erased) a record and moved on. Another
concurrent owner has attempted to modify (or erase) the same record, noticed that the
most recent version is one he can't see waited to find out how the other transaction
ends up, and found to his disappointment, that the other transaction succeeded.
In that case, our patient transaction can't modify the record because it can't find
out what its late contemporary actually did."

"Scan interval. The lock manager waits some period of time after a request starts
waiting before it starts a deadlock scan. The default interval is 10 seconds, which may be
long considering the change in CPU performance since 1983. Deadlock scans should not be done
whenever there's a wait because waiting is normal and scans are not free."

A deadlock scan would cause your server to hiccup while the lock manager tries to
find if there is a real deadlock within the lock manager. You might want to investigate
why deadlock scans are being instigated..

With thanks to Ann Harrison :-)

Paul Beach