CODEWITHSUNDEEP
phpmysql
Oliver
Oliver

Reputation: 865

MySQL PHP lock table, is there a better solution when expacting simultaneously connections

has any one a better solution for me for that code:

<?php

try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $dbh->exec("LOCK TABLES test2 WRITE");

    $row = $dbh->query('SELECT * from test2 WHERE c > 0 LIMIT 1');

    $stmt = $dbh->prepare("UPDATE test2 SET c=c-1 WHERE a=:a and c>0");
    $stmt->bindParam(':a', $row['a']);
    $stmt->execute();

    /**
    ...
    ....
    .....
    **/

    $dbh->exec("UNLOCK TABLES");

    $dbh->commit();
    $dbh = null;
} catch (PDOException $e) {
    error_log("Error!: " . $e->getMessage() . "\n", 3, "./my-errors.log");
    exit();
}

When i get simultaneously connections to that script, every connection should have his own row from the table test2 (field A).

Thanks for your ideas :-)

Upvotes: 3

Views: 1414

Answers (2)

&#193;lex Gama
&#193;lex Gama

Reputation: 3

LOCK TABLES and UNLOCK TABLES close your transaction automatically. You need a SELECT .. FOR UPDATE and forget lock and unlock tables.

SELECT FOR UPDATE locks at row level. Other executions of the SELECT FOR UPDATE will wait for the end of the first transition if they pretend obtain the same record.

Upvotes: 0

CyberDem0n
CyberDem0n

Reputation: 15046

If you don't need to know a parameter, you can use following query

UPDATE test2 SET c=c-1 WHERE c>0 LIMIT 1;

Upvotes: 1

Related Questions