CODEWITHSUNDEEP
sqlt-sql
Ram Das
Ram Das

Reputation: 358

Can we wirte with(nolock) in update statement joining with other tables

I have two tables, let say Main,transactions table

Main : 

mainID Name  Active
1  sharath  1
2  kiran    0
3  ashish   1

Transaction : 

TransID TransType status MainID IsActive
101      D         22    1       0
102      R         27    2       1
103      R         32    2       1
104      D         11    1       0
105      R         43    3       0

which update statement is preferable in these :

1)

Update TR
set  status  = 0,
     Isactive = 0
from Transaction TR
inner join main MN with(nolock) on MN.MainID = TR.MainID 
where MN.Isactive = 0

2)

Update TR
set  status  = 0,
     Isactive = 0
from Transaction TR
inner join main MN on MN.MainID = TR.MainID 
where MN.Isactive = 0

In first query, I made inner join with Main and used with(nolock) as I want to update the records for those MainID where Isactive = 0

In second query, without using with(nolock) I have written update statement.

which one is preferrable, n will do update without any errors in future also.

Upvotes: 2

Views: 3845

Answers (1)

Gopal SA
Gopal SA

Reputation: 959

Using NOLOCK with SELECT will return data, that could be dirty . We can use this table hint to get results quickly when we are sure that dirty data is fine. But i do not see a reason why one should use NOLOCK on DML, as dirty data might get written to db and also committed. Uncommitted transactions in main table might introduce dirty data into Transaction table.

Upvotes: 1

Related Questions