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.
😎