13.3.5.3 Table-Locking Restrictions and Conditions

You can safely use KILL to terminate a session that is waiting for a table lock. See Section 13.7.6.4, “KILL Syntax”.

You should not lock any tables that you are using with INSERT DELAYED. An INSERT DELAYED in this case results in an error because the insert must be handled by a separate thread, not by the session which holds the lock.

LOCK TABLES and UNLOCK TABLES cannot be used within stored programs.

Tables in the performance_schema database cannot be locked with LOCK TABLES, except the setup_xxx tables.

The following statements are prohibited while a LOCK TABLES statement is in effect:

For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. The server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described:

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.proc
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement.

Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function. See Section 1.8.2.3, “Transaction and Atomic Operation Differences”.

You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 12.16, “Miscellaneous Functions”.

See Section 8.10.1, “Internal Locking Methods”, for more information on locking policy.