1

I've an issue with deadlock..

I'm trying to provide sql dependency on updates to my database.

I do this by creating a trigger on a series of tables. When tables are modified, I increment a integer in a secondary table eg. [dbo].[counters]. I then place a SQL dependency on this table to know which table is updated from code.

However, the target tables that I'm placing the triggers on could be used in a transaction, in which case, It's very easy to deadlock the transaction.

i.e. tbl1 has trigger on insert/update/delete to update counter table tbl2 has trigger on insert/update/delete to update counter table

transaction A occurs on thread1 tbl1 is modified, which triggers an update on counter table

transaction B occurs on thread2 tbl2 is modified, which triggers an update on counter table, thread 2 is now blocked waiting for thread1

meanwhile on thread1, transaction A continues, and updates another table, (either tbl1 or tbl2)

Now deadlock occurs and one of the thread is choosen as the victim.

My question is, if there is a better way to create the triggers to remove possibility of a deadlock? Anyone know how the default SQLDependency in ASP.NET would work to get around this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
savagepanda
  • 857
  • 12
  • 25
  • 3
    "I do this by creating a trigger on a series of tables. When tables are modified, I increment a integer in a secondary table eg. [dbo].[counters]. I then place a SQL dependency on this table to know which table is updated from code." - that sounds like a very poor design. – Mitch Wheat Jan 25 '13 at 23:44
  • 3
    What RDBMS? Some of them have 'sequences', which are basically the incrementing-table setup, but without a physical table. Although, they don't store other information, so they wouldn't include the name of the incrementing table... You should probably include your table and trigger definitions. What is it you're attempting to do here? – Clockwork-Muse Jan 25 '13 at 23:55
  • don't use triggers, they are nothing but trouble... – nathan hayfield Jan 25 '13 at 23:57
  • I fail to see why it's a poor design, as it's based on pretty much the same architecture as SQLDependency in asp.net.. http://www.tebyan.net/newindex.aspx?pid=31159&BookID=23727&PageIndex=51&Language=3 shows how it can be setup with aspnet_regsql. – savagepanda Jan 26 '13 at 01:06
  • Ok update to my investigation.. ASP.net SQLDependency uses a ROWLOCK during update. This helps reduce the deadlock as previously there were cases where the update query took range locks for the updates. By having rowlocks instead, the conflict is now a blocking call rather than a deadlock. – savagepanda Jan 28 '13 at 18:20
  • clockwork: Thanks for the suggestion, i'm using MSSQL. There is a context_info that corresponds to something you are talking about. This wont be part of the transaction, and might be just the piece needed to improve performance by removing/reducing the blocking behavior. – savagepanda Jan 28 '13 at 18:47

2 Answers2

1

Can you tolerate unused counter values? If so, just drop out of the transaction when updating the counters.

zmbq
  • 38,013
  • 14
  • 101
  • 171
1

Ok to summarize,

deadlock was caused by different transaction taking a range lock and row lock at the same time on the incrementing table.

ASP.NET SQLDependency uses ROWLOCK hint for the triggers to remove the deadlock scenario. It however this introduces blocking behavior so that if 2 transactions are modifying the same incrementing table, the transactions are essentially serialized.

A recommendation from MS Support is to change ROWLOCK Hint to READPAST. This reduces the blocking behavior if different rows within the incrementing table are being modified simutaneously.

Since notification does not require absolute ACID properties of the RDBMS, An advanced strategy could be to use a non blocking global variable like context_info as a set of binary flags to store the fact that a row has changed (this would remove the blocking on the same table), however requires logic change on the calling application to handle and reset the variables.

savagepanda
  • 857
  • 12
  • 25