ID-ing Deadlocks in a Thread using Firebird

Question

Developer looking for best method to identify a deadlock on a specific transaction inside a specific thread. We are getting deadlock errors but these are very general in FB 2.0 Deadlocks happening and they are leading to breakdowns in the DB connection between client and the DB. We send live ( once a second) data to the DB. We open a thread pool of around 30 threads and use them to ingest the data ( about 1-2 kB each second). Sometimes the DB can only take so much that we use the next thread in the pool to keep the stream current as possble. On occasion this produces a deadlock in addition to reaching the max thread count and breaking the connection. So we really need opinions on if this is the best method to ingest this amount of data every second. We have up to 100 on these clients hitting the DB at the same time. Average transactions are about 1.5 to 1.8 million per day.

Thanks,

Mike

Answer 1

I don't know of a specific way to identify the particular thread or statement. I've had to deal with FB deadlocks many times. You probably have two theads that are trying to update the same row in some table but they are doing it in separate transactions.

The best solution I've found is to design things so threads never have to update a row that any other thread might update. Sometimes that means having a thread that just exists to update a common table/row. The worker threads send a message to this thread. (The message could be done via another table.)

We run FB in many systems in the field that generate transactions (not millions per day) and we have found FB to be rock solid once we get the design correct.

Answer 2

In Firebird 2.1 there's new monitoring capabilities for tables, connections and transactions, maybe that can help you (if you can upgrade). See README.monitoring_tables.txt.

Example, get active statements:

SELECT ATT.MON$USER, ATT.MON$REMOTE_ADDRESS, STMT.MON$SQL_TEXT, STMT.MON$TIMESTAMP
FROM MON$ATTACHMENTS ATT 
JOIN MON$STATEMENTS STMT ON ATT.MON$ATTACHMENT_ID = STMT.MON$ATTACHMENT_ID
WHERE ATT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION AND STMT.MON$STATE = 1

Answer 3

My suggestion would be to write a 3-tier application, serialize all access to database (inserting) to a single thread (other threads would just stack up data on the queue) and use Firebird embedded (which is much faster because it eliminates TCP/IP overhead).

Beside avoiding deadlocks, this approach would also allow you to monitor the queue and see how is the system able to cope with the load.