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

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;