Limit the row of the table to be used by one script at a time

I have a table tableName which contains some names to be used:

id    name    in_use
1     name1   0
2     name2   0

My script get the first name from this table with in_use = 0:

SELECT name FROM tableName WHERE in_use = 0 ORDER BY id

and immediately after set the row obtained to in_use = 1:

UPDATE tableName set in_use = 1 WHERE id = '$idObtained'

When the script is finishing, it set in_use = 0:

UPDATE tableName set in_use = 0 WHERE id = '$idObtained'

The problem is that when the script will be executed more than once in the same instant, two or more selects may get the same name form this table.

My aim is to avoid two scripts to get the same row in their execution.

There is a way to do that with PHP / MySQL?

Does locking the table before the select and unlocking it after the update do that?

The script (the code is only a draft. I'll develop it only if what I need is possible in PHP and MySQL):

<?php
    // ...

    // TODO: lock the raw, if found
    $query = "SELECT id, name
    FROM tableName
    WHERE in_use = 0
    ORDER BY id";
    $stmt = $connection->prepare($query);
    $stmt->execute();
    $data = $stmt->fetch();

    if(!empty($data['id'])) {

        $query = "UPDATE tableName
        SET in_use = 1
        WHERE id = '$data[id]'";
        $stmt = $connection->prepare($query);
        $stmt->execute();

        // do some with $data['name]

        $query = "UPDATE tableName
        SET in_use = 0
        WHERE id = '$data[id]'";
        $stmt = $connection->prepare($query);
        $stmt->execute();

        // TODO: unlock the raw
    }
?>

Thanks a lot.

Solution

To fix your approach

BEGIN;
SELECT id, name FROM tableName WHERE in_use = 0 ORDER BY id
                  FOR UPDATE;   -- Add this 
$idObtained = ...
UPDATE tableName set in_use = 1 WHERE id = '$idObtained';
COMMIT;

Later, after finishing the work with that row:

UPDATE tableName set in_use = 0 WHERE id = '$idObtained';

Discussion:

The FOR UPDATE prevents another connection from sneaking in between the SELECT and the UPDATE and also grabbing that row.

If the action on this row will take only a few seconds, this technique is overkill. So, I am assuming it will take a long time, hence two separate transactions -- one to 'grab' the row, and another to 'release' it.

Note that I did not put BEGIN and COMMIT around the 'release' UPDATE. Any standalone query is a transaction unto itself (assuming autocommit=ON). Hence, the release is easy to do with just that single query.

Alternative approach

It may be possible to 'simplify' the 'grab', if we can devise a single instruction that both looks for a 'free' row and 'grabs' it. One approach includes an extra column saying which connection 'owns' each grabbed row. But, let's simply change how set_in_use works. Assuming you are not going to grab more than one row per connection, the CONNECTION_ID() is convenient:

$conn = `SELECT CONNECTION_ID()`

UPDATE tbl SET in_use_by = $conn
    WHERE in_use_by IS NULL
    ORDER BY id
    LIMIT 1;      -- grab only 1 row
SELECT ... FROM tbl WHERE in_use_by = $conn;   -- Get the data to process

... process ...

UPDATE tbl SET in_use_by = NULL WHERE in_use_by = $conn;

With this technique, no explicit transactions are needed. autocommit=ON works just fine.

This technique gives you the ability to see who has what items. (Not that you can do much with such knowledge.)

(There are probably other approaches.)