Concurrent UPDATE operations on a large table in SQL Server 2012: Avoiding deadlocks

Data and Databases
1

Hey folks, I’ve got a big problem with my database setup. I’m working with a massive table (TABLE1) in SQL Server 2012. It’s huge - 9GB of data, 20GB index space, and about 12 million rows.

I’ve got several UPDATE statements that I need to run on this table. They’re all updating different columns, and none of them use data from columns that are being updated by other statements. Right now, I’m running them one after the other, but it’s taking forever.

I want to speed things up by running these UPDATEs at the same time. But I’m worried about deadlocks. Is there a way to avoid them? Would using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED help?

Here’s an example of what one of my UPDATE statements looks like:

UPDATE BigTable
SET NewColumn = OldColumn + 10
FROM BigTable
JOIN AnotherTable ON AnotherTable.ID = BigTable.ID
WHERE BigTable.Status = 'Active'

Any advice on how to handle this would be great. Thanks!

2

yo FlyingEagle, that’s a beast of a table! Have u tried using WITH (NOLOCK) hint? It might speed things up, but be careful cuz it can cause dirty reads. Also, maybe break ur updates into smaller chunks? Like update 100k rows at a time. Could help avoid those nasty deadlocks.

3

hey there flyingeagle! Wow, that’s a massive table you’re dealing with. Have you considered using partitioning? It might help with concurrent updates. Also, what about using batch updates instead of one big update? That could reduce lock contention.

curious to hear more about your setup. How often do you need to run these updates? Are they part of a regular maintenance process?

4

Concurrent updates on large tables can indeed be challenging. One approach to consider is implementing optimistic concurrency control. This involves adding a version column to your table and including it in your WHERE clause. For example:

UPDATE BigTable
SET NewColumn = OldColumn + 10,
Version = Version + 1
FROM BigTable
JOIN AnotherTable ON AnotherTable.ID = BigTable.ID
WHERE BigTable.Status = ‘Active’
AND BigTable.Version = @OriginalVersion

This method allows multiple transactions to proceed without locking, but only commits if the data hasn’t changed since the transaction began. It’s particularly effective for scenarios with low contention.

Additionally, consider upgrading to a more recent version of SQL Server if possible. Newer versions have improved concurrency handling mechanisms that could benefit your use case.