1

I have the following tables (greatly simplified):

Jobs: JobId, JobState
Data: DataId
JobsData: JobId, DataId

the idea for JobsData is that any item in Data can be associated to one or more item in Jobs and each item in Jobs can have one or more item from Data associated with it.

Now I have two transactions:

-- TRAN1
BEGIN TRAN
INSERT INTO Data VALUES (NewDataId)
INSERT INTO Jobs VALUES (NewJobId, StateInitial)
INSERT INTO JobsData VALUES (NewDataId, NewJobId)
UPDATE Jobs SET JobState=StateReady WHERE JobId=NewJobId
COMMIT TRAN

-- TRAN2
DECLARE @selectedId;
SELECT TOP (1) @selectedId=JobId FROM Jobs WITH (UPDLOCK, READPAST) WHERE JobState=StateReady
IF @selectedId IS NOT NULL
    SELECT DataId FROM JobsData WHERE JobId = @selectedId

The code with the locking hints comes from this answer. Its purpose is to have multiple TRAN2 instances running in parallel and never getting the same JobId.

That code has been working fine with SQL Server (default isolation level READ_COMMITTED) but in SQL Azure TRAN2 sometimes works incorrectly - the first SELECT yields a non-null JobId, but the second SELECT yields and empty resultset. I assume this is because the default isolation level in SQL Azure is READ_COMMITTED_SNAPSHOT.

I want to make minimum changes to get my problem resolved - so that TRAN2 either retrieves null in the first SELECT or retrieves the right result set in the second SELECT.

Which of the table hits do I apply to which of the SQL statements I have?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979

1 Answers1

0

For a starter, if you want queues in Azure, use Azure Queues or Service Bus Queues.

If you insist on implementing queues over relations, use the pattern from Using Tables as Queues. Specifically:

  • do not use state fields instead of event queues ('Ready' is a record in your jobs queue, an event, not a state on the job).
  • dequeue using DELETE ... WITH OUTPUT ...

Trust me on this one.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • This is all neat, but how does that pattern address cases when an item is fetched from the queue and then the entity that fetched it terminates? – sharptooth Jan 10 '13 at 12:49
  • Transaction semantics still apply. `BEGIN TRANSACTION/Dequeue/Process/COMMIT`. In case of crash, the dequeue is undone as part of the rollback. Of course, many 'processing' are too lenghty/unpredictable (eg. a HTTP call) to let them occur in a transaction. The typical pattern for such is to use a 'pending queue' and preemptively post a 'retry' event pending in, say, 10 minutes: `BEGIN TRAN/dequeue/post retry Time.Now+10.minutes/COMMIT/process/BEGIN TRAN/cancel retry/COMMIT`. This ensures that in case of a crash the event is retried after a reasonable time. – Remus Rusanu Jan 10 '13 at 13:02
  • I see the point, but that's a lot of complication to my code. Can I just fix my existing code with locking hints? – sharptooth Jan 10 '13 at 13:30
  • No. You can try to fix the 'dequeue' with something like `with u as (SELECT TOP (1) DataId, JobState FROM Jobs WITH (READPAST) WHERE JobState='StateReady') update u set JobState='Working' output deleted.DataId` but you still didn't solve the issue of crash/retry. – Remus Rusanu Jan 10 '13 at 13:43
  • How will that fix my problem? My problem is that `SELECT` returns an item in the correct state, but data in another table corresponding to that item has not yet become visible to the transaction. – sharptooth Jan 10 '13 at 13:49
  • No, that's not your issue. Your code is incorrect under concurrency. On Azure you get different timing and you see it more often. – Remus Rusanu Jan 10 '13 at 13:56
  • Well, okay, how do I change the code so that the second `SELECT` sees the right data? – sharptooth Jan 10 '13 at 14:22
  • How do you prevent the row(s) from `JobsData` with the @selectedId from being deleted between the two SELECTs? Can you guarantee that the removal occurs *only* if the corresponding @selectedId row in `Jobs` is X locked *first*? – Remus Rusanu Jan 10 '13 at 14:55
  • Let's assume that rows from `JobsData` are never deleted, only inserted. – sharptooth Jan 10 '13 at 14:57