Il punto di una transazione di database è così puoi essere sicuro che diversi fatti all'interno del database sono veri simultaneamente, nonostante ci siano altri utenti che scrivono contemporaneamente nello stesso database.
Prendi l'esempio cannone del trasferimento di denaro tra conti bancari. Il sistema deve garantire che l'account di origine esista, disponga di fondi sufficienti, che l'account di destinazione esista e che avvengano sia l'addebito che il credito o che nessuno dei due avvenga. Deve garantire questo mentre altre transazioni avvengono, forse anche tra questi due account. Il sistema assicura questo prendendo i blocchi sulle tabelle interessate.
Le serrature e quanto del lavoro degli altri utenti che vedi è controllato dal livello di isolamento della transazione. Esistono un paio di livelli di isolamento riconosciuti e ogni DBMS presenta variazioni specifiche dell'implementazione. Le serrature non dovrebbero essere temute. Esistono per proteggere i dati e garantire la coerenza in caso di rollback o arresto anomalo del sistema.
Il rovescio della medaglia è la concorrenza. Questo è il numero di attività diverse che possono accadere contemporaneamente su un singolo database. Ovviamente più blocchi ha un utente, più un altro utente sarà probabilmente bloccato. In definitiva, potrebbero esserci deadlock, come hai visto.
Per evitare deadlock, mantenere le transazioni brevi (ma non più breve del necessario per garantire la coerenza) eseguendo le istruzioni SQL il più vicino possibile. Esegui le transazioni non appena il lavoro è terminato. Leggi le tabelle in una sequenza specifica quando possibile. Ciò promuove il comportamento di accodamento e riduce i deadlock. Assicurati di avere query ben sintonizzate e buoni indici. Aumentare la RAM del server in modo che il working set sia in memoria e le query vengano completate più rapidamente. Elaborare piccole quantità di dati in una transazione DB; idealmente una transazione commerciale per transazione DB. Codifica l'applicazione per rilevare i codici errore deadlock e inviare nuovamente il lavoro.
In qualsiasi applicazione ragionevolmente complessa i deadlock sono inevitabili, alla fine, ma non è necessario che siano catastrofici.