Corso MariaDB / Verificare isolamento, blocchi e deadlock con due connessioni
L'isolamento stabilisce quali modifiche concorrenti può leggere una transazione, mentre i blocchi coordinano operazioni in conflitto. Due connessioni distinte, usate in una sequenza controllata, permettono di osservare separatamente REPEATABLE READ, READ COMMITTED, scadenza dell'attesa di un blocco e deadlock.
Preparare due connessioni indipendenti A e B
Aprire due client da riga di comando sullo stesso server di esercitazione e sul database shopcourse. La guida al client MariaDB spiega la connessione. Verificare che CONNECTION_ID() sia diverso: incollare entrambe le parti nello stesso client non crea concorrenza. Usare dati di prova e conoscere i confini di START TRANSACTION, COMMIT e ROLLBACK.
Eseguire il blocco seguente separatamente in A e in B. Ciascuna connessione salva il proprio isolamento e il tempo di attesa per ripristinarli alla fine. Controllare la versione con SELECT VERSION();. Le impostazioni dell'esercizio riguardano SESSION, non GLOBAL.
SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;
Creare la tabella seguente soltanto in A. Verificare prima con SHOW TABLES che accounts_36_lab non esista. Se esiste, esaminarne definizione e righe, salvando i dati necessari prima di considerarla eliminabile.
USE shopcourse;
CREATE TABLE accounts_36_lab (
account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
| Caso | Regola osservata | Distinzione |
|---|---|---|
| SELECT ordinaria in REPEATABLE READ | Lettura coerente dallo snapshot della transazione | Diversa da una lettura con blocco |
| SELECT ordinaria in READ COMMITTED | Versione confermata visibile a ogni istruzione | Letture ripetute possono cambiare |
| Attesa di un blocco | Attesa del rilascio di un blocco incompatibile | Timeout e deadlock sono eventi distinti |
Confrontare REPEATABLE READ nell'ordine A → B → A
Partire dal saldo iniziale 1000 dell'account 1. Rispettare le etichette delle connessioni: B interviene soltanto dopo la prima lettura di A. Uno snapshot è una visione coerente dei dati usata dalla lettura della transazione.
A-1: stabilire lo snapshot
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1: confermare la modifica
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2: rileggere nella stessa transazione
SELECT balance FROM accounts_36_lab WHERE account_id=1;
A-1 e A-2 restituiscono entrambi 1000. La seconda SELECT ordinaria di A continua a leggere lo snapshot, anche se B ha confermato il saldo 1100. Il confronto usa SELECT senza blocco; sostituirla con FOR UPDATE significherebbe eseguire una prova diversa.
A-3: concludere la transazione e rileggere
ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
La lettura dopo ROLLBACK restituisce 1100. La documentazione MariaDB di SET TRANSACTION descrive l'ambito dell'isolamento e le letture ordinarie rispetto a quelle con blocco.
Ripetere con READ COMMITTED dopo un ripristino esplicito
B-0: ripristinare il saldo della prova precedente
ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;
A-1: prima lettura in READ COMMITTED
ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;
B-1: confermare il saldo 1100
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;
A-2: seconda lettura e conclusione
SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;
A legge prima 1000 e poi 1100. Ogni SELECT ordinaria usa i dati confermati visibili a quella istruzione. Questa differenza da sola non determina l'isolamento migliore per un'applicazione: considerare anche conflitti di scrittura e requisiti di coerenza.
Distinguere un blocco mantenuto dalla scadenza dell'attesa
A-1: mantenere un blocco sulla riga 1
START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;
B-1: tentare di modificare la stessa riga
SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Lasciare aperta la transazione A. B dovrebbe esaurire l'attesa dopo circa tre secondi, normalmente con errore 1205. L'impostazione limita l'attesa nella sessione e non garantisce una durata esatta dell'esecuzione. Non presumere che l'errore concluda automaticamente l'intera transazione B.
B-2: annullare il tentativo e ripristinare l'attesa
ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
A-2: rilasciare il blocco
ROLLBACK;
Il blocco dell'esempio è acquisito in una transazione esplicita: conservare questo confine nelle ripetizioni. La documentazione MariaDB di FOR UPDATE ne spiega il requisito per InnoDB. Una dipendenza in attesa è diversa dal ciclo di attese della prova seguente.
Creare un deadlock con quattro passaggi alternati
Un deadlock, o stallo, si verifica quando le transazioni si attendono in un ciclo. Accertarsi che le due transazioni precedenti siano concluse. Alternare le finestre nell'ordine A-1 → B-1 → A-2 → B-2, senza eseguire tutti i passaggi come un solo script sequenziale.
| Passaggio | Connessione | Azione |
|---|---|---|
| 1 | A | Modificare e bloccare la riga 1 |
| 2 | B | Modificare e bloccare la riga 2 |
| 3 | A | Richiedere la riga 2 e attendere |
| 4 | B | Richiedere la riga 1 e formare il ciclo |
A-1: bloccare soltanto la prima riga
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
B-1: bloccare l'altra riga
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A-2: richiedere la riga mantenuta da B
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;
A rimane in attesa. Passare a B ed eseguire il comando seguente senza aspettare che A termini.
B-2: richiedere la riga mantenuta da A
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;
Con il rilevamento dei deadlock InnoDB attivo, una transazione può ricevere Deadlock found, normalmente errore 1213. La connessione scelta come vittima non è fissa. L'altra operazione può continuare dopo il rilascio dei blocchi. Eseguire ROLLBACK; separatamente in entrambe le finestre affinché la prova non lasci incrementi permanenti.
L'applicazione deve distinguere il deadlock dal timeout e prevedere tentativi limitati dell'intera transazione, proteggendosi dalle operazioni duplicate. Ripetere soltanto un'istruzione o riprovare senza limiti non definisce un recupero corretto. La documentazione MariaDB dell'errore 1213 descrive il deadlock e la ripetizione della transazione.
Ripristinare le sessioni e mantenere un ordine coerente dei blocchi
Accedere a più righe secondo un ordine di chiavi coerente e mantenere brevi le transazioni riduce le occasioni di attesa circolare. Condizioni troppo ampie e indici inadeguati possono estendere l'ambito dei blocchi. Esaminare query, log e sequenza delle transazioni, distinguendo un deadlock da un generico errore di connessione.
ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;
Eseguire il ripristino in A e in B e confrontare i risultati con i valori salvati all'inizio in ciascuna connessione. Le variabili utente scompaiono chiudendo la connessione. In una nuova connessione controllare nuovamente le impostazioni senza presumere che le variabili precedenti esistano ancora.
Concludere entrambe le transazioni prima di eliminare la tabella seguente e verificare che contenga ancora soltanto dati di prova eliminabili. DROP TABLE elimina struttura e righe e non viene annullato da un normale ROLLBACK.
DROP TABLE shopcourse.accounts_36_lab;




