SQL Server Deadlock OPENXML insert

Question

I am running into a deadlock issue when I call a stored procedure in Sql Server 2014 Enterprise Edition. An XML string is passed into the stored procedure and can be called many times in quick succession, from different connections.

The problem I am running into is that if the procedure is called from 2 different connections at the same time, one call is rolled back and the other call is committed to the database due to the deadlock. The code for the procedure uses OPENXML to parse de XML string and insert into table CTACTES, then take the primary key from table CTACTES (cueprefi, codctacte) and insert multiple child records into other several tables (CTACTESCMPS, CTACTESCONDICIONES, CTACTESCONT, CTACTESCUEN and CTACTESEMP). All this tables have a foreign key that references CTACTES table primary key.

The error message is:

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

Using the profiler I was able to detect that the lock is an index lock on the CTACTES table primary key. The only thing I’ve been able to get to work is relaxing the foreign key constraints.

Any guidance on how to resolve this issue would be much appreciated.

Tables

 CREATE TABLE [dbo].[CTACTES](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [NOMBRE] [char](60) NOT NULL,
 [CODCLIEMP] [char](10) NULL,
 [codempalta] [smallint] NOT NULL DEFAULT (1),
 CONSTRAINT [PK_CTACTES] PRIMARY KEY CLUSTERED 
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC
)
)
GO
CREATE TABLE [dbo].[CTACTESCMPS](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [CODCMP] [char](2) NOT NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [CTACTESCMPS_CODCMP] ON [dbo].[CTACTESCMPS]
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [CODCMP] ASC
)
GO
ALTER TABLE [dbo].[CTACTESCMPS] ADD CONSTRAINT [FK_CTACTESCMPS_CTACTES] FOREIGN KEY([CUEPREFI], [CODCTACTE])
REFERENCES [dbo].[CTACTES] ([CUEPREFI], [CODCTACTE])
GO
CREATE TABLE [dbo].[CTACTESCONDICIONES](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [CODCONDI] [char](3) NOT NULL,
 CONSTRAINT [PK_CTACTESCONDICIONES] PRIMARY KEY CLUSTERED 
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [CODCONDI] ASC
)
)
GO
ALTER TABLE [dbo].[CTACTESCONDICIONES] ADD CONSTRAINT [FK_CTACTESCONDICIONES_CTACTES] FOREIGN KEY([CUEPREFI], [CODCTACTE])
REFERENCES [dbo].[CTACTES] ([CUEPREFI], [CODCTACTE])
GO
CREATE TABLE [dbo].[CTACTESCONT](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [NOMBRE] [char](60) NULL,
 timestamp timestamp not null,
 CONSTRAINT [PK_CTACTESCONT] PRIMARY KEY NONCLUSTERED 
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [TIMESTAMP] ASC
)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [CTACTESCONT_NOMBRE] ON [dbo].[CTACTESCONT]
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [NOMBRE] ASC
)
GO
ALTER TABLE [dbo].[CTACTESCONT] ADD CONSTRAINT [FK_CTACTESCONT_CTACTES] FOREIGN KEY([CUEPREFI], [CODCTACTE])
REFERENCES [dbo].[CTACTES] ([CUEPREFI], [CODCTACTE])
GO
CREATE TABLE [dbo].[CTACTESCUEN](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [CODCUE] [int] NOT NULL,
 CONSTRAINT [PK_CTACTESCUEN] PRIMARY KEY CLUSTERED 
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [CODCUE] ASC
)
)
GO
CREATE NONCLUSTERED INDEX [CTACTESCUEN_CODCTACTE] ON [dbo].[CTACTESCUEN]
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC
)
GO
ALTER TABLE [dbo].[CTACTESCUEN] ADD CONSTRAINT [FK_CTACTESCUEN_CTACTES] FOREIGN KEY([CUEPREFI], [CODCTACTE])
REFERENCES [dbo].[CTACTES] ([CUEPREFI], [CODCTACTE])
GO
CREATE TABLE [dbo].[CTACTESEMP](
 [CUEPREFI] char(1) NOT NULL,
 [CODCTACTE] [char](8) NOT NULL,
 [CODEMP] [smallint] NOT NULL,
 CONSTRAINT [PK_CTACTESEMP] PRIMARY KEY CLUSTERED 
(
 [CUEPREFI] ASC,
 [CODCTACTE] ASC,
 [CODEMP] ASC
)
)
GO
ALTER TABLE [dbo].[CTACTESEMP] ADD CONSTRAINT [FK_CTACTESEMP_CTACTES] FOREIGN KEY([CUEPREFI], [CODCTACTE])
REFERENCES [dbo].[CTACTES] ([CUEPREFI], [CODCTACTE])
GO

Stored procedure

CREATE PROCEDURE [dbo].[SP_CREATE] @xml XML AS
SET NOCOUNT ON
DECLARE
@codctacte varchar(8), 
@msgerror varchar(255)='00',
@cueprefi char(1),
@iddoc integer
If @xml is null
 GOTO FIN
select @cueprefi='C'
begin try
 EXEC sp_xml_preparedocument @iddoc OUTPUT, @xml 
 SELECT @codctacte=CODCTACTE
 FROM OPENXML (@iddoc, '/Cliente', 2)
 WITH (CODCTACTE varchar(8) 'Codigo[.!=""]')
 BEGIN TRANSACTION
 -- Ctactes
 INSERT INTO CTACTES (CUEPREFI,Codcliemp,CODCTACTE,Codempalta,Nombre)
 SELECT @cueprefi,Codcliemp,CODCTACTE,Codempalta,Nombre
 FROM OPENXML (@iddoc, '/Cliente', 2)
 WITH (Codcliemp varchar(10) 'NuestroCodigoProve[.!=""]',
 CODCTACTE varchar(8) 'Codigo[.!=""]',
 Codempalta smallint 'EmpresaAlta[.!=""]',
 Nombre varchar(60) 'RazonSocial[.!=""]') 
 -- Suspendidos
 INSERT INTO CTACTESCMPS(Codcmp,CODCTACTE,CUEPREFI)
 SELECT Codcmp,CODCTACTE,@cueprefi
 FROM OPENXML (@iddoc, '/Cliente/ComprobantesSuspendidos/ExceptoComprobante', 2)
 WITH (Codcmp varchar(2) 'Comprobante[.!=""]',
 CODCTACTE varchar(8) '../../Codigo[.!=""]')
 -- Condiciones
 INSERT INTO CTACTESCONDICIONES(Codcondi,CODCTACTE,CUEPREFI)
 SELECT Codcondi,CODCTACTE,@cueprefi
 FROM OPENXML (@iddoc, '/Cliente/CondicionesVenta/CondicionVenta', 2)
 WITH (Codcondi varchar(3) 'Codigo[.!=""]',
 CODCTACTE varchar(8) '../../Codigo[.!=""]') 
 -- Contactos
 INSERT INTO CTACTESCONT (Nombre,CODCTACTE,CUEPREFI)
 SELECT Nombre,CODCTACTE,@cueprefi
 FROM OPENXML (@iddoc, '/Cliente/Contactos/Contacto', 2)
 WITH (Nombre varchar(60) 'Nombre[.!=""]',
 CODCTACTE varchar(8) '../../Codigo[.!=""]') 
 -- Imputaciones contables
 INSERT INTO CTACTESCUEN(Codcue,CODCTACTE,CUEPREFI)
 SELECT Codcue,CODCTACTE,@cueprefi
 FROM OPENXML (@iddoc, '/Cliente/CuentasCorrientes/CuentaCorriente', 2)
 WITH (Codcue int 'ImputacionContable[.!=""]',
 CODCTACTE varchar(8) '../../Codigo[.!=""]') 
 -- Empresas
 INSERT INTO CTACTESEMP(Codemp,CODCTACTE,CUEPREFI)
 SELECT Codemp,CODCTACTE,@cueprefi
 FROM OPENXML (@iddoc, '/Cliente/Empresas/Empresa', 2)
 WITH (Codemp smallint 'Codigo[.!=""]',
 CODCTACTE varchar(8) '../../Codigo[.!=""]') 
 COMMIT TRANSACTION
end try
begin catch
 SELECT @msgerror=rtrim(ERROR_MESSAGE()) + '(' + ERROR_PROCEDURE() + ')'
 ROLLBACK TRANSACTION
 GOTO FIN
end catch 
FIN:
If @iddoc is not null
 EXEC sp_xml_removedocument @iddoc
IF @msgerror<>'00'
 RAISERROR('%s', 16, 10, @msgerror)
Else
 SELECT @codctacte

XML

<Cliente>
 <Codigo>CLI1</Codigo>
 <RazonSocial>Customer</RazonSocial>
 <NuestroCodigoProve></NuestroCodigoProve>
 <EmpresaAlta>1</EmpresaAlta>
 <ComprobantesSuspendidos>
 <ExceptoComprobante>
 <Comprobante>FC</Comprobante>
 </ExceptoComprobante>
 </ComprobantesSuspendidos>
 <CondicionesVenta>
 <CondicionVenta>
 <Codigo>2</Codigo>
 </CondicionVenta>
 </CondicionesVenta>
 <Contactos>
 <Contacto>
 <Nombre>aaa</Nombre>
 </Contacto>
 </Contactos>
 <CuentasCorrientes>
 <CuentaCorriente>
 <ImputacionContable>1250</ImputacionContable>
 <PorDefecto>S</PorDefecto>
 </CuentaCorriente>
 </CuentasCorrientes>
 <Empresas>
 <Empresa>
 <Codigo>2</Codigo>
 </Empresa>
 <Empresa>
 <Codigo>1</Codigo>
 </Empresa>
 </Empresas>
</Cliente>
<Cliente>
 <Codigo>CLI2</Codigo>
 <RazonSocial>Customer</RazonSocial>
 <NuestroCodigoProve></NuestroCodigoProve>
 <EmpresaAlta>1</EmpresaAlta>
 <ComprobantesSuspendidos>
 <ExceptoComprobante>
 <Comprobante>FC</Comprobante>
 </ExceptoComprobante>
 </ComprobantesSuspendidos>
 <CondicionesVenta>
 <CondicionVenta>
 <Codigo>2</Codigo>
 </CondicionVenta>
 </CondicionesVenta>
 <Contactos>
 <Contacto>
 <Nombre>aaa</Nombre>
 </Contacto>
 </Contactos>
 <CuentasCorrientes>
 <CuentaCorriente>
 <ImputacionContable>1250</ImputacionContable>
 <PorDefecto>S</PorDefecto>
 </CuentaCorriente>
 </CuentasCorrientes>
 <Empresas>
 <Empresa>
 <Codigo>2</Codigo>
 </Empresa>
 <Empresa>
 <Codigo>1</Codigo>
 </Empresa>
 </Empresas>
</Cliente>

Script

set nocount on 
Declare
@xml xml
select @xml='
<Cliente>
 <Codigo>CLI1</Codigo>
 <RazonSocial>Customer</RazonSocial>
 <NuestroCodigoProve></NuestroCodigoProve>
 <EmpresaAlta>1</EmpresaAlta>
 <ComprobantesSuspendidos>
 <ExceptoComprobante>
 <Comprobante>FC</Comprobante>
 </ExceptoComprobante>
 </ComprobantesSuspendidos>
 <CondicionesVenta>
 <CondicionVenta>
 <Codigo>2</Codigo>
 </CondicionVenta>
 </CondicionesVenta>
 <Contactos>
 <Contacto>
 <Nombre>aaa</Nombre>
 </Contacto>
 </Contactos>
 <CuentasCorrientes>
 <CuentaCorriente>
 <ImputacionContable>1250</ImputacionContable>
 <PorDefecto>S</PorDefecto>
 </CuentaCorriente>
 </CuentasCorrientes>
 <Empresas>
 <Empresa>
 <Codigo>2</Codigo>
 </Empresa>
 <Empresa>
 <Codigo>1</Codigo>
 </Empresa>
 </Empresas>
</Cliente>'
exec sp_create @xml

Answer 1

I would start by trying to move the slow and heavy selects to before the transaction block storing the intermediate results in temp tables (or table variables) and just do the inserts within the transaction block. You may also have to change the Transaction Isolation Level to something more restrictive (Serializable ?) https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15

Create table #t (etc etc)
INSERT #t (Codcmp, Codctacte, cueprefi)
SELECT Codcmp,CODCTACTE,@cueprefi
FROM OPENXML (@iddoc, '/Cliente/ComprobantesSuspendidos/ExceptoComprobante', 2)
WITH (Codcmp varchar(2) 'Comprobante[.!=""]',
CODCTACTE varchar(8) '../../Codigo[.!=""]')

BEGIN TRAN INSERT INTO CTACTESCMPS(Codcmp,CODCTACTE,CUEPREFI) SELECT * FROM #t

• Adrye: Thank you for your answer. I've tried this solution previously and knew it worked but I am still curious about the original solution, that doesn't work.