March 17, 2003 at 12:28 pm
I have a single message recorded today: Your transaction (process ID #54) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
This is the first and only time that this has occured, however more people are using our system so I anticipate that this wont be the last and it could get worse!
I have identified the SQL that was terminated as;
SELECT Count(*) AS RecordCount FROM vw_CallList3 WHERE UPPER(PrimeNumber) LIKE '%2676%' AND RegionID = 5 AND (CallStatusID = 0 OR CallStatusID = 1 OR CallStatusID = 2)
This is used from a VB6 application using ADO. The database is SQL Server 7.
vw_CallList3 does a number of joins so that I can let users create complex searches.
Not being a very experienced SQL programmer I am a bit lost as to what to do next.
Can anyone suggest strategies that I can use to;
(a) avoid this deadlock again.
(b) identify the other process that 'won' the deadlock war...
(c) the best way of dealing with the situation if it occurs
--
regards,
Paul Durdin
Taradata Ltd
March 17, 2003 at 1:32 pm
Here are a few points.
1. To find a process that blocked the given one you can run
sp_who2 Active
It will show the blocking process id in the BlkBy column. However it won't help because by the time you got the message the lock was already released.
2. Deadlocks happen when there are two or more processes that need the same resources and block each other. Look at this situation:
-- Process 1:
BEGIN TRANSDACTION
-- update table A
-- update table B
COMMIT TRANSACTION
-- Process 2:
BEGIN TRANSDACTION
-- update table B
-- update table A
COMMIT TRANSACTION
If there two processes happen to run in the same time exactly, they will block each other resulting in a deadlock.
3. I am not sure how you get a deadlock with a SELECT only. Perhaps, your SELECT is a part of a larger transaction.
4. Your SELECT looks big and overweight. View with joins, comprehensive WHERE clause, etc. There is no way to use any kind of indexes there. Just hope the underlying tables are very small...
Michael
March 25, 2003 at 2:47 am
Hey,
If deadlocks increase, you can run a trace and specifically look for deadlocks. You can then identify the spid that was rolledback and also the spid that completed it's transaction. Note, that sql server will rollback the transaction that causes less work!
A couple of things to bear in mind...
1) Keep your transaction batches short.
2) Access your Objects in the same order
Clive Strong
March 25, 2003 at 5:05 am
One thing to bear in mind when accessing databases using ADO is the cursortype.
Depending on the type, ADO will lock the complete resultset, only a part of the set or nothing at all. Just check the cursortype of your RecordSet object.
Best value is 'adOpenForwardOnly' (default) or 'adOpenStatic'.
March 25, 2003 at 8:21 am
Also to help you identify deadlocks, sometimes you know one of the sides often
deadlock, but unless you see which spid is deadlocked against you, and are able
to immediately run "DBCC INPUTBUFFER(spid)" against that spid then finding out
what the other SQL in contention that was running is often very hard. I enable
trace flag 1204 in my development environment so that extended deadlock
information is written to the error log. See :
http://www.sqlservercentral.com/columnists/RDyess/traceflags.asp
Also search these forums for 1204 or deadlock.
Tim C.
//Will write code for food
Tim C //Will code for food
March 25, 2003 at 11:19 am
Another thing you can do from ADO, if you don't mind doing dirty reads is set
the IsolationLevel on your connection object to adXactReadUncommitted, then call
BeginTrans on your object. The IsolationLevel works the same as the T-SQL "SET
TRANSACTION ISOLATION LEVEL". I recommend this technique rarely though.
Tim C.
//Will write code for food
Tim C //Will code for food
March 26, 2003 at 2:08 am
quote:
Another thing you can do from ADO, if you don't mind doing dirty reads is setthe IsolationLevel on your connection object to adXactReadUncommitted, then call
BeginTrans on your object. The IsolationLevel works the same as the T-SQL "SET
TRANSACTION ISOLATION LEVEL". I recommend this technique rarely though.
Alternatively, use the SQL Optimizer hint "NOLOCK" in the select statements that build the view. For example,
SELECT * FROM BusyTable
will potentially be subject to locking issues, whereas
SELECT * FROM BusyTable (NOLOCK)
will avoid this problem.
However, you may end up with inconsistent data, as the SELECT statement bypasses any transactions that are in progress. We use this a lot.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 27, 2003 at 2:31 am
Chances are there is something that needs to do a exclusive lock on the tables referenced by vw_CallList3 and someone or something has accessed your SQL statement.
Make a list of the tables in your view then start looking for places that update or delete data from those tables
I would also refine your SQL Statement.
SELECT Count(1) AS RecordCount FROM vw_CallList3 WHERE PrimeNumber LIKE '%2676%' AND RegionID = 5 AND CallStatusID BETWEEN 0 AND 2
Anything that refines a query is good news.
March 27, 2003 at 9:42 am
Use stored procedures instead of embedding Transact-SQL in your VB code. This significantly reduces network traffic and speeds up query execution
See this topic:
http://www.sql-server-performance.com/visual_basic_performance.asp
Darren
March 27, 2003 at 11:54 pm
I have done some reading and it appears that a SELECT can cause a deadlock because it needs to lock both the table and the indexes before it reads data, and it you are unlucky enough to have another process that it locking them in the reverse order... then deadlock
March 27, 2003 at 11:58 pm
It was a select that was being thrown out, although it is a horrible query (lots of joins).
quote:
Hey,If deadlocks increase, you can run a trace and specifically look for deadlocks. You can then identify the spid that was rolledback and also the spid that completed it's transaction. Note, that sql server will rollback the transaction that causes less work!
A couple of things to bear in mind...
1) Keep your transaction batches short.
2) Access your Objects in the same order
Clive Strong
March 28, 2003 at 12:00 am
Tne query is run as OpenStatic for this reason.
quote:
One thing to bear in mind when accessing databases using ADO is the cursortype.Depending on the type, ADO will lock the complete resultset, only a part of the set or nothing at all. Just check the cursortype of your RecordSet object.
Best value is 'adOpenForwardOnly' (default) or 'adOpenStatic'.
March 28, 2003 at 12:05 am
Trouble is that by the time i know about this the process has been thrown off. If would be neat to be able to trap the deadlock event itself and log both of the processes.
I did the trace thing on my development system, but of course there's no deadlock's there!
I tried to set a trace on the live system, but the volume of stuff being logged was enormous and i could'nt let it run for any useful period of time.
quote:
Also to help you identify deadlocks, sometimes you know one of the sides oftendeadlock, but unless you see which spid is deadlocked against you, and are able
to immediately run "DBCC INPUTBUFFER(spid)" against that spid then finding out
what the other SQL in contention that was running is often very hard. I enable
trace flag 1204 in my development environment so that extended deadlock
information is written to the error log. See :
http://www.sqlservercentral.com/columnists/RDyess/traceflags.asp
Also search these forums for 1204 or deadlock.
Tim C.
//Will write code for food
March 28, 2003 at 12:09 am
This is what i have not in fact (NOLOCK) - i'll see if any more deadlocks occur in the future...
quote:
quote:
Another thing you can do from ADO, if you don't mind doing dirty reads is setthe IsolationLevel on your connection object to adXactReadUncommitted, then call
BeginTrans on your object. The IsolationLevel works the same as the T-SQL "SET
TRANSACTION ISOLATION LEVEL". I recommend this technique rarely though.
Alternatively, use the SQL Optimizer hint "NOLOCK" in the select statements that build the view. For example,
SELECT * FROM BusyTablewill potentially be subject to locking issues, whereas
SELECT * FROM BusyTable (NOLOCK)will avoid this problem.
However, you may end up with inconsistent data, as the SELECT statement bypasses any transactions that are in progress. We use this a lot.
March 28, 2003 at 12:19 am
The idea of using Count(1) sounds interesting - why is this better?
Also the BETWEEN statement - Is this this faster because it dosnt have to do three descrete checks?
I appreciate the comment about UPPER - trouble is that the user can type anything into the VB front end and a want to ensure that i always get a match. I need to apply some more intelligence to how i build the query i guess (check to see if the query includes consecutive status values, or fields not include alphas).
quote:
Chances are there is something that needs to do a exclusive lock on the tables referenced by vw_CallList3 and someone or something has accessed your SQL statement.Make a list of the tables in your view then start looking for places that update or delete data from those tables
I would also refine your SQL Statement.
- Use Count(1) rather than Count(*)
- Your like statement is looking for numerics therefore eliminate the UPPER statement
- If CallStatusID are discreet integer values then try using a BETWEEN clause
SELECT Count(1) AS RecordCount FROM vw_CallList3 WHERE PrimeNumber LIKE '%2676%' AND RegionID = 5 AND CallStatusID BETWEEN 0 AND 2
Anything that refines a query is good news.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply