The News‎ > ‎

SQL Server WITH (NOLOCK)

posted Dec 18, 2012, 12:48 AM by Leigh Williams
The with NOLCOK option is the same as the READUNCOMMITTED option.
 
Explanation:
Lets say that you have an update statement that updates everyone's salary. You use the BEGIN TRAN statement and the update stament finishes in a couple of seconds. You do not issue a COMMIT or a ROLLBACK staement yet.
Now HR runs a query "select salary from HR.employee", but to everyone's surprise, their select statement never finishes. It just hangs. The reason for this is because the data has been changed but not yet comitted.
This is where NOLOCK (READUNCOMMITTED) comes in. Adding this statement will allow "dirty" reads. This means you will read the changed salary's even though they haven't been comitted yet.
 
So remember to COMMIT (or ROLLBACK) and DML statements as soon as possible to avoid hanging select statements or dirty reads.
Comments