Showing posts with label locking. Show all posts
Showing posts with label locking. Show all posts

Friday, March 30, 2012

Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?


I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Tuesday, March 20, 2012

Problem with ghost locking...

Hi

I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.

After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it...
When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").

Any ideas ?
I'm using SQL-Server y2k + sp3.

Thanks,
Inon.Mode S means that the 2 processes where doing SELECT with default TIL (READ UNCOMMITTED) while you were trying to UPDATE the table (maybe accidentally you hit a key while browsing the table in EM?) UPDATE is not allowed on a table that has shared lock placed on it.

Problem with Ghost Cleaner...

I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner?
Thanks in advance..Sorry, never heard of 'Ghost Cleaner'.|||As I understand it, when you run a query, the record on disk is simply flagged as deleted, and the Ghost Cleanup process does the actual deletes. If you are already on the latest service pack, then you would want to look at how often your data is deleted/refreshed or updated.

EDIT: Oh, as such, the Ghost Celaner can not be disabled, or else your database would simply bloat out of proportion.|||We actually had a great thread on the subject of "ghost records" and the cleanup process a few years ago... We had a number of posts from Paul Randall, the GPM responsible for the Microsoft SQL Server physical storage engine that covered the topic in some detail. Unfortunately that was a bit of DBForums history that was lost in a server melt-down.

The actual thread is called the "Ghost Exorcisor" and it runs as a system task (higher CPU priority than a user task) according to rules that are fairly simple, but beyond user control. There is a trace flag that you can set to prevent the Ghost Exorciser from running, but this is a VERY bad idea in most cases.

If you want to pursue this issue, I would suggest that you contact Microsoft Professional Support Services. They will want a bunch of information from you, will probably not charge you for the call, and will probably have a lot of good ideas that will help you more than just turning the exorciser off.

-PatP