December 17, 2008 at 2:10 am
Hi,
I have an update statement which will update 46 columns of a table based on one primary key. This particular update statement is giving a deadlock. So, I reduced the size of Update statement to only the required fields which are 16 in number. So the update statement which updates 16 columns is not throwing any deadlock.
What can be the issue here? What is the solution if I want to update all the 46 columns.
I have Primary Key Index on just one column.
Thanks,
TNV
December 17, 2008 at 3:33 am
How many rows are you updating in one shot.
I would update rows in batches....
December 17, 2008 at 3:40 am
Did you trace the deadlock graph (T1222)? That should help you solving the problem.
[font="Verdana"]Markus Bohse[/font]
December 17, 2008 at 6:21 am
Simply updating 46 columns should not cause a deadlock. This would cause blocking, as one resource would have to wait on another while it's update completed. You have other processes around the deadlock that are leading to it. Are there triggers on the table that update other tables? Do you have a series of reads and then updates? Are you updating more than one table in different sequences? Something along these lines is causing the deadlock, not simply requesting an udpate of a given row.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 6:22 am
Also, if this question is related to this post:
http://www.sqlservercentral.com/Forums/Topic621088-145-1.aspx
You really shouldn't post multiple times because it just diffuses the conversation, making it harder for people to help and duplicating effort.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 6:39 am
The post over http://www.sqlservercentral.com/Forums/Topic621088-145-1.aspx is related to this one.
But I mistook blocking as deadlock. I have resolved the blocking one. But the deadlock is the one I couldn't understand.
Thanks for your time.
December 17, 2008 at 6:43 am
Ah sorry, different issue, different post. My mistake.
BTW, it's always helpful, but hardly necessary, that if you answer a question you posted, go ahead and post the answer. It'll not only prevent people late the party from trying to help, but someone else may have the exact same problem and you'll be the guy to provide them with the answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply