Table-Locking Restrictions and Conditions
You can safely use KILL
to terminate a session that is waiting for a table lock. See , "KILL
Syntax".
Do 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_
tables.
xxx
The following statements are prohibited while a LOCK TABLES
statement is in effect: CREATE TABLE
, CREATE TABLE ... LIKE
, CREATE VIEW
, DROP VIEW
, and DDL statements on stored functions and procedures and events.
For some operations, system tables in the MariaDB
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:
- If you are going to run many operations on a set of
MyISAM
tables, it is much faster to lock the tables you are going to use. LockingMyISAM
tables speeds up inserting, updating, or deleting on them because MariaDB does not flush the key cache for the locked tables untilUNLOCK TABLES
is called. Normally, the key cache is flushed after each SQL statement.The downside to locking the tables is that no session can update a
READ
-locked table (including the one holding the lock) and no session can access aWRITE
-locked table other than the one holding the lock. - If you are using tables for a nontransactional storage engine, you must use
LOCK TABLES
if you want to ensure that no other session modifies the tables between aSELECT
and anUPDATE
. The example shown here requiresLOCK TABLES
to execute safely:LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=
some_id
; UPDATE customer SET total_value=sum_from_previous_statement
WHERE customer_id=some_id
; UNLOCK TABLES;Without
LOCK TABLES
, it is possible that another session might insert a new row in thetrans
table between execution of theSELECT
andUPDATE
statements.
You can avoid using LOCK TABLES
in many cases by using relative updates (UPDATE customer SET
) or the value
=value
+new_value
LAST_INSERT_ID()
function. See , "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 , "Miscellaneous Functions".
See , "Internal Locking Methods", for more information on locking policy.