What is the LOCK TABLES command for?

Question

These days I came across a snippet of an SQL that had the command LOCK TABLES.

I had never seen this before and, as little as I could see, it seems to me that this would be to lock the table during insertion or update.

But I wish I could better understand how it works:

• How it works LOCK TABLES? I would like some examples.
• When should I use this command?
• Should I avoid using this command in some cases? Is there any risk?

Answer 1

How does LOCK TABLES work? I would like some examples.

Simply explicitly lock the table, locking only for the session that executed the command.
Mysql documentation: https://dev.mysql.com/doc/refman/5.5/en/lock-tables.html:

"A table lock only protects Against inappropriate reads or Writes by other Sessions"

That is, protects the records during some change

Example:

LOCK TABLES nome-da-tabela WRITE;

... comandos ...

UNLOCK TABLES;

LOCK TABLES can make two types of lock:

LOCK TABLES READ: locks for reading and lets you read a locked table. Multiple sessions can use that lock or even time.

LOCK TABLES WRITE: locks for changes. Only one session can run this lock at a time.

When should I use this command?

You must use when you wish that during some change (Insert, update or delete) other users do not have access to the table.

"Locks may be used to emulate transactions or to get more speed when updating table"

That is, to work as if you were in a transaction or improve performance. A sequence of commands that requires a lot of change for example is a case that can be used lock tables.

Should I avoid using this command in some cases? Is there any risk?

Except for a very specific operation, you should not use this command. mysql automatically makes the necessary lock when executing changes, including a row lock and not a table lock when needed, so leave the mysql manage this.
The risk is obvious: leave the table leased, slowing the bank and harming other operations.

Other than a lock that the engine of mysql does, the table will not exit the lock automatically, it is necessary to execute UNLOCK TABLES at the end of the process.

Answer 2

To use LOCK TABLES, you need the global LOCK TABLES privilege and a SELECT privilege over the tables involved.

The main reasons to use LOCK TABLES are to emulate transactions or to get faster when updating tables.

LOCK TABLES works as follows:

• Sorts all tables to be locked in an internally defined order (from the user’s point of view, the order is undefined).
• If a table is locked with a read and write lock, it will lock the write before the read.
• Locks one table at a time until the thread gets all locks.

The policy ensures that the blocking of tables is free of deadlosks*. There are, however, other things you should be aware of with this scheme:

If you are using a LOW_PRIORITY write lock on a table, it only means that Mysql will wait for this specific lock until there are no threads that want a read lock. When the thread has the write lock and is waiting to get the next table locked in the list of tables to block, all other threads will wait for the write lock to be released. If this becomes a serious issue with your application, you should convert some of your tables into transaction tables.

You can safely terminate a thread that is waiting for a table lock with KILL.

Note that you should not block tables you are using with INSERT DELAYED. This is because in this case INSERT is done by a separate thread.

Normally, you don’t have to block tables because all unique UPDATE statements are atomic, no other thread can interfere with some other currently running SQL statement. There are some cases where you would like to block tables anyway:

• If you are going to perform many operations on multiple tables, it is much faster to block the tables you are going to use. The problem is that no other thread can update a table with read lock and no other thread can read a blocked table for write.

  The reason some things are faster under LOCK TABLES is that Mysql will not download the key cache of the locked tables until UNLOCK TABLES is called (usually the key cache is downloaded after each SQL statement). This increases the speed of insertion, update and deletion in Myisam tables.

• If you are using a Mysql storage engine that does not support transactions, you should use LOCK TABLES if you want to make sure that no other thread is between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to run quickly:

  mysql> LOCK TABLES trans READ, clientes WRITE;
  mysql> SELECT SUM(valor) FROM trans WHERE id_do_cliente=algum_id;
  mysql> UPDATE cliente SET valor_total=soma_das_declarações_anteriores
      ->   WHERE id_do_cliente=algum_id;
  mysql> UNLOCK TABLES;
  

  Without UNLOCK TABLES there is a chance that another thread will insert a new row in the trans table between the execution of SELECT and UPDATE declarations.

*Deadlock in the context of a database (MS-SQL/Mysql), characterizes a situation where a deadlock occurs and two or more processes are prevented from continuing their executions, that is, they are blocked.

Source: Mysql Study Guide for Certification (translation: Acauan Fernandes)

This article is also published in 6.7.5. LOCK TABLES and UNLOCK TABLES syntax

The Handbook is available online in various formats and languages on the Mysql AB website (http://www.mysql.com/)

Answer 3

One thing that confused me a little bit and I don’t think it’s quite clear is that:

• Read Locks are "shared" Locks, they prevent a write lock from being purchased, but not another read lock, which means other sessions can read table, they just can’t write until the lock is released.

• Write Locks are "exclusive", prevent any other lock of either writing or reading, any other sessions that try to read or write some record, will be in a standby state, until the lock is released.