THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

T-SQL Tuesday #006: LOB, row-overflow and locking behavior

This post is my contribution to T-SQL Tuesday #006, hosted this time by Michael Coles.

Actually this post was born last Thursday when I attended Kalen Delaney's "Deep dive into SQL Server Internals" seminar in Tel-Aviv. I asked question, Kalen didn't have answer at hand, so during a break I created demo in order to check certain behavior. Demo goes later in this post but first small teaser. I have MyTable table with 10 rows. I take 2 rows that reside on different pages. In first session transaction is opened and first row is deleted using PAGLOCK hint (without COMMIT for a meantime). In second session I select all columns from the second row(both sessions access rows by primary key which is also clustered index). Second session got locked. How come?

Here is the full story and demo script. First we create table with 10 rows, 2 of which are long.

CREATE TABLE dbo.MyTable( c1 INT NOT NULL PRIMARY KEY, c2 VARCHAR(8000), c3 VARCHAR(8000), c4 VARCHAR(8000), c5 VARCHAR(MAX) )
GO

;WITH Nums( Num )
AS
(
  
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) AS Num
  
FROM sys.objects
)
INSERT INTO dbo.MyTable(c1, c2, c3, c4, c5)
SELECT 
  
Num AS c1,
  
CASE WHEN Num IN (2, 8) THEN REPLICATE('a', 3000) ELSE 'a' END AS c2,
  
CASE WHEN Num IN (2, 8) THEN REPLICATE('b', 3000) ELSE 'b' END AS c3,
  
CASE WHEN Num IN (2, 8) THEN REPLICATE('c', 4000) ELSE 'c' END AS c4,
  
CASE WHEN Num IN (2, 8) THEN REPLICATE('d', 4000) ELSE 'd' END AS c5
FROM Nums AS n1
WHERE Num <= 10
GO

Let's examine, how out table is stored (7 is ID of my database; I don't show entire output but only relevant parts):

DBCC IND(7, MyTable, -1)

image

 

So we have 2 leaf-level data pages, 1 page for row-overflow data and 1 page for LOB data plus 3 IAM pages - one per page type. We can also verify that row-overflow and LOB pages contain 2 rows each (using DBCC PAGE we check that m_slotCnt = 2 in page's header). Now open new window in Management Studio and execute:

BEGIN TRAN

DELETE FROM
dbo.MyTable WITH(PAGLOCK) WHERE c1 = 2

In another session execute:

SELECT c1, c2, c3, c5 FROM dbo.MyTable WHERE c1 = 8

We see that second session is locked, so let's check locking details with sp_lock procedure:

 

image

 

Page in contention is 200585 which is LOB data page as we can see from the first table. Actually that's perfectly natural behavior - DELETE acquires exclusive lock, SELECT tries to acquire shared. But I'm sure it would have taken some time for me to solve the puzzle whether it was real case in my database. We're used to think of a row sitting at one page, so rowlock locks a row or a key. Probably entire page if we used PAGLOCK hint or Optimizer decided to start from a page-level lock. But we aren't accustomed to think of a row which spans over multiple pages.

And what happens if we query row-overflow page? Pretty much the same (just substitute c5 by c4 in the second query):

 

image

 

Another interesting issue is - what happens when we don't query LOB and row-overflow columns? We know that SQL Server doesn't know to lock particular columns but always entire row of a table or an index. So what will happen if we query only first 3 columns: c1, c2 and c3? No blocking lock this time. It means that SQL Server knows to lock particular columns and leave other columns unlocked but only if they reside on separate pages. Actually it is similar to column-oriented databases behavior.

Finally I want to check what happens when LOB column is updated - whether in-row data is also being locked. So I update LOB column in the first session and try to query same row but without LOB column.

First session:

BEGIN TRAN

UPDATE
dbo.MyTable SET c5 = REPLICATE('e', 4000) WHERE c1 = 2

Second session:

SELECT c1, c2, c3, c4 FROM dbo.MyTable WHERE c1 = 2

Blocking lock again. Now on a keylock:

 

image

 

Here I don't see any reason for exclusive lock - shared would be enough in order to prevent DML operations on the row. No danger of dirty read here since in-row data page isn't updated at all. So first step was nice - shared lock isn't acquired when we don't read row-overflow and/or LOB page. Second step in my opinion should be - for DML operations acquire only shared lock for pages/rows/keys that aren't updated.

Published Tuesday, May 11, 2010 7:51 AM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Misha

I remember you question at the seminar. I read Craig Freedman article that perhaps shared some lights on this behaviour

http://blogs.msdn.com/craigfr/archive/2007/05/31/read-committed-and-large-objects.aspx

"SQL Server is overly conservative and retains the locks even though they are technically unnecessary."

May 11, 2010 1:50 AM
 

Uri Dimant said:

Hi Misha

I remember you question at the seminar. I read Craig Freedman article that perhaps shared some lights on this behaviour

http://blogs.msdn.com/craigfr/archive/2007/05/31/read-committed-and-large-objects.aspx

"SQL Server is overly conservative and retains the locks even though they are technically unnecessary."

May 11, 2010 1:50 AM
 

Michael Coles: Sergeant SQL said:

T-SQL Tuesday this month was all about LOB (large object) data. Thanks to all the great bloggers out

May 13, 2010 7:51 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement