You can safely use KILL
to
terminate a session that is waiting for a table lock. See
Section 12.4.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.
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. Before MySQL 5.1.17, to perform such
operations while a LOCK TABLES
statement is in effect, you must also lock the requisite system
tables explicitly or a lock error occurs. As of 5.1.17, 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. Locking
MyISAM
tables speeds up inserting,
updating, or deleting on them because MySQL does not flush
the key cache for the locked tables until
UNLOCK
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 a
WRITE
-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 a
SELECT
and an
UPDATE
. The example shown
here requires LOCK 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 the
trans
table between execution of the
SELECT
and
UPDATE
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 Section 1.8.5.2, “Transactions and Atomic Operations”.
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 11.11.4, “Miscellaneous Functions”.
See Section 7.3.1, “Internal Locking Methods”, for more information on locking policy.
User Comments
Add your own comment.