August 6, 2013 at 5:43 am
I have been doing good some research on locks and isolation levels within sql server. From my understanding an exclusive lock will block a request to a shared lock. So if an update is being carried out on a table and another session attempts to carry out a select 7query on the same table, then it will block the select until the update has been carried out (this is Assuming we use read committed isolation).
Is this correct?
August 6, 2013 at 7:36 am
Yes, you are correct. And it is easy to validate.
Open 2 windows in SSMS. In one, do this (and leave this window open):
begin tran
update sometable
set somefield = 1
where someotherfield = 'a'
now in the second window, run this:
select *
from sometable
where someotherfield = 'a' --or no where clause
You will find that the select statement just sits there, returning no data. Now if you go back to the first window and type in "commit tran" (or rollback tran) and select/run that the select finishes immediately.
If you don't yet have a sample database to play with, go to codeplex.com and download the appropriate flavor of AdventureWorks for the version of SQL Server you are using.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 6, 2013 at 8:06 am
The SELECT won't be blocked, unless the SELECT attempts to read a page that has some type of exclusive lock on it. Update locks are generally at page level, unless the UPDATE operation is covering a large number of pages and SQL Server escalates intent exclustive (IX) lock to table level.
If you are seeing your SELECT operations frequently blocked by X or IX locks, then insure there is an index on the WHERE condition of the UPDATE, so the number of pages held by IX locks are minimized.
I found an article that goes into more detail with an example:
http://www.mssqltips.com/sqlservertip/1485/using-sql-server-indexes-to-bypass-locks/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 6, 2013 at 8:57 am
Eric M Russell (8/6/2013)
Update locks are generally at page level, unless the UPDATE operation is covering a large number of pages and SQL Server escalates intent exclustive (IX) lock to table level.
There will always be an IX lock at the table level. If a data modification locks at the row level there will be a table level IX, a page level IX and a row level X. If the data modification locks at page level then there will be a table level IX and a page level X lock.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply