0

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
Improve this question

1 Answer 1

Reset to default
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

Improve this answer
1
  • 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.
    – Adrye
    Commented May 8, 2020 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.