1

Topic: DELETE leads DEADLOCK

Greetings!
Idle time

 DELETE FROM MyTable WHERE ID = @ID 

  to DEADLOCK, because  locks to [b X. [/b]
The matter is that system  so that  parallely a heap similar DELETE commands and they "" with each other for the same table.
Two questions:
1. What <censored> is locked all table, instead of a line if search goes on a key (clustered index)?
2. How it to force without rewriting request to fulfill ROW_LOCK?

2

Re: DELETE leads DEADLOCK

And triggers on removal are not present?

3

Re: DELETE leads DEADLOCK

LSV;
Yes, is  AFTER DELETE and FOR UPDATE.

4

Re: DELETE leads DEADLOCK

Yuri Abele;
1. Escalation to X is a delirium since escalation is reduction  (integration) instead of change of type of lock.
2. At you an index that on ID is?
3. At you "a heap similar DELETE" in one transaction?
To forbid escalation (if it is a problem) at table level
alter table... set (lock_escalation = disable)

5

Re: DELETE leads DEADLOCK

I do not know there are changes  locks in the course of performance, or at once, but locks all table.
The reason is similar clear - at once did not think, the colleague prompted - an index cluster.
I.e. at line delete on this index, correct if I am not right, there is a physical reorganization of the table that demands all its lock. So?

6

Re: DELETE leads DEADLOCK

msLex wrote:

3. At you "a heap similar DELETE" in one transaction?

Different transactions, sessions and even users (security contexts)

7

Re: DELETE leads DEADLOCK

Yuri Abele wrote:

I.e. at line delete on this index, correct if I am not right, there is a physical reorganization of the table that demands all its lock. So?

My God, whence it is you all a beret?

8

Re: DELETE leads DEADLOCK

Yuri Abele wrote:

LSV;
Yes, is  AFTER DELETE and FOR UPDATE.

Also what you in them do?

9

Re: DELETE leads DEADLOCK

I.e. it simply releases pages (i.e. speech about a fragmentation)?

10

Re: DELETE leads DEADLOCK

TaPaK wrote:

it is passed...
Also what you in them do?

Data of absolutely other table changes.
IN AFTER DELETE:

 UPDATE, and then DELETE WHERE SomeFK IN (SELECT ID FROM deleted) 

IN FOR UPDATE:

 UPDATE OtherTable INNER JOIN inserted... 

11

Re: DELETE leads DEADLOCK

Yuri Abele;
And  under the text it is not so assured that you correctly define  locks.

12

Re: DELETE leads DEADLOCK

Yuri Abele wrote:

it is passed...
Data of absolutely other table changes.
IN AFTER DELETE:

 UPDATE, and then DELETE WHERE SomeFK IN (SELECT ID FROM deleted) 

IN FOR UPDATE:

 UPDATE OtherTable INNER JOIN inserted... 

imaginations, the graph  give

13

Re: DELETE leads DEADLOCK

TaPaK;
As on a picture. Quite often at the left in top lock S , and on the right below X

14

Re: DELETE leads DEADLOCK

Both requests of a type

 DELETE FROM SomeTable WHERE ID = ID 

15

Re: DELETE leads DEADLOCK

Yuri Abele wrote:

both requests of a type

 DELETE FROM SomeTable WHERE ID = ID 

on such operations does not arise. Though with forced tablock, all wait.
The graph not  would be necessary

16

Re: DELETE leads DEADLOCK

+ You do not see that in the column different objects??  it roughly telling it is cross were blocked

17

Re: DELETE leads DEADLOCK

TaPaK wrote:

different objects

Tooltip over  at the left and on the right show (those see above)  the same table.
But is also such,  updates in  (see above) other table have been killed:

18

Re: DELETE leads DEADLOCK

In the form of XML now I will pull out.
Still, experiments showed that activation in database SNAPSHOT ISOLATION repeatedly reduced amount DEADLOCK, but at all did not remove.

19

Re: DELETE leads DEADLOCK

Here: Similar that different tables:

<deadlock>
<victim-list>
<victimProcess id = "process2b6d2c81468"/>
</victim-list>
<process-list>
<process id = "process2b6d2c81468" taskpriority = "0" logused = "0" waitresource = "PAGE: 18:1:614134" waittime = "8886" ownerId = "1390589" transactionname = "implicit_transaction" lasttranstarted = "2017-09-08T03:56:17.777" XDES = "0x2b6d2c94e58" lockMode = "S" schedulerid = "4" kpid = "16772" status = "suspended" spid = "77" sbid = "0" ecid = "0" priority = "0" trancount = "1" lastbatchstarted = "2017-09-08T03:56:17.773" lastbatchcompleted = "2017-09-08T03:56:17.740" lastattention = "1900-01-01T00:00:00.740" clientapp = "Microsoft JDBC Driver for A SQL Server" hostname = "bm-deka-si.c1064.brandmaker.net" hostpid = "0" loginname = "deka_si_media1" isolationlevel = "read committed (2)" xactid = "1390589" currentdb = "18" lockTimeout = "4294967295" clientoption1 = "671088672" clientoption2 = "128058">
<executionStack>
<frame procname = "adhoc" line = "1" stmtstart = "94" stmtend = "520" sqlhandle = "0x020000003267b3003f8fbbed8699ca5da6181f591c2f02780000000000000000000000000000000000000000"> unknown </frame>
<frame procname = "unknown" line = "1" sqlhandle = "0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame>
</executionStack>
<inputbuf> (@P0 nvarchar (4000) ,@P1 nvarchar (4000) ,@P2 int) SELECT m. MEDIA_GUID FROM media m LEFT JOIN media_hashes mh ON m. MEDIA_GUID = mh. MEDIA_GUID AND m. VERSION = mh. VERSION_NUMBER WHERE (m. MD5_HASH = @P0 OR mh. MD5_HASH = @P1) AND ISNULL (m. DATABASE_ID,-1) <> @P2 </inputbuf>
</process>
<process id = "process2b6d2c84108" taskpriority = "0" logused = "11432" waitresource = "PAGE: 18:1:614133" waittime = "7787" ownerId = "1387486" transactionname = "implicit_transaction" lasttranstarted = "2017-09-08T03:56:12.770" XDES = "0x2b6d2c958a8" lockMode = "IX" schedulerid = "1" kpid = "6728" status = "suspended" spid = "76" sbid = "0" ecid = "0" priority = "0" trancount = "2" lastbatchstarted = "2017-09-08T03:56:17.157" lastbatchcompleted = "2017-09-08T03:56:17.157" lastattention = "1900-01-01T00:00:00.157" clientapp = "Microsoft JDBC Driver for A SQL Server" hostname = "bm-deka-si.c1064.brandmaker.net" hostpid = "0" loginname = "deka_si_media1" isolationlevel = "read committed (2)" xactid = "1387486" currentdb = "18" lockTimeout = "4294967295" clientoption1 = "671219744" clientoption2 = "128058">
<executionStack>
<frame procname = "c1064_deka_si_media.dbo.asset_delete_after" line = "5" stmtstart = "358" stmtend = "492" sqlhandle = "0x030012005b063637b5b70701d1a7000000000000000000000000000000000000000000000000000000000000"> DELETE FROM media WHERE MEDIA_GUID IN (select ASSET_ID from deleted </frame>
<frame procname = "adhoc" line = "1" stmtstart = "122" stmtend = "192" sqlhandle = "0x0200000075310c17002dff4a97004f3bea783b6fa30f524f0000000000000000000000000000000000000000"> unknown </frame>
</executionStack>
<inputbuf> (@P0 int)/* delete com.brandmaker.mms.mp.domain. AssetBean */delete from asset where ASSET_ID = P0 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid = "1" pageid = "614134" dbid = "18" subresource = "FULL" objectname = "c1064_deka_si_media.dbo. MEDIA" id = "lock2b6c4014900" mode = "IX" associatedObjectId = "72057611050811392">
<owner-list>
<owner id = "process2b6d2c84108" mode = "IX"/>
</owner-list>
<waiter-list>
<waiter id = "process2b6d2c81468" mode = "S" requestType = "wait"/>
</waiter-list>
</pagelock>
<pagelock fileid = "1" pageid = "614133" dbid = "18" subresource = "FULL" objectname = "c1064_deka_si_media.dbo. MEDIA" id = "lock2b6d78ccb80" mode = "SIU" associatedObjectId = "72057611050811392">
<owner-list>
<owner id = "process2b6d2c81468" mode = "S"/>
</owner-list>
<waiter-list>
<waiter id = "process2b6d2c84108" mode = "IX" requestType = "convert"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>

20

Re: DELETE leads DEADLOCK

Yuri Abele wrote:

In the form of XML now I will pull out.
Still, experiments showed that activation in database SNAPSHOT ISOLATION repeatedly reduced amount DEADLOCK, but at all did not remove.

.  you untied readers, and there  with S locks at .
Assort objects which you change in triggers. Well and generally understand that such .
the Big picture not is in xml

21

Re: DELETE leads DEADLOCK

Yuri Abele;

wrote:

Here: Similar that different tables:

To receive deadlock on one object it is possible, but it at all your case

22

Re: DELETE leads DEADLOCK

TaPaK wrote:

to receive deadlock on one object it is possible, but it at all your case

23

Re: DELETE leads DEADLOCK

Yuri Abele;
It  on different operations with object. You declare that at you DELETE and all. Look at the triggers

24

Re: DELETE leads DEADLOCK

Yuri Abele;
that you resulted that type in a type xml the reader-writer should leave as a matter of fact after switching on RCSI, there at you struggle.

25

Re: DELETE leads DEADLOCK

I at all do not understand, how at RCSI can to DEDALOCK result.
Someone could explain to me on any simple examples?