Deadlocks with read
-
I have this very strange situation where I am getting deadlocks when I try and read from the database.
i.e. so the process being killed is something simple like "select blah blah blah from BlahTable where id = @k"
Now I wouldn't have thought that this would would try an obtain a lock that could cause a deadlock.
There are a lot of users on the system so it is very difficult to know what the successfull process was doing at the time of deadlock but I'm wondering if there is something wrong with the databse that this is happening.
Any thoughts appreciated.
-
try BOL, search for "deadlocks, troubleshooting". This may help to trace where the deadlock is occuring.
HTH Andy
-
At least on SQL 7.0, not sure about 2000 or 2005, you can go to managment, current activity, locks / process ID and see who was locking and if you double click the one that it says is blocking you can see the SQL that it is processing. You can also kill that process from there.
Hope that helps.
-
Hello Dale,
This is something that also happened to us: Having a simple "firehose" cursor involved in a deadlock (doing nothing else on the connection and not being in a transaction).
When it happened to us (about 2 years ago) I could not understand why.
But after having read the Kalen Delaney document "Hands-On SQL Server 2000 Troubleshooting : Locking and blocking" from NetImpress, it becomes clearer.
I strongly suggest you to read it.
N.B. In SQL Server 2005 if you use "Row Versionning" with "Read commited" or "Snapshot" isolation level there will be no more read locks... thus no more deadlock possible on reading data.
Best regards,
Carl
-
Dale,
You likely have another process (or two) that is performing an insert, update, or delete on the resources of the data you are requesting. sp_who2 is a helpful system stored proc to identify the locking process.
Here is a script that expands on sp_who2 to also display the last batch each process last executed:
/******************************************************
** Expansion on sp_who2 to also display the
** last batch each process executed for the
** requested database
******************************************************/
USE master
DECLARE @SPID int,
@dbname varchar(255)
SET @dbname = 'database' /* database name */
CREATE TABLE #status (
SPID int,
Status varchar(255),
Login varchar(255),
HostName varchar(255),
BlkBy varchar(20),
DBName varchar(255),
Command varchar(255),
CPUTime int,
DiskIO int,
LastBatch varchar(255),
ProgramName varchar(255),
SPID1 int
)
INSERT INTO #status
EXEC sp_who2
ALTER TABLE #status ADD LastBatchStatement varchar(255)
CREATE TABLE #inputbuffer (
EventType varchar(30),
Parameters int,
EventInfo varchar(255)
)
SET @SPID = 0
WHILE EXISTS (
SELECT TOP 1 SPID
FROM #status
WHERE SPID > @SPID
AND Login <> 'sa'
AND DBName = @dbname
)
BEGIN
SELECT @SPID = MIN(SPID)
FROM #status
WHERE SPID > @SPID
AND Login <> 'sa'
AND DBName = @dbname
INSERT INTO #inputbuffer
EXEC('DBCC INPUTBUFFER(' + @SPID + ')')
UPDATE s
SET LastBatchStatement = ib.EventInfo
FROM #status s,
#inputbuffer ib
WHERE s.SPID = @SPID
TRUNCATE TABLE #inputbuffer
END
SELECT *
FROM #status
WHERE Login <> 'sa'
AND DBName = @dbname
DROP TABLE #status
DROP TABLE #inputbuffer
GO
The following script may be useful in identifying specific resources being locked:
/******************************************************
** Returns the locks occuring in a database
** (including indexes)
******************************************************/
USE master
DECLARE @dbid int
SET @dbid = 0 /* dbid in master.dbo.sysdatabases for the database */
CREATE TABLE #lock (
spid int,
dbid int,
ObjId int,
IndId int,
Type varchar(20),
Resource varchar(255),
Mode varchar(20),
Status varchar(255)
)
INSERT INTO #lock
EXEC sp_lock
SELECT *
FROM #lock
WHERE dbid = @dbid
DROP TABLE #lock
GO
I hope these scripts are helpful to you in troubleshooting. Enjoy...
-
Thanks for all the responses:
try BOL, search for "deadlocks, troubleshooting". This may help to trace where the deadlock is occuring.
Having read everything I could find regarding deadlocks in the BOL I could still not see how this could occur.
At least on SQL 7.0, not sure about 2000 or 2005, you can go to managment, current activity, locks / process ID and see who was locking and if you double click the one that it says is blocking you can see the SQL that it is processing. You can also kill that process from there.
This is an extremely busy system with many users, deadlocks only happen when it is fairly busy so not really possible to look at the current locks.
You likely have another process (or two) that is performing an insert, update, or delete on the resources of the data you are requesting. sp_who2 is a helpful system stored proc to identify the locking process.
I am fairly certain that no insert, update, or delete is happening on the row in question, therefore I think that there must be a table lock happening?
Given what I was saying above about how busy the system is, and that I am only notified about a deadlock after the event is there any way I can use sp_who2 to determine what is happening?
But after having read the Kalen Delaney document "Hands-On SQL Server 2000 Troubleshooting : Locking and blocking" from NetImpress, it becomes clearer.
That sounds great.
-
But after having read the Kalen Delaney document "Hands-On SQL Server 2000 Troubleshooting : Locking and blocking" from NetImpress, it becomes clearer.
Except that Amazon will not allow this book to be sold to people in the UK
-
Hi Carl,
Thanks for the info, I managed to get hold of a copy of the book and read it this morning.
Can you say a bit more about what was causing the problem in your case?
It is something to do with the combination of cursors and triggers that is causing us the problems - but its proving difficult to track down.
Dale
-
Hi Dale,
To be honest when this situation arise we were having a lot of other troubles involving LOCKs, DEADLOCKs, application bugs, ... it was in the summer of 2002.
We were never able to reproduce this particular situation at will.
All I remember is that, having only a "firehose" cursor, The application was invloved in (choosen as the victim of) deadlock. So we modified the application to retry the read or the transaction if we were in it.
The first time I read the document (twice), I identified 2 or 3 possible situations but I don't remember exactly which ones.
I should read it again.
Best regards,
Carl
-
I investigated the query further that was getting killed by the deadlock, and this query only uses the following locks:
S
IS
Sch-S
Now I thought that a deadlock occured when 2 proceses end up waiting on each other (directly or indirectly), but I cannot understand how a process that has only S, IS & Sch-S locks could even participate in a deadlock?
For example if someone else was updating the table then this process might get blocked, but I cannot see how it would ever get deadlocked?
Any ideas?
-
What isolation level is it running in?
Here is an example to deadlock a reading-only process.
-- Run this in first query anylyzer window:
BEGIN TRAN
UPDATE sometable SET something = whatever
-- Now open a second window and run this:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM othertable
-- Back in the first window now run this:
UPDATE othertable SET somethingelse = whatever
-- In the second window, run this, which will produce a deadlock:
SELECT * FROM sometable
-
Also, maybe lock escalation is involved... not sure.
Carl
-
What's that??
-
Thanks Chris... that is very helpful... Now I know they are possible
I still can't see how I would be getting that situation, because I don't use transactions on the reads, let alone transactions using ISOLATION LEVEL REPEATABLE READ
In fact the only thing I am doing is sub-queries within the main query
i.e. select x, (select y from table2) from table1
So I do wonder what the implicit transactions look like when using a sub-query such as this - would it be using an implicit transaction with REPEATABLE READ?
Unless ADO sets the level to REPEATABLE READ - hmmm something to investigate.
Thanks again,
-
From BOL:
Lock Escalation
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.
For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.
SQL Server may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data. This reduces the likelihood that lock escalation will be necessary.
Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.
Best regards,
Carl
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply