Blocco di MySQL spiegato: controlla lo stato dei lock, rilascia i lock in sicurezza e previeni i deadlock (5.6–8.0)

1. Introduzione

MySQL è ampiamente utilizzato come sistema di gestione di database, ma quando più query tentano di accedere agli stessi dati, viene attivato un meccanismo di blocco. I lock sono essenziali per mantenere la coerenza dei dati; tuttavia, una gestione impropria può portare a deadlock e a un degrado delle prestazioni.

In questo articolo spiegheremo i concetti fondamentali del locking in MySQL e forniremo indicazioni dettagliate su come verificare lo stato dei lock, come rilasciare i lock e come prevenire i deadlock.

Cosa Imparerai in Questo Articolo

• I tipi di lock di MySQL e il loro impatto
• Come verificare i lock in base alla versione di MySQL
• Procedure sicure per rilasciare i lock
• Consigli pratici per prevenire i deadlock

Iniziamo spiegando i concetti di base del locking in MySQL.

2. Concetti di Base del Locking in MySQL

In un database, un “lock” è un meccanismo che limita l’accesso al fine di mantenere l’integrità dei dati quando più transazioni tentano di modificare i dati simultaneamente. Se i lock non sono gestiti correttamente, possono verificarsi degrado delle prestazioni e deadlock.

2.1 Tipi Principali di Lock

In MySQL esistono diversi tipi di lock a seconda del livello di protezione dei dati richiesto.

Lock di Riga

• Blocca solo righe specifiche, riducendo al minimo l’impatto sulle altre transazioni.
• Supportato solo dal motore di archiviazione InnoDB.
• Attivato quando si utilizza SELECT ... FOR UPDATE o SELECT ... LOCK IN SHARE MODE .

Lock di Tabella

• Blocca l’intera tabella, impedendo l’esecuzione simultanea di più query.
• Comunemente usato con il motore di archiviazione MyISAM.
• Attivato quando si utilizza l’istruzione LOCK TABLES.

Lock di Intenzione

• Un lock utilizzato per coordinare i lock di riga e i lock di tabella al fine di evitare conflitti.
• Utilizzato solo in InnoDB e gestito automaticamente.

Deadlock

• Uno stato in cui più transazioni attendono i lock delle altre.
• Se le transazioni non sono progettate correttamente, l’elaborazione può fermarsi completamente.

2.2 Esempi di Occorrenza di Lock

Esaminiamo query SQL specifiche per capire come si verificano i lock.

Esempio di Lock di Riga

L’esecuzione del seguente SQL bloccherà una riga specifica.

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

Se un’altra sessione tenta di aggiornare la stessa riga, entrerà in uno stato di attesa del lock (contenzione del lock).

Esempio di Lock di Tabella

Per bloccare un’intera tabella, utilizzare il comando seguente.

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

Finché questo lock non viene rilasciato, gli altri utenti non possono modificare i dati nella tabella products.

Esempio di Deadlock

Il seguente mostra uno scenario tipico di deadlock.

-- 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

In questa situazione, ogni transazione sta aspettando che l’altra rilasci il proprio lock, risultando in un deadlock.

3. Come Verificare lo Stato dei Lock MySQL (Per Versione)

Per determinare se i lock si stanno verificando, è necessario eseguire i comandi appropriati per la versione di MySQL in uso.

3.1 Come Verificare i Lock in MySQL 5.6 e Precedenti

In MySQL 5.6 e versioni precedenti, è possibile verificare le informazioni sui lock usando SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

L’esecuzione di questo comando mostra informazioni dettagliate sui lock attualmente attivi.

3.2 Come Verificare i Lock in MySQL 5.7

In MySQL 5.7 e versioni successive, il metodo più semplice è utilizzare la tabella sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

By querying this table, you can identify which transactions are waiting for locks.

3.3 How to Check Locks in MySQL 8.0 and Later

In MySQL 8.0 and later, you can retrieve more detailed lock information using performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

To identify the session holding the lock, use the following SQL:

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

This allows you to pinpoint the process responsible for the lock.

4. How to Release MySQL Locks (Risks Explained)

If a lock occurs in MySQL and is not handled properly, processing may stall and database performance may degrade.
In this section, we will explain how to release locks and the risks involved.

4.1 Identifying the Session Holding the Lock

Before releasing a lock, you must identify which session is holding it. Use the following SQL to check sessions experiencing lock waits:

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

This query lists sessions that are waiting for a lock.

In MySQL 8.0 and later, you can obtain detailed lock information using:

SELECT * FROM performance_schema.data_locks;

4.2 Releasing Locks Using the KILL Command

Once you identify the session holding the lock, you can release it by forcibly terminating the process.

1. Check the process holding the lock

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

2. Terminate the session using the KILL command

KILL <process_id>;

For example, to terminate a process with ID=12345, execute:

KILL 12345;

⚠️ Risks of the KILL Command

• Forcefully terminated transactions are rolled back
• For example, changes made by an interrupted UPDATE statement may be discarded.
• It may cause application errors
• If you frequently need to use KILL , you should review your application design.

4.3 Releasing Locks with ROLLBACK (Safer Method)

Before using the KILL command, if possible, try manually ending the transaction causing the lock.

1. First, check the current transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. If you find the problematic transaction, execute ROLLBACK

ROLLBACK;

This method allows you to release the lock while maintaining data consistency.

4.4 Automating Lock Handling with SET innodb_lock_wait_timeout

Instead of manually releasing locks, you can configure a lock wait timeout so that the transaction automatically times out if the lock is not released within a specified time.

SET innodb_lock_wait_timeout = 10;

With this setting, if the lock is not released within 10 seconds, MySQL returns an error and automatically ends the transaction.

5. Important Notes and Best Practices for MySQL Locks

Proper lock management helps reduce the risk of deadlocks and performance degradation. Below are best practices to minimize locks and manage them efficiently.

5.1 How to Prevent Deadlocks

To prevent deadlocks, keep the following points in mind:

1. Standardize the Transaction Execution Order

• For example, when updating multiple tables, always update them in the same order .
• Example:

  -- 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: Different execution orders can cause deadlocks

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

-- Sessione 2 (il deadlock può verificarsi se eseguito in ordine inverso)
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