2

I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes are blocked, I assume this happens because the awaiting command involves a lock on a table that the rest of processes are trying to update). But this is not the case actually.

In SQL Activity => Locks I've noticed that there's plenty of locks in S mode on DATABASE (Lock Type), would anyone explain me what that actually means, is it harmful for the database and what may be the cause of it?

I've used sys.dm_tran_locks and what I'm receiving are locks on resource_type DATABASE. This is basically what I'd like to get insight on. I don't understand what that means.

Improve this question
1
  • We list all known (to us) wait types in the Waitopedia, although I can't find AWAITING_COMMAND in there nor on MSDN. Which version are you using? You would gain most from also checking against the workload analysis
    – Sebastian Godelet
    Commented Oct 10, 2016 at 14:08

2 Answers 2

Reset to default
2

Shared database locks are normal, simply meaning a connection is open in that database context. That lock prevents another session from performing an operation like dropping the database while it's being used.

A session AWAITING_COMMAND that is blocking other sessions is an indicator the session still has an open transaction holding a lock on a resource (probably not database) that is incompatible with the other session's requested lock type. That resource is typically a table or row but you mentioned that's not the case here. You can query sys.dm_tran_locks to identify granted and waiting locks on resources for each session. I expect Spotlight would use this or similar a DMV.

Improve this answer
1

some reasons can be app waiting for user inputs,time outs with out proper exceptional handling.

you can easily simulate this by

Begin tran Update authors …. Waitfor delay ’10:00:00’ --- time out will occur here (simulates long workload) rollback

For second question on Slocks,These mean shared locks which are taken by database engine for any select statements.

Good place to know more on locking would be https://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

References: Link

Sleeping SPID blocking other transactions

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.