How to lock table with Laravel?
Solution 1
One can lock a table in Laravel like this:
DB::raw('LOCK TABLES important_table WRITE')
;
Solution 2
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.
Solution 3
DB::unprepared('LOCK TABLES important_table WRITE'); this one worked for me
Related videos on Youtube
Adam
I am big fan of character design, first-order logic and set theory.
Updated on June 23, 2022Comments
-
Adam almost 2 years
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?
-
Raymond Nijland over 5 yearsI 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.
-
-
尤川豪 almost 4 yearsTo anyone tried this answer but failed, try this:
DB::unprepared('LOCK TABLES important_table WRITE');
-
尤川豪 almost 4 years
-
Adam almost 4 years@尤川豪 what Laravel version are you using? Seems like it should be fixed since 2013 github.com/laravel/framework/issues/53
-
尤川豪 almost 4 yearsI just want to remind others that
DB::raw
will only generate string. It will not execute sql statement directly.