I'm on MariaDB 10.6.5, and I have this code :

$pdo->query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
$pdo->query("SET autocommit = 0;");

try
{
    $max_id = $pdo->query("SELECT MAX(id) FROM test")->fetchColumn();
    sleep(3);
    $insert_sql = $pdo->prepare("INSERT INTO test(test) VALUES(:test)");
    $insert_sql->execute(['test' => $max_id   1]);
}
catch (Throwable $e)
{
    $pdo->query("ROLLBACK;");
}

$pdo->query("COMMIT;");

The test table has two columns : id (auto incremented) & test (int).

When two users execute this code at the same time, I want the first transaction to lock the test table at the SELECT statement and for the second transaction to wait at the SELECT statement for the first one to finish. If everything goes well, the id column should always be equal to the test column.

Is this possible ? And if so, how ?

For clarification, here's what I want to happen :

• Two users U1 and U2 run this code at the same time, U1 runs it a few microseconds earlier
• U1 runs the SELECT statement, locking the table test
• U1 runs the INSERT statement
• U1 runs the COMMIT statement, unlocking the table test
• U2 runs the SELECT statement, reading the new MAX(id) after the INSERT of U1
• U2 runs the INSERT statement
• U2 runs the COMMIT statement

CodePudding user response：

You can use GET_LOCK

add the query DO GET_LOCK('lockname', 30) at the start and DO RELEASE_LOCK('lockname') after the query.

So when User 1 starts the query it sets the lock lockname and only releases it when finished, If User 2 starts the script DO GET_LOCK('lockname', 30) waits for the lock to be released before continuing.