LOCK TABLES
and
UNLOCK TABLES
Syntax
1118
and implicit commits. For example,
START TRANSACTION
does not release the global read lock.
See
Section 13.7.6.2, “
FLUSH
Syntax”
.
• Other statements that implicitly cause transactions to be committed do not release existing table
locks. For a list of such statements, see
Section 13.3.3, “Statements That Cause an Implicit Commit”
.
• The correct way to use
LOCK TABLES
and
UNLOCK TABLES
with transactional tables, such as
InnoDB
tables, is to begin a transaction with
SET autocommit = 0
(not
START TRANSACTION
)
followed by
LOCK TABLES
, and to not call
UNLOCK TABLES
until you commit the transaction
explicitly. For example, if you need to write to table
t1
and read from table
t2
, you can do this:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
When you call
LOCK TABLES
,
InnoDB
internally takes its own table lock, and MySQL takes its
own table lock.
InnoDB
releases its internal table lock at the next commit, but for MySQL to release
its table lock, you have to call
UNLOCK TABLES
. You should not have
autocommit = 1
[436]
,
because then
InnoDB
releases its internal table lock immediately after the call of
LOCK TABLES
,
and deadlocks can very easily happen.
InnoDB
does not acquire the internal table lock at all if
autocommit = 1
[436]
, to help old applications avoid unnecessary deadlocks.
•
ROLLBACK
does not release table locks.
13.3.5.2.
LOCK TABLES
and Triggers
If you lock a table explicitly with
LOCK TABLES
, any tables used in triggers are also locked implicitly:
• The locks are taken as the same time as those acquired explicitly with the
LOCK TABLES
statement.
• The lock on a table used in a trigger depends on whether the table is used only for reading. If so, a
read lock suffices. Otherwise, a write lock is used.
• If a table is locked explicitly for reading with
LOCK TABLES
, but needs to be locked for writing
because it might be modified within a trigger, a write lock is taken rather than a read lock. (That is, an
implicit write lock needed due to the table's appearance within a trigger causes an explicit read lock
request for the table to be converted to a write lock request.)
Suppose that you lock two tables,
t1
and
t2
, using this statement:
LOCK TABLES t1 WRITE, t2 READ;
If
t1
or
t2
have any triggers, tables used within the triggers will also be locked. Suppose that
t1
has a
trigger defined like this:
CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE t4 SET count = count+1
WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
INSERT INTO t2 VALUES(1, 2);
END;
The result of the
LOCK TABLES
statement is that
t1
and
t2
are locked because they appear in the
statement, and
t3
and
t4
are locked because they are used within the trigger:
•
t1
is locked for writing per the
WRITE
lock request.
•
t2
is locked for writing, even though the request is for a
READ
lock. This occurs because
t2
is
inserted into within the trigger, so the
READ
request is converted to a
WRITE
request.
•
t3
is locked for reading because it is only read from within the trigger.
•
t4
is locked for writing because it might be updated within the trigger.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
