CODEWITHSUNDEEP
phpmysqlcounter
Michael
Michael

Reputation: 2942

Make a reliable counter in MySQL

I want to create a counter in an application written in PHP using a MySQL database. The user gives a string as input. This string represents the key to a database record. It stores a integer counter value. The user gets back the incremented value.

This is how I do it at the moment:

// connect to database
try {
    $db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpasswd);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch(Exception $ex) {
    echo "DB Error: ".$ex->getMessage();
    exit;
}

// update and get new value
try {
    $db->beginTransaction();
    $nc = updateNC($db, $grundsig, $library);
    $db->commit();
} catch(Exception $ex) {
    $db->rollBack();
    $err = 'DB Error: '.$ex->getMessage();
}

// [...]

function updateNC($db, $grundsig, $library) {

    // increment counter ("nc")
    $query = $db->prepare('
        update numeruscurrens n
        join libraries l using(libid)
        set n.nc = n.nc+1
        where n.grundsig = :grundsig and l.libname = :libname
    ');
    $query->execute([
        ':grundsig' => $grundsig,
        ':libname' => $library
    ]);

    // get new counter value
    $query = $db->prepare('
        select *
        from numeruscurrens n
        join libraries l using(libid)
        where n.grundsig = :grundsig and l.libname = :libname
    ');
    $query->execute([
        ':grundsig' => $grundsig,
        ':libname' => $library
    ]);
    $result = $query->fetchAll();
    return $result[0]['nc'];
}

The important part happens in updateNC function. I do a update to increment the counter and I want to have the new counters value.

Now the problem is between these two commands another user could do a update. It could happen that two users get wrong counter values.

How can I reliably increment a counter and get the new value for this user?

I also thought about using a key-value-database like Redis which has the INCR command. But this would be a little exaggerated, I think.

Upvotes: 2

Views: 1216

Answers (1)

jorgonor
jorgonor

Reputation: 1719

If what you want is to be sure they will get the immediately incremented value then you should be using a way to lock the row you are going to update before commiting the transaction. This can be done using the FOR UPDATE hint and a previous SELECT query to lock the row you're about to update.

I will add only the SQL code to focus on the actual problem.

SET @counter = 0;

BEGIN;

SELECT n.nc INTO @counter
FROM numeruscurrens n
JOIN libraries l using(libid)
WHERE n.grundsig = @grundsig and l.libname = @libname
FOR UPDATE;

update numeruscurrens n
join libraries l using(libid)
set n.nc = n.nc+1
where n.grundsig = @grundsig and l.libname = @libname

/* You are always incrementing, aren't you? */
SET @counter = @counter + 1;

SELECT @counter;

COMMIT;

Also notice, this is not free. It has to actually be a requirement to have this increment value properly returned to the user because this locking will have a negative impact on performance due to row locking and might create deadlocks. Last but not least, ensure the table is using the InnoDB engine. MyIsam doesn't support row-level locks.

Upvotes: 1

Related Questions