May 17, 2013 at 10:57 am
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. 🙂
May 17, 2013 at 11:32 am
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
May 17, 2013 at 11:35 am
Ohhhh,
Well that makes sense. It had me seriously scratching my head. Thanks for the reply. 😎
May 17, 2013 at 11:38 am
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
May 17, 2013 at 11:51 am
I don't 'see' a lock when I USE ADVENTUREWORKS.
Is the lock only present while making the context switch?
May 17, 2013 at 11:57 am
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
May 17, 2013 at 12:29 pm
You are 100% correct. The lock showed up just like in your image. Very interesting. Thanks for taking the time to teach me something. 😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply