Cause of a lock being placed on the database

Question

Cause of a lock being placed on the database

Bill Kline-270970

SSC Eights!

Points: 915
More actions
April 2, 2010 at 1:55 pm

#217376

SQL Server 2005 SP3 x64
We have a third-pary application and the users are complaining about performance. The perfmon metrics look fine. No memory, CPU or IO pressure.
When I look at process activity, I see a lot of processes being blocked because another process has placed a lock on a resource.
To get a high level view, I ran the following query:
create table #Temp_Locks (spid int, [dbid] int, [Objid] int, IndID int, [type] varchar(30), Resource varchar(500), Mode varchar(50), [Status] varchar(50))
insert into #Temp_Locks
exec sp_lock
select [dbid], [type], count(*)
from #Temp_Locks
group by [dbid], [type]
order by count(*) desc
drop table #Temp_Locks
The results are as follows:
7DB148
7APP20
8DB4
4DB3
7TAB2
15DB1
1TAB1
2TAB1
Database ID = 7 represents a user database that the application populates. I was suprised to see so many Database locks. I've reviewed BOL and can't find an explanation of the events that cause a lock to be placed on a database.
Can someone enlighten me?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

If I remember this correctly, lots of changes to things like schemas result in database locks. The idea is that you don't want the database to be changed, in terms of some options/settings, while this are in process. As long as they're shared locks, I think it's fine.

If you have performance issues, I would look at more granular locks, not the db ones.

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 2

I'm sure you're correct. I ran the following query -

SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode

FROM sys.dm_tran_locks

order by resource_type

All the database locks have a request_owner_type of "SHARED_TRANSACTION_WORKSPACE" and a request_mode of "S" (shared). So, I don't think database locks are the cause of our performance issues.

Interestingly, the above query shows a number of Application locks, and all of those consistently have a request_status of "WAIT".

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

The database lock prevents a DBA from detaching or dropping the database while it's being used. If you've ever attempted to detach a database and couldn't because it's "In use" and you can't see who is using it in sysprocesses or a DMV, you can look for the SPIDS that have a database lock using sp_lock and kill them. That will allow you to then detach / drop the database. I've seen this many times when a query is executed from one database and joins to objects in another database.

Paul White SSC Guru Points: 150467 More actions · Answer 4

Bill Kline-270970 (4/2/2010)
All the database locks have a request_owner_type of "SHARED_TRANSACTION_WORKSPACE" and a request_mode of "S" (shared). So, I don't think database locks are the cause of our performance issues.

They're not. Shared database locks are taken by every session that connects to a database. This is how SQL Server keeps track of whether anyone is using a database or not. Ignore there locks.

Interestingly, the above query shows a number of Application locks, and all of those consistently have a request_status of "WAIT".

When you say Application locks, do you mean locks acquired through sp_getapplock?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 5

Thanks for the responses, George and Paul.

Paul, the query to sys.dm_tran_locks returns a resource_type of "APPLICATION" for the spids that are waiting. The actual sql stmt being executed by these spids is "xp_userlock". Research tells me that sp_getapplock is a wrapper for xp_userlock. There are 4 procs in the database that make calls to sp_getapplock.

I guess a profiler trace is in order to verify if these procedures are the source of the waits due to locks. I will also research sp_getapplock further to determine if there can be a performance impact.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Bill Kline-270970 (4/2/2010)
We have a third-pary application and the users are complaining about performance.
...
Can someone enlighten me?

Yes... it seems that everyone approaches the problem of 3rd party code incorrectly... they try to fix it themselves. Get that bloody vendor off his haunches and make the vendor fix it! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 7

Bill Kline-270970 (4/5/2010)
I guess a profiler trace is in order to verify if these procedures are the source of the waits due to locks. I will also research sp_getapplock further to determine if there can be a performance impact.

Application locks are certainly slower to acquire than standard locks, but that is almost certainly not the problem. If you are seeing long waits on custom locks, it is a consequence of the application design.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 8

Jeff Moden (4/5/2010)
Yes... it seems that everyone approaches the problem of 3rd party code incorrectly... they try to fix it themselves. Get that bloody vendor off his haunches and make the vendor fix it! 😉

Speaking as one of those bloody vendors, checking with them can save a lot of time if they've already seen this issue. Even if they haven't heard of it before they'll have more application knowledge to should be find out what's going on more quickly. Now, if this is their DB but you've written custom code to run against it that's another matter. We usually find that using sp_who2 to track down the blocking SPID and dbcc inputbuffer goes a long way towards finding the cause.

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 9

Many thanks to everyone for their comments.

Please see my follow up post "SP_GetApplock - Understanding what is being locked". I did provide the information to my internal customers who have in turn forwarded to the vendor.

No attempts for fix 3rd party code on my part! 🙂