0

A large SQL Server 2008 table is normally being updated in (relatively) small chunks using a SNAPSHOT ISOLATION transaction. Snapshot works very well for those updates since the chunks never overlap. These updates aren't a single long running operation, but many small one-row insert/update grouped by the transaction.

I would like a lower priority transaction to update all the rows which aren't currently locked. Does anyone know how I can get this behavior? Will another SNAPSHOT ISOLATION transaction fail as soon as it a row clashes, or will it update everything it can before failing?

Could SET DEADLOCK_PRIORITY LOW with a try-catch be of any help? Maybe in a retry loop with a WHERE which targets only rows which haven't been updated?

Djof
  • 603
  • 1
  • 7
  • 20

2 Answers2

2

Snapshot isolation doesn't really work that way; the optimistic locking model means it won't check for locks or conflicts until it's ready to write/commit. You also can't set query 'priority' per se, nor can you use the READPAST hint on an update.

Each update is an implicit atomic transaction so if 1 update out of 10 fails (in a single transaction) they all roll back.

SET DEADLOCK_PRIORITY only sets a preference for which transaction is rolled back in the event of a dealdlock (otherwise the 'cheapest' rollback is selected).

A try-catch is pretty much a requirement if you're expecting regular collisions.

The retry loop would work as would using a different locking model and the NOWAIT hint to skip queries that would be blocked.

Code Magician
  • 23,217
  • 7
  • 60
  • 77
  • I'm curious why you think snapshot isolation isn't adapted to write concurrency. I've seen concurrent updates fail on row-lock so I think the table itself isn't locked. I've modified a bit the description to explain the workload a bit, and why I was talking of priority. Too bad there isn't a `WRITEPAST`. Thanks for your input. – Djof Aug 04 '11 at 15:46
  • :S I had read committed snapshot in my head when I wrote that. Still, it won't work the way you want (only updating rows that aren't locked) because it doesn't really check for locks/conflicts until the 'last moment' – Code Magician Aug 04 '11 at 16:18
  • I ended up just doing a serializable transaction. You were the most helpful, so I'm accepting your answer, even if it's not the one I was hoping for. – Djof Sep 12 '11 at 20:55
0

SNAPSHOT ISOLATION transaction fails as soon as it encounters an update conflict. However, I would use some queue outside the database to prioritize updates.

A-K
  • 16,804
  • 8
  • 54
  • 74
  • Thank you for clarifying this point. These two types of updates come from different components, but I'll look into scheduling them. – Djof Aug 04 '11 at 15:27