CODEWITHSUNDEEP
mysqlsql
John Nagle
John Nagle

Reputation: 1578

SQL table locking race condition - SELECT then INSERT

Here's a neat locking problem with MariaDB/MySQL.

A server is reassembling multipart SMS messages. Messages arrive in segments. Segments with the same "smsfrom" and "uniqueid" are part of the same message. Segments have a segment number starting from 1 up to "segmenttotal". When all segments of a message have arrived, the message is complete. We have a table of unmatched segments waiting to be reassembled, as follows:

    CREATE TABLE frags (
       smsfrom TEXT,
       uniqueid VARCHAR(32) NOT NULL,
       smsbody TEXT,
       segmentnum INTEGER NOT NULL,
       segmenttotal INTEGER NOT NULL);

When a new segment comes in, we do, in a transaction, 

   SELECT ... FROM frags WHERE smsfrom = % AND uniqueid = %;

This gets us all the segments received so far. If the new one plus these has all the segment numbers, we have a complete message. We send the message off for further processing and delete the fragments involved. Fine.

If not all segments have arrived yet, we do an INSERT of the segment we just got. Autocommit is off, so both operations are part of a transaction. InnoDB engine, incidentally.

This has a race condition. Two segments come in at the same time for a two-segment message, and are processed by separate processes. Process A does the SELECT, finds nothing. Process B does the SELECT, finds nothing. Process A inserts segment 1, no problem. Process B inserts segment 2, no problem. Now we're stuck - all segments are in the table but we didn't notice. So the message is stuck there forever. (In practice we do a purge every few minutes to remove old unmatched stuff, but ignore that for now.)

So what's wrong? The SELECTs lock no rows, because they find nothing. We need a row lock on a row that doesn't exist yet. Adding FOR UPDATE to the SELECT doesn't help; nothing to lock. Nor does LOCK IN SHARE MODE. Even going to a transaction type of SERIALIZABLE doesn't help, because that's just global LOCK IN SHARE MODE.

OK, so suppose we do the INSERT first and then do a SELECT to see if we have all the segments. Process A does the INSERT of 1, no problem. Process B does the insert of 2, no problem. Process A does a SELECT, and sees only 1. Process B does a SELECT, and sees only 2. That's repeatable read semantics. No good.

The brute force approach is a LOCK TABLE before doing any of this. That ought to work, although it's annoying, because I'm in a transaction involving other tables and LOCK TABLE implies a commit.

Doing a commit after each INSERT might work, but I'm not totally sure.

Is there a more elegant solution?

Upvotes: 2

Views: 327

Answers (2)

John Nagle
John Nagle

Reputation: 1578

As I wrote above, I ended up doing this:

INSERT  ... -- Insert new fragment.
COMMIT
SELECT ... FROM frags WHERE smsfrom = % AND uniqueid = % FOR UPDATE;

Check if the SELECT returned a complete set of fragments. If so, reassemble and process message, then

DELETE ... FROM FRAGS WHERE smsfrom = % AND uniqueid = %;

Both the COMMIT and the FOR UPDATE are necessary. The COMMIT is needed so that each process sees any INSERT from another process. The FOR UPDATE is needed on the SELECT to row lock all the fragments until the DELETE can be done. Otherwise, two processes might see the complete set of fragments in the SELECT and reassemble and process the message twice.

This is surprisingly complicated for a one-table problem, but seems to work.

Upvotes: 0

Tim Seed
Tim Seed

Reputation: 5289

Why not

1) Process 1. Insert Into your frag table. Nothing else

Insert .... Commit;

2) Process 2 This find the complete multipart SMS by

select smsfrom, unique, uniqueid,count() from frags group by smsfrom, unique, unique having count() == segmenttotal;

Move them to the new table

delete from frags where smsfrom=<> and unique = <>;

commit;

Upvotes: 1

Related Questions