February 17, 2006 at 6:15 pm
In SQL Server 2000, can a Select query ever cause locking?
We're running with the default. I think the default is as follows.(isolation level - read committed) This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.
So with that default, if we have a long running select query and an update transaction that occur at the same time, can the select query have any performance impact on the update transaction?
Thanks
February 20, 2006 at 1:58 am
Yes it can. As that piece from books online stated, a select takes shared locks for the duration of it's execution. A shared lock means that while other reads can occur on the locked rows, modifications cannot.
Hence for the duration of a select query, any updates, inserts or deletes that affect the locked ows/pages/extents (depending which SQL has decided to lock) will be blocked and will have to wait for the select to complete
Think of it this way, you can't have one process modifying data that another is reading or you will get inconsistent results.
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
February 20, 2006 at 9:02 am
Thank you, GilaMonster.
February 20, 2006 at 9:39 am
If a dirty read on the select query is OK, open all the tables with nolock,or set the tranaction isolation level to read uncommitted. This will allow your updates to occur but they will not be reflected in the select.
Terry
February 20, 2006 at 9:43 am
I will look into coding NOLOCK on all select to avoid impact on the updates. Thanks for the tip, TDuffy.
February 20, 2006 at 10:42 pm
I would suggest NOLOCK as a last resort. Look at optimising your select as much as possible to get it running faster, also look at optimising the update. Firstly you may be able to get the select nning faster, second you may be able to get it to take more specific locks (row instead of page, page instead of table) which improves the scalability of your db.
It's easy to get into the habit of sticking NOLOCK on everything, then wondering where the inconsistent data comes from when a dirty read happens. Note that with NOLOCK, you can read data as it's been written. You can read data that's updated then rolled back.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply