Explain locking behavior in SQL Server

Why is that, with default settings on Sql Server (so transaction isolation level = read committed), that this test:

CREATE TABLE test2 ( ID bigint, name varchar(20) )

then run this in one SSMS tab:

begin transaction SH insert into test2(ID,name) values(1,'11') waitfor delay '00:00:30' commit transaction SH

and this one simultaneously in another tab:

select * from test2

requires the 2nd select to wait for the first to complete before returning??

We also tried these for the 2nd query:

select * from test2 NOLOCK WHERE ID = 1

and tried inserting one ID in the first query and selecting a different ID in the second.

Is this the result of page locking? When running the 2 queries, i've also ran this:

select object_name(P.object_id) as TableName, resource_type, resource_description from sys.dm_tran_locks L join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

and gotten this result set:

test2 RID 1:12186:5
test2 RID 1:12186:5
test2 PAGE 1:12186
test2 PAGE 1:12186

-------------Problems Reply------------

requires the 2nd select to wait for the first to complete before returning??

read commited prevents dirty reads and by blocking you will get a consistent result, snapshot isolation gets around this but you will get slightly worse performance because now sql server will hold the old values for the duration of the transaction (better have your tempdb on a good drive)

BTW, try changing the query from

select * from test2

to

select * from test2 where id <> 1

assuming you have more than 1 row in the table and it will be over a page, insert a couple of thousand rows

List traversal with node locking is done by 'crabbing':

  • you have a lock current node
  • you grab a lock next node
  • you make the next node current
  • you release the lock on the previous node (former current)

This techniques is common in all list traversal algorithms and is meant to keep stability while traversing: you are never making a 'leap' w/o having yourself anchored in a lock. It is often compared to the techniques used by rock climbers.

A statement like SELECT ... FROM table; is a scan over the entire table. As such, it can be compared with a list traversal, and the thread doing the table scan traversal will 'crabb' ver the rows just like one doing a list traversal will crabb over the nodes. Such list traversal is guaranteed that it will attempt to lock, eventually, every single node in the list, and a table scan will similarly attempt to lock, at one time or another, every single row in the table. So any conflicting lock held by another transaction on row will block the scan, 100% guaranteed. Everything else you observe (page locks, intent locks etc) is implementation details, irrelevant to the fundamental issue.

The proper solution to this problem is to optimize the queries that they don't scan tables end-to-end. Only after that is achieved you can turn your focus to eliminate whatever contention is left: deploy snapshot isolation based row-level versionning. In other words, enable read-committed snapshot on the database.

Category:sql server Views:1 Time:2010-03-06

Related post

  • Lock Sequences In SQL Server 2009-09-07

    Does anyone know of a resource that will tell me the sequence of locks that will be taken out on a table/page/row/index during a select/insert/update/delete in SQL Server 2005 AND how different table hints and isolation levels will impact the locks t

  • How do you lock tables in SQL Server 2005, and should I even do it? 2008-09-11

    This one will take some explaining. What I've done is create a specific custom message queue in SQL Server 2005. I have a table with messages that contain timestamps for both acknowledgment and completion. The stored procedure that callers execute to

  • How to change locking strategy in SQL Server? 2009-08-31

    I've read articles like these: http://www.codinghorror.com/blog/archives/001166.html http://www.databasejournal.com/features/mssql/article.php/3566746/Controlling-Transactions-and-Locks-Part-5-SQL-2005-Snapshots.htm And from what I understand, SQL Se

  • The endless debate: atomic inserts, locking, & transactions in SQL Server. It ends here 2010-12-11

    Edit: My question is, "Why does my first code example work?" Please read on... Edit1: There is no doubt that a unique constraint is the correct way to ensure duplicates don't happen. This is a given. However, sometime we need to know that we're attem

  • what is the equivalent of EXPLAIN form SQLite in SQL Server? 2011-02-02

    I used an SQLite database and run an EXPLAIN statement before executing the actual query to verify if there was any attempt to write on the database. Now, we have migrated to SQL Server and I need to know if a query tries to write on the database or

  • Change default nolock behavior of SQL Server CE 2012-02-14

    Is there a way to change the default behavior of selects in SQL Server CE, with nolock in selects, without having to explicitly ask for a lock in the query? I don't want nolock per default, and I don't want to explicitly set with(rowlock).

  • MS Access Locks Tables in SQL Server? 2012-04-23

    I've been using SQL Server for 2+ years but I am new to MS Access. I have just started to look at MS Access as a potential front-end/portal to access SQL Server and I was talking with a colleague who said I should be wary of the potential for MS Acce

  • Find Locked Table in SQL Server 2009-03-01

    How can we find which table is locked in the database? Please, suggest. --------------Solutions------------- You can use sp_lock (and sp_lock2), but in SQL Server 2005 onwards this is being deprecated in favour of querying sys.dm_tran_locks: select o

  • how to find who locks who in SQL Server 2005 2009-06-16

    I am working on SQL Server 2005 SP3. From Activity monitor, I notice a lot of processors were locked in suspended mode. do you know how to find who locks my processes? --------------Solutions------------- exec sp_who will tell you what processes are

  • Why would this SELECT statement lock up on SQL Server? 2009-11-18

    I have a simple query like this SELECT * FROM MY_TABLE; When I run it, SQL Server Management Studio hangs. Other tables and views are working fine. What can cause this? I've had locks while running UPDATE statements before, and I know how to approach

  • Getting account 'locked' status in SQL Server 2010-04-09

    I want to unlock one account in SQL Server. Before unlocking I have to check whether that account is locked or not. I want to unlock only if the account is locked. Is there any SQL query or stored procedure to get the "Locked" status of SQL user? ---

  • Anybody seen this behavior with Sql Server Reporting Services, a 64bit OS and an Oracle datasource? 2010-05-20

    I'm working on a Sql Server Reporting Services solution that queries across both a Sql Server data source and an Oracle 10g data source. My dev box is Windows 7 64bit with Sql Server 2008R2 and I'm hosting IIS7 and SSRS on that system for development

  • Default table lock hint on SQL Server 2005/2008 2010-10-06

    How do you look up a default global table locking hint? -- Questions Are there any DMV/DMF (Dynamic Management View/Function) that return such information? And also, is there a way to change the default lock hint? Currently I am adding nolock hint al

  • web service timing out due to locks in database SQL Server 2008 2011-07-08

    We have a web service that times out periodically. It looks like there is a blocker/waiter happening at the same time on the db side. It also appears that the application pool fills up at the same time. We would like to monitor the lead up to the out

  • Handle Lock Manually in SQL Server? 2011-07-26

    I am new to SQL Server, but am having a fair knowledge of simple things like select/update/delete and other transaction. I am facing a dead lock scenario in my application. I have understood the scenario as many threads are parallel trying to run a s

  • Find what rows are locked for a given table and who is locking them in SQL Server 2011-09-15

    Is there a way to find out what rows are locked for a specific table in SQL Server 2008? I'd also like to know the user who is locking them. --------------Solutions------------- sys.dm_tran_locks, as already said in 694581. To identity which rows are

  • Strange behavior of sql server 2009-04-16

    I have following table structure in my DB ID Name -------------------------- ID_1 Name1 ID1 Name2 when I execute the following query SELECT * FROM tblNames WHERE ID LIKE '_1' I get both records selected... any suggestions how to get rid off it?? ----

  • Tracing or Logging Resource Governor classification function behavior in Sql Server 2008 2010-01-05

    I'm trying to use the Resource Governor in SQL Server 2008 but I find it hard to debug the classification function and figure out what the input variables will have, i.e. does SUSER_NAME() contain the domain name? What does the APP_NAME() string look

  • DateTime Behavior in SQL Server & C# 2010-12-12

    I am pulling data from an XML feed via C# code. When I get the data, there is a datetime in the following format: <a:updated>2010-11-05T20:21:43.8303793Z</a:updated> I read that into variable of DateTime type. I then (using EF) to put tha

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.155 (s). 11 q(s)