WAITFOR DELAY causes a lock

Question

WAITFOR DELAY causes a lock

facemann

Right there with Babe

Points: 797
More actions
May 17, 2013 at 10:57 am

#146057

Not an issue, but I found this curious. If I run the code:
USE AdventureWorks
GO
WAITFOR DELAY '00:00:15'
Then in a 2nd window run
EXEC sp_WhoIsActive
@get_locks = 1,
@output_column_list = '[sql_text][sql_command][locks][%]'
It shows a lock?!?
The lock info is as follows
<Database name="AdventureWorks">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
</Database>
I also tried updating the AdventureWorks.Production.Product table during the lock and it updated immediately with no issues. I don't understand:
1. Why is a lock is issued on a WAITFOR command?
2. How can I update even while there is a lock in place?
Just curious.
Thanks. 🙂

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's a shared database lock. Each and every running query, no matter what it's running, takes a shared database lock to ensure that the database can't be dropped while the query's running.

You can run updates with that lock there because the lock is not on a table, page or row, it's just a shared database 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

facemann Right there with Babe Points: 797 More actions · Answer 2

Ohhhh,

Well that makes sense. It had me seriously scratching my head. Thanks for the reply. 😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

btw, the WAITFOR is not necessary to get that lock. USE ADVENTUREWORKS is sufficient.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

facemann Right there with Babe Points: 797 More actions · Answer 4

I don't 'see' a lock when I USE ADVENTUREWORKS.

Is the lock only present while making the context switch?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

If you don't see it, then there's something in sp_whoisactive that's filtering out those locks. Probably it's only showing sessions that are running, not ones that are sleeping and holding locks.

The lock is there from the time you connect to the database til the time you close the connection or change database context.

Trivial to show too.

Run in one window (and note the session_id)

USE AdventureWorks

Run in another window

SELECT resource_type ,

resource_database_id ,

request_mode ,

request_type ,

request_status ,

request_session_id ,

request_owner_type

FROM sys.dm_tran_locks

WHERE request_session_id = <session id from the first window>

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

facemann Right there with Babe Points: 797 More actions · Answer 6

You are 100% correct. The lock showed up just like in your image. Very interesting. Thanks for taking the time to teach me something. 😎