Practical MySQL & MariaDB

Vysvětlení zamykání v MySQL: kontrola stavu zámků, bezpečné uvolňování zámků a prevence deadlocků (5.6–8.0)

Konfigurace a správa serveru
目次

1. Introduction

MySQL je široce používán jako systém pro správu databází, ale když se více dotazů pokusí přistupovat ke stejným datům, spustí se zamykací mechanismus. Zámky jsou nezbytné pro udržení konzistence dat; však nesprávná správa může vést k deadlockům a degradaci výkonu.

V tomto článku vysvětlíme základní koncepty zamykání v MySQL a poskytneme podrobný návod na to, jak zkontrolovat stav zámků, jak uvolnit zámky a jak předcházet deadlockům.

What You Will Learn in This Article

  • Typy zámků v MySQL a jejich dopad
  • Jak zkontrolovat zámky podle verze MySQL
  • Bezpečné postupy pro uvolňování zámků
  • Praktické rady, jak předcházet deadlockům

Začněme vysvětlením základních konceptů zamykání v MySQL.

2. Basic Concepts of MySQL Locking

V databázi je „zámek“ mechanismus, který omezuje přístup za účelem zachování integrity dat, když se více transakcí snaží data měnit současně. Pokud nejsou zámky spravovány správně, může dojít k degradaci výkonu a deadlockům.

2.1 Main Types of Locks

V MySQL existuje několik typů zámků v závislosti na požadované úrovni ochrany dat.

Row Lock

  • Zamyká pouze konkrétní řádky, čímž minimalizuje dopad na ostatní transakce.
  • Podporováno pouze úložištěm InnoDB.
  • Spouští se při použití SELECT ... FOR UPDATE nebo SELECT ... LOCK IN SHARE MODE .

Table Lock

  • Zamyká celou tabulku, čímž zabraňuje současnému spouštění více dotazů.
  • Obvykle se používá s úložištěm MyISAM.
  • Spouští se při použití příkazu LOCK TABLES.

Intention Lock

  • Zámek používaný k koordinaci řádkových a tabulkových zámků, aby se předešlo konfliktům.
  • Používá se pouze v InnoDB a je spravován automaticky.

Deadlock

  • Stav, kdy více transakcí čeká na zámky ostatních.
  • Pokud nejsou transakce navrženy správně, může se zpracování úplně zastavit.

2.2 Examples of Lock Occurrence

Podívejme se na konkrétní SQL dotazy, abychom pochopili, jak zámky vznikají.

Row Lock Example

Spuštěním následujícího SQL se zamkne konkrétní řádek.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Pokud se jiná relace pokusí aktualizovat stejný řádek, vstoupí do stavu čekání na zámek (konflikt zámků).

Table Lock Example

Pro zamknutí celé tabulky použijte následující příkaz.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Dokud není tento zámek uvolněn, ostatní uživatelé nemohou měnit data v tabulce products.

Deadlock Example

Následující ukazuje typický scénář deadlocku.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

V této situaci každá transakce čeká, až druhá uvolní svůj zámek, což vede k deadlocku.

3. How to Check MySQL Lock Status (By Version)

Abyste zjistili, zda dochází k zámkům, musíte spustit příkazy odpovídající vaší verzi MySQL.

3.1 How to Check Locks in MySQL 5.6 and Earlier

V MySQL 5.6 a starších můžete informace o zámcích zkontrolovat pomocí SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Spuštěním tohoto příkazu se zobrazí podrobné informace o aktuálně aktivních zámcích.

3.2 How to Check Locks in MySQL 5.7

V MySQL 5.7 a novějších je nejjednodušší metodou použít tabulku sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

Dotazováním na tuto tabulku můžete zjistit, které transakce čekají na zámky.

3.3 Jak zkontrolovat zámky v MySQL 8.0 a novějších

V MySQL 8.0 a novějších můžete získat podrobnější informace o zámcích pomocí performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Pro identifikaci relace, která drží zámek, použijte následující SQL:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

To vám umožní přesně určit proces odpovědný za zámek.

4. Jak uvolnit zámky v MySQL (Vysvětlení rizik)

Pokud v MySQL nastane zámek a není řádně ošetřen, může dojít k zastavení zpracování a snížení výkonu databáze.
V této sekci vysvětlíme, jak uvolnit zámky a jaká jsou s tím spojená rizika.

4.1 Identifikace relace, která drží zámek

Před uvolněním zámku musíte zjistit, která relace jej drží. Použijte následující SQL k ověření relací, které čekají na zámek:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Tento dotaz vypíše relace, které čekají na zámek.

V MySQL 8.0 a novějších můžete získat podrobné informace o zámcích pomocí:

SELECT * FROM performance_schema.data_locks;

4.2 Uvolnění zámků pomocí příkazu KILL

Jakmile identifikujete relaci, která drží zámek, můžete jej uvolnit násilným ukončením procesu.

1. Zkontrolujte proces, který drží zámek

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Ukončete relaci pomocí příkazu KILL

KILL <process_id>;

Například pro ukončení procesu s ID=12345 spusťte:

KILL 12345;

⚠️ Rizika příkazu KILL

  • Násilně ukončené transakce jsou vráceny (rollback)
  • Například změny provedené přerušeným příkazem UPDATE mohou být zahozeny.
  • Může způsobit chyby v aplikaci
  • Pokud často potřebujete používat KILL, měli byste přezkoumat návrh vaší aplikace.

4.3 Uvolnění zámků pomocí ROLLBACK (bezpečnější metoda)

Před použitím příkazu KILL zkuste, pokud je to možné, ručně ukončit transakci způsobující zámek.

1. Nejprve zkontrolujte aktuální transakce

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Pokud najdete problematickou transakci, proveďte ROLLBACK

ROLLBACK;

Tato metoda vám umožní uvolnit zámek při zachování datové konzistence.

4.4 Automatizace zpracování zámků pomocí SET innodb_lock_wait_timeout

Místo ručního uvolňování zámků můžete nastavit časový limit čekání na zámek, aby transakce automaticky vypršela, pokud zámek není uvolněn během určené doby.

SET innodb_lock_wait_timeout = 10;

S tímto nastavením, pokud zámek není uvolněn během 10 sekund, MySQL vrátí chybu a automaticky ukončí transakci.

5. Důležité poznámky a osvědčené postupy pro zámky v MySQL

Správná správa zámků pomáhá snížit riziko deadlocků a degradace výkonu. Níže jsou osvědčené postupy, jak minimalizovat zámky a efektivně je spravovat.

5.1 Jak předcházet deadlockům

Aby se předešlo deadlockům, mějte na paměti následující body:

1. Standardizujte pořadí provádění transakcí

  • Například při aktualizaci více tabulek vždy je aktualizujte ve stejném pořadí.
  • Příklad:
    -- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: Různá pořadí provádění mohou způsobit deadlocky

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Relace 2 (může dojít k deadlocku, pokud je provedeno v opačném pořadí)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks .
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .
    CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads