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.

Is the first query executed correctly when you get the error "update `sessions` SET (...)"? — Qullbrune, Dec 05 '13 at 12:02
When you use DB::query() with a SELECT, PDO expects you to exhaust the buffer before the next query. So use DB::query($select)->closeCursor() or DB::connection()->pdo->exec($select) to avoid the SQL error. — ColinM, Dec 11 '13 at 17:46
could you try my solution below? i keep coming to check if it worked or not! — Carlos Robles, Dec 12 '13 at 08:59
@colinm, FYI your comment is just a fragment of an answer you can read from a day before. — Carlos Robles, Dec 12 '13 at 09:03

score 5 · Answer 1 · answered Dec 06 '13 at 21:41

5

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;

answered Dec 06 '13 at 21:41

mike

5,047
2
26
32

Why I don't need to lock the row? What happens when multiple queries (let say 50) run at the same time? how can I be sure the values are 100% up-to-date, accurate and secure without locking the row? – Jonathan Dec 07 '13 at 17:59
If you run just an `UPDATE` query, it's always going to increment the number by one, no matter what the number is before update. The updates in mysql are done sequentially, no need to lock the row. It would be a different story if you'd have to work with the money value, for example first `SELECT money FROM projects WHERE id=1`, then `UPDATE money = money + 10` and then `INSERT INTO log (money, project_id, time) VALUES ($select['money'], 1, NOW())` to log when the money changed to what value. In this case you'd have to lock the row either by `SELECT ... FOR UPDATE` or `UPDATE..` – mike Dec 10 '13 at 22:05

score 4 · Answer 2 · answered Dec 10 '13 at 21:50

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

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();
}

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);
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().
Then you can exec the update
```
$result= $db->exec($update);
```
And after all, just in case, i would call again $result->fetchAll(), or $result->closeCursor().

score 1 · Answer 3 · answered Dec 05 '13 at 13:23

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();

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

3 Answers3