LOCK TABLES inside TRANSACTION does not ROLLBACK

Question

I have a problem and it is that I need to lock tables within a transaction to prevent data from being inserted into it, but when I do this when I have an error the transaction rollback does not work.

I read in mysql articles that table locks break a transaction, but is there any other way to lock tables to prevent data from being inserted into it temporarily?

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

try {
     DB::beginTransaction();

     DB::unprepared('LOCK TABLES table_name WRITE');
     //
     DB::unprepared('UNLOCK TABLES');

     DB::commit();
} catch (\Exception $e){
     DB::rollBack();
}

Answer 1

You can lock the tables in the outer scope, outside of the transaction.

Then, your example code would look like:

try
{
    DB::unprepared('LOCK TABLES table_name WRITE');

    try
    {
        DB::beginTransaction();
        
        //...

        DB::commit();
    }
    catch (\Exception $e)
    {
        DB::rollBack();
    }
}
finally
{
    DB::unprepared('UNLOCK TABLES');
}