1

How is it possible to make such scenario:

I have table with Order objects. There are many orders in DB. There is mechanism with few threads that can get the next order from DB (but need locks, that same order won't be executed few times).

How is it possible in SQL Server level to make that one thread reads the row (order), deletes it from DB and returns it to C# written mechanism which executes it, when other thread can see the row is locked, it skips that row and takes the next one?

The orders have execution priority and clustered index is on priority column.

This stuff will be executed in stored procedure.

Tomas
  • 1,212
  • 1
  • 9
  • 10
  • 3
    [Locking hints](http://technet.microsoft.com/en-us/library/aa213026%28v=sql.80%29.aspx), and [Transaction Levels](http://technet.microsoft.com/en-us/library/ms173763.aspx). There are also tons of questions here about row locking. – Andrew Jan 03 '14 at 17:48
  • 2
    (Note the dupe uses `UPDATE` and you need `DELETE` but essentially the same) – Martin Smith Jan 03 '14 at 18:04
  • Note that you have to be extremely careful with the ordering of operations between the DB and the application, or you can lose items (application crash after removal from db). – Clockwork-Muse Jan 05 '14 at 08:31

1 Answers1

0

I'd add a column to the table called "locked_by_thread" with a thread number, and it'd be null when no thread had it locked. You'd have to commit more often with this scenario, but it's what I'd do.

Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • i am not allowed to change the table structure. I was told to make it using SQL Server locks/isolation level or sth similar, but thanks, good idea – Tomas Jan 03 '14 at 17:55
  • 2
    Why don't simple call this new column as OrderState with some possible values: New, Processing, Processed. – bjnr Jan 03 '14 at 19:52
  • 1
    That's a great idea, but every time I've tried such a thing I also needed to know, when something was blocking it, who was blocking it-- in case the process dies or something and we need to zero them out. I suppose you could have a timestamp to say when the column was set. – Joe Love Jan 03 '14 at 19:55