June 19, 2003 at 8:53 am
Is it possible in SQL SERVER to prioritize an Insert over a Select statment?
The way this project is set up I am going to deadlock on apps inserting while anothers select on the same table and rows.
June 19, 2003 at 5:06 pm
If the SELECT statements aren't fussy about uncommitted data you could give them a NOLOCK hint, and they won't interfere with the INSERTs.
If you're resigned to deadlocks and don't want the INSERTs to fail, you can set DEADLOCK PRIORITY to LOW for the SELECTs thus making them the victims in any deadlock situation.
Finally, deadlocks are not at all desirable, whereas locking and blocking are normal for SQL Server. Analyse why deadlocks are happening and see what steps can be taken to eliminate them. Even though it may seem like drastic action, if the INSERT transaction takes a table lock initially until the transaction is completed, then deadlocks can be dramatically reduced. Of course this impacts concurrency, so is just one option to be explored.
Cheers,
- Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply