1

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 possible.

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.

S.L. Barth is on codidact.com
  • 8,198
  • 71
  • 51
  • 66

3 Answers3

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.

Kevin Gale
  • 4,350
  • 6
  • 30
  • 31
  • I think this is as accurate an answer as any so far - we've been working with Firebird for a while and it seems like attention to detail in stored procedures eventually reduces or eliminates the issue. I'd also suggest IBExpert for monitoring - their license for a single developer seat is inexpensive, and their feature set is impressive. – g.d.d.c Dec 01 '12 at 21:49
1

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
Harriv
  • 6,029
  • 6
  • 44
  • 76
-1

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.

Milan Babuškov
  • 59,775
  • 49
  • 126
  • 179
  • I'm down-voting because I don't think that bottle-necking database access to a single thread is the right answer to this particular question. The main reason you use Firebird's Server Model instead of the Embedded Library is to support multiple concurrent clients. They're already dependent on this feature of the Service. We recently moved to it (away from embedded) so that a User Interface and Service could both access data directly, reducing serialization overhead back and forth between the components, as just one use case example. – g.d.d.c Dec 01 '12 at 21:48
  • The more concurrent clients you have, the higher chance of the deadlock. I agreed with your comment when you have multiple independent operations that can be executed concurrently, but in this case, the question was how to handle concurrent operations that deadlock each other. – Milan Babuškov Dec 02 '12 at 08:55