I have 15 tables which are normalized to contain records and many user may be inserting/updating data in these tables( No two user can update the same record(s) simultaneously, this is restricted by client implementation). these 15 tables are updated 1 by 1 in a stored procedure. WITH(NOLOCK) is used where select statements are to avoid read locks as some user may be viewing the data at same time. But sometimes there is locking in these select statements in these procedures. As my past experience I put these nolock to avoid blocking.
I am not sure if there is WITH(NOLOCK) is missing somewhere or this is some other problem. Should I search for missing WITH(NOLOCK)?
or what other ways are there to avoid this locking?
What are the restrictions with NOLOCK? I’m just reading the data and I don’t care if I read committed or uncommitted data. What else steps can be taken to remove this blocking?
Advertisement
Answer
WITH(NOLOCK) :
WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level.
Basically it gives the dirty read. i.e. if any transaction hold some data and then we try to get the data it will give the output without wait of transaction commit. so that we get the dirty read without wait.
So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it’s probably not going to be the right solution to whatever problem you’re trying to solve with it.