Search code examples
sqldatabaseconcurrencymariadbrace-condition

Synchronization of queries to a MariaDB database

Because of some high-availability considerations, I design a system where multiple processes will communicate/synchronize via the database (most likely MariaDB, but I am open to looking into PostgreSQL and MySQL options).

One of the requirements identified is that a process must take a piece of work from the database, without letting another process take the same piece of work concurrently.

Specifically, here is the race condition I have in mind:

  1. Process A starts a SQL transaction and runs SELECT * FROM requests WHERE ReservedTS IS NULL ORDER BY CreatedTS LIMIT 100. Here ReservedTS and CreatedTS are DATETIME columns storing the time the piece of work was created by a work submitter process and reserved by a work executor process correspondingly.
  2. Process B starts a transaction, runs the same query and gets the same set of results.
  3. Process A runs UPDATE requests WHERE id IN (<list of IDs selected above>) AND ReservedTS IS NULL SET ReservedTS=NOW()
  4. Process B runs the same query, however, because its transaction has its own snapshot of the data, the ReservedTS will appear not null to Process B, so the items get reserved twice.
  5. Process A commits the transaction.
  6. Process B commits the transaction, overwriting the values of process A.

Could you please help to resolve the above data race?

Solution

  • You can easily do that by using exclusive locks:

    For simplification the test table:

    CREATE TABLE t1 (id int not null auto_increment primary key, reserved int);
INSERT INTO t1 VALUES (0,0), (1,0);

    Process A:

    BEGIN
SELECT id, reserved from t1 where id=2 and reserved=0 FOR UPDATE;
UPDATE t1 SET reserved=1 WHERE id=2 and reserved=0;
COMMIT

    If Process B tries to update the same entry before Process A finished the transaction it has to wait until lock was released (or a timeout occurred):

    update t1 set reserved=1 where id=2 and reserved=0;
Query OK, 0 rows affected (12.04 sec)
Rows matched: 0  Changed: 0  Warnings: 0

    And as you can see, Process B didn't update anything.