2

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.

like:

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

Or:

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?

SHR
  • 886
  • 3
  • 15
  • 37

1 Answers1

5

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.

Blake
  • 205
  • 1
  • 4