5

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?

ekstro
  • 461
  • 4
  • 15
  • Try `DB::connection()->getPdo()->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);`. – Siper Jul 23 '15 at 09:32
  • Okay, I see in docs and you can only lock table for update or for share. You can do that using `DB::table('imports')->lock($lockUpdate);` – Siper Jul 23 '15 at 09:40

2 Answers2

6

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.

l3ehnam
  • 541
  • 5
  • 9
  • 1
    NB: this will lock the table for read *and* write. It will stay locked until `DB::unprepared('UNLOCK TABLES')` is used. – jonlink Oct 14 '17 at 04:15
3
// 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.

Lance Pioch
  • 1,147
  • 6
  • 18
  • 5
    Hi, when is the lock released? – supersan Sep 16 '16 at 03:26
  • NB: sharedLock will lock write to a *row* (not a table). To answer your question, Supersan, it will release once the transaction is complete. – jonlink Oct 14 '17 at 04:13
  • 1
    @jonlink , if i have 2 threads accessing my db and i am using sharedLock to avoid collision, what happen if the 2nd thread want to update through a transaction and cannot as the first transaction has the lock. Will the thread wait or just throw an error, which mean i need to catch it to re-try...or is there a wait time and time out? – LearningPath Jun 01 '18 at 12:20