Chyba: Data jsou dočasně uzamčena jiným uživatelem -DEADLOCK

[jiri.dolezal]

Popis chyby:
Vznik tzv. deadlocků (to je ta chyba SQL:1205 Data jsou dočasně uzamčena jiným uživatelem. Akci opakujte) je vlastnost technologie MS SQL Serveru. Jde vlastně o to, že 2 (nebo více) uživatelé dělají operaci, která zamyká to "samé", nejčastěji např. realizace dokladu, zaúčtování apod. Přičemž SQL Server jednomu uživateli ohlásí hlášku 1205 a druhého nechá doběhnout.

Čím je hardware serveru pomalejší, tím ta operace trvá déle a je pravděpodobnější, že dojde k deadlocku. Čím více uživatelů se pokouší v jednom okamžiku dělat to "samé" (přistupovat do stejných objektů, tabulek), tím je pravděpodobnější, že dojde k deadlocku.

Řešení:
Řešení není jednoduché - klíčem k řešení je deadlock detekovat (ideálně pokud se dá nasimulovat scénář, při kterém k deadlocku dochází), zjistit procesy a SQL kód, které jsou v něm zainteresovány a následně se snažit zavést praktiku k eliminaci deadlocku (přeprogramovat, podpořit operaci indexem). Vždy se však dá začít "od píky" a aplikovat základní doporučení ohledně údržby a provozu databází, MS SQL Serveru obecně.

Více k obecným doporučením zde:
https://forum.helios.eu/orange/forum/viewforum.php?f=6

Např. databáze se reindexuji (=defragmentují) - Možnosti -> Konfigurace / správa systému -> Systémové konstanty - vybrat databázi - akce Údržba databáze a dát volbu Provést údržbu databáze nyní . Při větších (živějších) databázích provádět údržbu pravidelně - denně). Dále NIKDY !!! neprovádět SHRINK databáze, atd.

Dále zaměřit pozornost na veškerá externí řešení, zejm. počítané sloupce, definované vazby, triggery - zejm. ty "komplexní" (čti složité, spojující velké množství tabulek, nevhodně/neoptimálně napsané, apod.) a pokusít se je pro účely testování vyřadit z provozu a následně optimalizovat.

Informace v tomto příspěvku nejsou a ani nemohou být vyčerpávající. Jedná se o komplexní problematiku, která vyžaduje individuální přístup ve všech fázích - analýzy /detekce problému, získávání potřebného know-how, řešení. V případě, že problém nastává při definovatelném scénáři v rámci standardního Helios Orange (bez externích řešení), informujte o tomto scénáři prostřednictvím e-mailové linky hotline.

Další zdroje informací:
http://cs.wikipedia.org/wiki/Deadlock
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://msdn.microsoft.com/en-us/library/ms188246.aspx

[jan.novotny]

Helios disponuje možností jak poodhalit detail deadlocků. V menu Možnosti - Konfigurace/správa systému - Globální konstanty - v pravé části okna, kde jsou Informace o instalaci systému klikněte na řádek SQL server... (levým tlačítkem myši tak, aby byl text promodřen). Následně zde stiskněte pravé tl. myši a vyberte volbu Detailní detekce deadlocků. Dialog, který se objeví Vám zároveň napoví co se zapnutím této možnosti musí stát:

- aby logování deadlocků bylo aktivní, musí dojít k restartu služby SQL server
- následně - pokud dojde ke zmíněné chybě - je tato zaznamenána do souboru ERRORLOG v instalaci SQL serveru

[tomas.koci]

Před zapnutím detailního logování deadlocků (a generováním souborů ERRORLOG) je doporučeno zajistit několik věcí.

Řazeno dle priority:
1. na SQL Serveru je třeba instalovat SP3 na SQL2005, SP1 na SQL2008.

2. Nastavit pravidelnou reindexaci (1x denně) celé databáze jobem (např. Maintenance plan apod., ideálně před zálohováním).

3. Databáze musí mít vypnut Autoshrink (ideálně všechny databáze na SQL Serveru).

4. Databáze se nesmí shrinkovat ani jobem (způsobuje značnou fragmentaci, viz http://www.karaszi.com/SQLServer/info_dont_shrink.asp).

5. Databáze musí mít zapnuto Auto create statistics.

Pokud všechny tyto body nejsou splněny, nelze detekci deadlocků provádět, resp. nemá význam ji dělat.
Pokud po aplikaci uvedených kroků bude stále docházet k deadlockům v počtu větším než jednotky/den, tak má význam začít řešit ERRORLOG.

DEADLOCK obecně:
• Deadlock není chyba, ale vlastnost SQL Serveru. Je důsledkem vlastní MSSQL implementace zámků. Zámky nemůže Helios ovlivnit, o tom SQL Server rozhoduje sám.
• O tom, jestli k deadlocku dojde nebo ne rozhoduje výkonnost hardware > čím výkonější, tím nižší pravděpodobnost výskytu deadlocků. Pokud je databáze fragmentovaná, je potřeba "zbytečně" výkonnější hardware - proto jsou doporučeny reindexace a vypínání shrinků, které sníží zátěž serveru.
• Jednotky deadlocků za den lze považovat za normální.
• Pokud je deadlocků za den řádově více, je třeba nejprve optimalizovat server dle bodů výše.
• Při odhalování deadlocků je třeba se obrátit na dodavatele systému Helios Orange.

[jan.havranek]

Může nastat situace kdy tzv. DEADLOCK není detekován. K zamčení tabulky dojde, procesy vzájemně čekají na ukončení, ale SQL Server deadlock neidentifikuje a žádný z procesů neukončí. Teprve po "ručním" ukončení jednoho z procesů druhý doběhne.

Microsoft dokumentuje, že šlo o chybu na MSSQL 2005, nicméně zkušenost je podobná i na MSQL 2008 R2.
http://support.microsoft.com/kb/915918

V takových situacích lze zvážit na MSSQL vypnout parallelism. Po zkušenostech se pak monitor DEADLOCKU chová korektně. MSSQL Server – Properties - Advanced - Max Degree Of Parallelism - zadat hodnotu 1. K tomuto kroku přistupujte po zralém uvážení a pouze v případě, že se jedná o chybu detekce deadlocků v souvislosti s paralelismem.

[jan.havranek]

Spouštěcím mechanismem deadlocku mohou být i nevhodně postavené uživatelské atributy (počítané a obarvovací sloupce), které provádí nějaký náročný SELECT do stejné tabulky jako vlastní záznam, nad kterým deadlock nastává. Pokud k deadlocku dochází, je vhodné ověřit jestli se tak děje i nad přehledem, který má zobrazeny jen základní atributy.