CODEWITHSUNDEEP
c#.netsql-serverdatabasenetwork-programming
MichaelVerossa
MichaelVerossa

Reputation: 469

Centralized database: multiple consumers

I have one table with starting data and multiple consumer applications working on different machines in local network.

Please suggest how do I can orginize them to take each record from this database only once, so there will no be any situation where 2 or more apps taken the same record from the table.

I see 2 possible ways to resolve it, but not sure:

  1. One more app that would receive requests from consumer apps and give them unique non-processed records,
  2. Implement some stored procedure in database...

Please advise..

Upvotes: 3

Views: 712

Answers (4)

James Anderson
James Anderson

Reputation: 27478

What you are asking for is default database behavior. You could use "SELECT ...... FOR UPDATE" and the database engine will lock the row and make it unavailable to other users. However this is only good practice if your program wants to hold the data for a short length of time (less than 1 second) your application which holds the data for much longer would encounter a lot of deadlock and contention problems.

You could do this in application code by having a couple of columns like "RESERVED_BY_USER" and "RESERVED_UNTIL", which the applications could manage themselves.

However from what you are describing I think what you really want is a queue as described here

Upvotes: 0

Saanch
Saanch

Reputation: 1844

Add one more field to the table with ProcessedBy and you can set the value to null if no machine is started processing it and set the value to the machinename if it started processing. While getting the record you can verify this processedBy field and if it is null you can assign to any machine.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062745

Various options:

  • use a transaction and mark each record as in-progress when you take it; never take records that are already marked in-progress (note: you may need to "unmark" items that take too long, for example the machine died)
  • use a random sort (NEWID perhaps) to select rows that are unlikely to conflict

however, personally there are other storage metaphors that work nicely here, for example "redis" has a few suitable operations:

  • RPOPLPUSH (moves 1 record between lists, and returns it)
  • SPOP (removes and returns 1 record at random)
  • RPOP / LPOP (removes and returns 1 record from the end of a list)

all of these are atomic, avoiding all the bits that would make this a pain in SQL.

Upvotes: 1

Emond
Emond

Reputation: 50672

To retrieve the row, create a stored procedure that:

  • starts a transaction
  • selects and locks a row that is available
  • updates a column of the row to mark it unavailable for others
  • commits the transaction
  • passes the row to the client

To update the row, create a stored procedure that:

  • begins a transaction
  • updates the row (optionally check the rowversion)
  • updates the availability of the row
  • commits the transaction

Extra's:

  • you might want to add a date/time to the row so you'll know when a row was locked
  • if a row has been locked for a long time (longer than you expected) you could free the lock and let someone else select the row. However, to prevent the previous owner from updating (he doesn't know he lost the lock) you should have a rowversion column in the table.
  • you could keep the (un)available status in a separate table that might make it easier to find available rows.

Upvotes: 3

Related Questions