Search code examples
mysqllaravellockinginnodb

How to lock table with Laravel?

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?

Solution

  • 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.