3

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 :-)

Oliver
  • 855
  • 1
  • 7
  • 10
  • 1
    Why not do your `UPDATE` all in one query? – Brad Aug 16 '12 at 16:44
  • Have you tried using transactions? Here's a few examples: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – Ozzyberto Aug 16 '12 at 16:47
  • I do not think transactions will work. I tested them allready. `$dbh->beginTransaction();` There are no database violations so the transaction wont throw an error. – Oliver Aug 16 '12 at 20:25

2 Answers2

1

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;
CyberDem0n
  • 14,545
  • 1
  • 34
  • 24
0

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.

andr
  • 15,970
  • 10
  • 45
  • 59
Álex Gama
  • 3
  • 2