I'm developing a webapp that shares a database with an in-production desktop app (aka I cannot modify the database, only try to mimic behaviors). The module I'm working on now will store notes into this database in the notes table. I was able to get it to work, I added notes and they showed up in the desktop app, then after some time I realized the notes actual note text and descriptions were being overwritten. Looking at the rows in the database, I noticed modified_by user was set, telling me there was a duplicate key on insert, then later update. The primary key for this table is to auto-increment so I was very confused. After some digging I found a table called counters with a column called notes that had a count that matched the current index of notes table. Before just simply +1 the counter on every insert, I downloaded wireshark onto the db server and recorded the traffic on the db port and found this:
(Procedure when adding a note from desktop app)
UPDATE counters SET in_use = 'Y';
SELECT notes FROM counters WHERE key_col = 1;
/* Desktop app uses current count for new index */
UPDATE counters SET notes = /* current count +1 */ WHERE key_col = 1;
UPDATE counters SET in_use = 'N';
/* ...Inserts new note here with explicit ID = current count ... */
Now I'm even more confused. Why set the table to auto-increment at all? Second, there was never any checking of in_use before selecting the count and adding one... so what's the point of in_use? Couldn't this code lead to overwrites if two users inserted at the same time? Wouldn't the correct way to do this be to lock the counters table for every operation? I could try this, but I'm not sure how the desktop app will handle encountering a lock (based on experience - fatal error).
Aside from exactly duplicating this procedure and hoping for the best, I'm not exactly sure where to go from here. One thought is to:
<?php
const MAX_ATTEMPTS = 3;
$curKey;
for($i = 0; $i < MAX_ATTEMPTS; $i++){
/*
SELECT in_use, notes from counters where key_col = 1;
...
*/
if( 'N' === $result['in_use'] ){
$curKey = $result['notes'];
/* INSERT count here - $curKey++ */
break;
}
/* Sleep for .25 seconds to allow for current operation to finish */
usleep(250000);
}
if( null == $curKey ){
throw \Exception('Could not insert note because counter table locked after '. MAX_ATTEMPTS .' attempts');
}
/* INSET note code here... */
This seems ok, but could still possibly overwrite because a) time between select count and insert new count b) Desktop app does not seem to do any checking.
Any thoughts/suggestions?
EDIT: Made a stored procedure to do checking during select and insert.
DELIMITER $$
CREATE DEFINER=`testUser`@`%` FUNCTION `getNextNoteIndex`(appKey INTEGER) RETURNS int(11)
BEGIN
SELECT IF(`in_use` = 'N', `notes`, NULL) INTO @curIndex FROM `counters` WHERE `app_key` = appKey;
IF @curIndex IS NOT NULL
THEN
SET @newIndex = @curIndex + 1;
UPDATE `counters` SET `notes` = @newIndex WHERE `app_key` = appKey AND `in_use` = 'N' AND `notes` = @curIndex;
IF ROW_COUNT() = 1
THEN
RETURN @newIndex;
END IF;
END IF;
RETURN NULL;
END
Usage:
SELECT testDB.getNextNoteIndex(1) AS $index;
