CODEWITHSUNDEEP
sql-serversql-server-2008lockingdeadlockisolation-level
Paul Hiles
Paul Hiles

Reputation: 9788

Queue up requests to a sql server stored procedure

I am working with a stored procedure that:

  1. determines the number of rows in the table where the chosenBy column is null

  2. picks one of these rows at random

  3. updates the chosenBy column of this row

  4. returns the row to the client

How do I prevent clients from choosing the same row in situations where they choose at exactly the same time?

I have tried various table hints and isolation levels but just get deadlock exceptions at the client. I just want the second call to wait for the fraction of a second until the first call is completed.

Upvotes: 1

Views: 1818

Answers (1)

Martin Smith
Martin Smith

Reputation: 453910

One way of avoiding deadlocks (as indicated in your question title) would be to serialise access to that procedure.

You can do this with sp_getapplock and sp_releaseapplock

See Application Locks (or Mutexes) in SQL Server 2005 for some example code.

Upvotes: 2

Related Questions