Locking Behavior

Question

Locking Behavior

chandan_jha18

SSChampion

Points: 11330
More actions
September 14, 2011 at 4:07 am

#390402

Hi,
In my organization, we tried to have a separate instance to deal with reporting work(only selects).
We have created replication from Progress(using ODBC) as well as SQL Server to an instance of SQL Server.
Now the developers say that because there be be continuous inserts and updates happening due to replication agents, they want to use nolock hints.
I am totally against it as I do not want to have dirty reads related to financial data. How to convince them for the same?
Thanks
Chandan

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

You'll want to take a look at isolation levels. If dirty reads shouldn't be allowed (I agree with you that you need to be extremely cautious with them), but locks are a problem, READ COMMITED SNAPSHOT may be the best option (although read up on the potential overheads of this isolation level first)

In any case, they shouldn't be embedding NOLOCK hints in code - at worst the READ UNCOMMITTED isolation level should be used.

As to how to convince them...I'd hope it wouldn't be too hard an argument for financial data. If the developers don't take risk and liability seriously, management tend to.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Being a bit pedantic, but I've read that article before and some of the arguments against also apply to the default READ COMMITTED isolation level. In either case you can have missing rows or duplicated rows when a row is moved within an index as although READ COMMITTED issues shared locks, they're released once a page/row has been read. See the "Missing and Double Reads Caused by Row Updates" section here:

http://msdn.microsoft.com/en-us/library/ms190805%28v=SQL.105%29.aspx

Edit: I realise there's a difference when it comes to page splits, but wanted to point out that READ COMMITTED also doesn't mean a consistent dataset for a point in time.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

HowardW (9/14/2011)
Being a bit pedantic, but I've read that article before and some of the arguments against also apply to the default READ COMMITTED isolation level. In either case you can have missing rows or duplicated rows when a row is moved within an index as although READ COMMITTED issues shared locks, they're released once a page/row has been read. See the "Missing and Double Reads Caused by Row Updates" section here:
http://msdn.microsoft.com/en-us/library/ms190805%28v=SQL.105%29.aspx
Edit: I realise there's a difference when it comes to page splits, but wanted to point out that READ COMMITTED also doesn't mean a consistent dataset for a point in time.

Nice read, that's going to the briefcase.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

Thanks to both of you for your replies.

We are using default isolation level and in their stored procedure they will just write some select commands as this server is just for reporting purpose.

They argue that if the replication agent tries to insert or update a lot of rows, their select queries will wait. Do you see any valid reason here? I don't see. Only exclusive locks prevent select queries to read anything. I am not sure if one can read from a table having an exclusive lock by using a 'nolock' hint.

This hint exists to let everything else know that 'this' select has no locks and do what you want!

Paul White SSC Guru Points: 150468 More actions · Answer 6

HowardW (9/14/2011)
Being a bit pedantic, but I've read that article before and some of the arguments against also apply to the default READ COMMITTED isolation level. In either case you can have missing rows or duplicated rows when a row is moved within an index as although READ COMMITTED issues shared locks, they're released once a page/row has been read.

REPEATABLE READ too, see http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.aspx

I generally prefer a row-versioning isolation level (RCSI or SI) over READ UNCOMMITTED, but all isolation levels have their uses. Sprinkling magic NOLOCK dust on code is pretty much always to be avoided, however.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi