How to lock table with Laravel?

Question

I want to lock a table inside a transaction. Something like this:

  DB::transaction(function (){
    DB::statement('LOCK TABLES important_table WRITE');
    //....
  });

However, the line DB::statement('LOCK TABLES important_table WRITE'); always triggers the following error:

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 officeSeal WRITE)

How can I lock the table in Laravel?

I wonder why you would need to lock a InnoDB table.. You really want InnoDB to behave like MyISAM? Besides using a TRANSACTION will lock the records needed. — Raymond Nijland, Nov 28 '18 at 15:07
@RaymondNijland the reason why I need to lock a complete table can be found https://dba.stackexchange.com/questions/223608/using-transactions-without-select-for-update/223609 (see answer + comment) — Adam, Nov 28 '18 at 15:09
Besides TRANSACTIONS in MySQL also supports transaction [isolation](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html) also might be worth studing.. — Raymond Nijland, Nov 28 '18 at 15:18
@RaymondNijland not sure if I understood you correctly - are you suggesting to copy the computed gaps in a temporary table? Because I compute the gaps in step 1, not in step 3. Sorry I am alittle confused. I read the section about transaction isolation. — Adam, Nov 28 '18 at 15:33

Adam · Accepted Answer · 2022-12-02T17:00:27.073

12

One can lock a table in Laravel like this:

DB::statement(DB::raw('LOCK TABLES important_table WRITE'));

However, you should be aware that locking a table is not very performant and row-locking should be used instead if possible.

edited Dec 02 '22 at 17:00

answered Nov 28 '18 at 15:39

Adam

25,960
22
158
247

3

To anyone tried this answer but failed, try this: `DB::unprepared('LOCK TABLES important_table WRITE');` – 尤川豪 May 19 '20 at 10:19
https://stackoverflow.com/questions/25346162/difference-between-laravels-raw-sql-functions – 尤川豪 May 19 '20 at 10:20
1

@尤川豪 what Laravel version are you using? Seems like it should be fixed since 2013 https://github.com/laravel/framework/issues/53 – Adam May 19 '20 at 10:23
2

I just want to remind others that `DB::raw` will only generate string. It will not execute sql statement directly. – 尤川豪 May 19 '20 at 11:05
so what is row-locking? how to implement it? – shintaroid Feb 20 '23 at 08:40

Dhruv Saxena · Answer 2 · 2018-11-29T04:06:49.573

As pointed out in the comments by other users too, I don't feel certain that a table lock is absolutely the only way out. However, if you insist, you could use Laravel's Pessimistic Locking. Namely, sharedLock() and lockForUpdate() methods, as mentioned in the documentation.

You'd notice that the example in the documentation doesn't use Eloquent, but relies on Query Builder. However, this Q&A seems to suggest that it can be done with Eloquent too.

It may also be worthwhile to have a read through this article which contrasts Pessimistic and Optimistic locking implementations in Laravel.

score 1 · Answer 3 · answered Dec 12 '20 at 13:33

1

DB::unprepared('LOCK TABLES important_table WRITE'); this one worked for me

answered Dec 12 '20 at 13:33

Илья Вайнер

29
3

score 0 · Answer 4 · answered Aug 22 '22 at 03:03

0

DB::statement(DB::raw('LOCK TABLES the_table WRITE'));

answered Aug 22 '22 at 03:03

Sajjad

430
6
17

Please do not duplicate existing answers, especially if your answer does not contain any explanation – Nico Haase Aug 22 '22 at 07:59
@NicoHaase It's not duplicate, putting DB::raw in DB::statement makes this answer unique – Sajjad Aug 22 '22 at 10:19
Feel free to add some explanation to your answer such that others can learn from it – Nico Haase Aug 22 '22 at 11:18

How to lock table with Laravel?

4 Answers4

Linked