CODEWITHSUNDEEP
phptransactionsmariadb
Abderrahim Benmelouka
Abderrahim Benmelouka

Reputation: 405

MariaDB make SELECT wait for other transactions to finish

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 :

Upvotes: 1

Views: 632

Answers (1)

Moshe Gross
Moshe Gross

Reputation: 1395

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.

Upvotes: 5

Related Questions