I have a table which I'm only Insert, Delete and Select (no Updates)

on Insert, Sometimes it used a session lock (WITH (TABLOCKX))

I have a program using ADO which Select from the table using the RecordSet::Open command to open a simple sequential query.


Select * from t1 where id >= @from_id And >= @to_id`  


Select top(10) * from t1 where id >= @id Order by id 

The id column is an Identity column.

When I'm open the query, sometimes I'm getting a deadlock. (the table is locked by the Insert query and the id column's index is locked by the Select)

I'm trying to prevent the deadlocks.

Should I open the select queries WITH(NOLOCK)? may it prevent the deadlocks?

  • 8
    WITH (NODEADLOCK) – Philᵀᴹ Aug 13 '19 at 17:30
  • 5
    Are you ok with the possibility of reading the same rows twice, skipping rows, seeing data never committed, and having your query fail with error? Reference 1, Reference 2 – scsimon Aug 13 '19 at 17:50
  • @Philᵀᴹ so using WITH (NODEADLOCK) is safe enough? – SHR Aug 13 '19 at 19:52
  • 2
    @shr - Phil was being facetious. There is no such hint named NODEADLOCK – Max Vernon Aug 13 '19 at 20:33

A better idea would be to enable snapshot isolation: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

This will enable each transaction to have its own snapshot of data and will not lock, or block other transactions.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.