What is the LOCK TABLES command for?

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

Simply explicitly lock the table, locking it exclusively for the session that ran the command. Mysql documentation: link :

  

"A table lock only protects against reads or writes by   other sessions "

That is, it protects the records during some change

Example:

LOCK TABLES nome-da-tabela WRITE;

... comandos ..

UNLOCK TABLES;

LOCK TABLES can do two types of lock:

LOCK TABLES READ : locks to read and allows you to read a locked table. Multiple sessions can use this lock or even time.

LOCK TABLES WRITE : blocks for changes. Only one session can execute this lock per se.

When should I use this command?

You should use when you want other users not to have access to the table during some change (insert, update, or delete).

  

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

That is, to work as if you were in a transaction or to improve performance. A sequence of commands that requires a lot of change for example is a case that can use 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 table lock when needed, so let mysql manage it. The risk is obvious: leaving the table leased, slowing the bank and hampering other operations.

Unlike a% w_that engine of lock does, the table will not automatically exit lock, requiring mysql at the end of the process.     

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

The main reasons for using LOCK TABLES are to emulate transactions or to get more speed 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 lock and a write lock, it will place the write lock before the read.
• Blocks one table at a time until the thread gets all the locks.

The policy ensures that tablelocking 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, this simply means that MySQL will wait for this particular 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 lock in the list of tables to be locked, all other threads will wait for the write lock to be released. If this becomes a serious problem 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 lock tables that you are using using INSERT DELAYED. This is because, in this case, INSERT is done by a separate thread.

Typically, you do not have to lock tables because all single UPDATE declarations are atomic, no other thread can interfere with some other currently running SQL statement. There are some cases where you would like to lock tables anyway:

• If you perform many operations on multiple tables, it is much faster to lock the tables you will be using. The problem is that no other thread can update a table with read lock and no other thread can read a locked table for write.

  The reason some things are faster under LOCK TABLES is that MySQL will not unload the key cache of locked tables until UNLOCK TABLES is called (normally the key cache is unloaded after each SQL statement). This increases the speed of insertion, update, and deletion in MyISAM tables.

• If you are using a storage engine in MySQL that does not support transactions, you should use LOCK TABLES if you want to make sure that no other threads are 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 of another thread inserting a new row in the trans table between the execution of the SELECT and UPDATE statements.

  

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

Source: MySQL Certification Study Guide (translation: Acauan Fernandes)

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

The Handbook is available online in a variety of formats and languages on the MySQL AB website ( link )