Table locking issues with Laravel 5.1

Question

I can't lock MySQL tables with this query:

DB::statement('LOCK TABLES imports WRITE');

It gives those exception:

[Illuminate\Database\QueryException]

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: LOCK TABLES imports WRITE)

The same error occurs when I use PDO.

How i should use this?

Answer 1

If you want to lock the whole table and you want to use LOCK TABLES table_name WRITE it should be called in this way DB::unprepared('LOCK TABLES imports WRITE'); but if you only want to apply the lock on the selected rows then using sharedLock() or lockForUpdate() is the right way to do that so it will return and lock only the selected rows.

Answer 2

// prevent rows from being modified until you're finished
DB::table('imports')->where('total', '>', 10)->sharedLock()->get();

Use lockForUpdate() to instead prevent rows from being modified or selected with another shared lock.