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
    – Philᵀᴹ
    Aug 13, 2019 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
    – S3S
    Aug 13, 2019 at 17:50
  • @Philᵀᴹ so using WITH (NODEADLOCK) is safe enough?
    – SHR
    Aug 13, 2019 at 19:52
  • 2
    @shr - Phil was being facetious. There is no such hint named NODEADLOCK
    – Hannah Vernon
    Aug 13, 2019 at 20:33

1 Answer 1


A better idea would be to enable snapshot isolation: https://learn.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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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