deAttach database: process

Showing posts with label process. Show all posts

Thursday, March 29, 2012

Deadlocks and trace 1204

We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec:(0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec:(0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> > We had a deadlock the other day and would like to identify the offending
> > process. Is it too late if there were no traces?
> >
> > If we set trace 1204 to capture future deadlocks, will it write to the sql
> > error log? Against which database do you run the DBCC trace (master or
> > the
> > user database)?
> >
> > Thanks.
> >
> > Ron
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec:(0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec:(0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>> Hi Ron
>> run dbcc traceon (1204, 3605, -1) in any database. You'll then get
>> deadlock
>> graph reports in the sql error log when deadlocks occur.
>> If you have any trouble interpreting them, post the output here & I'm
>> sure
>> you'll get some help..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>> > We had a deadlock the other day and would like to identify the
>> > offending
>> > process. Is it too late if there were no traces?
>> >
>> > If we set trace 1204 to capture future deadlocks, will it write to the
>> > sql
>> > error log? Against which database do you run the DBCC trace (master or
>> > the
>> > user database)?
>> >
>> > Thanks.
>> >
>> > Ron
>>

Tuesday, March 27, 2012

deadlocks

If an instance of SQL 2005 was in use and was using row versioning,
under what circumstances would the below error occur?

Transaction (Process ID 56) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction

We used to get this sort of thing when a large copy process was running
under a transaction, but all it was doing was reading the records and
creating brand new records yet would still lock the entire table. Once
we enabled the row versioning, we stopped having this issue, but it
seems that there are some circumstances in which it still happens, i.e.
the above error.

Any ideas how that might occur?pb648174 (google@.webpaul.net) writes:
> If an instance of SQL 2005 was in use and was using row versioning,
> under what circumstances would the below error occur?
> Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction
> We used to get this sort of thing when a large copy process was running
> under a transaction, but all it was doing was reading the records and
> creating brand new records yet would still lock the entire table. Once
> we enabled the row versioning, we stopped having this issue, but it
> seems that there are some circumstances in which it still happens, i.e.
> the above error.
> Any ideas how that might occur?

Without knowledge of the code, and not have seen the deadlock trace?
Not even knowing which of the two varities of snapshot isolation
you are using. SET TRANSACTION LEVEL SHAPSHOT, or READ COMMITTED
SNAPSHOT?

To get a deadlock trace in the SQL Server error log, enable trace
flags 1222 and 3605. (It used be 1204, but 1222 is a new flag, which
gives better information.)

--
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|||I didn't realize there were multiple kinds.. We are using ALTER
DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

My questions is more of a general one - If row versioning is being used
and a particular record is involved in a transaction, should other
transactions just get the older version and not have to respect any
locks? We are seeing blocking happen for normal read operations, which
seems like it shouldn't happen. A write blocking I could see, but the
read blocking doesn't make sense to me.|||pb648174 (google@.webpaul.net) writes:
> I didn't realize there were multiple kinds.. We are using ALTER
> DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

The other one you achieve with ALTER DATABASE db SET
ALLOW_SNAPSHOT_ISOLATION ON. Transactions what want snapshots, then
need to say SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

The two yields slight different results. Pure shapshot isolation, gives
you the state of the database as it looked when the transaction started.
Read Committed Snapshot Isolation (RCSI) is an alternate implementation
of the read committed isolation level. An RCSI transaction can pick up
data that did not exist when the transaction started, but that committed
before the transaction came about to read it.

> My questions is more of a general one - If row versioning is being used
> and a particular record is involved in a transaction, should other
> transactions just get the older version and not have to respect any
> locks? We are seeing blocking happen for normal read operations, which
> seems like it shouldn't happen. A write blocking I could see, but the
> read blocking doesn't make sense to me.

Without any repro it's difficult to comment things out of the blue. However,
note that if you are using alternate isolation level, either by
SET TRANSACTION ISOLATION LEVEL or by query/table hints, the snapshot is
not involved. For instance, run this in one query window:

CREATE TABLE hubba (a int NOT NULL PRIMARY KEY)
go
INSERT hubba(a) VALUES (12)
go
BEGIN TRANSACTION
go
INSERT hubba(a) VALUES (2)
go

Then in another window run:

SELECT MAX(a), MIN(a) FROM hubba

This returns (12, 12). Now try_

SELECT MAX(a), MIN(a) FROM hubba WITH (REPEATABLEREAD)

This blocks, because the isolation level is no longer READ COMMITTED.

--
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|||Ahhhhh... Now we are getting somewhere. I think other transactions are
set as serializable, so that would explain it. Thanks for the tip.

Deadlocked on the same resource (same index)

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec

0x5A2E5578)
Value:0x52fa6780 Cost

0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)
Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have?) and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Deadlocked on the same resource (same index)

0x7C1615D8)
Value:0x52dd7340 Cost

0x5A2E5578)
Value:0x52fa6780 Cost

0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robertsql

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

deadlocked on lock resources. SQL Server 2000

Hi, i am getting this error when i am running a stored procedure.

Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

i think so it is getting this error becasue it blocking it self at one point in the SP

DECLARE cty_Cursor CURSOR FOR
SELECT Country FROM TB_Country

declare @.cty varchar(2)

OPEN cty_Cursor;
FETCH NEXT FROM cty_Cursor into @.cty;
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC SP_DO_SOMETHING @.cty
FETCH NEXT FROM cty_Cursor into @.cty;
END;
CLOSE cty_Cursor;
DEALLOCATE cty_Cursor;

i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.

how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.

Regards,

Haroon

what happens when you run the stored procedure outside of the cursor?

what's in the stored procedure?

Sunday, March 25, 2012

Deadlock within select?

My SQL Server has kicked out a deadlocked process, which should only be
running a select statement, though there is another select on one of
the tables in the WHERE clause (see code below). Can anyone tell me
whether this is possible, or is it that my system, which is re-using
connections, is trying to complete an earlier statement? I've looked
through the system and think I'm committing all transactions.

The query I'm running (simplified, I don't use daft names like 'table1'
or 'date_col', honest :) is:

SELECT table1.*, table2.*, table3.*
FROM table1, table2, table3
WHERE table2.col1 = table1.col1 AND table3.col1 = table1.col2
AND table1.col3 = 'xyz'
AND (table1.date_col >= '2006-02-01' OR table1.date_col IN
(SELECT date_col FROM table1 WHERE (col4 = 'A' OR col4 = 'B') AND col3
= 'xyz'))
ORDER BY table1.date_col

Thanks

J(jw_guildford@.yahoo.co.uk) writes:
> My SQL Server has kicked out a deadlocked process, which should only be
> running a select statement, though there is another select on one of
> the tables in the WHERE clause (see code below). Can anyone tell me
> whether this is possible, or is it that my system, which is re-using
> connections, is trying to complete an earlier statement? I've looked
> through the system and think I'm committing all transactions.

Presumably, there was an insert/update/delete operation that your SELECT
clashed with.

Have you looked at the deadlock trace?

If you don't have deadlock trace enabled, open Enterprise Manager, and
edit the startup parameters to include "-T 1204 -T 3605", and restart
the server.

--
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|||Thanks Erland, I've added those parameters, I'm afraid I don't have a
way of reproducing it at the moment so I'll just have to wait until it
happens again.

deadlock with replication's update/insert/delete process

We are using SQL 2K with sp4. We are using Push method and both publisher
and distribution db are on the same server.
Recently we have quite a few deadlocks involving a replication's sp such as
sp_MSupd_xxxxxxx and some select statement from our application. We have
deadlocks from time to time but I haven't seen a deadlock caused by sp_MSupd
until now. Is this an indication of something that I need to look into?
Any help is very much appreciated.
Wingman
Sounds like a transaction on the publisher is coming over as a transaction
on the subscriber which you're deadlocking with. Some ideas: you could look
at the order of the transaction on the publisher and check that your
application accesses tables in the same order. You could allow your
application to do dirty reads (not generally recommended but sometimes
useful). You could use SQL Server 2005 and the snapshot isolation level
where this problem goes away. Finally you could control when the
distribution agent runs to avoid the lcash.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communitie...qlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities...lserver.server
Try to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql

deadlock victim

Here is the exception I am getting, any idea why this might be happening:

Exception: ThisMethod is done: let this go: Exception:

Transaction (Process ID 74) was deadlocked on lock |

communication buffer resources with another process and has been chosen as the deadlock victim.

Rerun the transaction.Stack Trace:at

Jim,

Please check the following article for some insight:
http://support.microsoft.com/kb/224453|||

deadlock is a contention by two process on a resource

in a deadlock situtation one is choose as a deadlock victim and are rolled back

and the other rolls forward

you just ave to rerun the deadlock victim

|||

Thanks for the reply.

While users are working in the user interface I am inserting data to the table with another that is back end for the user interface. Although we do insert and update I do not think that user are dealing with the same data row, so I am assuming I should not be receiving deadlock. Does SQL server do RowLock or TableLock. How can I get my insert and update work on the RowLock. Can you give me sample sql statement.

|||sqlserver will escalate the lock (from row/page to table) as it sees

fit. While deadlock is not desired, it's necessary in order to allow

the system to proceed forward. Imagine a case where the system just

sits in blocking/contention. That would not be good for either user,

would it. So, the best you can do is to follow the recommendations in

the KB I've posted.

Here is some info on sqlserver locking:

http://msdn2.microsoft.com/en-us/library/ms190615.aspx

And some example of implementing rowlock (again, sql can escalate the lock if it wants to):

http://groups.google.com/groups?q=sqlserver+oj+rowlock+update

Deadlock transaction

I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?On Jun 22, 8:17 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

Read "Analyzing Deadlocks with SQL Server Profiler" in BOL.

http://sqlserver-tips.blogspot.com/|||Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

"Altman" <baltman@.easy-automation.comwrote in message
news:1182518265.867797.118630@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

>I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
>

|||Oscar Santiesteban (o_santiesteban@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.

--
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|||On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.|||On Jun 26, 10:30 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.

If you are on 2005, consider snapshot isolation.

http://sqlserver-tips.blogspot.com

Thursday, March 22, 2012

Deadlock problem

Can somebody help me.
I spend 2 days on this problem, and i stuck. i`ve got this error:
Transaction (Process ID ***) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction...
There is a trace:
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 1204, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 3605, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON -1, server process ID
(SPID) 57.
2006-06-01 23:37:43.24 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Wait-for graph
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:1
2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 0::
2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:2
2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 3::
2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x93212e40 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:142 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 142 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Wait-for graph
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:1
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 0::
2006-06-01 23:38:00.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:2
2006-06-01 23:38:00.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 3::
2006-06-01 23:38:00.74 spid4 Owner:0x9339d7a0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 130 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:3
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Wait List:
2006-06-01 23:38:00.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)
2006-06-01 23:38:00.74 spid4 Victim Resource Owner:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)(gaploid@.yandex.ru) writes:
> Can somebody help me.
> I spend 2 days on this problem, and i stuck. i`ve got this error:
> Transaction (Process ID ***) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction...
>...
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Wait-for graph
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:1
> 2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 0::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
> UPDATE Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:2
> 2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
> CleanCnt:2 Mode: S Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 3::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
> SELECT Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
> round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
> (sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
> complex, so.New_name FROM New_ServiceOrderProduct sop INNER
> JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
> 2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:55.74 spid4
Without knowing tables, and not seeing the text of the UDPATE statement
is a bit difficult to say for sure.
But there is an UPDATE statement, and the locks involve the clustered
index of two different tables. That would indicate that the UPDATE
includes a cascading foreign key - or a trigger. Or that it's part
of a longer transaction.
As a starting point, post the full text of the SELECT statement,
the full text of the UPDATE statement and the table definitions,
including constraints and indexes for the tables. Also, include
the output of
SELCECT object_name(894678285), object_name(862678171)
I assume that you know which database is database 8.
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|||I solve this problem. in my select statement i have a inner join to
another table, so i simply redisign my select query and make it without
inner join.
thx for your answer.

> Without knowing tables, and not seeing the text of the UDPATE statement
> is a bit difficult to say for sure.
> But there is an UPDATE statement, and the locks involve the clustered
> index of two different tables. That would indicate that the UPDATE
> includes a cascading foreign key - or a trigger. Or that it's part
> of a longer transaction.
> As a starting point, post the full text of the SELECT statement,
> the full text of the UPDATE statement and the table definitions,
> including constraints and indexes for the tables. Also, include
> the output of
> SELCECT object_name(894678285), object_name(862678171)
> I assume that you know which database is database 8.
>
> --
> 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

Wednesday, March 21, 2012

Deadlock on Update using temp table

I sometimes get the following error from an update statement in a
stored procedure:

Transaction (Process ID 62) was deadlocked on thread | communication
buffer resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.

The isolation level is READ UNCOMMITTED and there are no explicit
transactions in the stored procedure. The update statement is as
follows:

UPDATE PL
SET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @.comp
AND PL.PLAN_YEAR = @.year
AND PL.FORECAST_QUARTER = @.qtr
AND PL.VERSION_ID = @.ver
AND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODE
AND PL.BUSINESS_UNIT_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.
It has the values that need to be set in the PL table. The PL table
has a clustered index on 8 columns. The filters (@.comp, @.year, ...)
select 230,088 rows. When the update succeeds it updates 43,692 rows
in about 15 seconds. Why does this sometimes deadlock and other times
succeed? There is nothing else running, so the process is deadlocking
on itself.

Thanks,
FrankHi Frank

I don't have the skills and time to analyse your specific issue, but I
hope this general link could help you:
SQL Server technical bulletin - How to resolve a deadlock:
http://support.microsoft.com/kb/832524/en-us

Cheers
SMF|||Hi

You don't give the version of SQL Server you are running?

In general it is better to create the temporary table separately.

Heavy use of tempdb may be helped by moving it to it's own drive(s) and
using multiple files see
http://support.microsoft.com/defaul...kb;en-us;328551

Also check out http://support.microsoft.com/kb/271509/EN-US/ and
http://support.microsoft.com/kb/224453/EN-US/ on how to identify blocking.

John

<fmatamoros@.yahoo.com> wrote in message
news:1135823370.065365.186970@.g44g2000cwa.googlegr oups.com...
>I sometimes get the following error from an update statement in a
> stored procedure:
> Transaction (Process ID 62) was deadlocked on thread | communication
> buffer resources with another process and has been chosen as the
> deadlock victim. Rerun the transaction.
> The isolation level is READ UNCOMMITTED and there are no explicit
> transactions in the stored procedure. The update statement is as
> follows:
> UPDATE PL
> SET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNT
> FROM #tmpWorkPLPrior
> WHERE PL.COMPANY = @.comp
> AND PL.PLAN_YEAR = @.year
> AND PL.FORECAST_QUARTER = @.qtr
> AND PL.VERSION_ID = @.ver
> AND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNIT
> AND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_ID
> AND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODE
> AND PL.BUSINESS_UNIT_CODE <> 'G7'
> PL rows: 24,342,553
> PL rows - Filtered: 230,088
> #tmpWorkPLPrior rows: 3,641
> Updated rows: 43,692
> The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.
> It has the values that need to be set in the PL table. The PL table
> has a clustered index on 8 columns. The filters (@.comp, @.year, ...)
> select 230,088 rows. When the update succeeds it updates 43,692 rows
> in about 15 seconds. Why does this sometimes deadlock and other times
> succeed? There is nothing else running, so the process is deadlocking
> on itself.
> Thanks,
> Frank|||Hi,

Don't get distracted, the temporary table is not causing your deadlock,
rather the update on PL is.

Its feasible that your connection is locking index pages, data pages that
other connections also have locked before you grab them - you are updating
quite a lot of rows in one go so the transaction will be quite large.

Have you checked the query plan for this UPDATE? Index on some of the
columns in your WHERE clause will help reduce the IO.

READ UNCOMMITTED is redundant on the UPDATE - the locks will be placed
because you are updating the data, you could use READ UNCOMMITTED on ALL
your readers and that would help.

I don't tend to use such large composite keys like this, I would use a
surrogate - 'ID' integer column instead and update by joining using that (if
thats possible in your case).

Reading your end bit, if you are absolutely sure that nothing else is
accessing that table then the plan is probably deadlocking itself because of
parallelism which can happen, you can stop parallelism by using MAXDOP 1 on
the OPTIONS clause of the UPDATE statement.

Hope that helps.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Thanks,
Frank

Deadlock on different unique RIDs in SQL Server 2000 - how?

Ok, I'm in the process of unravelling some deadlocks we've got in our
current application. I've been able to work through the PAG and KEY
deadlocks with no problems. However, I'm absolutely stumped with a
deadlock that has two RID nodes.
There are 2 nodes in this deadlock, each with a different unique RID as
the resource currently deadlocked.
Using the DBCC PAGE command shows that the page for each of the RID
nodes is from the same table. But each individual RID node is for a
unique row in that table (obviously).
The deadlock wait-for graph report clearly identifies the two SPIDs
that are deadlocking. Using the information I have from the profiler
trace of the run I can see the history of the SQL up to and including
the point of deadlock.
But here's the crux of the problem I don't understand.
The SPID for Node 1 is updating row R1 in table T.
The SPID for Node 2 is inserting row R2 into table T.
The deadlock occurs in the code for the update trigger and insert
trigger that is attached to table T.
How can these two SPIDs deadlock each other on the RID?

>From the logic of the application, if I'm updating R1 it has no
"knowledge" of the fact that R2 is being added to the table. Likewise,
adding new row R2 has no "knowledge" of existing row R1.
I have simplified what actually happens slightly, but fundamentally the
problem boils down to the issue I've just highlighted. The two SPIDS
deadlocking on two completely different RIDs - I don't understand how
or why?
It's worth mentioning that both the insert and the update trigger use
the inserted virtual table to get the ID of the item just
added/updated. And the code does other processing using that ID.
I know this sounds like a real long shot, but is it at all possible
that the inserted table has got "confused" with the "wrong" ID in it
when each SPID starts executing its insert/update trigger?
Any help at all would be appreciated.I suggest you examine the execute plans of the trigger statements involved
in the deadlock. Check to see of there is a operation (e.g. scan) that
might require access to the row modified by the other transaction. Some
index tuning can avoid this situation.
Hope this helps.
Dan Guzman
SQL Server MVP
"MisterD" <david@.nfs-hospitality.com> wrote in message
news:1152630911.019162.260080@.b28g2000cwb.googlegroups.com...
> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
>
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||Dan,
Thanks for replying. I guess I missed one important point out - doh!
Both SPIDs have exclusive (X) locks on each RID and they are both
requesting an update (U) lock on the opposing RID.
If it was a simple shared (S) lock, I think I could understand, but
it's the fact that it's an update lock that I really don't understand.
FYI, the problem occurs at the end of the insert/update trigger which
tries to do an UPDATE statement on the row that has just been
inserted/updated.|||Hi MisterD,
I do not have solution for you, however I have the same type of problem, how
do you trace deadlock on the Profiler, I need to see which process, what is
sql statement is causing the trouble, can you tell me what I need to trace?
"MisterD" wrote:

> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
>
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||JIM.H. wrote:
> Hi MisterD,
> I do not have solution for you, however I have the same type of problem, h
ow
> do you trace deadlock on the Profiler, I need to see which process, what i
s
> sql statement is causing the trouble, can you tell me what I need to trace
?
>
http://www.sql-server-performance.com/deadlocks.asp
I have given you this URL twice, have you actually read that article?
What more do you need to know? A selection I copied from that page:
To help identify deadlock problems, use the SQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
will provide you with the raw data you need to help isolate the causes
of deadlocks in your databases. [7.0]
*****
Consider turning on SQL Server deadlock tracing in order to track
deadlocks, when you are having deadlock-related issues. The overhead for
doing this is minimal.
DBCC TRACEON (3605,1204,-1)
Once this is run, then all deadlocking activity will be written to the
SQL Server log file. [7.0, 2000] Added 11-22-2004
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for your help, I could not see how I can strat wizard to ru
n
"Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
2000 profiler?
"Tracy McKibben" wrote:

> JIM.H. wrote:
> http://www.sql-server-performance.com/deadlocks.asp
> I have given you this URL twice, have you actually read that article?
> What more do you need to know? A selection I copied from that page:
>
> To help identify deadlock problems, use the SQL Server Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
> will provide you with the raw data you need to help isolate the causes
> of deadlocks in your databases. [7.0]
> *****
> Consider turning on SQL Server deadlock tracing in order to track
> deadlocks, when you are having deadlock-related issues. The overhead for
> doing this is minimal.
> DBCC TRACEON (3605,1204,-1)
> Once this is run, then all deadlocking activity will be written to the
> SQL Server log file. [7.0, 2000] Added 11-22-2004
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||JIM.H. wrote:
> Hi Tracy, thanks for your help, I could not see how I can strat wizard to
run
> "Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
> 2000 profiler?
>
No, that wizard is exclusive to SQL 7, but that wasn't the reason I
highlighted that paragraph. I highlighted it so that you would see that
you can use Profiler to identify deadlocks. A quick Google search for
"profiler" and "Identify The Cause of a Deadlock" turns up numerous
articles that will tell you what you need to do. I'm not trying to be
vague, but deadlocking is a complex topic, and you will learn more by
doing some research than you will if I just give you the answers.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Lines: 29
Thread-Topic: Deadlock on different unique RIDs in SQL Server 2000 - how?
thread-index: AcalyHXJq6eo+Jx8STe3Acz3a5gfRQ==
X-WBNR-Posting-Host: 71.16.104.203
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:5451
Hi Tracy,
I am dong search on Google, there are many options but no time to read all
of them since there are other problems are going on around here. I am using
Server 2000 profiler, is this wizard available there or do I have any other
options to go with?
"Tracy McKibben" wrote:

> JIM.H. wrote:
> No, that wizard is exclusive to SQL 7, but that wasn't the reason I
> highlighted that paragraph. I highlighted it so that you would see that
> you can use Profiler to identify deadlocks. A quick Google search for
> "profiler" and "Identify The Cause of a Deadlock" turns up numerous
> articles that will tell you what you need to do. I'm not trying to be
> vague, but deadlocking is a complex topic, and you will learn more by
> doing some research than you will if I just give you the answers.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Jim H - Get off my thread!
Go and do some research yourself (like the lady said).
Deadlocks are not easy issues to resolve. If you don't understand them
then you need to go and read about them until you do.
Goodbye!|||MisterD wrote:
> Jim H - Get off my thread!
> Go and do some research yourself (like the lady said).
> Deadlocks are not easy issues to resolve. If you don't understand them
> then you need to go and read about them until you do.
> Goodbye!
>
Lady? What lady? :-)
He's got three or four threads going, all looking for an easy answer for
deadlocking. Somebody once said "You can lead a horse to water..."
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Deadlock on different unique RIDs in SQL Server 2000 - how?

Ok, I'm in the process of unravelling some deadlocks we've got in our
current application. I've been able to work through the PAG and KEY
deadlocks with no problems. However, I'm absolutely stumped with a
deadlock that has two RID nodes.
There are 2 nodes in this deadlock, each with a different unique RID as
the resource currently deadlocked.
Using the DBCC PAGE command shows that the page for each of the RID
nodes is from the same table. But each individual RID node is for a
unique row in that table (obviously).
The deadlock wait-for graph report clearly identifies the two SPIDs
that are deadlocking. Using the information I have from the profiler
trace of the run I can see the history of the SQL up to and including
the point of deadlock.
But here's the crux of the problem I don't understand.
The SPID for Node 1 is updating row R1 in table T.
The SPID for Node 2 is inserting row R2 into table T.
The deadlock occurs in the code for the update trigger and insert
trigger that is attached to table T.
How can these two SPIDs deadlock each other on the RID?
>From the logic of the application, if I'm updating R1 it has no
"knowledge" of the fact that R2 is being added to the table. Likewise,
adding new row R2 has no "knowledge" of existing row R1.
I have simplified what actually happens slightly, but fundamentally the
problem boils down to the issue I've just highlighted. The two SPIDS
deadlocking on two completely different RIDs - I don't understand how
or why?
It's worth mentioning that both the insert and the update trigger use
the inserted virtual table to get the ID of the item just
added/updated. And the code does other processing using that ID.
I know this sounds like a real long shot, but is it at all possible
that the inserted table has got "confused" with the "wrong" ID in it
when each SPID starts executing its insert/update trigger?
Any help at all would be appreciated.I suggest you examine the execute plans of the trigger statements involved
in the deadlock. Check to see of there is a operation (e.g. scan) that
might require access to the row modified by the other transaction. Some
index tuning can avoid this situation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MisterD" <david@.nfs-hospitality.com> wrote in message
news:1152630911.019162.260080@.b28g2000cwb.googlegroups.com...
> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
>>From the logic of the application, if I'm updating R1 it has no
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||Dan,
Thanks for replying. I guess I missed one important point out - doh!
Both SPIDs have exclusive (X) locks on each RID and they are both
requesting an update (U) lock on the opposing RID.
If it was a simple shared (S) lock, I think I could understand, but
it's the fact that it's an update lock that I really don't understand.
FYI, the problem occurs at the end of the insert/update trigger which
tries to do an UPDATE statement on the row that has just been
inserted/updated.|||Hi MisterD,
I do not have solution for you, however I have the same type of problem, how
do you trace deadlock on the Profiler, I need to see which process, what is
sql statement is causing the trouble, can you tell me what I need to trace?
"MisterD" wrote:
> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
> >From the logic of the application, if I'm updating R1 it has no
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||JIM.H. wrote:
> Hi MisterD,
> I do not have solution for you, however I have the same type of problem, how
> do you trace deadlock on the Profiler, I need to see which process, what is
> sql statement is causing the trouble, can you tell me what I need to trace?
>
http://www.sql-server-performance.com/deadlocks.asp
I have given you this URL twice, have you actually read that article?
What more do you need to know? A selection I copied from that page:
To help identify deadlock problems, use the SQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
will provide you with the raw data you need to help isolate the causes
of deadlocks in your databases. [7.0]
*****
Consider turning on SQL Server deadlock tracing in order to track
deadlocks, when you are having deadlock-related issues. The overhead for
doing this is minimal.
DBCC TRACEON (3605,1204,-1)
Once this is run, then all deadlocking activity will be written to the
SQL Server log file. [7.0, 2000] Added 11-22-2004
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for your help, I could not see how I can strat wizard to run
"Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
2000 profiler?
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > Hi MisterD,
> >
> > I do not have solution for you, however I have the same type of problem, how
> > do you trace deadlock on the Profiler, I need to see which process, what is
> > sql statement is causing the trouble, can you tell me what I need to trace?
> >
> http://www.sql-server-performance.com/deadlocks.asp
> I have given you this URL twice, have you actually read that article?
> What more do you need to know? A selection I copied from that page:
>
> To help identify deadlock problems, use the SQL Server Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
> will provide you with the raw data you need to help isolate the causes
> of deadlocks in your databases. [7.0]
> *****
> Consider turning on SQL Server deadlock tracing in order to track
> deadlocks, when you are having deadlock-related issues. The overhead for
> doing this is minimal.
> DBCC TRACEON (3605,1204,-1)
> Once this is run, then all deadlocking activity will be written to the
> SQL Server log file. [7.0, 2000] Added 11-22-2004
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||JIM.H. wrote:
> Hi Tracy, thanks for your help, I could not see how I can strat wizard to run
> "Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
> 2000 profiler?
>
No, that wizard is exclusive to SQL 7, but that wasn't the reason I
highlighted that paragraph. I highlighted it so that you would see that
you can use Profiler to identify deadlocks. A quick Google search for
"profiler" and "Identify The Cause of a Deadlock" turns up numerous
articles that will tell you what you need to do. I'm not trying to be
vague, but deadlocking is a complex topic, and you will learn more by
doing some research than you will if I just give you the answers.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
I am dong search on Google, there are many options but no time to read all
of them since there are other problems are going on around here. I am using
Server 2000 profiler, is this wizard available there or do I have any other
options to go with?
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > Hi Tracy, thanks for your help, I could not see how I can strat wizard to run
> > "Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
> > 2000 profiler?
> >
> No, that wizard is exclusive to SQL 7, but that wasn't the reason I
> highlighted that paragraph. I highlighted it so that you would see that
> you can use Profiler to identify deadlocks. A quick Google search for
> "profiler" and "Identify The Cause of a Deadlock" turns up numerous
> articles that will tell you what you need to do. I'm not trying to be
> vague, but deadlocking is a complex topic, and you will learn more by
> doing some research than you will if I just give you the answers.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Jim H - Get off my thread!
Go and do some research yourself (like the lady said).
Deadlocks are not easy issues to resolve. If you don't understand them
then you need to go and read about them until you do.
Goodbye!|||MisterD wrote:
> Jim H - Get off my thread!
> Go and do some research yourself (like the lady said).
> Deadlocks are not easy issues to resolve. If you don't understand them
> then you need to go and read about them until you do.
> Goodbye!
>
Lady? What lady? :-)
He's got three or four threads going, all looking for an easy answer for
deadlocking. Somebody once said "You can lead a horse to water..."
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My most sincere public apologies for calling you a lady - Mr Tracy
McKibben :)

deadlock issue in sql server 2000 enterprise edition version 8.00.

we have installed sql server 2000 enterprise edition on our erp server.
We are facing frequent deadlock problem ie one process blocks the other
process frequently.
The compatibility of the databases has been set to 80.
First of all whether the version is that of enterprise edition ?
secondly any particular setting to resolve the deadlock issues ?To see what version you're on issue the following :-
SELECT SERVERPROPERTY('Edition')
This article may provide help with your deadlocking :-
http://support.microsoft.com/kb/271509/
--
HTH. Ryan
"Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> we have installed sql server 2000 enterprise edition on our erp server.
> We are facing frequent deadlock problem ie one process blocks the other
> process frequently.
> The compatibility of the databases has been set to 80.
> First of all whether the version is that of enterprise edition ?
> secondly any particular setting to resolve the deadlock issues ?
>|||thanks for your feedback.
I have seen the article on deadlock but any simpler way to handle it.
like a sp_configure statement
"Ryan" wrote:
> To see what version you're on issue the following :-
> SELECT SERVERPROPERTY('Edition')
> This article may provide help with your deadlocking :-
> http://support.microsoft.com/kb/271509/
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> > we have installed sql server 2000 enterprise edition on our erp server.
> > We are facing frequent deadlock problem ie one process blocks the other
> > process frequently.
> > The compatibility of the databases has been set to 80.
> > First of all whether the version is that of enterprise edition ?
> > secondly any particular setting to resolve the deadlock issues ?
> >
> >
>
>|||I'm afriad there is no quick fix for deadlocking, there are some traceflags
you can turn on to give you detailed information about the nature of your
deadlock :-
DBCC TRACEON (1204,3605,-1)
This will write deadlock information to the SQL Server Errorlog, which can
be read using sp_ReadErrorLog.
Here's a good article about Anti-Blocking strategies :-
http://vyaskn.tripod.com/anti_blocking_strategies.htm
HTH. Ryan
"Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> thanks for your feedback.
> I have seen the article on deadlock but any simpler way to handle it.
> like a sp_configure statement
> "Ryan" wrote:
>> To see what version you're on issue the following :-
>> SELECT SERVERPROPERTY('Edition')
>> This article may provide help with your deadlocking :-
>> http://support.microsoft.com/kb/271509/
>> --
>> HTH. Ryan
>>
>> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
>> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
>> > we have installed sql server 2000 enterprise edition on our erp server.
>> > We are facing frequent deadlock problem ie one process blocks the other
>> > process frequently.
>> > The compatibility of the databases has been set to 80.
>> > First of all whether the version is that of enterprise edition ?
>> > secondly any particular setting to resolve the deadlock issues ?
>> >
>> >
>>|||thanks
"Ryan" wrote:
> I'm afriad there is no quick fix for deadlocking, there are some traceflags
> you can turn on to give you detailed information about the nature of your
> deadlock :-
> DBCC TRACEON (1204,3605,-1)
> This will write deadlock information to the SQL Server Errorlog, which can
> be read using sp_ReadErrorLog.
> Here's a good article about Anti-Blocking strategies :-
> http://vyaskn.tripod.com/anti_blocking_strategies.htm
>
> --
> HTH. Ryan
>
> "Rajeev Rivankar" <RajeevRivankar@.discussions.microsoft.com> wrote in
> message news:E5E165E2-E8CB-43D4-8F78-4F1CF3908B8A@.microsoft.com...
> > thanks for your feedback.
> >
> > I have seen the article on deadlock but any simpler way to handle it.
> > like a sp_configure statement
> >
> > "Ryan" wrote:
> >
> >> To see what version you're on issue the following :-
> >>
> >> SELECT SERVERPROPERTY('Edition')
> >>
> >> This article may provide help with your deadlocking :-
> >>
> >> http://support.microsoft.com/kb/271509/
> >>
> >> --
> >> HTH. Ryan
> >>
> >>
> >> "Rajeev Rivankar" <Rajeev Rivankar@.discussions.microsoft.com> wrote in
> >> message news:EEFEE7EC-A947-41F8-A92F-A3626B7A7BA6@.microsoft.com...
> >> > we have installed sql server 2000 enterprise edition on our erp server.
> >> > We are facing frequent deadlock problem ie one process blocks the other
> >> > process frequently.
> >> > The compatibility of the databases has been set to 80.
> >> > First of all whether the version is that of enterprise edition ?
> >> > secondly any particular setting to resolve the deadlock issues ?
> >> >
> >> >
> >>
> >>
> >>
>
>sql

Thursday, March 29, 2012

Deadlocks and trace 1204

Tuesday, March 27, 2012

deadlocks

Deadlocked on the same resource (same index)

Deadlocked on the same resource (same index)

Deadlocked on the same resource (same index)

deadlocked on lock resources. SQL Server 2000

Sunday, March 25, 2012

Deadlock within select?

deadlock with replication's update/insert/delete process

Deadlock victim (-2147467259)

Deadlock victim (-2147467259)

Deadlock victim (-2147467259)

deadlock victim

Deadlock transaction

Thursday, March 22, 2012

Deadlock problem

Wednesday, March 21, 2012

Deadlock on Update using temp table

Deadlock on different unique RIDs in SQL Server 2000 - how?

Deadlock on different unique RIDs in SQL Server 2000 - how?

deadlock issue in sql server 2000 enterprise edition version 8.00.

deAttach database

Blog Archive

About Me