Deadlock e_waitPipeGetRow

1

Hello,

we experience deadlock with the following log:

<deadlock-list>
 <deadlock victim="process46a3e4188">
  <process-list>
   <process id="process46a3e4188" taskpriority="0" logused="0" waitresource="KEY: 5:72057594040418304 (77de068d6ba0)" waittime="3395" ownerId="4597777" transactionname="implicit_transaction" lasttranstarted="2019-01-23T12:38:08.710" XDES="0x45ecd8bd0" lockMode="U" schedulerid="1" kpid="2464" status="suspended" spid="90" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2019-01-23T12:38:08.710" lastbatchcompleted="2019-01-23T12:38:08.710" lastattention="1900-01-01T00:00:00.710" clientapp="Microsoft JDBC Driver for SQL Server" hostname="HOST02" hostpid="0" isolationlevel="read committed (2)" xactid="4597777" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000063aeb2eb8500241f735704cf75283368d7445800000000000000000000000000000000000000000">
select datatable0_.id as id1_0_, datatable0_.app_version as app_vers2_0_, datatable0_.created_date as created_3_0_, datatable0_.created_from_ip as created_4_0_, datatable0_.deactivated_date as deactiva5_0_, datatable0_.deactivated_from_ip as deactiva6_0_, datatable0_.dedatatable_info as deactiva7_0_, datatable0_.dedatatable_message as deactiva8_0_, datatable0_.device_encryption_salt as device_e9_0_, datatable0_.device_id as device_10_0_, datatable0_.device_info as device_11_0_, datatable0_.last_signed_date as last_si12_0_, datatable0_.last_signed_from_ip as last_si13_0_, datatable0_.name as name14_0_, datatable0_.os as os15_0_, datatable0_.os_version as os_vers16_0_, datatable0_.payload as payload17_0_, datatable0_.pin_verification_key as pin_ver18_0_, datatable0_.public_key_a as public_19_0_, datatable0_.public_key_u as public_20_0_, datatable0_.user_id as user_id21_0_, datatable0_.wrong_signature_counter as wrong_s22_0_ from dbo.datatable datatable0_ with (updlock, rowlock ) w     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000))select datatable0_.id as id1_0_, datatable0_.app_version as app_vers2_0_, datatable0_.created_date as created_3_0_, datatable0_.created_from_ip as created_4_0_, datatable0_.deactivated_date as deactiva5_0_, datatable0_.deactivated_from_ip as deactiva6_0_, datatable0_.dedatatable_info as deactiva7_0_, datatable0_.dedatatable_message as deactiva8_0_, datatable0_.device_encryption_salt as device_e9_0_, datatable0_.device_id as device_10_0_, datatable0_.device_info as device_11_0_, datatable0_.last_signed_date as last_si12_0_, datatable0_.last_signed_from_ip as last_si13_0_, datatable0_.name as name14_0_, datatable0_.os as os15_0_, datatable0_.os_version as os_vers16_0_, datatable0_.payload as payload17_0_, datatable0_.pin_verification_key as pin_ver18_0_, datatable0_.public_key_a as public_19_0_, datatable0_.public_key_u as public_20_0_, datatable0_.user_id as user_id21_0_, datatable0_.wrong_signature_counter as wrong_s22_0_ from dbo.datatable datatable0_ with     </inputbuf>
   </process>
   <process id="process46b338188" taskpriority="0" logused="348" waitresource="KEY: 5:72057594040418304 (489413cc83fc)" waittime="3373" ownerId="4597776" transactionname="implicit_transaction" lasttranstarted="2019-01-23T12:38:08.707" XDES="0x467b0dcc0" lockMode="U" schedulerid="2" kpid="3736" status="suspended" spid="87" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2019-01-23T12:38:08.740" lastbatchcompleted="2019-01-23T12:38:08.740" lastattention="1900-01-01T00:00:00.740" clientapp="Microsoft JDBC Driver for SQL Server" hostname="HOST01" hostpid="0" isolationlevel="read committed (2)" xactid="4597776" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="806" sqlhandle="0x0200000018afe7022b8c232bea77a69c62de520404f60c650000000000000000000000000000000000000000">
update dbo.datatable set app_version=@P0, created_date=@P1, created_from_ip=@P2, deactivated_date=@P3, deactivated_from_ip=@P4, dedatatable_info=@P5, dedatatable_message=@P6, device_encryption_salt=@P7, device_id=@P8, device_info=@P9, last_signed_date=@P10, last_signed_from_ip=@P11, name=@P12, os=@P13, os_version=@P14, payload=@P15, pin_verification_key=@P16, public_key_a=@P17, public_key_u=@P18, user_id=@P19, wrong_signature_counter=@P20 where id=@P21     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000),@P1 datetime2,@P2 nvarchar(4000),@P3 datetime2,@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 datetime2,@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 varbinary(8000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 int,@P21 nvarchar(4000))update dbo.datatable set app_version=@P0, created_date=@P1, created_from_ip=@P2, deactivated_date=@P3, deactivated_from_ip=@P4, dedatatable_info=@P5, dedatatable_message=@P6, device_encryption_salt=@P7, device_id=@P8, device_info=@P9, last_signed_date=@P10, last_signed_from_ip=@P11, name=@P12, os=@P13, os_version=@P14, payload=@P15, pin_verification_key=@P16, public_key_a=@P17, public_key_u=@P18, user_id=@P19, wrong_signature_counter=@P20 where id=@P21                                                                                                                                                                </inputbuf>
   </process>
   <process id="process47f0450c8" taskpriority="0" logused="10000" waittime="3376" schedulerid="7" kpid="928" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-01-23T12:38:08.710" lastbatchcompleted="2019-01-23T12:38:08.710" lastattention="1900-01-01T00:00:00.710" clientapp="Microsoft JDBC Driver for SQL Server" hostname="HOST02" hostpid="0" loginname="SOMETHING\someone" isolationlevel="read committed (2)" xactid="4597777" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000063aeb2eb8500241f735704cf75283368d7445800000000000000000000000000000000000000000">
select datatable0_.id as id1_0_, datatable0_.app_version as app_vers2_0_, datatable0_.created_date as created_3_0_, datatable0_.created_from_ip as created_4_0_, datatable0_.deactivated_date as deactiva5_0_, datatable0_.deactivated_from_ip as deactiva6_0_, datatable0_.dedatatable_info as deactiva7_0_, datatable0_.dedatatable_message as deactiva8_0_, datatable0_.device_encryption_salt as device_e9_0_, datatable0_.device_id as device_10_0_, datatable0_.device_info as device_11_0_, datatable0_.last_signed_date as last_si12_0_, datatable0_.last_signed_from_ip as last_si13_0_, datatable0_.name as name14_0_, datatable0_.os as os15_0_, datatable0_.os_version as os_vers16_0_, datatable0_.payload as payload17_0_, datatable0_.pin_verification_key as pin_ver18_0_, datatable0_.public_key_a as public_19_0_, datatable0_.public_key_u as public_20_0_, datatable0_.user_id as user_id21_0_, datatable0_.wrong_signature_counter as wrong_s22_0_ from dbo.datatable datatable0_ with (updlock, rowlock ) w     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000))select datatable0_.id as id1_0_, datatable0_.app_version as app_vers2_0_, datatable0_.created_date as created_3_0_, datatable0_.created_from_ip as created_4_0_, datatable0_.deactivated_date as deactiva5_0_, datatable0_.deactivated_from_ip as deactiva6_0_, datatable0_.dedatatable_info as deactiva7_0_, datatable0_.dedatatable_message as deactiva8_0_, datatable0_.device_encryption_salt as device_e9_0_, datatable0_.device_id as device_10_0_, datatable0_.device_info as device_11_0_, datatable0_.last_signed_date as last_si12_0_, datatable0_.last_signed_from_ip as last_si13_0_, datatable0_.name as name14_0_, datatable0_.os as os15_0_, datatable0_.os_version as os_vers16_0_, datatable0_.payload as payload17_0_, datatable0_.pin_verification_key as pin_ver18_0_, datatable0_.public_key_a as public_19_0_, datatable0_.public_key_u as public_20_0_, datatable0_.user_id as user_id21_0_, datatable0_.wrong_signature_counter as wrong_s22_0_ from dbo.datatable datatable0_ with     </inputbuf>
   </process>
   <process id="process463eaecf8" taskpriority="0" logused="10000" waittime="3358" schedulerid="3" kpid="3964" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-23T12:38:08.740" lastbatchcompleted="2019-01-23T12:38:08.740" lastattention="1900-01-01T00:00:00.740" clientapp="Microsoft JDBC Driver for SQL Server" hostname="HOST01" hostpid="0" loginname="SOMETHING\someone" isolationlevel="read committed (2)" xactid="4597776" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="806" sqlhandle="0x0200000018afe7022b8c232bea77a69c62de520404f60c650000000000000000000000000000000000000000">
update dbo.datatable set app_version=@P0, created_date=@P1, created_from_ip=@P2, deactivated_date=@P3, deactivated_from_ip=@P4, dedatatable_info=@P5, dedatatable_message=@P6, device_encryption_salt=@P7, device_id=@P8, device_info=@P9, last_signed_date=@P10, last_signed_from_ip=@P11, name=@P12, os=@P13, os_version=@P14, payload=@P15, pin_verification_key=@P16, public_key_a=@P17, public_key_u=@P18, user_id=@P19, wrong_signature_counter=@P20 where id=@P21     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 nvarchar(4000),@P1 datetime2,@P2 nvarchar(4000),@P3 datetime2,@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 datetime2,@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 varbinary(8000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 int,@P21 nvarchar(4000))update dbo.datatable set app_version=@P0, created_date=@P1, created_from_ip=@P2, deactivated_date=@P3, deactivated_from_ip=@P4, dedatatable_info=@P5, dedatatable_message=@P6, device_encryption_salt=@P7, device_id=@P8, device_info=@P9, last_signed_date=@P10, last_signed_from_ip=@P11, name=@P12, os=@P13, os_version=@P14, payload=@P15, pin_verification_key=@P16, public_key_a=@P17, public_key_u=@P18, user_id=@P19, wrong_signature_counter=@P20 where id=@P21                                                                                                                                                                </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594040418304" dbid="5" objectname="someDB.dbo.datatable" indexname="datatable_pk" id="lock46cf79780" mode="X" associatedObjectId="72057594040418304">
    <owner-list>
     <owner id="process463eaecf8" mode="X"/>
     <owner id="process463eaecf8" mode="U"/>
     <owner id="process463eaecf8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process46a3e4188" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594040418304" dbid="5" objectname="someDB.dbo.datatable" indexname="datatable_pk" id="lock472ead000" mode="U" associatedObjectId="72057594040418304">
    <owner-list>
     <owner id="process47f0450c8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process46b338188" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <exchangeEvent id="Pipe465daab00" WaitType="e_waitPipeGetRow" nodeId="0">
    <owner-list>
     <owner id="process46a3e4188"/>
    </owner-list>
    <waiter-list>
     <waiter id="process47f0450c8"/>
    </waiter-list>
   </exchangeEvent>
   <exchangeEvent id="Pipe454ab6a80" WaitType="e_waitPipeGetRow" nodeId="2">
    <owner-list>
     <owner id="process46b338188"/>
    </owner-list>
    <waiter-list>
     <waiter id="process463eaecf8"/>
    </waiter-list>
   </exchangeEvent>
  </resource-list>
 </deadlock>
</deadlock-list>

Tech. info:
SQL Server 2012

Our history:

First, we are 100% sure we are not accessing same keys in different order.

We started having primary key with clustered index and multi-column non-clustered index. In that state we got hit by deadlock caused by something like:

SELECT * FROM datatable WHERE id = "foo"

conflictng with

UPDATE datatable set param1, param2, ... WHERE id = "bar"

We analyzed this scenario and found the reason of this was that UPDATE was causing PAGE_LOCK while select was trying to access a row that belonged to this page.

we turned off PAGE_LOCK-ing on this table and removed mentioned multi-column index. This reduced the number of deadlocks, but changed of deadlock type to type specified above.

We would appreciate any kind of help.

2

You should take a look at RCSI (read committed snapshot isolation) - if that is not possible then consider enabling snapshot isolation and using that for the select statements.

I also looks like you have set table hits to force updlock and rowlock on the select statements. Why are you using these lock hints? This will force each select statement to issue those locks which will block all other statements.

1 Like
3

how about this ^^, is this showing a run time value or it this an actual setting. Either way, "seems like a mighty long time, shoo bop shoo bop"

1 Like
4

Thanks for response.
updlock and rowlock are used for "select for update" scenario. Row is locked to prevent other transactions to edit it and in the same transaction, the row is updated.
Sorry for not mentioning this in my post.
Could RCSI help in this case where locking is intentional?

Also the table is attacked with many queries of type SELECT * FROM datatable WHERE id = "foo"
without explicit transaction or any locking hint. So I think this does not affect the cause of deadlock in any way as it always happens between the UPDATE and SELECT with rowlocking. However I can't prove it. Do you have the opposite opinion?

5

are these in stored procedures? or EF? or ?

1 Like
6

Thanks for response.
I guess it is the default value of max lock time as it is 2^32 (max. 32 bit number) in the meaning of "infinite". We have not set anything like that.

7

All of the queries come from application server.

8

Do you use stored procedures at all? Do you do heavy reporting (slect * from foo where bar ='tadaw')?

1 Like
9

No we don't use any stored procedures.
Also there is no reporting on this table. There are some other queries where we use the same table. These are something like what you mentioned - if you meant queries on non-primary key column (SELECT * FROM datatable WHERE os = "foo" and deviceid = "bar" and deactivated_date = "baz"), but they run very rarely, like once a week maybe even less often.

10

Are these select statements part of an update - or are they separate statements? Based on what you have shown - these select statements are not part of the update which is why you are getting deadlocks.

This 'select for update' - what exactly are you doing here? Are you selecting a row - and then in a separate statement updating the row?

SELECT ... FROM datatable WHERE ...;
UPDATE datatable SET ...

Or - are they part of the update?

UPDATE datatable
SET column = (SELECT ... FROM datatable WHERE ...);

RCSI could help - since your select statements would then be reading from the version store the updates won't be blocked. You wouldn't have to worry about 'select for update' - because any other in-flight transactions would not be available at that time.

However - if you are expecting the select statements to wait until another processes update has completed then you have a whole different problem.

1 Like
11

This 'select for update' - what exactly are you doing here? Are you selecting a row - and then in a separate statement updating the row?

SELECT ... FROM datatable WHERE ...;
UPDATE datatable SET ...

Yes, this is our case.
Those multiple selects - They are not part of an update, they are executed from another application server before actual select for update scenario happens.
It is always something like:
from first server: select, select
from second server: start of explicit transaction select with rowlock, update, end of transaction
from first server: select

However - if you are expecting the select statements to wait until another processes update has completed then you have a whole different problem.

No, we dont expect concurrent reading/modification on the same row. That is why we are so confused about this whole thing.

12

Oh so you have other servers as well? Via linked objects?

1 Like
13

They just access the same database and in one of them, just a subset of columns/properties is read.
When user requests the first one, it executes mentioned select queries and calls the other one, which should access the same row and possibly update it, while on the first one there are no explicit transactions, so it should not be blocking the second one. Its all synchronous, no parallelism.

14

Please show us your code if you can?
This is very confusing to me as to what youbare doing exactly

1 Like
15

Very confusing...

There is no reason to issue a SELECT with an update and row lock...why are you doing this? The SELECT with an update and row lock will block all other processes until it is completed - especially the UPDATEs.

1 Like
16

Are you familiar with Spring? I can prepare a code snippet.

17

It is not a select with update and rowlock in the same query.
It is:
select with rowlock (to prevent anything from changing the row)
#server application computing data to save
update of locked row (with computed data)
I think it is very common scenario.

18

Spring mvc framework? Is that it?

1 Like
19

Yes.

20

This is not how locking works in SQL Server. The SELECT does not lock the row and hold that lock until after you issue the UPDATE. As soon as the SELECT statement is completed - the lock is released unless you are performing this process in an explicit transaction...

This sounds like application locks - which is a completely different process.

1 Like