February 20, 2018 at 10:47 am
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>
February 27, 2018 at 2:49 pm
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?
_____________
Code for TallyGenerator
March 2, 2018 at 7:29 am
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.
March 2, 2018 at 12:22 pm
March 2, 2018 at 12:28 pm
It is the table name in update statement. Tbl .
March 2, 2018 at 12:36 pm
Can you script this table "for create" and post it here?
_____________
Code for TallyGenerator
March 3, 2018 at 5:58 am
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
March 3, 2018 at 9:26 am
They are set True. Will isolationlevel="repeatable read (3)" cause deadlock? Thank you.
March 3, 2018 at 9:41 am
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
March 3, 2018 at 12:42 pm
Thank you GilaMonster. I will check with apps developer.
March 3, 2018 at 3:08 pm
Both statements have this in their lock description:
transactionname="implicit_transaction"
It kinda indicates that there is no explicit transaction opened in the code.
The trick is - the both locks are IX.
Which means there is no direct impact from the update on the locked pages.
[Col] is not a part of any index, so updating a value in it does not lock anything directly, with an X lock.
It might be something more far fetching.
Col may be a "status description", or a delimited list of some events associated with the PK value.
The UPDATE statement increases it's length and, because the clustered index pages are compacted by overnight re indexing job, it causes the splits of clustered index pages.
Clustered index page split will cause IX lock on it, and actual number of pages affected is very unpredictable.
Different UPDATEs affecting different records may clash when reshuffling clustered index pages causing a deadlock.
ayemya, can you please describe what exactly happens in these updates?
How distant are Col2 = @P1 values used in both locked queries from each other?
What values of Col are updated with what values of @P0?
_____________
Code for TallyGenerator
March 3, 2018 at 3:39 pm
Sergiy - Saturday, March 3, 2018 3:08 PMBoth statements have this in their lock description:transactionname="implicit_transaction"It kinda indicates that there is no explicit transaction opened in the code.
Indeed. It's an implicit transaction, started by the first statement in the batch (under the user setting IMPLICIT_TRANSACTIONS).
Old, but still valid: https://technet.microsoft.com/en-us/library/ms188317(v=sql.105).aspx
If there was no transaction at all, the transaction name would have read UPDATE.
Any update will take an IX at the table level, that's how SQL's locking works (well, unless it takes an X lock at the table level). Intention to lock one or more sub-resources exclusive. SQL won't take the X locks until it's locked all higher resources IX (page and table if the X lock is going to be row, or just table if the X lock is going to be page)
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
March 5, 2018 at 8:25 am
Sergiy - Saturday, March 3, 2018 3:08 PMBoth statements have this in their lock description:transactionname="implicit_transaction"It kinda indicates that there is no explicit transaction opened in the code.The trick is - the both locks are IX.Which means there is no direct impact from the update on the locked pages.[Col] is not a part of any index, so updating a value in it does not lock anything directly, with an X lock.It might be something more far fetching.Col may be a "status description", or a delimited list of some events associated with the PK value.The UPDATE statement increases it's length and, because the clustered index pages are compacted by overnight re indexing job, it causes the splits of clustered index pages.Clustered index page split will cause IX lock on it, and actual number of pages affected is very unpredictable.Different UPDATEs affecting different records may clash when reshuffling clustered index pages causing a deadlock.ayemya, can you please describe what exactly happens in these updates?How distant are Col2 = @P1 values used in both locked queries from each other?What values of Col are updated with what values of @P0?
Hi Sergiy,
I have rebuild/reorg index job running daily.
(@P0 int,@P1 int)UPDATE Tbl SET Col=@P0 WHERE Col2 =@P1
Col is integer column and no indexes created for the Col. It is a simple update. I am checking with developers regarding implicit transaction. Thank you
March 5, 2018 at 9:02 am
You need to identify the SELECT that is happening before the update on the table named tbl, and why it needs a table-level shared lock.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply