keeping table unlocked during transation

Question

keeping table unlocked during transation

mishrakanchan86

SSC-Addicted

Points: 413
More actions
August 26, 2014 at 3:51 pm

#298861

Begin transaction
Delete from Table1 where UniqueKey in (Select UniqueKey from TAble2)
Commit transaction
While running this transaction the table1 locks out completely. Is there a way to lock only those rows that are being deleted and keep the read on the rest of the rows open. Actually i dont mind reading dirty data also. All I want is that the select on table1 should be open.
-- this dint work
Delete from Table1 with(noloack) where UniqueKey in (Select UniqueKey from TAble2)
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Deletes always take locks. That cannot be avoided.

Delete in batches if there are a lot of rows, that keeps the lock duration down and reduces the chance of a lock escalation occurring. Consider using one of the row versioning isolation levels for your selects.

And please do some reading on the side effects of nolock before you consider using it anywhere.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

mishrakanchan86 (8/26/2014)
Delete from Table1 with(noloack) where UniqueKey in (Select UniqueKey from TAble2)

Apart from being a futile effort using the nolock hint with update / delete, it is also a depreciated feature, avoid using it.

😎

hybg1107 Grasshopper Points: 21 More actions · Answer 3

so , if you don`t mind dirty data, you can`t use update/delete(nolock), why you don`t use select .....(nolock),

you can read the table which is during update/delete.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

As you mentioned dirty read is ok then use

SELECT <<column names>> FROM table1 WITH (NOLOCK)

Also, nolock hint should not be used with DELETE & UPDATE statements. This is in deprecated feature list & will be removed in future versions.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

For emphasis, since the people recommending nolock aren't mentioning the side effects

And please do some reading on the side effects of nolock before you consider using it anywhere.

It is not just the possibility of dirty reads.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass