0

TL;DR: I want a table in my database to act as an priority queue (Here a min-queue on ID), are there any free (for commercial use) DBMS that can solve my problem? And how would a query look like?

I want to have many concurrent users to select a single row from a table, e.g. I have the table

+------+-------+--------+------------+
| ID   | Col 1 | Col 2  | Processed  |
+------+-------+--------+------------+
| 1000 | data  | data   | False      |
+------+-------+--------+------------+
| 1001 | data  | data   | False      |
+------+-------+--------+------------+
| 1002 | data  | data   | False      |
+------+-------+--------+------------+

and if I have 3 users, I want to do atomic SELECT and UPDATE, such that the first query returns Col 1 and Col 2 from the row with the lowest ID and marks Processed as True, such that the the next query would return the row with ID = 1001.

The table is expected to be small, i.e. at most 100,000 rows and the queue is expected to be emptied in about 8 hours (around 3-4 queries per second, peak might be around 50 queries in a single second). I am used to PostgreSQL/MySQL and have used MongoDB briefly.

How should I design my system to cope with this? How would a query look like? Would a table lock noticable?

YnkDK
  • 681
  • 9
  • 26
  • You should look into [pessimistic concurrency](http://stackoverflow.com/a/129397/3410196) – Alexander Derck Jan 29 '16 at 09:10
  • If you're not using SQL Server you should remove the tag. SQL Server based answers are unlikely to help you. – David Rushton Jan 29 '16 at 09:25
  • "re there any free (for commercial use) DBMS that can solve my problem? " is not normally a question we answer. However, I would think you can use any DB that has row/document level locking can you not? – Sammaye Jan 29 '16 at 09:25
  • @Sammaye I suspect that I can (one of the three mentioned), but it might be that one was preferred over the others. If there was a DBMS that was designed for exactly this, I would most likely change to that - if it was free for commercial use. – YnkDK Jan 29 '16 at 09:32
  • So what you want is a work query? There are applications for that, please don't reinvent the wheel. – Jakub Kania Jan 29 '16 at 10:06

1 Answers1

0

I think PostgreSQL should be a good candidate for this.

Some interesting pointers:

Unless I am missing something, each thread does a SELECT FOR UPDATE (with SKIP ROW LOCKED) so that it picks the first non-locked row and locks it for everyone else, processes it, and flags it / deletes it once done.

Robins Tharakan
  • 2,209
  • 19
  • 17
  • That was a feature that I did not know. It seems like `MySQL` can also be used, however I am not sure how to handle the `SKIP ROW LOCKED` in this case. `PostgreSQL` it is then! – YnkDK Jan 29 '16 at 10:18