deAttach database: DeadLock - INSERT with foreign keys

Hi,

In my database I have 2 tables - Batch & Device and a stored procedure - AddBatchDevice which accepts a XML input as follows –

And inserts one record in Batch Table and N number of records as specified in the Device table.

<Size/>

<HwId/>

</Device>

<HwId/>

</Device>

</Devices>

</BatchDevice>

When I call the stored procedure from multiple instances - its getting into deadlock (KeyLock) on the INSERT Statement (for Device table).

If the foreign key constraint between Batch and Device table is deleted – the deadlock doesn’t happen but I cannot remove the foreign key constraint.

Please let me know if there are other ways to resolve this deadlock.

Steps for repro:

1) Create a test Database in the server

2) Run the code snippet Create.sql on the test database (it will create 2 tables and one stored procedure)

3) Open two query windows for the test Database and execute the command listed in code snippet deadlock.sql simultaneously.

Code Snippet - Create.sql

Code Snippet

CREATE TABLE [dbo].[Batch](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BatchGuid] [uniqueidentifier] NOT NULL,
[Size] [int] NOT NULL
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [ukBatchGuid_Batch] UNIQUE NONCLUSTERED
(
[BatchGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Device](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Hwid] [bigint] NOT NULL,
[BatchId] [int] NOT NULL,
CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [IX_Device_BatchID] ON [dbo].[Device]
(
[BatchId] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

ALTER TABLE [dbo].[Device] WITH CHECK ADD CONSTRAINT [FK_Device_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([Id])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Batch]
GO

CREATE PROCEDURE [dbo].[AddBatchDevice]
@.batchDeviceXML XML
AS
BEGIN

DECLARE @.batchId INT
DECLARE @.batchSize INT
DECLARE @.err INT
DECLARE @.rowCount INT
DECLARE @.itc INT

SET @.rowCount = 0
SET @.err = 1
SET @.itc = @.@.TRANCOUNT

SET NOCOUNT ON;

BEGIN TRY
IF (@.itc = 0) BEGIN TRANSACTION;

-- Populate the Batch Table

INSERT INTO dbo.Batch(BatchGuid, Size)
SELECT
BatchDetails.value('BatchGuid[1]','UNIQUEIDENTIFIER'),
BatchDetails.value('Size[1]','INT')
FROM @.batchDeviceXML.nodes('/BatchDevice') as R(BatchDetails)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT, @.batchId = SCOPE_IDENTITY()

-- Check if the Batch got added.
IF (@.rowCount = 0) OR (@.err <> 0)
BEGIN
SET @.err = 13
GOTO ROLLBACKTRAN;
END

-- Retrieve Batch Size for the current Batch
SELECT @.batchSize = Size FROM dbo.Batch WHERE Id = @.batchId;

-- Populate the Device table
INSERT INTO dbo.Device (HwId, BatchId)
SELECT x.value('./HwId[1]','BIGINT') AS HwId,
@.batchId
FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT

-- Check if the BatchSize is same as number of Device Added.
IF (@.batchSize <> @.rowCount)
BEGIN
SET @.err = 6
GOTO ROLLBACKTRAN;
END

COMMITTRAN:
IF (XACT_STATE() = 1) and (@.itc = 0) COMMIT TRAN;
GOTO RETURNPOINT;

ROLLBACKTRAN:
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) and (XACT_STATE() <> 0) ROLLBACK TRAN;

RETURNPOINT:
RETURN @.err;

END TRY
BEGIN CATCH
-- rollback if the transaction is active and start from the proc
-- @.itc is the the initial transaction count when it enters the proc,
-- XACT_STATE() is zero when the transaction inactive
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) AND (XACT_STATE() <> 0)
BEGIN
ROLLBACK TRANSACTION;
END
-- Display error Message
-- EXEC [dbo].[sp_RethrowError];
PRINT ERROR_MESSAGE()

END CATCH

RETURN @.err
END
GO

Code Snippet: Deadlock

Code Snippet

DECLARE @.p1 XML
DECLARE @.guid NVARCHAR(50)

SET @.guid = CONVERT(NVARCHAR(50),newid())

SET @.p1=convert(xml,N'<BatchDevice><BatchGuid>' + @.guid + N'</BatchGuid><Size>100</Size><Devices><Device><HwId>725186448764602445</HwId></Device><Device><HwId>725133527699733597</HwId></Device><Device><HwId>725163621214188850</HwId></Device><Device><HwId>725360424970446632</HwId></Device><Device><HwId>725155285022984412</HwId></Device><Device><HwId>725344818310067191</HwId></Device><Device><HwId>725088231823400156</HwId></Device><Device><HwId>725358312743308913</HwId></Device><Device><HwId>725188965066112420</HwId></Device><Device><HwId>725236470409711434</HwId></Device><Device><HwId>725214869542733619</HwId></Device><Device><HwId>725269236196070583</HwId></Device><Device><HwId>725245337788308830</HwId></Device><Device><HwId>725286270478665392</HwId></Device><Device><HwId>725122001939204270</HwId></Device><Device><HwId>725264967238497755</HwId></Device><Device><HwId>725278832846587913</HwId></Device><Device><HwId>725320615075719837</HwId></Device><Device><HwId>725304873858777678</HwId></Device>

<Device><HwId>725330167025361822</HwId></Device></Devices></BatchDevice>')

EXEC dbo.[AddBatchDevice] @.BatchDeviceXML=@.p1

Go 1000

From looking at the code the deadlock should be coming from the non-clustered index on the device table. What I have seen in the past is that sql server will take a more agressive lock, for instance a page lock instead of a row lock. If you run a sql profile trace and capture the locks being acquired you will likely see that a higher lock is being taken. My suggestions as a quick fix is to use the with clause on the insert statement providing the rowlock directive. The syntax is INSERT INTO TableA WITH (ROWLOCK) ...

HTH.

-Chris

|||

I tried adding WITH (ROWLOCK) for the insert statement - stilll deadlocks are occuring.

Please let me know if you have any other suggestions.

Thanks,
Loonysan

|||

You need to set the isolation level to SNAPSHOT. In order to do so you must first set the ALLOW_SNAPSHOT_ISOLATION database option to ON. Here is the command to do this:

Code Snippet

ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON

Next, add this statement at the beginning of your procedure:

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

I hope this solves your problem.

Best regards,

Sami Samir

|||

I looked at the execution plan of the insert statement into the device table and the foreign key lookup was doing a scan and a merge join. This was what was causing the issue. I have seen this before when the datatypes do not match, for example int to bigint, but that is not the case with your code. I believe it has something to do with the xml function that is causing the problems. I modified the code to parse the xml and put the results in a table variable and then do the insert and I did not receive any deadlocks. Let me know if you have the same results on your side. Below is the code snippet. Thanks.

-Chris

-- Populate the Device table

declare @.myTable TABLE (hwID bigint,BatchID int);

INSERT INTO @.myTable (HwId, BatchId)

SELECT x.value('./HwId[1]','BIGINT') AS HwId,

@.batchId

FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

INSERT INTO dbo.Device (HwId, BatchId)

SELECT hwID,BatchID

FROM @.myTable;

|||

Hi Chris,

Your logic works

Thanks a lot.

- Loonysan

Sunday, March 11, 2012

DeadLock - INSERT with foreign keys

No comments:

Post a Comment

deAttach database

Blog Archive

About Me