Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.
Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
sql

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:
> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

Deadlock Problem

What's the best way to avoid a deadlock in the following situation:
One query deletes a row from a table i.e.,
delete MyTable
where Id = 1
Another query wants to update the same row in 'MyTable' at the same
time that the first query wants to delete the record, i.e.,
update MyTable
set SomeField = 1
where id = 1
The first query above is called from one process, the second query
above is called from a different process.
If the update fails to update the row because the row has been
deleted this is ok. So I want the delete to take priority.
The 2 processes are processing up to 30 transactions per second.
How can I guarantee that I won't get a deadlock'Hi,
See the command SET DEADLOCK_PRIORITY in books online.
Thanks
Hari
MCDBA
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'|||If there is only one table being updated it should not deadlock, it will
only block. Both the update and delete will lock the row while it is
updating or deleting the row and will only temporarily block the other. If
the update is being blocked by the delete it will simply not find the row to
delete once the delete is finished. As long as you don't update 2 or more
tables in reverse order you will most likely only block and not deadlock.
--
Andrew J. Kelly SQL MVP
"j allen" <jallen_12342000@.yahoo.com> wrote in message
news:a0048d52.0409161535.7a9e8c1b@.posting.google.com...
> What's the best way to avoid a deadlock in the following situation:
> One query deletes a row from a table i.e.,
> delete MyTable
> where Id = 1
> Another query wants to update the same row in 'MyTable' at the same
> time that the first query wants to delete the record, i.e.,
> update MyTable
> set SomeField = 1
> where id = 1
> The first query above is called from one process, the second query
> above is called from a different process.
> If the update fails to update the row because the row has been
> deleted this is ok. So I want the delete to take priority.
> The 2 processes are processing up to 30 transactions per second.
> How can I guarantee that I won't get a deadlock'sql

Deadlock problem

I've load testing a database solution and keep hitting a deadlock situation that I don't understand. I'm hoping that someone on this forum might have some solutions..

I've 3 tables: document, documentVersion and promotion table where documentVersion stores XML and the promotion table stores data extracted from the XML. I've an insertDocument stored procedure that:

Begins a transaction Inserts a new row into document (which has an identity column as a primary key) and records the scope_identity Inserts a new row into documentVersion with a FK reference to the new document row. The documentVersion table also has an identity columan as a primary key). Again scope_identity is recorded. The name of a custom stored procedure is formed and an execute statement is used to run the stored procedure. The custom stored procedure uses XQuery to extract rows from the XML and inserts the rows into the promotion table with a FK reference to the new documentVersion. The transaction is committed.

The deadlocks always occur in step 5 and invariably are caused by process1 having an X lock on PK_documentVersion (presumably because of the insert) and waiting for a shared lock on PK_documentVersion (presumably to check the FK constraint from the promotion table insert). Process2 is in exactly the same situation (i.e. holding X lock on PK_documentVersion and waiting for shared lock on PK_documentVersion).

If I run the test without the promotion (i.e. just inserting into document and documentVersion) and build up several thousand rows then I can reenable promotion and run my load test without deadlocks.

I've tried changing lock hints on the inserts, changing the isolation level (including trying snapshot) but all to know avail.

Can anyone explain the cause of the deadlock and suggest a remedy?

Much obliged,

David.

P.S. there are clustered indexes on the identity columns of document and documentVersion.

Here is an article I wrote a few months ago regarding how to track deadlock errors with SQLDiag, a helpful tool for such a purpose. http://articles.techrepublic.com.com/5100-9592_11-6116287.html

Have you tried using the table hint READPAST in your sql statements?|||

Thanks I'll look at the article.

Unfortunately, I have no control of the shared locks because the database engine sets these because of the FK check. If I was doing a select I could use the READPAST hint. Similarly, approaches such as using READ_COMMITTED_ISOLATION or SET TRANSACTION ISOLATION LEVEL SNAPSHOT have no effect on the FK check's use of locks.

David

|||

not sure if you have resolved this now,

if not, do you have deadlock trace information? deadlocks can occur for non-obvious reasons at the auto commit level, which won't be directly apparent from the sql

Deadlock problem

I've load testing a database solution and keep hitting a deadlock situation that I don't understand. I'm hoping that someone on this forum might have some solutions..

I've 3 tables: document, documentVersion and promotion table where documentVersion stores XML and the promotion table stores data extracted from the XML. I've an insertDocument stored procedure that:

Begins a transaction Inserts a new row into document (which has an identity column as a primary key) and records the scope_identity Inserts a new row into documentVersion with a FK reference to the new document row. The documentVersion table also has an identity columan as a primary key). Again scope_identity is recorded. The name of a custom stored procedure is formed and an execute statement is used to run the stored procedure. The custom stored procedure uses XQuery to extract rows from the XML and inserts the rows into the promotion table with a FK reference to the new documentVersion. The transaction is committed.

The deadlocks always occur in step 5 and invariably are caused by process1 having an X lock on PK_documentVersion (presumably because of the insert) and waiting for a shared lock on PK_documentVersion (presumably to check the FK constraint from the promotion table insert). Process2 is in exactly the same situation (i.e. holding X lock on PK_documentVersion and waiting for shared lock on PK_documentVersion).

If I run the test without the promotion (i.e. just inserting into document and documentVersion) and build up several thousand rows then I can reenable promotion and run my load test without deadlocks.

I've tried changing lock hints on the inserts, changing the isolation level (including trying snapshot) but all to know avail.

Can anyone explain the cause of the deadlock and suggest a remedy?

Much obliged,

David.

P.S. there are clustered indexes on the identity columns of document and documentVersion.

Here is an article I wrote a few months ago regarding how to track deadlock errors with SQLDiag, a helpful tool for such a purpose. http://articles.techrepublic.com.com/5100-9592_11-6116287.html

Have you tried using the table hint READPAST in your sql statements?|||

Thanks I'll look at the article.

Unfortunately, I have no control of the shared locks because the database engine sets these because of the FK check. If I was doing a select I could use the READPAST hint. Similarly, approaches such as using READ_COMMITTED_ISOLATION or SET TRANSACTION ISOLATION LEVEL SNAPSHOT have no effect on the FK check's use of locks.

David

|||

not sure if you have resolved this now,

if not, do you have deadlock trace information? deadlocks can occur for non-obvious reasons at the auto commit level, which won't be directly apparent from the sql

deadlock problem

I have an application that calls many stored procedures. They are
occassionally getting into a deadlock situation.
I have used DBCC TraceOn (-1,1204) to trace the problem and have had
moderate success. However, I have hit a particular deadlock that I just
don't understand and was hoping someone here may be able to help.
The two sps in question are Item_I and Item_DNPK.
The output from DBCC Trace (see below) tells me that SPID 60 (see red text)
has an exclusive (X) lock on the Item table index (KEY: 10:613577224:1 is a
clustered index XPKItem). SPID 60 is currently deadlocked at line 29 of
Item_I. Line 29 is the very first transaction in this sp.
The other client, SPID 56 (see green text) has a Range-Shared-Update lock on
the same resource as SPID 60 and is currently deadlocked at line 60 of
Item_DNPK. Line 60 is the very first meaningful transaction in this sp (not
counting declares and create operations on #temp tables).
The deadlock is, I think, occurring because SPID 60, which has an X lock on
the resource, is waiting for a Range-Insert-Null lock (see maroon text),
while SPID 56 is waiting for a Range-S-U lock on the same resource.
What I don't understand is why there is a deadlock in this case; with one
resource? Also, how can I prevent it, given that the lines in question are
right at the beginning of the transactions in the respective sps?
Any help would be very much appreciated
Adrian
Output of DBCC TraceOn (1204) for the deadlock described above::
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:1
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (100095e758e0)
CleanCnt:1 Mode: X Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42be3fe0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:60 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 60 ECID: 0 Statement Type: INSERT
Line #: 29
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_I;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368) Value:0x42bd7920 Cost:(0/0)
2006-02-21 14:25:06.21 spid4
2006-02-21 14:25:06.21 spid4 Node:2
2006-02-21 14:25:06.21 spid4 KEY: 10:613577224:1 (ffffffffffff)
CleanCnt:1 Mode: Range-S-U Flags: 0x0
2006-02-21 14:25:06.21 spid4 Grant List 0::
2006-02-21 14:25:06.21 spid4 Owner:0x42bd85a0 Mode: Range-S-U Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
2006-02-21 14:25:06.21 spid4 SPID: 56 ECID: 0 Statement Type: INSERT
Line #: 60
2006-02-21 14:25:06.21 spid4 Input Buf: RPC Event: dbo.Item_DNPK;1
2006-02-21 14:25:06.21 spid4 Requested By:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-Insert-Null SPID:60 ECID:0 Ec:(0x4428D368) Value:0x42bd29e0
Cost:(0/214)
2006-02-21 14:25:06.21 spid4 Victim Resource Owner:
2006-02-21 14:25:06.21 spid4 ResType:LockOwner Stype:'OR' Mode:
Range-S-U SPID:56 ECID:0 Ec:(0x44111368)See if this helps
http://sql-server-performance.com/deadlocks.asp
Madhivanan|||Please post the queries.
ML
http://milambda.blogspot.com/|||Here are the two queries involved in the deadlck I am referring to:
Query 1 - sp Item_DNPK (I have left some of this stored proc out just
because it is very long. My deadlocking is happening on the Insert so I
don't think the rest of the sp (after this Insert) is relevant. If you think
it will help, I can post it as well):
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
BEGIN TRAN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
Query 2: Item_I:
CREATE PROC [dbo].[Item_I]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : Generated from schema stored procedure template 'SP_I'
-- Description : Generic identity based insert stored procedure for
table 'Item'
-- Returns : New Identity Key if successful or negative error
number
----
--
@.aItemTypeKey int ,
@.aParentKey int ,
@.aItemPartitionKey int ,
@.aOwnerKey int ,
@.aExternalKey sql_variant ,
@.aName nvarchar(256) ,
@.aDescription nvarchar(256) ,
@.aInternal varbinary(100) ,
@.aIsLeaf bit = 0,
@.aIsDeleted bit = 0,
@.aCreatedOn datetime ,
@.aModifiedOn datetime
AS
BEGIN
DECLARE @.Result int
SET NoCount ON
INSERT INTO [dbo].[Item]
(
ItemTypeKey,
ParentKey,
ItemPartitionKey,
OwnerKey,
ExternalKey,
Name,
Description,
Internal,
IsLeaf,
IsDeleted,
CreatedOn,
ModifiedOn
)
VALUES
(
@.aItemTypeKey,
@.aParentKey,
@.aItemPartitionKey,
@.aOwnerKey,
@.aExternalKey,
@.aName,
@.aDescription,
@.aInternal,
@.aIsLeaf,
@.aIsDeleted,
@.aCreatedOn,
@.aModifiedOn
)
IF (@.@.Error = 0)
BEGIN
SELECT @.Result = @.@.Identity
END
ELSE
BEGIN
SELECT @.Result = -@.@.Error
END
RETURN @.Result
END
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D0BAC95E-35B8-47A2-9F51-2BF174E5EF0F@.microsoft.com...
> Please post the queries.
>
> ML
> --
> http://milambda.blogspot.com/|||Comments inline:

> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
Repeatable read? Is there another reason for this isolation level? As far as
I see it you could just use defaults here (READ COMMITED).

> BEGIN TRAN
> CREATE TABLE #Temp (MimeElementKey int)
> -- primary key has to exist in order to prevent the subsequent deletes
> from hanging
> CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
> RecursionDepth int not null,
> PRIMARY KEY CLUSTERED
> (
> [ItemKey],
> [RecursionDepth]
> ))
> -- get the dependent items of the initial item being deleted
> INSERT INTO #Temp2
> SELECT ItemKey, ParentKey, @.RecursionDepth
> FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
The HOLDLOCK hint instructs the procedure not to release the lock until it
ends. As I see it you select a set of values here and store them in a local
temporary table, I guess you make a few changes, then update the values
appropriately or what? Could you do these in a single update statement? It
would really help if we could see the rest of the procedure here - especiall
y
the part where the explicit lock is acquired.
Also try adding the ROWLOCK hint.

> WHERE ParentKey = @.aItemKey
> END
The other procedure looks OK to me. It's a simple insert procedure, and has
little or no room for improvement. I, personally, prefer the INSERT...SELECT
syntax to INSERT...VALUES, but I've never heard that choosing either one
would affect locking.
ML
http://milambda.blogspot.com/|||Thanks for the reply.
I tried these two isolation levels (REPEATABLE READ and SERIALIZABLE), but
they don't seem to make a dramatic difference. I'm a little in the dark
ragarding this deadlock, so I must admit that I don't really know whether
READ REPEATABLE is better than READ COMMITTED or not.
The rest of this stored procuder involves several (7 or 8) different
delete/update queries which themselves were involved in earlier deadlocks.
It is for that reason I had started introducing these non-default isolation
levels. Again, though, I am largely driving by the seat of my pants, here;
no explicit reason for having done that.
The reason for the temp table is actually this: my schema is represented as
meta data within several tables and I need to delete a hierarchy of items
defined within this meta schema. To do this, I recurse the sp looking for
children of items at each level. I place the identifiers of all affected
items in the #temp table and then, as I come out of the recursion, I delete
and update various aspects of my schema to delete the children in a clean
and orderly fashion. The semantics of the sp may not make much sense to you
since the rest of the schema is not known to you. IF you have any questions,
though, I will be happy to try and work through them with you.
Thanks for your help in looking into this.
Here is the rest of the sp:
CREATE PROC [dbo].[Item_DNPK]
----
--
-- Schema Version : 2.2 Build 198 Revision 0
-- Date Generated : Mon Feb 20 09:56:52 2006
-- Author : APD
-- Description : Cascade primary key based delete stored procedure for
table 'Item'
-- Recursively calls into itself deleting all children of
the
-- item passed in as the parameter
-- Returns : Zero if successful or negative error number
-- Returns : and List of dependent items that were deleted during
the cascade
----
--
@.aItemKey int,
@.RecursionDepth int = 0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --SERIALIZABLE--
DECLARE @.IsLocalTran bit
DECLARE @.Result int
-- This procedure is recursive; keep track of the depth of recursion
SELECT @.RecursionDepth = @.RecursionDepth + 1
IF (@.@.TranCount = 0)
BEGIN
SET @.IsLocalTran = 1
BEGIN TRAN
END
ELSE
BEGIN
SET @.IsLocalTran = 0
END
SET @.Result = IsNull(Object_Id('tempdb..#Temp'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp
END
-- A temporary table to hold the cascaded item Ids for later deletion
SET @.Result = IsNull(Object_Id('tempdb..#Temp2'), 0)
IF ((@.Result > 0) AND (@.RecursionDepth = 1))
BEGIN
DROP TABLE #Temp2
END
-- for the first entry, create holding tables
IF (@.RecursionDepth = 1)
BEGIN
CREATE TABLE #Temp (MimeElementKey int)
-- primary key has to exist in order to prevent the subsequent deletes
from hanging
CREATE TABLE #Temp2 (ItemKey int not null , ParentKey int,
RecursionDepth int not null,
PRIMARY KEY CLUSTERED
(
[ItemKey],
[RecursionDepth]
))
-- get the dependent items of the initial item being deleted
INSERT INTO #Temp2
SELECT ItemKey, ParentKey, @.RecursionDepth
FROM [dbo].[Item] WITH (UPDLOCK,HOLDLOCK)
WHERE ParentKey = @.aItemKey
END
ELSE
BEGIN
-- get the dependent items of the items at the previous recursion level
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i WITH (UPDLOCK,HOLDLOCK)
JOIN #Temp2 t WITH (NOLOCK)
ON i.ParentKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth-1
END
-- if the "get" of dependent children returned a non-zero number of items,
recurse to get their children
IF (SELECT @.@.ROWCOUNT) > 0
BEGIN
-- get these children to delete as well
EXEC Item_DNPK null, @.RecursionDepth
END
-- delete all items and clean up links as a result of these deletions
INSERT INTO #Temp
SELECT ME.[MimeElementKey]
FROM [dbo].[MimeElement] ME
JOIN [dbo].[Property] P
ON ME.[MimeElementKey] = P.[MimeElementKey]
WHERE P.[ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Property]
WHERE [ItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[MimeElement]
FROM [dbo].[MimeElement] ME
JOIN #Temp WITH (NOLOCK)
ON ME.[MimeElementKey] = #Temp.[MimeElementKey]
DELETE FROM #Temp
END
IF (@.@.Error = 0)
BEGIN
/* DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[SourceItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Link]
FROM [dbo].[Link] l
JOIN #Temp2 t WITH (NOLOCK) ON l.[TargetItemKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth */
DELETE [dbo].[Link]
WHERE [SourceItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
OR [TargetItemKey] in (SELECT ItemKey FROM #Temp2 WITH (NOLOCK) WHERE
RecursionDepth = @.RecursionDepth)
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Property]
SET [ReferenceTargetItem] = NULL
FROM [dbo].[Property] p
JOIN #Temp2 t WITH (NOLOCK) ON p.[ReferenceTargetItem] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
UPDATE [dbo].[Item]
SET [ParentKey] = NULL
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK) ON i.[ParentKey] = t.ItemKey
WHERE RecursionDepth = @.RecursionDepth
END
IF (@.@.Error = 0)
BEGIN
DELETE [dbo].[Item]
FROM [dbo].[Item] i
JOIN #Temp2 t WITH (NOLOCK)
ON i.ItemKey = t.ItemKey
WHERE t.RecursionDepth = @.RecursionDepth
END
IF (SELECT @.RecursionDepth) = 1
BEGIN
-- delete the primary item passed in as a parameter
IF (@.@.Error = 0)
BEGIN
-- put it in #Temp2 so we can return it in the "cascadedDeletedItems"
resultset
INSERT INTO #Temp2
SELECT i.ItemKey, i.ParentKey, @.RecursionDepth
FROM [dbo].[Item] i -- WITH (NOLOCK)
WHERE i.ItemKey = @.aItemKey
exec Item_D1PK @.aItemKey
END
if (@.IsLocalTran = 1)
BEGIN
IF (@.@.Error = 0)
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
END
SELECT @.Result = -@.@.Error
-- return the items affected by this query
SELECT distinct ItemKey FROM #Temp2
RETURN @.Result
END
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1D228D99-FB06-4FB4-AEC2-24653942A8B4@.microsoft.com...
> Comments inline:
>
> Repeatable read? Is there another reason for this isolation level? As far
> as
> I see it you could just use defaults here (READ COMMITED).
>
> The HOLDLOCK hint instructs the procedure not to release the lock until it
> ends. As I see it you select a set of values here and store them in a
> local
> temporary table, I guess you make a few changes, then update the values
> appropriately or what? Could you do these in a single update statement? It
> would really help if we could see the rest of the procedure here -
> especially
> the part where the explicit lock is acquired.
> Also try adding the ROWLOCK hint.
>
> The other procedure looks OK to me. It's a simple insert procedure, and
> has
> little or no room for improvement. I, personally, prefer the
> INSERT...SELECT
> syntax to INSERT...VALUES, but I've never heard that choosing either one
> would affect locking.
>
> ML
> --
> http://milambda.blogspot.com/|||Before I read through the entire procedure - is there a tree or a hierarchy
involved in these deletes?
This could be optimized, take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Deletes require exclusive locks - you should make the delete procedure as
concise as possible - maybe even by building the list outside of a
transaction, and only beginning an explicit transaction just before the
actual delete statement. The delete will block other users anyway, so make i
t
as short as possible.
ML
http://milambda.blogspot.com/|||Yes. Typically an item in the item table has reference to it's parent which
is another item in the item table. The relationship from parent to children
is one to many. A row representing an item in the item table has references
to other tables that define things like properties and property types, etc.
Essentially, then, the hierarchy is to determine all the children of the
item whose id is passed in as a parameter to the sp, and recursively do that
for all those children until the end of the line is reached
I hope this answers your question.
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/|||That certainly is one of the function's purposes - to get a list of all
descendants in a hierarchy.
ML
http://milambda.blogspot.com/|||Thanks for the link and suggestion. I'll try putting the select outside of
the transaction and get back to you on my results
Adrian
"ML" <ML@.discussions.microsoft.com> wrote in message
news:1DECC75A-38CD-40AB-BC63-50FD758231C9@.microsoft.com...
> Before I read through the entire procedure - is there a tree or a
> hierarchy
> involved in these deletes?
> This could be optimized, take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> Deletes require exclusive locks - you should make the delete procedure as
> concise as possible - maybe even by building the list outside of a
> transaction, and only beginning an explicit transaction just before the
> actual delete statement. The delete will block other users anyway, so make
> it
> as short as possible.
>
> ML
> --
> http://milambda.blogspot.com/

Deadlock on simple update related to clustered index

I am encountering a deadlock situation, with two threads
running two updates to the same table, if that table has a
clustered index on it that is NOT the primary key, and the
where clause of the UPDATE statement is on the primary
key. Very strange... Here is the test case:
create table x
(id int not null,
lastModified datetime not null,
altkey varchar(3) not null)
GO
CREATE UNIQUE CLUSTERED INDEX IAK_X ON x (altKey)
GO
alter table x add constraint pk_x primary key(id)
GO
insert into x
values (1, getdate(), 'XXX');
Then, from 2 ISQL threads, ran the following code...
begin tran
waitfor time '15:06' -- ensure they start at same time
update x
set lastModified = getdate()
where id = 1;
waitfor delay '000:00:10' -- wait to ensure lock occurs
update x
set lastModified = getdate()
where id = 1;
commit tran;
When I monitor the locks, the following happens:
Thread A successfully performs Update 1
Thread B is blocked as A has Exclusive lock on the row.
Thread B, however, successfully gets a KEY UPDATE lock on
PK_X
Thread A, when it tries to perform Update 2, is blocked
trying to get a KEY UPDATE lock on PK_X, causing the
deadlock.
Even if I then drop the clustered index with
DROP INDEX x.iak_x;
the problem remains.
However, if I NEVER create the clustered index. The
threads both work fine and never encounter a deadlock.
Neither transaction attempts to get a KEY UPDATE lock on
the primary key index PK_X.
We try to use clustered indexes on fields other than the
ID # (one-up number, but NOT an IDENTITY column in this
case), because we encountered deadlocks on Inserts in the
past.
Any help would be greatly appreciated.On Thu, 24 Jul 2003 14:02:58 -0700, "Corey" <chorton1@.austin.rr.com>
wrote:
>Any help would be greatly appreciated.
That's pretty humorous.
I've never gotten into the detailed guts of SQLServer like some around
here, but it sounds to me like you've hit a SQLServer bug. It looks
like SQLServer locks the data record in thread A, but allows thread B
to begin and get a lock on an index entry while waiting for the data,
and voila, deadlock. If that is the case, I'd call it a bug.
I assume it works correctly if you have the PK on id?
J.sql

Deadlock on replication update and NOLOCK hint question

Hi all,
I have an interesting situation. We have the following scenario:
1. Server A, Database A, table A as the replication publisher
2. Server B, Database B, table B as the replication subscriber.
3. Server B, Database C
Database A was replicating an update to Server B, Database B, table
B
At the same time, a stored procedure on Server B, Database C attempted
to perform the following type of query on Server B, Database B, table
B:
insert into table B
select ...
from database D (NOLOCK)
inner join other tables all with (NOLOCK) hints
Server B, Database C's stored procedure was the deadlock victim.
My understanding of a deadlock is when two queries are competing for
the same resources and the resource with the least cost or work done
is the victim.
I would think that the NOLOCK hint would not allow Server B, Database
C's stored proc to hold resources and therefore I could see blocking
occuring with the replication update but not deadlocking since I would
think that NOLOCK would not hold on to resources.
Could the insert select with the NOLOCK have held a page lock that
caused it to hold the same resources that the update replication
statement needed and vice-versa?
Btw, I am aware of the dirty reads for the NOLOCK statements and we
use them for business purposes for our DML statements.
Any ideas would be helpful.
Thanks in advance!Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>|||Hi,
I suggest you try out the tool called SQL Deadlock Detector. It monitors
your database for locks and deadlocks and provides complete information on
captured events. It tells you everything you need to know (locked objects,
blocked statements, blocking statements, etc.) to solve your
blocking/deadlock problems. The great thing about this tool is it's event
diagram which makes it exremely easy to see what exactly is going on.
You can download it from here:
http://lakesidesql.com/downloads/DLD2/2_0_2007_809/DeadlockDetector2_Setup_08-09-2007.zip.
HTH.
"techgrl" <lfischmar@.yahoo.com> wrote in message
news:1188404072.124101.68920@.k79g2000hse.googlegroups.com...
> Hi all,
> I have an interesting situation. We have the following scenario:
> 1. Server A, Database A, table A as the replication publisher
> 2. Server B, Database B, table B as the replication subscriber.
> 3. Server B, Database C
> Database A was replicating an update to Server B, Database B, table
> B
> At the same time, a stored procedure on Server B, Database C attempted
> to perform the following type of query on Server B, Database B, table
> B:
> insert into table B
> select ...
> from database D (NOLOCK)
> inner join other tables all with (NOLOCK) hints
>
> Server B, Database C's stored procedure was the deadlock victim.
> My understanding of a deadlock is when two queries are competing for
> the same resources and the resource with the least cost or work done
> is the victim.
> I would think that the NOLOCK hint would not allow Server B, Database
> C's stored proc to hold resources and therefore I could see blocking
> occuring with the replication update but not deadlocking since I would
> think that NOLOCK would not hold on to resources.
> Could the insert select with the NOLOCK have held a page lock that
> caused it to hold the same resources that the update replication
> statement needed and vice-versa?
> Btw, I am aware of the dirty reads for the NOLOCK statements and we
> use them for business purposes for our DML statements.
> Any ideas would be helpful.
> Thanks in advance!
>

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
Olivier GIL
LAFON SA
On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
> A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint
|||Hello,
Process B should not set a shared locked, because it has not opened any
transaction.
Olivier GIL
LAFON SA
"M A Srinivas" wrote:

> On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> My opinion is
> First Process MAX is completed by Process A
> Process B which is waiting for Process A acuquires Share lock on
> Table IDENTIFIANT
> Process B share lock and subsequent Process A lock on table is
> incompatible ,
> as Process A can not acquire XLOCK on a shared lock table
> In process B try table IDENTIFIANT WITH (nolock ) hint
>
|||On May 25, 2:19 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> Process B should not set a shared locked, because it has not opened any
> transaction.
> --
> Olivier GIL
> LAFON SA
>
> "M A Srinivas" wrote:
>
>
>
>
>
>
> - Show quoted text -
Whether Process B in a transaction OR Not it will acquire SHARE lock
on a ROW/PAGE/TABLE .
|||Hi Olivier,
Per my analysis, in this case, a block may be caused but dead lock should
not be caused since when B get a shared lock, A cannot lock the table until
B finishes the query.
To track the root cause, I recommend that you enable the trace flags -T1204
and -T3605 to the startup parameters and then restart your SQL Server.
Once the issue reoccurs, please post the error logs here or mail it to me
(changliw_at_microsoft_dot_com) for further research.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
--
Olivier GIL
LAFON SAOn May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint|||Hello,
Process B should not set a shared locked, because it has not opened any
transaction.
--
Olivier GIL
LAFON SA
"M A Srinivas" wrote:
> On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> > Hello,
> >
> > I have a deadlock situation that seems odd to me :
> >
> > process A :
> > BEGIN TRANSACTION
> >
> > process A :
> > SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> > FROM IDENTIFIANT
> > WITH (TABLOCKX, HOLDLOCK)
> > => process A locks table IDENTIFIANT
> >
> > process B :
> > exec sp_executesql
> > N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> > I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> > SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> > FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> > SOUS_ALARME SA
> > WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => > A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> > AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> > SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> > C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> > C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> > ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> > datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> > => process B waits because table IDENTIFIANT is locked
> >
> > process A:
> > exec sp_executesql
> > N'SELECT SITE.ID_SITE AS ID
> > FROM SITE
> > WITH (TABLOCKX, HOLDLOCK)
> > WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> > => deadlock
> >
> > It looks as if process B locked table SITE although no transaction is open
> > on process B.
> >
> > I'm using SQLSERVER 2005 EXPRESS SP2.
> >
> > Can anyone explain to me the raison of the deadlock situation ?
> >
> > --
> > Olivier GIL
> > LAFON SA
> My opinion is
> First Process MAX is completed by Process A
> Process B which is waiting for Process A acuquires Share lock on
> Table IDENTIFIANT
> Process B share lock and subsequent Process A lock on table is
> incompatible ,
> as Process A can not acquire XLOCK on a shared lock table
> In process B try table IDENTIFIANT WITH (nolock ) hint
>|||On May 25, 2:19 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> Process B should not set a shared locked, because it has not opened any
> transaction.
> --
> Olivier GIL
> LAFON SA
>
> "M A Srinivas" wrote:
> > On May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> > > Hello,
> > > I have a deadlock situation that seems odd to me :
> > > process A :
> > > BEGIN TRANSACTION
> > > process A :
> > > SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> > > FROM IDENTIFIANT
> > > WITH (TABLOCKX, HOLDLOCK)
> > > => process A locks table IDENTIFIANT
> > > process B :
> > > exec sp_executesql
> > > N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> > > I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> > > SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> > > FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> > > SOUS_ALARME SA
> > > WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT => > > A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
> > > AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> > > SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> > > C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> > > C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> > > ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> > > datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> > > => process B waits because table IDENTIFIANT is locked
> > > process A:
> > > exec sp_executesql
> > > N'SELECT SITE.ID_SITE AS ID
> > > FROM SITE
> > > WITH (TABLOCKX, HOLDLOCK)
> > > WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> > > => deadlock
> > > It looks as if process B locked table SITE although no transaction is open
> > > on process B.
> > > I'm using SQLSERVER 2005 EXPRESS SP2.
> > > Can anyone explain to me the raison of the deadlock situation ?
> > > --
> > > Olivier GIL
> > > LAFON SA
> > My opinion is
> > First Process MAX is completed by Process A
> > Process B which is waiting for Process A acuquires Share lock on
> > Table IDENTIFIANT
> > Process B share lock and subsequent Process A lock on table is
> > incompatible ,
> > as Process A can not acquire XLOCK on a shared lock table
> > In process B try table IDENTIFIANT WITH (nolock ) hint- Hide quoted text -
> - Show quoted text -
Whether Process B in a transaction OR Not it will acquire SHARE lock
on a ROW/PAGE/TABLE .|||Hi Olivier,
Per my analysis, in this case, a block may be caused but dead lock should
not be caused since when B get a shared lock, A cannot lock the table until
B finishes the query.
To track the root cause, I recommend that you enable the trace flags -T1204
and -T3605 to the startup parameters and then restart your SQL Server.
Once the issue reoccurs, please post the error logs here or mail it to me
(changliw_at_microsoft_dot_com) for further research.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Oliver,
Just a kind reminder that I have not received your response. Please feel
free to post back at your convenience if you need further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Deadlock between a transactional request and a non transaction joi

Hello,
I have a deadlock situation that seems odd to me :
process A :
BEGIN TRANSACTION
process A :
SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
FROM IDENTIFIANT
WITH (TABLOCKX, HOLDLOCK)
=> process A locks table IDENTIFIANT
process B :
exec sp_executesql
N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
SOUS_ALARME SA
WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAGE
AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
datetime',@.dateCourante=''2007-05-24 17:50:01:593''
=> process B waits because table IDENTIFIANT is locked
process A:
exec sp_executesql
N'SELECT SITE.ID_SITE AS ID
FROM SITE
WITH (TABLOCKX, HOLDLOCK)
WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
=> deadlock
It looks as if process B locked table SITE although no transaction is open
on process B.
I'm using SQLSERVER 2005 EXPRESS SP2.
Can anyone explain to me the raison of the deadlock situation ?
Olivier GIL
LAFON SAOn May 25, 1:09 pm, Olivier GIL <o...@.newsgroup.nospam> wrote:
> Hello,
> I have a deadlock situation that seems odd to me :
> process A :
> BEGIN TRANSACTION
> process A :
> SELECT MAX(ID_IDENTIFIANT) + 1 as MAX
> FROM IDENTIFIANT
> WITH (TABLOCKX, HOLDLOCK)
> => process A locks table IDENTIFIANT
> process B :
> exec sp_executesql
> N'SELECT DISTINCT(SA.ID_SS_ALARME), SO.NOM_SOCIETE, S.NOM_SITE,
> I.NOM_IDENTIFIANT, SA.BLOQUANTE, A.TYPE_ACTIVATION, SA.VAL_ACTIV_DATE,
> SA.VAL_ACTIV_COMPTEUR, M.LIBELLE_MESSAGE, A.PERIODICITE
> FROM ALARME A, IDENTIFIANT I, SITE S, COMPTEUR C, MESSAGE M, SOCIETE SO,
> SOUS_ALARME SA
> WHERE A.ID_ALARME = SA.ID_ALARME AND SA.ACTIVE = 1 AND I.ID_IDENTIFIANT =
> A.ID_IDENTIFIANT AND S.ID_SITE = I.ID_SITE AND M.ID_MESSAGE = SA.ID_MESSAG
E
> AND S.ID_SOCIETE = SO.ID_SOCIETE AND (( A.TYPE_ACTIVATION = ''D'' AND
> SA.VAL_ACTIV_DATE < @.dateCourante) OR (A.TYPE_ACTIVATION = ''C'' AND
> C.ID_PRODUIT = A.ID_PRODUIT AND C.ID_IDENTIFIANT = A.ID_IDENTIFIANT AND
> C.VALEUR_COMPTEUR>SA.VAL_ACTIV_COMPTEUR)) AND SO.NO_GROUPE = 1
> ORDER BY SA.ID_SS_ALARME',N'@.dateCourante
> datetime',@.dateCourante=''2007-05-24 17:50:01:593''
> => process B waits because table IDENTIFIANT is locked
> process A:
> exec sp_executesql
> N'SELECT SITE.ID_SITE AS ID
> FROM SITE
> WITH (TABLOCKX, HOLDLOCK)
> WHERE ID_SOCIETE = @.idSociete',N'@.idSociete int',@.idSociete=2
> => deadlock
> It looks as if process B locked table SITE although no transaction is open
> on process B.
> I'm using SQLSERVER 2005 EXPRESS SP2.
> Can anyone explain to me the raison of the deadlock situation ?
> --
> Olivier GIL
> LAFON SA
My opinion is
First Process MAX is completed by Process A
Process B which is waiting for Process A acuquires Share lock on
Table IDENTIFIANT
Process B share lock and subsequent Process A lock on table is
incompatible ,
as Process A can not acquire XLOCK on a shared lock table
In process B try table IDENTIFIANT WITH (nolock ) hint