Parallel update on same table but different columns

Question

Parallel update on same table but different columns

sumit4bansal

SSC Veteran

Points: 237
More actions
August 5, 2015 at 1:23 am

#299385

I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
Please advice! Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

sumit4bansal (8/5/2015)
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
Please advice! Thanks

Quick suggestion, don´t try to vertically segment the batch, split the work horizontally by limiting the number of rows in each batch. There is no such thing as a column lock, the finest lock granularity is a row, so if you are trying to simultaneously update more than one column in a row, with different spid for each column then you are bound to have collisions.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post the deadlock graph, the table definitions and the update statements.

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

Eric M Russell SSC Guru Points: 125522 More actions · Answer 3

sumit4bansal (8/5/2015)
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
Please advice! Thanks

It's "working fine" except for persistent deadlocks?

What you've just described is a text book example of how to create a blocking and potentially deadlocking scenario. SQL Server locks rows and writes whole pages, but there is no advantage to updating columns separately. If you can update all 8 columns in a single update operation, then that should be ideal.

Sometimes when updates or deletes are performed on very large tables, like starting with 10 million rows and higher, then it's beneficial to update in multiple samller sized batches (like 100,000 rows at a time) to reduce the commit size and cycle the transaction log.

http://www.sqlservercentral.com/Forums/FindPost1690031.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

r.scott.maxwell Old Hand Points: 332 More actions · Answer 4

Rowlocks are across an entire row, so you cannot do parallel updates on the same row only on different columns.

One option, however, is to vertically partition a table, which is a pain and only worthwhile on very wide tables,

The best bet is to either update in batches of, say, 50000 rows, which will remove the rowlocks and/or table lock quickly, and will result in a virtual queue of update statements, which may mimic parallel updates, though they are still serial.

I can elaborate more for your particular situation if I knew what you are looking for. Frankly, the DBA truism (mantra?) of "it depends" holds true here.