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 :
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 ?
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.