在INSERT语句中使用多个SELECT防止竞争条件

Question

I am writing a query in order to prevent concurrent queries to mess things up. I'm pretty sure there's a much better way to do it, and that's why I'm here.

Basically the columns of the table look like this:

id      : INT(10) unsigned auto_incremented
number  : INT(6) unsigned zerofill
date    : DATETIME

When I insert a new row, here is how I need to set its number value :

• If the last inserted row's date is today, then number = last inserted row's number + 1
• If the last inserted row's date is yesterday or before, then number = 1

I thought about first select the last row's date and number, set the new number using php and then make my insert.

$pdo->beginTransaction();

$stmt = $pdo->prepare("SELECT `number`, `date` FROM `table` ORDER BY `id` DESC LIMIT 1");
$stmt->execute();
$infos = $stmt->fetch(PDO::FETCH_ASSOC);

$date = date("Y-m-d H:i:s");
if (date("Ymd", strtotime($infos['date'])) < date("Ymd", strtotime($date))) {
    $number = 1;
} else {
    $number = $infos['number'] + 1;
}

$stmt2 = $pdo->prepare("INSERT INTO `table` (`number`, `date`) VALUES (:number, :date)");
$stmt2->execute(array(":number" => $number, ":date" => $date));

$pdo->commit();

Are transactions enough to prevent another thread selecting the same number and therefor trying to insert the same number I am trying to ?

Since I couldn't answer that question myself, even using our dear friend Google and SO, I thought "Can't I just have MySQL do all the work ?" and came up with this query :

INSERT INTO `table`(`number`, `date`)
SELECT IF(
    DATE((SELECT `date` FROM `table` ORDER BY `id` DESC LIMIT 1)) < CURDATE(),
    '1',
    (SELECT `number` + 1 FROM `table` ORDER BY `id` DESC LIMIT 1)
), NOW()

Is it a better way to do it ?

Should I just lock the table before doing anything ?

Am I just too dumb to see how this should be done ?

Answer 1

This may suffice:

First, change the table. No id, date is aDATE`, "natural" PK.

number INT(6) unsigned zerofill,
date   DATE,
PRIMARY KEY(date, number)

Then the transaction is

BEGIN;
($not_first, $number) = SELECT date = CURDATE(), number
                     FROM tbl
                     ORDER BY date DESC, number DESC
                     LIMIT 1
                     FOR UPDATE;  -- important
$number = $not_first ? $number+1 : 1;
INSERT INTO tbl (date, number, ...)
    VALUES
    ( CURDATE(), $number, ...);
COMMIT;

But there is still a bug if the SELECT runs just before midnight, but the INSERT runs just after.

So, this may avoid the bug, and avoid the transaction, since the statement should be atomic:

INSERT INTO tbl (date, number, ...)
    VALUES
    ( CURDATE(),
      IFNULL( ( SELECT MAX(id) FROM tbl WHERE date = CURDATE() ),
                   1 ) AS number,
      ...  -- whatever else you are inserting into `tbl`
    );

Regardless of how you do it, you must check for errors -- another connection could be doing the same thing and lead to a deadlock or something.