Deadlocks workaround?

Hi All,

I have read about deadlocks here on Google and I was surprised to read
that an update and a select on the same table could get into a
deadlock because of the table's index. The update and the select
access the index in opposite orders, thereby causing the deadlock.
This sounds to me as a bug in SQL Server!

My question is: Could you avoid this by reading the table with a
'select * from X(updlock)' before updating it? I mean: Would this
result in the update transaction setting a lock on the index rows
before accessing the data rows?

Merry Christmas!
/Fredrik Mllerlouis nguyen (louisducnguyen@.hotmail.com) writes:
> In the example you posted, I typically use a "set transaction" option.
> My understanding is that this would prevent all shared locks. What
> is your opinion (pros/cons) of this? Thanks, Louis.
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl
> INSERT tbl (id, ...) VALUES (@.id, ...)
> COMMIT TRANSACTION

This is very likely to cause deadlocks. The isolation level does not
affect the ability to get shared locks. It only affects what you can
see if you issue the same statement later in the query.

Try this:

CREATE TABLE tbl (id int NOT NULL)
go
DECLARE @.id int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl
WAITFOR DELAY '00:00:10'
INSERT tbl (id) VALUES (@.id)
COMMIT TRANSACTION

First create the table, then run the batch from two windows. You will
get a deadlock. Add "WITH (UPDLOCK)" after the table, and both
batches will succeed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Wow. I learned my lesson. Thanks, Louis.|||Hi Erland and Louis,
The example you provided was very enlightening in showing the
difference between UPDLOCK and HOLDLOCK/SERIALIZABLE. Thank you!
As to the locking of index and data rows I might come back later with
an example illustrating the problem.
Regards
Fredriksql

deadlocks not resolved by SQL Server

I am under the impression that SQL Server would automatically detect a
deadlock and roll back a participating transaction. However, I have had a
few cases where automatic resolution did not occur. Instead, I had to go in
and manually kill the transaction that is causing a deadlock.
Is there anyway to avoid this? I don't want to have to manually kill a
transaction to unwind a deadlock if that's at all possible.
Thanks in advanceHi
It sounds like you have prolonged blocking rather than a deadlock, in which
case your query should timeout. Make sure that your application has
overridden the default timeouts and requested to wait indefinitely.
You should also investigate if you application has long running transactions
that have not been correctly committed/rolled back or if poor indexing is
affecting performance.
John
"C.W." wrote:

> I am under the impression that SQL Server would automatically detect a
> deadlock and roll back a participating transaction. However, I have had a
> few cases where automatic resolution did not occur. Instead, I had to go i
n
> and manually kill the transaction that is causing a deadlock.
> Is there anyway to avoid this? I don't want to have to manually kill a
> transaction to unwind a deadlock if that's at all possible.
> Thanks in advance
>
>

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.

deadlock and error code returned

The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ? be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ? --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNum
BOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:

> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ? be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ? --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>
|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-536X45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
> - Show quoted text -
thankyou

deadlock and error code returned

The question is: if the below sproc execution transaction becomes a
deadlock victim, would it give a error? If yes, what kind of error
would it give.
In the below example, what should ' be?
/*
Sample Call:
declare @.ret int
@.ret = exec usp_testSproc
if @.ret = ' --
Print "The sproc is a deadlock victim"
*/
create proc usp_testSproc
as
declare @.errNum int
set @.errNum = 0
select name, dept
from department
where name ="xyz"
set @.errNum = @.@.ERROR
if @.errNum <> 0
print cast(@.errNum as varchar(10) + ": Error occured"
return @.errNumBOL Ref:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5364
5181bc40.htm
Error Number is 1205.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"schal" wrote:

> The question is: if the below sproc execution transaction becomes a
> deadlock victim, would it give a error? If yes, what kind of error
> would it give.
> In the below example, what should ' be?
> /*
> Sample Call:
> declare @.ret int
> @.ret = exec usp_testSproc
> if @.ret = ' --
> Print "The sproc is a deadlock victim"
> */
>
> create proc usp_testSproc
> as
> declare @.errNum int
> set @.errNum = 0
> select name, dept
> from department
> where name ="xyz"
> set @.errNum = @.@.ERROR
> if @.errNum <> 0
> print cast(@.errNum as varchar(10) + ": Error occured"
> return @.errNum
>|||On Jul 13, 3:18 pm, Mohit K. Gupta <mohitkgu...@.msn.com> wrote:
> BOL Ref:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-5=
36=AD45181bc40.htm
> Error Number is 1205.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "schal" wrote:
>
>
>
>
>
>
>
> - Show quoted text -
thankyou