0

I have n machines writing to DB (sql server) at the exact same time (initiating a transaction). I'm setting the Isolation level to serializable. My understanding is that whichever machine's transaction gets to the DB first, gets executed and the other transactions will be blocked while this completes.

Is that correct?

Sam
  • 933
  • 5
  • 14
  • 26
  • A transaction does not lock the whole server. At the table level you can take a row lock, page lock, or table lock. Are you sure you need serializable? The more extensive the lock the more you limit concurrency. – paparazzo Aug 14 '12 at 15:32

3 Answers3

2

It depends - are they all performing the same activities? That is, exactly the same statements executing in the same order, with no flow control?

If not, and two connections are accessing independent objects in the DB, they can run in parallel.

If there's some overlap of resources, then some progress may be made by multiple connections until one of them wants to take a lock that another already has - at which point it will wait. There is then the possibility of deadlocks.

SERIALIZABLE:

  • Statements cannot read data that has been modified but not yet committed by other transactions.

  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • They are all performing the same activities. Flow control is there. At the start of the transaction, if the DB reflects that same set of data that this transaction has, is already committed, it completes the transaction. – Sam Aug 14 '12 at 20:25
2

whichever machine's transaction gets to the DB first, gets executed and the other transactions will be blocked while this completes

No, this i incorrect. The results should be as if each transaction was executed one after another (serially, hence the isolation level name). But the engine is free to use any implementation it likes, as long as it honors the guarantees of the serializable isolation model. And some engines actually implement it pretty much as you describe it, eg. Redis Transactions (although Redis has no 'isolation level' concept).

For SQL Server the transactions will execute in parallel until they hit a lock conflict. When a conflict occurs the transaction that has the lock granted continues undisturbed, while the one that requests the lock in a conflicting mode has to wait for the lock to free (for the granted transaction to commit). Which transaction happens to be the request and which one happens to be the granted is entirely up to what is being executed. That means that it well may be the case that the second machine gets the grant first and finishes first, while the first machine waits.

For a better understanding how the locking behavior differs under serializable isolation level, see Key-Range Locking

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

Yes, this will be true for write operations at any isolation level: "My understanding is that whichever machine's transaction gets to the DB first, gets executed and the other transactions will be blocked while this completes."

The isolation level helps determine what happens when you READ data while this is going on. Serializable read operations will block your write operations, which might be the behavior you want.

criticalfix
  • 2,870
  • 1
  • 19
  • 32