Rechercher dans le manuel MySQL
8.11.4 Metadata Locking
MySQL uses metadata locking to manage concurrent access to
database objects and to ensure data consistency. Metadata
locking applies not just to tables, but also to schemas, stored
programs (procedures, functions, triggers, scheduled events),
tablespaces, user locks acquired with the
GET_LOCK()
function (see
Section 12.14, “Locking Functions”), and locks acquired with
the locking service described in
Section 29.3.1, “The Locking Service”.
The Performance Schema
metadata_locks
table exposes
metadata lock information, which can be useful for seeing which
sessions hold locks, are blocked waiting for locks, and so
forth. For details, see Section 26.12.12.3, “The metadata_locks Table”.
Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.
Metadata locking is not a replacement for the table definition
cache, and its mutexes and locks differ from the
LOCK_open
mutex. The following discussion
provides some information about how metadata locking works.
Metadata Lock Acquisition
If there are multiple waiters for a given lock, the
highest-priority lock request is satisfied first, with an
exception related to the
max_write_lock_count
system
variable. Write lock requests have higher priority than read
lock requests. However, if
max_write_lock_count
is set
to some low value (say, 10), read lock requests may be
preferred over pending write lock requests if the read lock
requests have already been passed over in favor of 10 write
lock requests. Normally this behavior does not occur because
max_write_lock_count
by
default has a very large value.
Statements acquire metadata locks one by one, not simultaneously, and perform deadlock detection in the process.
DML statements normally acquire locks in the order in which tables are mentioned in the statement.
DDL statements, LOCK TABLES
,
and other similar statements try to reduce the number of
possible deadlocks between concurrent DDL statements by
acquiring locks on explicitly named tables in name order.
Locks might be acquired in a different order for implicitly
used tables (such as tables in foreign key relationships that
also must be locked).
For example, RENAME TABLE
is a
DDL statement that acquires locks in name order:
This
RENAME TABLE
statement renamestbla
to something else, and renamestblc
totbla
:The statement acquires metadata locks, in order, on
tbla
,tblc
, andtbld
(becausetbld
followstblc
in name order):This slightly different statement also renames
tbla
to something else, and renamestblc
totbla
:In this case, the statement acquires metadata locks, in order, on
tbla
,tblb
, andtblc
(becausetblb
precedestblc
in name order):
Both statements acquire locks on tbla
and
tblc
, in that order, but differ in whether
the lock on the remaining table name is acquired before or
after tblc
.
Metadata lock acquisition order can make a difference in operation outcome when multiple transactions execute concurrently, as the following example illustrates.
Begin with two tables x
and
x_new
that have identical structure. Three
clients issue statements that involve these tables:
Client 1:
The statement requests and acquires write locks in name order
on x
and x_new
.
Client 2:
The statement requests and blocks waiting for a write lock on
x
.
Client 3:
The statement requests exclusive locks in name order on
x
, x_new
, and
x_old
, but blocks waiting for the lock on
x
.
Client 1:
- UNLOCK TABLES;
The statement releases the write locks on x
and x_new
. The exclusive lock request for
x
by Client 3 has higher priority than the
write lock request by Client 2, so Client 3 acquires its lock
on x
, then also on x_new
and x_old
, performs the renaming, and
releases its locks. Client 2 then acquires its lock on
x
, performs the insert, and releases its
lock.
Lock acquisition order results in the
RENAME TABLE
executing before
the INSERT
. The
x
into which the insert occurs is the table
that was named x_new
when Client 2 issued
the insert and was renamed to x
by Client
3:
- +------+
- | i |
- +------+
- | 1 |
- +------+
Now begin instead with tables named x
and
new_x
that have identical structure. Again,
three clients issue statements that involve these tables:
Client 1:
The statement requests and acquires write locks in name order
on new_x
and x
.
Client 2:
The statement requests and blocks waiting for a write lock on
x
.
Client 3:
The statement requests exclusive locks in name order on
new_x
, old_x
, and
x
, but blocks waiting for the lock on
new_x
.
Client 1:
- UNLOCK TABLES;
The statement releases the write locks on x
and new_x
. For x
, the
only pending request is by Client 2, so Client 2 acquires its
lock, performs the insert, and releases the lock. For
new_x
, the only pending request is by
Client 3, which is permitted to acquire that lock (and also
the lock on old_x
). The rename operation
still blocks for the lock on x
until the
Client 2 insert finishes and releases its lock. Then Client 3
acquires the lock on x
, performs the
rename, and releases its lock.
In this case, lock acquisition order results in the
INSERT
executing before the
RENAME TABLE
. The
x
into which the insert occurs is the
original x
, now renamed to
old_x
by the rename operation:
- +------+
- | i |
- +------+
- | 1 |
- +------+
If order of lock acquisition in concurrent statements makes a difference to an application in operation outcome, as in the preceding example, you may be able to adjust the table names to affect the order of lock acquisition.
Metadata locks are extended, as necessary, to tables related by a foreign key constraint to prevent conflicting DML and DDL operations from executing concurrently on the related tables. When updating a parent table, a metadata lock is taken on the child table while updating foreign key metadata. Foreign key metadata is owned by the child table.
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
This principle applies not only to transactional tables, but
also to nontransactional tables. Suppose that a session begins
a transaction that uses transactional table
t
and nontransactional table
nt
as follows:
The server holds metadata locks on both t
and nt
until the transaction ends. If
another session attempts a DDL or write lock operation on
either table, it blocks until metadata lock release at
transaction end. For example, a second session blocks if it
attempts any of these operations:
The same behavior applies for The
LOCK TABLES ...
READ
. That is, explicitly or implicitly started
transactions that update any table (transactional or
nontransactional) will block and be blocked by LOCK
TABLES ... READ
for that table.
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.
Metadata locks acquired during a
PREPARE
statement are released
once the statement has been prepared, even if preparation
occurs within a multiple-statement transaction.
As of MySQL 8.0.13, for XA transactions in
PREPARED
state, metadata locks are
maintained across client disconnects and server restarts,
until an XA
COMMIT
or XA
ROLLBACK
is executed.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-metadata-locking.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.