SQLServer Locked

Question

SQLServer Locked

I have a java application that performs several concurrent CRUD operations in a database. I am adding SQLServer support, but I am having problems with locking while uninstalling. After some investigation, it turned out that the problem could be related to escalating locks on a specific table.

In an attempt to fix this, I decided to make all the tables in question read, to do "for updating" using the UPDLOCK hint to avoid deadlock. However, I still see the problem. I turned on tracking in SQLServer and found the following deadlock error in SQLServer logs:

Counter deadlock .... Printing lock information Waiting schedule>

Node: 1 KEY: 5: 72057594042384384 (54048e7b3828) CleanCnt: 3 Mode: X Flags: 0x0 Grant List 1: Owner: 0x03D08C40 Mode: X Flg: 0x0 Ref: 0 Life: 02000000 SPID: 62 ECID: 0 XactLockInfo: 0x034 62 ECID: 0 Operator type: DELETE String #: 1 Input Buf: Language Event: (@ P0 nvarchar (4000)) remove from part_data, where part_id = @ P0 Requested: ResType: LockOwner Stype: 'OR'Xdes: 0x04B511C8 Mode: U SPID: 60 BatchID: 0 ECID: 0 TaskProxy: (0x058BE378) Value: 0x3d08500 Cost: (0/1296)

Node: 2

: 5: 72057594042384384 (f903d6d6e0ac) CleanCnt: 2 : X : 0x0 0: : 0x03D088A0 : X Flg: 0x0 Ref: 0 Life: 02000000 SPID: 60 ECID: 0 XactLockInfo: 0x04B511EC SPID: 60 ECID: 0 : DELETE #: 1 Input Buf: Language Event: (@P0 nvarchar (4000)) part_data, part_id = @P0 : ResType: LockOwner Stype: 'OR'Xdes: 0x04834250 : U SPID: 62 BatchID: 0 ECID: 0 TaskProxy: (0x047BA378) : 0x3d089e0 : (0/4588)

: ResType: LockOwner Stype: 'OR'Xdes: 0x04B511C8 : U SPID: 60 BatchID: 0 ECID: 0 TaskProxy: (0x058BE378) : 0x3d08500 : (0/1296)

SQLServer , (U) (X) . SQLServer, , , (U) , , , .

, , . -, , , .

, .

EDIT xml :

<deadlock-list>
 <deadlock victim="process989018">
  <process-list>
   <process id="process6aa7a8" taskpriority="0" logused="4844" waitresource="KEY: 5:72057594042384384 (5504bdfb7529)" waittime="9859" ownerId="613553" transactionname="implicit_transaction" lasttranstarted="2009-05-08T11:52:39.137" XDES="0x5fcbc30" lockMode="U" schedulerid="1" kpid="3516" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-05-08T11:52:39.183" lastbatchcompleted="2009-05-08T11:52:39.183" clientapp="jTDS" hostname="LOIRE" hostpid="123" loginname="sa" isolationlevel="read committed (2)" xactid="613553" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x0200000007c76c39efdd8317c6fa7b611b4fd958f05cfcf4">
delete from part_data where part_id =  @P0     </frame>
    </executionStack>
    <inputbuf>(@P0 nvarchar(4000))delete from part_data where part_id = @P0</inputbuf>
   </process>
   <process id="process989018" taskpriority="0" logused="1528" waitresource="KEY: 5:72057594042384384 (5e0405cb0377)" waittime="1250" ownerId="613558" transactionname="implicit_transaction" lasttranstarted="2009-05-08T11:52:39.183" XDES="0x48318f0" lockMode="U" schedulerid="2" kpid="2692" status="suspended" spid="60" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-05-08T11:52:39.183" lastbatchcompleted="2009-05-08T11:52:39.183" clientapp="jTDS" hostname="LOIRE" hostpid="123" loginname="sa" isolationlevel="read committed (2)" xactid="613558" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x0200000007c76c39efdd8317c6fa7b611b4fd958f05cfcf4">
delete from part_data where part_id =  @P0     </frame>
    </executionStack>
    <inputbuf>(@P0 nvarchar(4000))delete from part_data where part_id =  @P0</inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594042384384" dbid="5" objectname="MESSAGESTOREDB61.dbo.part_data" indexname="idx_part_data_part_id" id="lock3cab740" mode="X" associatedObjectId="72057594042384384">
    <owner-list>
     <owner id="process6aa7a8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process989018" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594042384384" dbid="5" objectname="MESSAGESTOREDB61.dbo.part_data" indexname="idx_part_data_part_id" id="lock3cad340" mode="X" associatedObjectId="72057594042384384">
    <owner-list>
     <owner id="process989018" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6aa7a8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

+5

spring sql-server jdbc deadlock transactions

Brad 08 '09 8:37

7

Nathan Feger · Answer 1 · 2009-06-11T12:48:41+0000

.

, , , , , . , .

, , sql , .

- , where.

Frans Bouma · Answer 2 · 2009-05-08T13:37:10+0000

deadlocks SQLServer , , , . , , , , . , , , - ( ) , , .

(-)

(, INSERT, UPDATE) Foo
( ) Foo, DEADLOCK, ,

massimogentilini · Answer 3 · 2009-11-05T23:44:12+0000

, SQL Server .

Secondo , ( - ).

-, - , , .

, - , .

SQL Server, , , , SQL Server 2000 .

Massimo

KM. · Answer 4 · 2009-05-08T13:07:00+0000

, - : DBCC TRACEON (1222, -1) "/" DBCC TRACEON (1204, -1). , SQLServer . , (X) ?

( ), " " ( ), ( " " ") . xml, , . //etc .., . , DELETE, col = @ , , - ?

" " , , , . , , .

MyItchyChin · Answer 5 · 2009-07-20T18:36:01+0000

... " UPDLOCK, ...
... , , , . , , ...

, , , . , , .

...

, , SQL . , SQL Server Row, Key-Range Table Locks. , , , .

, , TABLOCK delete , .

SQL-, , .

Ivandolchevic · Answer 6 · 2013-12-27T14:54:09+0000

, .

tomcat -. , , - . IX, , , .

, . . sql , , " ".

Ambareesh Surendran · Answer 7 · 2016-11-30T09:24:37+0000

, delete. SPID

delete from part_data where part_id = @P0

, , . , SQLServer / part_data, part_id, .

,

part_id ( )
. (, )
Select primary_key into #temp From part_data where part_id = @p0
- ,
delete a from part_data a join #temp b on a.primary_key = b.primary_key

- ,

SQLServer Locked

More articles: