1

I am using SQL server 2008 R2 edition and want to apply lock on table level while selecting the data from the table.

As applying NO_LOCK can led to DIRTY READ problem so want to apply NO_LOCK on the tables that contains only domain data not the transaction data. i.e. the data which is very less frequent to be changed.

Please suggest any way to apply the LOCK on the domain tables.

Tarun Arora
  • 113
  • 2
  • 3
  • 9

1 Answers1

0

You don't need to lock a table while reading (SELECT) since reading always acquires a shared lock on the table or row. WITH(NOLOCK) table hint just allows to read uncommited data as well; that is rows that are yet to be inserted and commited by other session. You can consider, setting TRANSACTION ISOLATION LEVEL to READ COMMITED to make sure that uncommited data is never red.

Rahul
  • 76,197
  • 13
  • 71
  • 125