0

The following link describes the serializable transaction isolation level.

http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

Suppose I have one user updating table [dbo].[Table_A]. And another user is updating table [dbo].[Table_B]. And I want to serialize these two update statements (meaning wait for the first to finish before the second one begins), despite the fact that we are touching different tables. I'm guessing I cannot use a table lock, but perhaps a range lock would accomplish this. Can someone help me understand what the code may look like? Or won't this work with transaction isolation level functionality in SQL Server 2008?

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
  • 1
    Why would you want to serialize updates to different tables? What problem are you trying to solve with that? – Quassnoi Apr 25 '13 at 08:17
  • I am dealing with a directed acyclic graph (poly-hierarchy) data model. One table is joined with the other and enforces inheritance. – JustBeingHelpful Apr 25 '13 at 08:18
  • So you are updating `table_b` using data from `table_a`? Could you please post your update statements? – Quassnoi Apr 25 '13 at 08:18
  • For the sake of argument, let's suppose they are not joined. Because I have examples where I have no joins. update [dbo].[Table_A] set x=1 & update [dbo].[Table_B] set y=1 – JustBeingHelpful Apr 25 '13 at 08:20
  • I don't see why you can't do this in parallel – Quassnoi Apr 25 '13 at 08:23
  • Suppose you have a hierarchy and multiple users are dragging and dropping nodes where they please at the same time. In that case, we have parent/child records changing constantly, and both foreign keys are referenced from the same table. In my case, I have a poly-hierarchy, so I want all application operations serialized for the beta release, to avoid complications. – JustBeingHelpful Apr 25 '13 at 08:27
  • MacGyver: this is better explained with examples. – Quassnoi Apr 25 '13 at 08:33
  • I'll be back with examples when the applications is functioning. For now, I want to simplify development so we don't waste time. What you gave me is great in the mean time! – JustBeingHelpful Apr 25 '13 at 08:35
  • seems you've chosen the right nick! :) – Quassnoi Apr 25 '13 at 08:41

1 Answers1

1

You may place application locks:

EXEC  sp_getapplock @resource = 'my_resource_token', @lockMode = 'Exclusive'

UPDATE ...

EXEC  sp_releaseapplock @resource = 'my_resource_token'

This will lock if another session has aquired my_resource_token.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614