locked
Deadlocks: How do I avoid them in this scenario? RRS feed

  • Question

  • I have a WCF process that executes continually 24/7 importing data from a hosted environment to a local mssql 2012 server. One of the things that occurs within this process is that the incoming XML is passed over to a stored proc that writes to several tables in one single transaction. This process has worked for the past 3 years, however, lately the end users want to look at the arriving data in an ever increasing shorter time frame.  

    The data from the shredded xml that gets written is currently being exposed as a view. I've been seeing deadlocks on the core tables that comprise this view from the process that is going to the view to import the data elsewhere. Any advice on the best way to avoid the deadlocks on the read while the transaction is active? A dirty read with (nolock) hint would not work as this would not bring back the full complement of data.

    Thursday, June 9, 2016 7:11 PM

Answers

  • You could put the database in READ_COMMITTED_SNAPSHOT. When using the snapshot, readers and writers will not block each other. The readers will see the data that was committed when the statement started running.

    Note that enabling snapshot will take a toll on tempdb, so you may want to enlarge it.

    Thursday, June 9, 2016 9:36 PM

All replies

  • If you put timestamps (or date) fields in the data, and the imports always write and don't update, then that might help.

    Josh

    Thursday, June 9, 2016 8:16 PM
  • You could put the database in READ_COMMITTED_SNAPSHOT. When using the snapshot, readers and writers will not block each other. The readers will see the data that was committed when the statement started running.

    Note that enabling snapshot will take a toll on tempdb, so you may want to enlarge it.

    Thursday, June 9, 2016 9:36 PM