SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is this Deadlock due to Lock Partitions?


Is this Deadlock due to Lock Partitions?

Author
Message
ayemya
ayemya
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3115 Visits: 1254

The same update ran a few miliseconds apart. It causes deadlock. Col2 is primary clustered key. In execution plan, It's only one step which is Clustered Index Update on Col2.
is there any other way to resolve this kind of deadlock? I have 10 Cpus on the server.
I ran the query below. Resource lock partition returns 0.

SELECT OBJECT_NAME(resource_associated_entity_id) as objectName,resource_description, resource_lock_partition,request_mode FROM sys.dm_tran_locks
WHERE resource_type = ‘OBJECT’

Please see below Deadlock graph. Thanks.
<deadlock>
<victim-list>
<victimProcess id="process5ff029088" />
</victim-list>
<process-list>
<process id="process5ff029088" taskpriority="0" logused="0" waitresource="OBJECT: 9:1666104976:0 " waittime="2189" ownerId="5663717355" transactionname="implicit_transaction" lasttranstarted="2018-02-20T11:48:44.707" XDES="0x4ca672d90" lockMode="IX" schedulerid="3" kpid="3180" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-20T11:48:46.243" lastbatchcompleted="2018-02-20T11:48:46.243" lastattention="1900-01-01T00:00:00.243" clientapp="xxx Application Server" hostname="xxx-p01.xxx.us.dom" hostpid="0" loginname="xxx" isolationlevel="repeatable read (3)" xactid="5663717355" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="34" stmtend="134" sqlhandle="0x020000000ecd701357c74976549f81b3771f9af278cc7be70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 int)UPDATE Tbl SET Col=@P0 WHERE Col2 =@P1 </inputbuf>
</process>
<process id="process5ff0168c8" taskpriority="0" logused="0" waitresource="OBJECT: 9:1666104976:0 " waittime="2810" ownerId="5663717003" transactionname="implicit_transaction" lasttranstarted="2018-02-20T11:48:44.667" XDES="0x4e58043b0" lockMode="IX" schedulerid="1" kpid="2152" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-20T11:48:45.623" lastbatchcompleted="2018-02-20T11:48:45.623" lastattention="1900-01-01T00:00:00.623" clientapp="xxx Application Server" hostname="xxx-p02.xxx.us.dom" hostpid="0" loginname="xxx" isolationlevel="repeatable read (3)" xactid="5663717003" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="34" stmtend="134" sqlhandle="0x020000000ecd701357c74976549f81b3771f9af278cc7be70000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 int)UPDATE Tbl SET Col=@P0 WHERE Col2 =@P1 </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="1666104976" subresource="FULL" dbid="9" objectname="Tbl" id="lock3e7232e00" mode="S" associatedObjectId="1666104976">
<owner-list>
<owner id="process5ff0168c8" mode="S" />
<owner id="process5ff0168c8" mode="IX" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process5ff029088" mode="IX" requestType="convert" />
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1666104976" subresource="FULL" dbid="9" objectname="Tbl" id="lock3e7232e00" mode="S" associatedObjectId="1666104976">
<owner-list>
<owner id="process5ff029088" mode="S" />
<owner id="process5ff029088" mode="IX" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process5ff0168c8" mode="IX" requestType="convert" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>


Sergiy
Sergiy
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97614 Visits: 14183
3 questions.

1. How many rows are returned by
SELECT * FROM Tbl WHERE  Col2 =@P1


2. Do both updates use the same value assigned to @P1?


3. Is updated column - SET Col=@P0 - different from Col2? What kind of value is assigned to it? Does it participate in any indexes?
ayemya
ayemya
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3115 Visits: 1254
Hi Sergiy,
The table has 14835392 rows total and 23 columns. Col2 is clustered Primary key. Col and Col2 are integer. no indexes on col.
indexes on the table
Col2 (primary,clustered)
Col2 and Col 3 (nonclustered)
Col 5 (nonclustered)

I added rowlock in update statement. It introduces more deadlocks. Any thoughts? Running out of ideas. Thank you.
Sergiy
Sergiy
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97614 Visits: 14183
Hi,

What is OBJECT_NAME(1666104976) ?
ayemya
ayemya
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3115 Visits: 1254
It is the table name in update statement. Tbl .
Sergiy
Sergiy
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97614 Visits: 14183
Can you script this table "for create" and post it here?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896378 Visits: 48655
What are the settings for Allow_row_locks and Allow_Page_locks for that table?
An update of 3 rows, filtered by the clustered index should not be taking table locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ayemya
ayemya
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3115 Visits: 1254
They are set True. Will isolationlevel="repeatable read (3)" cause deadlock? Thank you.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896378 Visits: 48655
Ah, I see what's happening.

There's a SELECT earlier in the (implicitly started) transaction that's taking a table-level shared lock. You're going to have to identify what that select is and why it's taking a table lock in order to fix this (changing to read committed isolation level will also work, but be careful, there may be a good reason why the isolation level is repeatable read, and by changing it you could break application functionality)

You could also investigate why implicit transactions are on. That's a transaction behaviour more associated with Oracle than SQL Server (where auto_commit is the default and most transactions you'll see are started explicitly via BEGIN TRANSACTION)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ayemya
ayemya
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3115 Visits: 1254
Thank you GilaMonster. I will check with apps developer.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search