ansaurus

Question

Conversion Deadlock problem, please help!

Answer 1

+1 A:

First off, if you can post the procedure code, table schema, and index structure it would be very helpful in determining what is going on specifically.

Next thing to note is that you are using serializable transactions, the most restrictive form of pessimistic locking (the session's isolation level is seen in the deadlock graph output process info list). Chances are, you don't need this - if you are using the .NET TransactionScope library, I believe they use Serializable by default and you'll want to explicitly specify the appropriate Isolation Level. If you do need the semantics of a serializable transaction for some reason, take a look at Snapshot Isolation instead, which is an optimistic form of concurrency which supports the serialized semantics. This is almost certainly playing a part in your deadlock issue here, which I explain further below.

As for the deadlock in your case - you mention in your question that to avoid deadlocks you explicitly select with an xlock,rowlock from the [cmsreceiptarchive] table in the procedure before the insert to [cmsreceipt] which fires a trigger to update the [cmsreceiptarchive] table (I'm not going to get in to whether or not this is the correct approach here because we can't see the code or the scenario, but this would most likely be unnecessary). Back to the problem at hand - in this case you aren't getting a deadlock on the [cmsreceiptarchive] table/indexes, you are getting a deadlock on the [cmsreceipts] table itself at the point of the insert, so the fact that you are performing the select against the [cmsreceiptarchive] actually has nothing to do with this particular deadlock. Interpreting the deadlock graph to a more simplistic approach:

SPID 57 is running (line 43 of procedure XXX.dbo.Main_InsertCmsReceipt):
    insert into CmsReceipt with (updlock) ( CmsReceiptId, ModifiedAt, ModifiedBy, CmsMessageId, Status, Details, ReceiptTimestamp, SenderName, SenderId ) 
    values ( @New_CmsReceiptId, @New_ModifiedAt, @New_ModifiedBy, @New_CmsMessageId, @New_Status, @New_Details, @New_ReceiptTimestamp, @New_SenderName, @New_SenderId )

    * HOLDS a Shared lock on dbo.CmsReceipt

    * WAITING for an IX lock (convert from the S lock) on dbo.CmsReceipt
     (SPID 69 holds a conflicting Shared Object)

SPID 69 is running (line 43 of procedure XXX.dbo.Main_InsertCmsReceipt):
    insert into CmsReceipt with (updlock) ( CmsReceiptId, ModifiedAt, ModifiedBy, CmsMessageId, Status, Details, ReceiptTimestamp, SenderName, SenderId ) 
    values ( @New_CmsReceiptId, @New_ModifiedAt, @New_ModifiedBy, @New_CmsMessageId, @New_Status, @New_Details, @New_ReceiptTimestamp, @New_SenderName, @New_SenderId )

    * HOLDS a Shared lock on dbo.CmsReceipt

    * WAITING for an IX lock (convert from the S lock) on dbo.CmsReceipt
     (SPID 57 holds a conflicting Shared Object)

As you can see, there is no mention of the [cmsreceiptarchive] table. You have 2 spids that each hold an object-level Shared lock on the [cmsreceipt] table - this is most likely (can't tell for sure without the code) due to the combination of 2 things:

You are using a serializable isolation model, which will hold shared locks for the duration of the transaction (as opposed to the duration of a particular statement within a transaction as would be the case in a read-committed type isolation mode)
You are doing something earlier in the procedure/code to require a table-level shared lock (likely a table scan, or large block scan touching enough rows to escalate to the table-level lock vs. a range lock as you'd typically see in a serializable transaction).

Unless we can see the code in the procedure and possibly the table schema including indexes, this is probably the best guess/info I can give you. If you can post the procedure code, table schema, and index structure, should be able to easily determine what is going on specifically.

As for interpreting your deadlock output, Bart Duncan has a 3-part series on deciphering deadlock output which is a highly recommended read (and is what I used here and typically always) to help understand/decipher what is going on. You can also see an overview of concurrency, isolation models and the effect on standard DML operations along with demo scripts in here.

EDIT: Adding response to new questions posed in an answer

Ok, couple of things we need to set straight from your new question:

DBCC USEROPTIONS isn't a database level context, it's a session (i.e. spid, connection) level context - what you see returned from this is specific to the session it is run in (in this case, your connection in SSMS or whatever you are running it from. READ COMMITTED is most likely the default isolation level (unless that's been changed), however the spids involved in your deadlock scenario ARE using the SERIALIZABLE isolation model (it's shown in the output above).
You are mis-interpreting what SQL_Menace is saying in the link you posted - he is saying that the code inside the procedure will run at the serializable isolation model for inline code in a session outside the procedure execution because it is explicitly set to that level within the procedure, not because it is that way by default. To try and better explain, here is the exact example he used with some additional commentary to help understand

Code sample taken from here, added some commentary:

use tempdb;
go

-- Create a test procedure to demonstrate with
create proc usp_test
as
-- Set the isolation level to read uncommitted - this will be the level used
-- for the duration of the procedure execution and any code within this procedure
-- unless explicitly set otherwise via another set statement or query hints
set transaction isolation level read uncommitted;

-- This will show you that the isolation level is 1, which is equivalent
-- to read uncommitted
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;
go


-- Now, run some code (what SQL_Menace is referring to as *inline* code)


-- Check the current isolation level, should be the default, which is 
-- by default READ COMMITTED (equivalent to 2)
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;

-- Explicitly set the isolation level to something else, serializable. This
-- will set the isolation method to serializable for this session and any
-- code executed in this context, unless explicitly set to something else
set transaction isolation level serializable;

-- Take another look at the isolation level - now will be 4, serializable
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;

-- Execute the stored procedure - note that within the stored procedure's
-- context, the isolation level is running at 1 (read uncommitted)
exec usp_test;

-- Check the isolation level in this session/context again - note that it
-- is again running under the serializable isolation level, since the
-- read uncommitted level only applies for the duration of the procedure
-- code context
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;

-- Repeat the same tests using a different isolation level - it isn't
-- always serializable, it is whatever the session is set to, which can
-- be the default or whatever you explicitly set it to
set transaction isolation level repeatable read;

-- Now it is 3 (repeatable read)...
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;

-- Still going to be 1 within the procedure
exec usp_test;

-- Back to 3 again (repeatable read)
select transaction_isolation_level, session_id 
from sys.dm_exec_sessions
where session_id = @@spid;

go


-- Cleanup
drop procedure usp_test;
go

Ok, now back to the deadlock situation. As I mentioned above, your deadlock is occurring on the [CmsReceipt] table, not the [CmsMessageUnarchived] table, so the dummy select you are doing prior to the insert into the [CmsReceipt] table has nothing to do with the deadlock (or most likely does not) - the deadlock is on the CmsReceipt table, not the Unarchived table.

Can you also post the code that is included in the trigger so we can see what you are doing within the trigger that might be affecting things (i.e. is it an instead-of trigger vs. an for/after trigger)? Additionally, is there any code run within the same session prior to the execution of the stored procedure in question?

chadhoc 2009-11-25 15:30:58

Hi chadhoc, answered your post above as there wasn't enough room to post everything in a comment. Thanks again.

Defdaz 2009-11-26 15:36:12

Answer 2

A:

Hi chadhoc,

First off, thank you very much indeed for your answer - it is most appreciated.

You have confirmed what I thought, that the shared locks were prenting the IX locks and thus deadlocking but my question is why the shared locks are happening (and even the IX locks) since I'm giving xlock or updlock hints. I've included the proc, table schema and indexes below and as you'll see the proc is very simple. I don't understand why an insert statement is causing a shared lock and keeping it long enough for another SPID to do the same.

I checked the isolation level on the database using

DBCC USEROPTIONS

GO

and it shows that it is set to read committed, the same as with the other databases where deadlocking isn't occuring.

Set Option Value

isolation level read committed

According to SQL_Menace (see link below), any inline code in a proc gets ran at serializable (4)? http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/54dafdab-fb18-4d47-b607-e4c661726574

Thanks again for your help, I hope the extra information below helps as I'm completely stuck as to why the shared locks are occuring despite hints. There seems to be little on the net about locking sequences when using hints?

Best regards,

Daz

ALTER PROCEDURE [dbo].[Main_InsertCmsReceipt]
@Val_ModifiedAt datetime,
@Val_ModifiedBy int,
@New_CmsReceiptId uniqueidentifier,
@New_ModifiedAt datetime output,
@New_ModifiedBy int output,
@New_CmsMessageId uniqueidentifier,
@New_Status nvarchar(20),
@New_Details nvarchar(2000),
@New_ReceiptTimestamp datetime,
@New_SenderName nvarchar(50),
@New_SenderId nvarchar(50)

AS

set nocount on declare @error int

-- propagate ModifiedAt/ModifiedBy back to caller's dataset set @New_ModifiedAt = @Val_ModifiedAt set @New_ModifiedBy = @Val_ModifiedBy

-- there is a trigger on the CmsReceipt table that updates CmsMessageUnarchived -- to avoid deadlocks go ahead and get an update lock on the row in CmsMessageUnarchived first --begin tran /* declare @dummy1 uniqueidentifier set @dummy1 = ( select top 1 CmsReceiptId from CmsReceipt with (updlock) --where CmsMessageId = @New_CmsMessageId ) */ declare @dummy2 uniqueidentifier set @dummy2 = ( select CmsMessageId from CmsMessageUnarchived with (updlock, rowlock) where CmsMessageId = @New_CmsMessageId )

insert into CmsReceipt with (updlock) ( CmsReceiptId, ModifiedAt, ModifiedBy, CmsMessageId, Status, Details, ReceiptTimestamp, SenderName, SenderId ) values ( @New_CmsReceiptId, @New_ModifiedAt, @New_ModifiedBy, @New_CmsMessageId, @New_Status, @New_Details, @New_ReceiptTimestamp, @New_SenderName, @New_SenderId ) --commit tran

return @@error

CREATE TABLE [dbo].CmsReceipt NOT NULL, [Details] nvarchar NULL, [ReceiptTimestamp] [datetime] NOT NULL, [SenderName] nvarchar NOT NULL, [SenderId] nvarchar NOT NULL, CONSTRAINT [PK_CmsReceipt] PRIMARY KEY CLUSTERED ( [CmsReceiptId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY]

GO ALTER TABLE [dbo].[CmsReceipt] WITH NOCHECK ADD CONSTRAINT [FK_CmsReceipt_CmsMessage] FOREIGN KEY([CmsMessageId]) REFERENCES [dbo].[CmsMessage] ([CmsMessageId]) GO ALTER TABLE [dbo].[CmsReceipt] NOCHECK CONSTRAINT [FK_CmsReceipt_CmsMessage] GO ALTER TABLE [dbo].[CmsReceipt] WITH CHECK ADD CONSTRAINT [FK_CmsReceipt_Session] FOREIGN KEY([ModifiedBy]) REFERENCES [dbo].[Session] ([SessionId]) GO ALTER TABLE [dbo].[CmsReceipt] CHECK CONSTRAINT [FK_CmsReceipt_Session]

Defdaz 2009-11-26 14:51:25

See new comments in answer below - would help to see the trigger code as well...

chadhoc 2009-11-30 16:16:22

Generally edit your question to provide more info if it's this much, it helps people who are having the a similar problem later understand what's going on.

Donnie 2009-11-30 20:14:22

ansaurus

tags:

views:

answers:

Conversion Deadlock problem, please help!

EDIT: Adding response to new questions posed in an answer

related questions