How to run 'SELECT FOR UPDATE' in Laravel 3 / MySQL

Question

I am trying to execute SELECT ... FOR UPDATE query using Laravel 3:

SELECT * from projects where id = 1 FOR UPDATE;
UPDATE projects SET money = money + 10 where id = 1;

I have tried several things for several hours now:

DB::connection()->pdo->exec($query);

and

DB::query($query)

I have also tried adding START TRANSACTION; ... COMMIT; to the query and I tried to separate the SELECT from the UPDATE in two different parts like this:

DB::query($select);
DB::query($update);

Sometimes I get 0 rows affected, sometimes I get an error like this one:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.    
SQL: UPDATE `sessions` SET `last_activity` = ?, `data` = ? WHERE `id` = ?

I want to lock the row in order to update sensitive data, using Laravel's database connection.

Thanks.

Answer 1

If the aim is

to lock the row in order to update sensitive data, using Laravel's database connection.

Maybe you can use PDO transactions :

    DB::connection()->pdo->beginTransaction();
    DB::connection()->pdo->commit();
    DB::connection()->pdo->rollBack();

Answer 2

In case all you need to do is increase money by 10, you don't need to lock the row before update. Simply executing the update query will do the job. The SELECT query will only slow down your script and doesn't help in this case.

UPDATE projects SET money = money + 10 where id = 1;

Answer 3

1. I would use diferent queries for sure, so you can have control on what you are doing.

2. I would use a transaction. If we read this simple explanations, pdo transactions are quite straightforward. They give us this simple but complete example, that ilustrates how everithing is as we should expect (consider $db to be your DB::connection()->pdo).

   try {
    $db->beginTransaction();
   
    $db->exec("SOME QUERY");
   
    $stmt = $db->prepare("SOME OTHER QUERY?");
    $stmt->execute(array($value));
   
    $stmt = $db->prepare("YET ANOTHER QUERY??");
    $stmt->execute(array($value2, $value3));
   
    $db->commit();
   } 
   catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
   }
   

3. Lets go to your real statements. For the first of them, the SELECT ..., i wouldn't use exec, but query, since as stated here

   PDO::exec() does not return results from a SELECT statement. For a SELECT statement that you only need to issue once during your program, consider issuing PDO::query(). For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute().

   And assign its result to some temp variable like

   $result= $db->query ($select);

4. After this execution, i would call $result->fetchAll(), or $result->closeCursor(), since as we can read here

   If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

5. Then you can exec the update

   $result= $db->exec($update);
   

6. And after all, just in case, i would call again $result->fetchAll(), or $result->closeCursor().