How to lock an SQL row with PDO

Asked at 2017-01-11 00:59:37Z
  • 5 Subscribers
  • 114 Views
0

I'm trying to lock a row of a table for updates only, while allowing reading, but the following code isn't locking the row. I executed "code1" followed by "code2" without commiting code1, through a POST request. So each of those codes lies in a distinct .php file.

I also tried it with "SELECT ... FOR UPDATE", but that didn't work too.

code1:

try{
    $pdo->beginTransaction();
    $sql = "SELECT assertedFlags FROM statementcontent WHERE id = $statementId LOCK IN SHARE MODE;";
    $statement = $pdo->query($sql);
    var_dump($statement->fetchAll());
}catch(Exception $e){
    $pdo->rollBack();
    exit('Exception ' . $e->getMessage());
}

code2:

try{
    $pdo->beginTransaction();
    $sql = "SELECT assertedFlags FROM statementcontent WHERE id = $statementId LOCK IN SHARE MODE;";
    $statement = $pdo->query($sql);
    var_dump($statement->fetchAll());

    //New segment starts here:

    $sql = "UPDATE statementcontent SET assertedFlags = '4' WHERE id = $statementId;";
    $statement = $pdo->exec($sql);
    var_dump($statement);
    $pdo->commit();
}catch(Exception $e){
    $pdo->rollBack();
    exit('Exception ' . $e->getMessage());
}

Could it be that it's not locked because after code1 exits the lock gets released?

EDIT

I put both in one file with two pdo instances and this time the lock worked. I don't really understand how a server manages queries when having many requests. Are there going to be many processes or many threads? Will it stay locked for all cases or only in the case of many threads? Thanks.


1 answers in total

-1
Ukuser32 Posted at 2017-01-11 09:00:51Z

For proper row locking you should look at:

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html

However this requires your database table is innodb not myisam.

Answer this questsion