Share via

How to solve deadlock on SQLServer 2019 database with KEY and PAGE LOCK

Anonymous
2024-05-08T21:38:07+00:00

I've a lot of deadlock on the same object of a SQLServer database.

I'm trying to solve the issue.

/****** Object: Index [PK_M6.TBL_PDC_T] ******/

ALTER TABLE [dbo].[M6.TBL_PDC_T] ADD CONSTRAINT [PK_M6.TBL_PDC_T] PRIMARY KEY CLUSTERED

( 

[PDCT\_CENTATT] ASC, 

[PDCT\_DATACONS] ASC, 

[PDCT\_NUMPDC] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

GO

/****** Object: Table [dbo].[M6.TBL_PDC_T] Script Date: 08/05/2024 23:36:16 ******/

CREATE TABLE [dbo].[M6.TBL_PDC_T]( 

[PDCT\_CENTATT] [varchar](10) NOT NULL, 

[PDCT\_DATACONS] [datetime] NOT NULL, 

[PDCT\_NUMPDC] [numeric](10, 0) NOT NULL, 

[PDCT\_TPCAR] [numeric](5, 0) NULL, 

[PDCT\_TPMIS] [numeric](3, 0) NULL, 

[PDCT\_TIPOMEZZO] [numeric](5, 0) NULL, 

[PDCT\_TIPOATM] [numeric](5, 0) NULL, 

[PDCT\_CODATB1] [varchar](10) NULL, 

[PDCT\_CODATB2] [varchar](10) NULL, 

[PDCT\_NUMVIA] [numeric](5, 0) NULL, 

[PDCT\_TARGA1] [varchar](15) NULL, 

[PDCT\_TARGA2] [varchar](15) NULL, 

[PDCT\_MASSACOMPL1] [numeric](15, 5) NULL, 

[PDCT\_MASSACOMPL2] [numeric](15, 5) NULL, 

[PDCT\_CAPACITA1] [numeric](15, 5) NULL, 

[PDCT\_CAPACITA2] [numeric](15, 5) NULL, 

[PDCT\_TARADIC1] [numeric](15, 5) NULL, 

[PDCT\_TARADIC2] [numeric](15, 5) NULL, 

[PDCT\_PUNCAR] [varchar](10) NULL, 

[PDCT\_CODVOLO] [varchar](30) NULL, 

[PDCT\_CODVAGONE] [varchar](30) NULL, 

[PDCT\_CODAUT1] [varchar](10) NULL, 

[PDCT\_BDGAUT1] [varchar](10) NULL, 

[PDCT\_CODAUT2] [varchar](10) NULL, 

[PDCT\_BDGAUT2] [varchar](10) NULL, 

[PDCT\_NOMEAUT1] [varchar](100) NULL, 

[PDCT\_NOMEAUT2] [varchar](100) NULL, 

[PDCT\_CODSOC] [varchar](10) NULL, 

[PDCT\_ORA\_INICAR] [varchar](8) NULL, 

[PDCT\_ORA\_FINCAR] [varchar](8) NULL, 

[PDCT\_ORA\_INIVIAG] [varchar](8) NULL, 

[PDCT\_NUMVIASOC] [varchar](20) NULL, 

[PDCT\_DATAVIAORD] [datetime] NULL, 

[PDCT\_NUMVIAORD] [varchar](10) NULL, 

[PDCT\_NUMRIGAVIAO] [numeric](4, 0) NULL, 

[PDCT\_DATAINS] [datetime] NULL, 

[PDCT\_USERINS] [varchar](50) NULL, 

[PDCT\_USERMOD] [varchar](50) NULL, 

[PDCT\_DATASINCR] [datetime] NULL, 

[PDCT\_FLSINCR] [numeric](2, 0) NULL, 

[PDCT\_STATO] [numeric](5, 0) NOT NULL, 

[PDCT\_TPINVIO] [numeric](5, 0) NOT NULL, 

[PDCT\_STPRT] [numeric](5, 0) NULL, 

[PDCT\_NUMBAIA] [varchar](50) NULL, 

[PDCT\_STATOCONV] [numeric](5, 0) NULL, 

[PDCT\_COD\_VETT] [varchar](10) NULL, 

[PDCT\_RAGSOC\_VETT] [varchar](100) NULL, 

[PDCT\_IND\_VETT] [varchar](100) NULL, 

[PDCT\_LOC\_VETT] [varchar](100) NULL, 

[PDCT\_PROV\_VETT] [varchar](5) NULL, 

[PDCT\_CAP\_VETT] [varchar](5) NULL, 

[PDCT\_PIVACF\_VETT] [varchar](50) NULL, 

[PDCT\_CIVICO\_VETT] [numeric](5, 0) NULL, 

[PDCT\_COD\_ITRASP] [varchar](10) NULL, 

[PDCT\_RAGSOC\_ITRASP] [varchar](100) NULL, 

[PDCT\_IND\_ITRASP] [varchar](100) NULL, 

[PDCT\_LOC\_ITRASP] [varchar](100) NULL, 

[PDCT\_CIVICO\_ITRASP] [numeric](5, 0) NULL, 

[PDCT\_PROV\_ITRASP] [varchar](5) NULL, 

[PDCT\_CAP\_ITRASP] [varchar](5) NULL, 

[PDCT\_PIVACF\_ITRASP] [varchar](50) NULL, 

[PDCT\_CAMPO\_LIBERO\_1] [varchar](50) NULL, 

[PDCT\_CAMPO\_LIBERO\_2] [numeric](10, 0) NULL, 

[PDCT\_CAMPO\_LIBERO\_3] [varchar](50) NULL, 

[PDCT\_CAMPO\_LIBERO\_4] [numeric](10, 0) NULL,

CONSTRAINT [PK_M6.TBL_PDC_T] PRIMARY KEY CLUSTERED

( 

[PDCT\_CENTATT] ASC, 

[PDCT\_DATACONS] ASC, 

[PDCT\_NUMPDC] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'M6.TBL_PDC_T', @level2type=N'COLUMN',@level2name=N'PDCT_ORA_INIVIAG'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'M6.TBL_PDC_T', @level2type=N'COLUMN',@level2name=N'PDCT_CAMPO_LIBERO_1'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', PDCT_CAMPOLIBERO_2 =1 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'M6.TBL_PDC_T', @level2type=N'COLUMN',@level2name=N'PDCT_CAMPO_LIBERO_2'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'M6.TBL_PDC_T', @level2type=N'COLUMN',@level2name=N'PDCT_CAMPO_LIBERO_3'

GO

<deadlock>

 <victim-list>

  <victimProcess id="process16956b7dc28" />

 </victim-list>

 <process-list>

  <process id="process16956b7dc28" taskpriority="0" logused="0" waitresource="KEY: 7:72057594104774656 (90b6445d876b)" waittime="3620" ownerId="401225007" transactionname="SELECT" lasttranstarted="2024-05-08T17:28:30.393" XDES="0x169560a5c08" lockMode="S" schedulerid="5" kpid="3660" status="suspended" spid="140" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2024-05-08T17:28:30.393" lastbatchcompleted="2024-05-08T17:28:30.400" lastattention="1900-01-01T00:00:00.400" clientapp="Core Microsoft SqlClient Data Provider" hostname="ITNADAS66" hostpid="25188" loginname="M6USER_DB" isolationlevel="read committed (2)" xactid="401225007" currentdb="7" currentdbname="mbr06" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

   <executionStack>

    <frame procname="adhoc" line="1" stmtstart="384" stmtend="1118" sqlhandle="0x0200000014179707ffccd408f238abae1d1af2135f89b7f20000000000000000000000000000000000000000">

unknown    </frame>

    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">

unknown    </frame>

   </executionStack>

   <inputbuf>

(@__pdcT_PDCT_CENTATT_0 nvarchar(450),@__pdcT_PDCT_DATACONS_1 datetime2(7),@__p_2 datetime2(7),@__stato_3 decimal(1,0),@__pdcT_PDCT_FLSINCR_4 decimal(1,0),@__pdcT_PDCT_CODSOC_5 nvarchar(4000))SELECT [m].[PDCT_NUMPDC]

FROM [M6.TBL_PDC_T] AS [m]

WHERE (((((([m].[PDCT_CENTATT] = @__pdcT_PDCT_CENTATT_0) AND ([m].[PDCT_DATACONS] <= @__pdcT_PDCT_DATACONS_1)) AND ([m].[PDCT_DATACONS] >= @__p_2)) AND ([m].[PDCT_STATO] = @__stato_3)) AND ([m].[PDCT_FLSINCR] = @__pdcT_PDCT_FLSINCR_4)) AND ([m].[PDCT_STATO] <> 5.0)) AND ([m].[PDCT_CODSOC] = @__pdcT_PDCT_CODSOC_5)   </inputbuf>

  </process>

  <process id="process1695319dc28" taskpriority="0" logused="272" waitresource="PAGE: 7:1:4089552 " waittime="3649" ownerId="401225077" transactionname="user_transaction" lasttranstarted="2024-05-08T17:28:30.650" XDES="0x1695b890460" lockMode="IX" schedulerid="2" kpid="5852" status="suspended" spid="141" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-05-08T17:28:30.660" lastbatchcompleted="2024-05-08T17:28:30.650" lastattention="1900-01-01T00:00:00.650" clientapp=".Net SqlClient Data Provider" hostname="ITNADAS66" hostpid="6320" loginname="M6USER_DB" isolationlevel="read committed (2)" xactid="401225077" currentdb="7" currentdbname="mbr06" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

   <executionStack>

    <frame procname="adhoc" line="1" stmtstart="2422" stmtend="6378" sqlhandle="0x02000000762c4937922f9c4167f98265edc0b5dc20f5be300000000000000000000000000000000000000000">

unknown    </frame>

    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">

unknown    </frame>

   </executionStack>

   <inputbuf>

(@p0 varchar(8000),@p1 datetime,@p2 decimal(10,0),@p3 decimal(5,0),@p4 decimal(3,0),@p5 decimal(5,0),@p6 decimal(5,0),@p7 varchar(8000),@p8 varchar(8000),@p9 decimal(5,0),@p10 varchar(8000),@p11 varchar(8000),@p12 decimal(15,5),@p13 decimal(15,5),@p14 decimal(15,5),@p15 decimal(15,5),@p16 decimal(15,5),@p17 decimal(15,5),@p18 varchar(8000),@p19 varchar(8000),@p20 varchar(8000),@p21 varchar(8000),@p22 varchar(8000),@p23 varchar(8000),@p24 varchar(8000),@p25 varchar(8000),@p26 varchar(8000),@p27 varchar(8000),@p28 varchar(8000),@p29 varchar(8000),@p30 varchar(8000),@p31 varchar(8000),@p32 datetime,@p33 varchar(8000),@p34 decimal(4,0),@p35 datetime,@p36 varchar(8000),@p37 varchar(8000),@p38 datetime,@p39 decimal(2,0),@p40 decimal(5,0),@p41 decimal(5,0),@p42 decimal(5,0),@p43 varchar(8000),@p44 decimal(5,0),@p45 varchar(8000),@p46 varchar(8000),@p47 varchar(8000),@p48 varchar(8000),@p49 varchar(8000),@p50 varchar(8000),@p51 varchar(8000),@p52 decimal(5,0),@p53 varchar(8000),@p54 varchar(8000),@p55 varchar(8000),   </inputbuf>

  </process>

 </process-list>

 <resource-list>

  <keylock hobtid="72057594104774656" dbid="7" objectname="mbr06.dbo.M6.TBL_PDC_T" indexname="PK_M6.TBL_PDC_T" id="lock16d6e3ef080" mode="X" associatedObjectId="72057594104774656">

   <owner-list>

    <owner id="process1695319dc28" mode="X" />

   </owner-list>

   <waiter-list>

    <waiter id="process16956b7dc28" mode="S" requestType="wait" />

   </waiter-list>

  </keylock>

  <pagelock fileid="1" pageid="4089552" dbid="7" subresource="FULL" objectname="mbr06.dbo.M6.TBL_PDC_T" id="lock16dbb6c4b00" mode="S" associatedObjectId="72057594104840192">

   <owner-list>

    <owner id="process16956b7dc28" mode="S" />

   </owner-list>

   <waiter-list>

    <waiter id="process1695319dc28" mode="IX" requestType="wait" />

   </waiter-list>

  </pagelock>

 </resource-list>

</deadlock>

* * Moved from Windows/other

Windows for business | Windows Server | Performance | Application technologies and compatibility

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. To protect privacy, user profiles for migrated questions are anonymized.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-06-11T01:06:51+00:00

    Hello

    Thank you for posting in Microsoft Community forum.

    Based on the description, I understand your question is related to the SQL Server.

    Since there are no engineers dedicated to SQL Server in this forum. in order to be able to get a quick and effective handling of your issue, I recommend that you repost your question in the Q&A forum, where there will be a dedicated engineer to give you a professional and effective reply.

    Here is the link for Q&A forum.

    Questions - Microsoft Q&A

    Click the "Ask a Question" button in the upper right corner to post your question and select "SQL Server" tag.

    Thank you for your understanding and support. If you have any question or concern, please feel free to let us know.

    Have a nice day.

    Best Regards,

    Molly

    0 comments