Go to Main page..

Deadlocks with Intent Locks Objects , Microsoft SQL Server (Sql.ru)





Deadlocks with Intent Locks Objects [new]
Good day.

I help developers with the definition of the causes of deadlocks. It was originally similar to the classics: a problem with one of the tables, two pages in it, two queries.
The first request with Insert and Select blocks the first page and wants the second, and the second request has blocked the second page and wants the first. Standard deadlock, there were no questions.

This part was reworked, but the problem remained. Now at deadlock on the same table two select each other.

Data collected through Extended Events. I attach the scheme.
According to the scheme, blocking the first page does for some reason not S, but IX and SIX, which is redundant for caught queries. As far as I understand, to solve a problem, you need to understand why it is not an S lock, and if possible, change this point. But for now I see no reason.

The problem table "n_order_marketing_complex_service" is one of the three tables participating in the join.

Request victim:

 @P1 nvarchar ( 4000 )) ( @ P1 nvarchar ( 4000 ))
             SELECT
                 1 flow_doctype ,
                 ote .  order_num flow_id ,
                 ote .  order_num order_num ,
                 ( omcs . service_id IS NULL , od . serv_id , omcs . service_id ) AS serv_id , IIF ( omcs . Service_id IS NULL , od . Serv_id , omcs . Service_id ) AS serv_id ,
                 , 1 count ,
                 ( omcs . service_id IS NULL , od . price , omcs . price ) AS price , IIF ( omcs . Service_id IS NULL , od . Price , omcs . Price ) AS price ,
                 1 flow_type
                 ote .  DateReg flow_date ,
                 ( omcs . service_id IS NULL , od . total , omcs . total ) AS total , IIF ( omcs . Service_id IS NULL , od . Total , omcs . Total ) AS total ,
                 od .  bonuses ,
                 od .  cito_factor
                 ote .  OrderKontragentID sender_id ,
                 ote .  OrderDiscount discount ,
                 ( omcs . service_id IS NULL , od . outlet_price , dbo . GetPrice ( ote . reg_point , omcs . service_id , ote . DateIns )) outlet_price , IIF ( omcs . Service_id IS NULL , od . Outlet_price , dbo . GetPrice ( ote . Reg_point , omcs . Service_id , ote . DateIns )) outlet_price ,
                 ( omcs . service_id IS NULL , od . order_service_id , CONCAT ( od . order_num , '/' , omcs . service_id )) AS order_service_id , IIF ( omcs . Service_id IS NULL , od . Order_service_id , CONCAT ( od . Order_num , '/' , omcs . Service_id )) AS order_service_id ,
                 omcs .  complex_id
             n_OrderDetail od FROM n_OrderDetail od
                 JOIN OrdersToExport ote ON ote . LEFT JOIN OrdersToExport ote ON ote .  order_num = od .  order_num
                 JOIN n_order_marketing_complex_service omcs ON omcs . LEFT JOIN n_order_marketing_complex_service omcs ON omcs .  order_num = od .  omcs . order_num AND omcs .  complex_id = od .  serv_id
             ote . WHERE ote .  order_num = @ P1


Remaining request:
 @P1 nvarchar ( 8 )) SELECT 1 flow_doctype , ote . ( @ P1 nvarchar ( 8 )) SELECT 1 flow_doctype , ote .  ote . order_num flow_id , ote .  IIF ( omcs . service_id IS NULL , od . serv_id , omcs . service_id ) AS serv_id , 1 count , IIF ( omcs . service_id IS NULL , od . price , omcs . price ) AS price , 1 flow_type , ote . order_num order_num , IIF ( omcs . service_id IS NULL , od . serv_id , omcs . service_id ) AS serv_id , 1 count , IIF ( omcs . service_id IS NULL , od . price , omcs . price ) AS price , 1 flow_type , ote .  IIF ( omcs . service_id IS NULL , od . total , omcs . total ) AS total , od . DateReg flow_date , IIF ( omcs . Service_id IS NULL , od . Total , omcs . Total ) AS total , od .  od . bonuses , od .  ote . cito_factor , ote .  ote . OrderKontragentID sender_id , ote .  IIF ( omcs . service_id IS NULL , od . outlet_price , dbo . GetPrice ( ote . reg_point , omcs . service_id , ote . DateIns )) outlet_price , IIF ( omcs . service_id IS NULL , od . order_service_id , CONCAT ( od . order_num , '/' , omcs . service_id )) AS order_service_id , omcs . OrderDiscount discount , IIF ( omcs . Service_id IS NULL , od . Outlet_price , dbo . GetPrice ( ote . Reg_point , omcs . Service_id , ote . DateIns )) outlet_price , IIF ( omcs . Service_id IS NULL , od . Order_service_id , CONCAT ( od . Order ) order_num , '/' , omcs . service_id )) AS order_service_id , omcs .  n_OrderDetail od LEFT JOIN OrdersToExport ote ON ote . complex_id FROM n_OrderDetail od LEFT JOIN OrdersToExport ote ON ote .  order_num = od .  JOIN n_order_marketing_complex_service omcs ON omcs . order_num LEFT JOIN n_order_marketing_complex_service omcs ON omcs .  order_num = od .  omcs . order_num AND omcs .  complex_id = od .  ote . serv_id WHERE ote .  order_num = @ P1 


Attached file Size - 46Kb
Re: Deadlocks with Intent Locks Objects [new]
Danion,

those. SELECT for no apparent reason decided that he needed SIX? Dedlok graph in xml show
Re: Deadlocks with Intent Locks Objects [new]
TaPaK,

The reason for sure is somewhere there.

XML plan: (hid hostname, username, and base)

 deadlock > < victim-list > < victimProcess id ="process42eef144e8" / > < /victim-list > < process-list > < process id ="process42eef144e8" taskpriority ="0" logused ="12536" waitresource ="PAGE: 9:1:116120662 " waittime ="1049" ownerId ="30254457035" transactionname ="user_transaction" lasttranstarted ="2018-12-18T10:48:41.147" XDES ="0x4e382696c8" lockMode ="S" schedulerid ="1" kpid ="11736" status ="suspended" spid ="3547" sbid ="1" ecid ="0" priority ="0" trancount ="1" lastbatchstarted ="2018-12-18T10:48:41.283" lastbatchcompleted ="2018-12-18T10:48:41.283" lastattention ="1900-01-01T00:00:00.283" hostname =" скрыто " hostpid ="0" loginname =" скрыто " isolationlevel ="read committed (2)" xactid ="30254457035" currentdb ="9" lockTimeout ="4294967295" clientoption1 ="671088672" clientoption2 ="128056" > < executionStack > < frame procname ="adhoc" line ="2" stmtstart ="66" stmtend ="2428" sqlhandle ="0x02000000b2230d0756002740d3d3ca09f315bfc98114fd7c0000000000000000000 < deadlock > < victim-list > < victimProcess id = "process42eef144e8" / > < / victim-list > < process-list > < process id = "process42eef144e8" taskpriority = "0" logused = "12536" waitresource = "PAGE: 9: 1: 116120662 " waittime =" 1049 " ownerId =" 30254457035 " transactionname =" user_transaction " lasttranstarted =" 2018-12-18T10: 48: 41.147 " XDES =" 0x4e382696c " lockMode =" S " schedulerid =" 1 " kpid = "11736" status = "suspended" spid = "3547" sbid = "1" ecid = "0" priority = "0" trancount = "1" lastbatchstarted = "2018-12-18T10: 48: 41.283" lastbatchcompleted = " 2018-12-18T10: 48: 41.283 " lastattention =" 1900-01-01T00: 00: 00.283 " hostname =" hidden " hostpid =" 0 " loginname =" hidden " isolationlevel =" read committed (2) " xactid =" 30254457035 " currentdb =" 9 " lockTimeout =" 4294967295 " clientoption1 =" 671088672 " clientoption2 =" 128056 " > < executionStack > < frame procname =" adhoc " line =" 2 " stmtstart =" 66 " stmtend =" 2428 " sqlhand = "0x02000000b2230d0756002740d3d3ca09f315bfc98114fd7c0000000000000000000  > unknown < /frame > < frame procname ="unknown" line ="1" sqlhandle ="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" > unknown < /frame > < /executionStack > < inputbuf > (@P1 nvarchar(4000)) SELECT 1 flow_doctype, ote.order_num flow_id, ote.order_num order_num, IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id, 1 count, IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price, 1 flow_type, ote.DateReg flow_date, IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total, od.bonuses, od.cito_factor, ote.OrderKontragentID sender_id, ote.OrderDiscount discount, IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price, IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id, omcs.complex_id FROM n_OrderDetail od LEFT JOIN OrdersToExport ote ON ote.o < /inputbuf > < /process > < pro < / Frame > < frame procname = "unknown" = "1" sql ote.order_num flow_id, ote.order_num order_num, IIF (omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id, 1 count, IIF (omcs.service_id IS NULL, od.price, omcs.price) AS price , 1 flow_type, ote.DateReg flow_date, IIF (omcs.service_id IS NULL, od.total, omcs.total) AS total, od.bonuses, od.cito_factor, ote.OrderKontragentID sender_id, ote.OrderDiscount discount, IIF (omcs. service_id IS NULL, od.outlet_price, dbo.GetPrice (ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price, IIF (omcs.service_id IS NULL, od.order_service_id, CONCAT (od.order_num, '/', om.order_id .service_id)) AS order_service_id, omcs.complex_id FROM n_OrderDetail od LEFT JOIN OrdersToExport ote ON ote.o < / inputbuf > < / process > < pro  ="process65e4063c28" taskpriority ="0" logused ="28444" waitresource ="PAGE: 9:1:116241658 " waittime ="1250" ownerId ="30254454272" transactionname ="user_transaction" lasttranstarted ="2018-12-18T10:48:40.977" XDES ="0x58bcadb948" lockMode ="S" schedulerid ="10" kpid ="10540" status ="suspended" spid ="3316" sbid ="1" ecid ="0" priority ="0" trancount ="1" lastbatchstarted ="2018-12-18T10:48:41.077" lastbatchcompleted ="2018-12-18T10:48:41.077" lastattention ="1900-01-01T00:00:00.077" clientapp ="Apache HTTP Server" hostname =" скрыто " hostpid ="11796" loginname =" скрыто " isolationlevel ="read committed (2)" xactid ="30254454272" currentdb ="9" lockTimeout ="4294967295" clientoption1 ="671088672" clientoption2 ="128056" > < executionStack > < frame procname ="adhoc" line ="2" stmtstart ="62" stmtend ="2464" sqlhandle ="0x02000000115f650aea85b094ea93aa7fdedb9f1fbc4383670000000000000000000000000000000000000000" > unknown < /frame > < frame procname ="unknown" lin cess id = "process65e4063c28" taskpriority = "0" logused = "28444" waitresource = "PAGE: 9: 1: 116241658" waittime = "1250" ownerId = "30254454272" transactionname = "user_transaction" lasttranstarted = "2018-12-18TT10 : 48: 40.977 " XDES =" 0x58bcadb948 " lockMode =" S " schedulerid =" 10 " kpid =" 10540 " status =" suspended " spid =" 3316 " sbid =" 1 " ecid =" 0 " priority =" 0 " trancount = "1" lastbatchstarted = "2018-12-18T10: 48: 41.077" lastbatchcompleted = "2018-12-18T10: 48: 41.077" lastattention = "1900-01-01T00: 00: 00.077" clientapp = "Apache HTTP Server " hostname =" hidden " hostpid =" 11796 " loginname =" hidden " isolationlevel =" read committed (2) " xactid =" 30254454272 " currentdb =" 9 " lockTimeout =" 4294967295 " clientoption1 =" 671088672 " clientoption2 =" 128056 " > <executionStack> <frame procname = "adhoc" line = " 2" stmtstart = "62" stmtend = "2464" sqlhandle = "0x02000000115f650aea85b094ea93aa7fdedb9f1fbc4383670000000000000000000000000000000000000000"> unknown </ frame> <frame procname = "unknown" lin  ="1" sqlhandle ="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" > unknown < /frame > < /executionStack > < inputbuf > (@P1 nvarchar(8)) SELECT 1 flow_doctype, ote.order_num flow_id, ote.order_num order_num, IIF(omcs.service_id IS NULL, od.serv_id, omcs.service_id) AS serv_id, 1 count, IIF(omcs.service_id IS NULL, od.price, omcs.price) AS price, 1 flow_type, ote.DateReg flow_date, IIF(omcs.service_id IS NULL, od.total, omcs.total) AS total, od.bonuses, od.cito_factor, ote.OrderKontragentID sender_id, ote.OrderDiscount discount, IIF(omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice(ote.reg_point, omcs.service_id, ote.DateIns)) outlet_price, IIF(omcs.service_id IS NULL, od.order_service_id, CONCAT(od.order_num, '/', omcs.service_id)) AS order_service_id, omcs.complex_id FROM n_OrderDetail od LEFT JOIN OrdersToExp < /inputbuf > < /process > < /process-list > < resource-list > < pagelock fileid ="1" pageid ="116120662" dbid ="9" subresourc e = "1" sqlhandle = "0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </ frame> </ executionStack> <inputbuf> (@ P1 nvarchar ( 8)) SELECT 1 flow_doctype, ote.order_num flow_id, ote.order_num order_num, IIF (omcs.service_id IS NULL, od.serv_id, omcs.service_id AS serv_id, 1 count, IIF (omcs.service_id IS NULL, od.price, omcs.price) AS price, 1 flow_type, ote.DateReg flow_date, IIF (omcs.service_id IS NULL, od.total, omcs.total) AS total, od.bonuses, od.cito_factor, ote.OrderKontragentID, sender_id, ote.OrderDiscount discount, IIF (omcs.service_id IS NULL, od.outlet_price, dbo.GetPrice (ote.reg IS , omcs.service_id, ote.DateIns)) outlet_price, IIF (omcs.service_id IS NULL, od.order_service_id, CONCAT (od.order_num, '/', omcs.service_id)) AS order_service_id, omcs.complus_id FROM n_OrderDetethe OrdersToExp < / inputbuf > < / process > < / process-list > < resource-list > < pagelock fileid = "1" pageid = "116120662" dbid = "9" subresourc  ="FULL" objectname ="скрыто.dbo.n_order_marketing_complex_service" id ="lockca1cd1e500" mode ="SIX" associatedObjectId ="72057596708061184" > < owner-list > < owner id ="process65e4063c28" mode ="SIX" / > < /owner-list > < waiter-list > < waiter id ="process42eef144e8" mode ="S" requestType ="wait" / > < /waiter-list > < /pagelock > < pagelock fileid ="1" pageid ="116241658" dbid ="9" subresource ="FULL" objectname =" скрыто.dbo.n_order_marketing_complex_service" id ="lock50c07c2c00" mode ="IX" associatedObjectId ="72057596708061184" > < owner-list > < owner id ="process42eef144e8" mode ="IX" / > < /owner-list > < waiter-list > < waiter id ="process65e4063c28" mode ="S" requestType ="wait" / > < /waiter-list > < /pagelock > < /resource-list > < /deadlock > e = "FULL" objectname = "hidden.dbo.n_order_marketing_complex_service" id = "lockca1cd1e500" mode = "SIX" associatedObjectId = "72057596708061184" > < owner-list > < owner id = "process65e4063c28" mode = "SIX" / > < / owner-list > < waiter-list > < waiter id = "process42eef144e8" mode = "S" requestType = "wait" / > < / waiter-list > < / pagelock > < pagelock fileid = "1" pageid = "116241658 " dbid =" 9 " subresource =" FULL " objectname =" is hidden.dbo.n_order_marketing_complex_service " id =" lock50c07c2c00 " mode =" IX " associatedObjectId =" 72057596708061184 " > < owner-list < owner id =" process42eef144e4e4OejectOdjectId = "72057596708061184 "IX" / > < / owner-list > < waiter-list > < waiter id = "process65e4063c28" mode = "S" requestType = "wait" / > < / waiter-list > < / pagelock > < / resource-list > < / deadlock > 
Re: Deadlocks with Intent Locks Objects [new]
Danion,

See transactions in its entirety. inputbuf shows not all
Re: Deadlocks with Intent Locks Objects [new]
Your selects are executed in transactions that were previously modified "hidden.dbo.n_order_marketing_complex_service". This is indicated by the presence of ix.
Re: Deadlocks with Intent Locks Objects [new]
TaPaK,

Can you tell where? I laid out from xml_deadlock_report from extended events.

Attached file Size - 45Kb
Re: Deadlocks with Intent Locks Objects [new]
invm,

That is - I see a part of the transaction with the select and where there seems to be no reason, but it blocks the part that I didn’t catch through Extended Events?
Re: Deadlocks with Intent Locks Objects [new]
Danion
That is - I see a part of the transaction with the select and where there seems to be no reason, but it blocks the part that I didn’t catch through Extended Events?
You see the deadlock graph with conflicting instructions. But this does not mean that the resources for which the fight is going on are blocked only by these instructions.
You have this particular case.
Re: Deadlocks with Intent Locks Objects [new]
invm,

Thanks for the answer.

Can I somehow get full visibility of the situation with these locks?
Or is it possible only through the developers to see that it is fully executed with this request?
Re: Deadlocks with Intent Locks Objects [new]
Danion
invm,

Thanks for the answer.

Can I somehow get full visibility of the situation with these locks?
Or is it possible only through the developers to see that it is fully executed with this request?

Faster from developers, judging by the fact that you have the same thing there
Re: Deadlocks with Intent Locks Objects [new]
TaPaK,

Well, I'll try through the developers. Stability in time of the beginning and end of mass deadlocks is present.
Re: Deadlocks with Intent Locks Objects [new]
Danion,

Most of all, your select scans the n_order_marketing_complex_service table.
Perhaps the creation of the correct index will remove such and requests from different sessions will no longer intersect according to the data. At the same time, granularity of locks can be reduced.
Accordingly, this deadlock will disappear.
Re: Deadlocks with Intent Locks Objects [new]
invm,

I do not understand how adding an index will help with locks that are superimposed until it is very clear where.

In the select from this table, the join fields omcs.order_num and omcs.complex_id.

There are indexes on the table and for example this non-clustered index includes these fields:
 UNIQUE NONCLUSTERED INDEX [ iu_n_order_marketing_complex_service$order_num$complex_id$service_id ] ON [ dbo ].[ n_order_marketing_complex_service ] CREATE UNIQUE NONCLUSTERED INDEX [ iu_n_order_marketing_complex_service $ order_num $ complex_id $ service_id ] ON [ dbo ]. [ N_order_marketing_complex_service ]
 (
	 order_num ] ASC , [ order_num ] ASC ,
	 complex_id ] ASC , [ complex_id ] ASC ,
	 service_id ] ASC [ service_id ] ASC
 WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARM ]
 GO
Re: Deadlocks with Intent Locks Objects [new]
Danion
I do not understand how adding an index will help with locks
The index may remove the table scan from the execution plan. Accordingly, competing queries will no longer intersect by readable data.
The shown index may be of little use for this request, since for the values ​​of the omcs.price and omcs.total columns, you still have to climb into the table.
See the request plan - everything is visible there.
Re: Deadlocks with Intent Locks Objects [new]
invm,

Here is the query plan, search by index was 91%. Somehow a lot.
! the sign swears at more data type conversions within the query plan.

Attached file Size - 106Kb
Re: Deadlocks with Intent Locks Objects [new]
Danion
invm,

Here is the query plan, search by index was 91%. Somehow a lot.

This is interpreted as follows: 91% of the entire execution time occurred index seek (the fastest that can be)

% is a relative, not absolute
Re: Deadlocks with Intent Locks Objects [new]
Need a current execution plan
Re: Deadlocks with Intent Locks Objects [new]
Komrad,

It's just that there are several index scans and 91% were surprised by one.

And what this plan is not relevant? Data from the cache. With Extended Events deadlock, it does not seem to collect data, I will try it with a profiler tomorrow, it seems to be there.

I'm trying to find out the details of the developers, until one transaction comes out with a bunch of Select, update, delete, insert, one of the pieces of which is with the select. Then why doesn't everything else fall ...
Re: Deadlocks with Intent Locks Objects [new]
Danion,

The "correct" index for this request is (though not the one that you showed) and it is involved. So the version of the full table scan disappears.
Re: Deadlocks with Intent Locks Objects [new]
invm,

This index was used:

CREATE NONCLUSTERED INDEX [in_n_order_marketing_complex_service $ complex_id $ order_num $ service_id $ price $ total] ON [dbo]. [N_order_marketing_complex_service]
(
[complex_id] ASC
)
INCLUDE ([order_num],
[service_id],
[price],
[total]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


A transaction with a bunch of actions with different tables, one of the stages of which is in the first post with selects. When falling should roll back all stages.
At the same time it seemed to me that the locks imposed in the transaction remain until it ends, and problems with one table only.

With the table on which the update occurs update, then this is a query with selects, after it nothing changes.
In addition to the option of dirty reading (which can give false information) something can be done? For example, somehow change the lock level on S after update ...
Re: Deadlocks with Intent Locks Objects [new]
Danion
With the table on which the update occurs update, then this is a query with selects, after it nothing changes.
Show this update.
Danion
can something be done?
Radically - turn on RCSI.
Palliatively, until the cause of the deadlock is clarified, add the rowlock hint to the table in the select.
Re: Deadlocks with Intent Locks Objects [new]
invm,

Here is:

  [ dbo ].[ n_order_marketing_complex_service ] SET [ total ] =:yp0 , [ bonuses ] =:yp1 WHERE [ dbo ].[ n_order_marketing_complex_service ].[ aid ] =3028507 --- -------------- UPDATE [ dbo ].[ n_order_marketing_complex_service ] SET [ total ] =:yp0 , [ bonuses ] =:yp1 WHERE [ dbo ].[ n_order_marketing_complex_service ].[ aid ] =3028504 --- -------------- UPDATE [ dbo ].[ n_order_marketing_complex_service ] SET [ total ] =:yp0 , [ bonuses ] =:yp1 WHERE [ dbo ].[ n_order_marketing_complex_service ].[ aid ] =3028505 --- -------------- UPDATE [ dbo ].[ n_order_marketing_complex_service ] SET [ total ] =:yp0 , [ bonuses ] =:yp1 WHERE [ dbo ].[ n_order_marketing_complex_service ].[ aid ] =3028506 UPDATE [ dbo ]. [ N_order_marketing_complex_service ] SET [ total ] =: yp0 , [ bonuses ] =: yp1 WHERE [ dbo ]. [ N_order_marketing_complex_service ]. [ Aid ] = 3028507 --- ----------- --- UPDATE [ dbo ]. [ N_order_marketing_complex_service ] SET [ total ] =: yp0 , [ bonuses ] =: yp1 WHERE [ dbo ]. [ N_order_marketing_complex_service ]. [ Aid ] = 3028504 --- -------- ------ UPDATE [ dbo ]. [ N_order_marketing_complex_service ] SET [ total ] =: yp0 , [ bonuses ] =: yp1 WHERE [ dbo ]. [ N_order_marketing_complex_service ]. [ Aid ] = 3028505 --- ----- --------- UPDATE [ dbo ]. [ N_order_marketing_complex_service ] SET [ total ] =: yp0 , [ bonuses ] =: yp1 WHERE [ dbo ]. [ N_order_marketing_complex_service ]. [ Aid ] = 3028506 
Re: Deadlocks with Intent Locks Objects [new]
aid is a pc?
Re: Deadlocks with Intent Locks Objects [new]
When enabled, RCSI found a mention of problems with peace in the beginning. It seems not critical with this blocking problem, but the base is in AlwaysOn. How can something influence? It seems not to.

invm,

No, it looks like an order number. On it is configured the primary key of the table.

ALTER TABLE [dbo]. [N_order_marketing_complex_service] ADD PRIMARY KEY CLUSTERED
(
[aid] 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) ON [PRIMARY]
GO
Re: Deadlocks with Intent Locks Objects [new]
you have interesting conversations here
invm
aid is a pc?

Danion
Not
...
 TABLE [ dbo ].[ n_order_marketing_complex_service ] ADD PRIMARY KEY CLUSTERED ( [ aid ] ASC ) ALTER TABLE [ dbo ]. [ N_order_marketing_complex_service ] ADD PRIMARY KEY CLUSTERED ( [ aid ] ASC )

Re: Deadlocks with Intent Locks Objects [new]
Yasha123,
and here it is:
author
It's just that there are several index scans and 91% were surprised by one.
And what this plan is not relevant? Data from the cache.


there will still be on this update trigger that wool the entire table. Just because these update will not ask SIX
Re: Deadlocks with Intent Locks Objects [new]
Danion
base in AlwaysOn. How can something influence?
Can. Turning on RCSI will have problems - http://www.sqlnuggets.com/blog/change-the-isolation-level-of-an-availability-group-database/
Danion
On it is configured the primary key of the table.
The PC is the primary key.
Is this update string the same in every transaction? Those. updates the same lines?
Re: Deadlocks with Intent Locks Objects [new]
and that is true.
actual cache -> current plan.
not sturgeon, you know, "second freshness"
Re: Deadlocks with Intent Locks Objects [new]
TaPaK
Just because these update will not ask SIX
SIX asks for select, not update.
Because at the time of the request S on the page there is already IX on this page.
Re: Deadlocks with Intent Locks Objects [new]
invm
TaPaK
Just because these update will not ask SIX
SIX asks for select, not update.
Because at the time of the request S on the page there is already IX on this page.

it sounds strange
Re: Deadlocks with Intent Locks Objects [new]
TaPaK,

 tempdb ; use tempdb ;
 go

 table dbo . create table dbo .  ( id int primary key , v int ); t ( id int primary key , v int );
 into dbo . insert into dbo .  ( 1 , 1 ); t values ( 1 , 1 );
 go

 tran ; begin tran ;
 dbo . update dbo .  v = 2 where id = 1 ; t set v = 2 where id = 1 ;
 sp_lock @@spid ; exec sp_lock @@ spid ;
 * from dbo . select * from dbo .  ( paglock , repeatableread ) where id = 1 ; t with ( paglock , repeatableread ) where id = 1 ;
 sp_lock @@spid ; exec sp_lock @@ spid ;
 ; commit ;
 go

 table dbo . drop table dbo .  t ;
 go
Re: Deadlocks with Intent Locks Objects [new]
invm,

not very similar to the "simple" select, but oh well.
According to the scheme, is six already provided before select?
Re: Deadlocks with Intent Locks Objects [new]
TaPaK
not very similar to the "simple" select, but oh well.
Hints are added solely to demonstrate the effect, not to "complicate" the select.
TaPaK
According to the scheme, is six already provided before select?
Based on what is such a conclusion?
Re: Deadlocks with Intent Locks Objects [new]
invm
TaPaK
not very similar to the "simple" select, but oh well.
Hints are added solely to demonstrate the effect, not to "complicate" the select.
TaPaK
According to the scheme, is six already provided before select?
Based on what is such a conclusion?

Something like this, correct: sp 3316 already checks the SIX on page ... 662 and wants S on page ... 658. How did you get the SIX from all provided is not visible
Re: Deadlocks with Intent Locks Objects [new]
TaPaK
How to get SIX from all provided is not visible
Very obvious.
SIX is not a separate lock, but a combination of S and IX locks. How it turns out, see the example above.
But this is not important at all: in the context of this deadlock, it can be interpreted simply as IX.
Re: Deadlocks with Intent Locks Objects [new]
invm
TaPaK
How to get SIX from all provided is not visible
Very obvious.
SIX is not a separate lock, but a combination of S and IX locks. How it turns out, see the example above.
But this is not important at all: in the context of this deadlock, it can be interpreted simply as IX.

Ok, i.e. to treat theoretically with research?
Then yes, RSCII and the next
Re: Deadlocks with Intent Locks Objects [new]
Do I still need some information from me?

And while I understand on other server with "bonuses" 1C. Although it seems there is a problem with the OS.
By the way, if someone met a similar:

At night, there were massive changes in the 1C erp database. There auto growth is included, the standard 10% + limit of 2,097,152, is essentially unlimited. Log base normally grew, and then:
C: \ SQL \ erp_log.ldf: Operating system error message 665 encountered.
And we went The transaction log for database 'erp' is full due to 'LOG_BACKUP'. before backup log, after expectedly normal.

On the disk, less than 10% of the space did not become. And this is more than 10% of the size of the log.

I tried to increase the file with my hands on the MODIFY FILE encountered while I was trying to expand the physical file 'C: \ SQL \ erp_log.ldf'.
At 1 MB allowed. I read it by mistake, they write that one of the possible reasons is disk fragmentation, I checked - now there is 42% fragmentation.
Re: Deadlocks with Intent Locks Objects [new]
Danion
Do I still need some information from me?
The question was here - 21768041

You can try to rewrite something like this:
             SELECT
              ...
             FROM
              (
               top ( cast ( 0x7fffffff as int )) select top ( cast ( 0x7fffffff as int ))
                ...
               from
                n_OrderDetail od
                OrdersToExport ote ON ote . JOIN OrdersToExport ote ON ote .  order_num = od .  order_num
               WHERE
                ote .  order_num = @ P1
               by order by
                od .  serv_id
              ttt ) ttt
              loop JOIN n_order_marketing_complex_service omcs ON omcs . LEFT loop JOIN n_order_marketing_complex_service omcs ON omcs .  order_num = ttt .  omcs . order_num AND omcs .  complex_id = ttt .  serv_id
Re: Deadlocks with Intent Locks Objects [new]
author
Is this update string the same in every transaction? Those. updates the same lines?


Developed say the action is the same. SET [total] =: yp0, [bonuses] =: yp1 for some kind of aid
Re: Deadlocks with Intent Locks Objects [new]
Danion
Developed say the action is the same
Did I mean the list of aid's the same in competing sessions?
Okay it does not matter.

1. Index redo this:
 NONCLUSTERED INDEX [ in_n_order_marketing_complex_service$complex_id$order_num$service_id$price$total ] ON [ dbo ].[ n_order_marketing_complex_service ] CREATE NONCLUSTERED INDEX [ in_n_order_marketing_complex_service $ complex_id $ order_num $ service_id $ price $ total ] ON [ dbo ]. [ N_order_marketing_complex_service ]
  complex_id ], [ order_num ], [ service_id ]) ([ complex_id ], [ order_num ], [ service_id ])
 INCLUDE
  price ], [ total ]) ([ price ], [ total ])
 ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , drop_existing = on ) ON [ PRIMARY ] WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , drop_existing = on ) ON [ PRIMARY ]
If the combination (complex_id, order_num, service_id) is unique, then make the index unique.
If unique (complex_id, order_num), leave the service_id in include and also make the index unique.

2. Rewrite this pack of updates so:
 t as with t as
 (
  top ( cast ( 0x7fffffff as int )) select top ( cast ( 0x7fffffff as int ))
  from
   dbo ].[ n_order_marketing_complex_service ] [ dbo ]. [ n_order_marketing_complex_service ]
  where
   ( 3028507 , 3028504 , ..., 3028506 ) aid in ( 3028507 , 3028504 , ..., 3028506 )
  by order by
   order_num , aid complex_id , order_num , aid
 )
 t update t
  set
   total ] = :yp0 , [ bonuses ] = :yp1 ; [ total ] =: yp0 , [ bonuses ] =: yp1 ;


3. If item 2 is not applicable, rewrite select as shown here - 21768307 , adding order_num to the sort.

Generally, taking into account what he saw and
Danion
A transaction with a bunch of actions with different tables, one of the stages of which is in the first post with selects.
RCSI suggests itself, so as not to place the crutches at every step or to bring the code into an adequate form.
Re: Deadlocks with Intent Locks Objects [new]
invm,
Thanks for the advice, see what you can apply.



Old articles you may read here
Go to Main page