May 5, 2017 at 12:01 pm
Hi guys,
I am trying to understand why Cluster index update deadlock occurred.I have 2 tables:
table1: date,item_id and client_id. (actually it has more columns)
date: cluster index
Item_id: none cluster index
client_id: none cluster index
table2:Item_ID,Col1,Col2
Item_id: cluster index
I have update and select statements involved in deadlock:
Update table1 set date='20170101' where item_id=10
Select t2.Item_ID,t2.Col1,t2.Col2 from table2 t2,table1 t1 where t2.Item_ID=t1.Item_ID and t1.date>'20170401' and t.client_id=123
My question is since i am updating only date column why is select statement holding UPDATE?
May 8, 2017 at 6:00 am
Both statements are part of a transaction. It's not that the SELECT is holding the UPDATE, it's that the UPDATE and the SELECT are executing as a set of operations.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2017 at 11:17 am
Thank you Grant for reply.They are separate processes,i just listed together.
Session 1: Update table1 set date='20170101' where item_id=10
Session 2: Select t2.Item_ID,t2.Col1,t2.Col2 from table2 t2,table1 t1 where t2.Item_ID=t1.Item_ID and t1.date>'20170401' and t.client_id=123.
I've done some research and found that clustered index column update is row update(delete+insert) not in-place,which answers my question.
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply