Tämj SQL Server låsningar och deadlocks
Förord
Relationsdatabaser såsom Microsoft SQL Server använder låsningar för att motverka att flera användare kan uppdatera samma data samtidigt. När information är hämtad för en användare så kan ingen annan modifiera samma data förrän den första användaren är klar med att modifiera datat. Det finns undantag som alltid, men kommer inte presenteras i denna artikel.
Tämj SQL Server låsningar och deadlocks
Dra nytta av Of NOLOCK samt ROWLOCK
Skribent: Michael Balloni
Vissa databaser inklusive SQL Server använder låsningar (locks) för att förhindra användaren se information som är under förändring. Om användare A modifierar data så kommer Användare B och alla övriga få vänta till Användare A är färdig med sina modifieringar av visst data innan dom ens kan få läsa samma data, ännu mindre skriva i samma data.
Databaser placerar något som kallas (locks = låsningar) vid alla nivåer i den fysiska hiearkin, rader, sidor (typiskt några få KB av rader), extents (vanligtvis några få sidor), hela tabellen, och slutligen hela databasen. Vissa databaser såsom Oracle använder en finare radlåsning, andra gör inte radlåsningar alls och endast några gör grova låsningar på radnivå. Detta är pg.a. att låsningsmekanismen är en svår sak. Låsningar är inte små och enkla saker, så om du endast gör radlåsningar kan du få mycket problem. En miljon radlåsningar kan enkelt ta massor av minne och därmed göra systemet intill okörbart.
Låsningstvist förklaras
Databaser som inte bara gör rad-låsningar ofta använder en teknik som kallas "lock escalation" för att uppnå högre prestanda. Om det inte är bestämt att hela tabellen skall modifieras så kommer databasen att stänga av låsningen och lägga upp en plan för att ersätta dessa låsningar senare om för många rader kommer modifieras.
Tyvärr så startar "lock escalation" ett helt nytt problem: deadlocks. Om två användare försöker ändra data i två separata tabeller i fallande ordning så kommer båda användarna starta med en radlåsning, därefter försöka att uppdatera detta via en page locks och situationen kommer göra att varje användare behöver något från varandra, och då är dom låsta. Detta är det som du hört kallas deadlock.
Ett exempel:
- UserA modifierar några rader i tabellA som medför en låsning som inte bara AnvändareA modifierar utan även många andra.
- AnvändareB modifierar några rader i tabellB vilket skapar en låsning som inte bara påverkar de rader AnvändareA modifierar utan även flera andra.
- AnvändareA vill modifiera några rader som AnvändareB har låst (men inte ändrat) i TabellB
- AnvändareB vill modifiera eller bara komma åt några rader som användare A har låst (men inte ändrat) i tabellA
Någon måste ge ifrån sig. För att hantera ett problem som detta måste databasen mellan varven leta efter deadlocks och städa undan någon av transaktionerna så andra kan avsluta sina. Oftast dödas den som har gjort minst förändringar för att minimera kostnaden för att rulla tillbaka förändringarna (rollback). Databaser som endast använder rad-låsningar har nästan aldrig detta problem eftersom användare sällan vill modifiera exakt samma rad och än mer ovanligt att dom försöker låsa samma post samtidgt vilket minskar behövet för att göra deadlocks.
Även databaser som denna använder en timeout för låsningar från användare så inte låsningen blir för lång. Samma sak gäller förfrågning som är ytterligare en faktor som inte får ta för lång tid. Du kan skriva frågor som försöker igen om den fått timeout men dennaa automatisering stoppar endast upp hanteringen. Alla timeouts som uppdagas kommer användare att drabbas av. Helt enkelt skall saker och ting inte ta för lång tid.
Enligt tester och under hög belastning så är SQL Server's postlåsning (som är baserad på lock escalation) inte riktigt bra. Varför då? Svaret är att det är en intern tävlan. Tävlan att låsa, vänta på låsningar osv. I ett system som många användare modifierar datat samtidigt och likaså många fler som försöker komma åt informationen samtidigt så går det inte att undvika att låsningarna blir många. Användarna spenderar lång tid på att vänta tills låsningen släpper och därmed är låsningarna frekventa vilket medför att användarna är långt ifrån glada.
Om du kanske bara har några få användare så kommer du inte märka problem med SQL Servers beteende. Du måste kämpa på för att se dessa problem med en applikation som du kanske presenterar och ett fåtal användare testar. Men kasta på ett par hundra användare på din databas med en konstant ström av INSERTS och UPDATES samt massor av DELETE så kommer du börja märka skillader. Men det finns faktiskt en lösning och det kostar inte mycket mer än att göra några små förändringar.
Lösning på tvisten
Om du besöker vissa hemsidor så kommer du få felmeddelande som Object Required, Lock Timeout och liknande. Alla dessa fel beror på låsningarna. Efter att ha läst igenom dokumentationer, pratat med lite folk så kan jag summera följande:
"SQL Server börjar med row-level locks, men går ofta vidare till page och table locks, vilket medför deadlocks."
SQL Server kräver låsning för att läsa från databasen (Select satser), så även folk som inte försöker modifiera databasens innehåll berörs av låsningar. Lyckligtvis så finns det faktiskt några underliga kommandon som förklaras i SQL Servers lexikon. Dessa är NOLOCK samt ROWLOCK.
Dessa används på följande sätt:
SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE 'foobar'
samt
UPDATE Users WITH (ROWLOCK)
SET Username = 'fred' WHERE Username = 'foobar'
Att använda kommandot NOLOCK
När du använder NOLOCK så frågar du snällt SQL Server att ignorera låsningar och direkt läsa från tabellen. Detta betyder att du kan kringå låsningen vilket är en stor prestandaförbättring för skalbara system. Men, du kringår även låsningsmekanismen och därmed låter din kod leva lite farligt. Du kanske läser information som inte är (exakt) då någon redigerar information som inte skickats av en pågående transaktion. Detta är en kalkylerad risk.
För kod som innebär exakta värden såsom valutor, börssiffror och liknande som måste vara exakta - rekomenderas absolut inte dessa kommandon. Men jag tror att du kommer finna att nästan 90% av din applikation är inte så viktig att detta måste efterföljas. I de flesta fall så är det väldigt mycket data som nästan aldrig förändras och därmed är det nästan onödigt att alltid låsa informationen innan den läses.
Ett exempel kan vara om vi vill räkna alla användare som finn i en tabell mellan 1 Januari och 1:a Augusti i år. Det finns ingen anledning för mig att låsa allting - dessa poster kommer ju inte ens att kunna förändras eftersom jag inte vill ha med de som även registrerar sig fram till idag. Ett annat exempel är en lista med filer. Det spelar inte så stor roll om du inte ser exakt vilka filer det är, eftersom du inte äger datat och det inte spelar så stor roll vad du ser och kommer troligen kanske titta på listan senare igen då nya filer laddats upp.
Använd bara inte denna typ av data som en grund för att modifiera databasen, och använd detta när det verkligen är viktigt att användaren inte ser fel information (exempelvis ett konto eller balansräkning).
Att använda kommandot ROWLOCK
Genom att använda ROWLOCK så frågar du snällt SQL Server att endast använda sig av radlåsning. Du kan använda detta i SELECT, UPDATE och DELETE kommandon men använder det endast för UPDATE och DELETE. Du kanske tror att en UPDATE som innehåller ett fält med en primärnyckel skapar en radlåsning, men när SQL server får en batck med en hög av dessa och några av dom råkar vara på samma sida (beroende på denna situation så kan likaväl uppdatera alla filer i en katalog som är skapade och är oförändrade) så kommer du se en page locks varpå det inte blir bra. Och om du inte specifierar den primära nyckeln vid UPDATE eller DELETE så finns det ingen anledning att tro att många av dessa inte kommer påverkas. Så troligtvis blir det en direkt page locks och det är inte bra.
Genom att specifikt kräva rad-nivå låsning så undviker man problemen. Men, var försiktig med att om du har fel och många rader kommer att påverkas - så kommer antingen databasen ta ett eget initiativ att uppgradera till en page lock eller så kommer du ha en hel hög med rad låsningar som fyller din servers internminne och drar ner prestandan. En sak man skall vara extra försiktig med är att "Management/Current activity" mappen i Enterprise Manager. Det tar lång tid att ladda information om alla låsningar. Informationen är värdefull och tekniken är väldigt bra men bli inte förvånad om du ser hundratals låsningar i "Locks/Process" fliken efter att testa denna teknik. Var glad om du inte har timeout på låsningar och deadlocks.
Kompletterande notering
Jag har känsla av att SQL Server uppmuntrar NOLOCK meddelanden men är diskret med ROWLOCK. Du kan endast använda NOLOCK i SELECT frågor. Detta inkluderar inner queries samt SELECT som är resultat av INSERT kommandon. Du kan och ska använda NOLOCK i joins. Ett exempel:
SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups
WITH (NOLOCK) ON
Users.UserID = UsersInUserGroups.UserID
Resultat av att använda NOLOCK samt ROWLOCK
Det är svårt att bedöma prestandan du tjänar på genom att använda denna teknik och det är omöjligt att spekulera hur effekten blir på din hemsida. Innan vi gjorde detta var vår hemsida långsam, ofta oanvändbar och alltid opolitlig. Efter vi gjorde detta blev hemsidan snabb, användbar och stabil. Helt klart var detta en förbättring såsom natt och dag och en sak till, du kommer inte finna information om detta om du söker igenom dokumentationen rörande låsningar.
Dokumentationen rekommenderar dig att skriva om dina applikationer så att tabeller hänvisar rätt. Hålla transaktioner korta och i en batch. Använda en och samma connection för att dela processer och låsningar. Låter bra men är svårt att genomföra. Många konsulter sliter sig i håret världen om utan att finna det - men nu har du läst det här och har du problem med låsningar kan detta faktiskt fungera för din hemsida också.
Några reservationer
Använd denna teknik med försiktighet. Själv gick jag igenom alla mina lagrade procedurer samt frågor som skickades direkt och baserat på när och hur de skulla användas - använda mig av NOLOCKS och där det troligen var fler än ett 50-tal rader som skulle låsas med ROWLOCK.
I nästan alla fall blev det bra, men för dig kanske du måste vara mer försiktig. Du kanske måste skapa separata procedurer baserat på om eller om du inte skall låsa och även för hur det skall låsas eller inte. Det finns även två låsningar till (PAGLOCK samt TABLOCK) som du kanske vill använda när du kör UPDATE eller DELETE som skall påverka många rader.
Hendrik Olsson
d
Jesper Lidefelt
Är inte denna artikel väldigt lik http://binaryworld.net/Main/CodeDetail.aspx?CodeId=1285
Pelle Johansson
Dessa artiklar är direkt översatta från artiklar skrivna med godkännande från http://www.sql-server-performance.com/
Per Edgren
Jag tycker det är toppen att ni översätter artiklar från engelska till svenska, det kan hjälpa ibland att få information serverat på sitt hemspråk. Jag önskar dock en länk till orginalartikeln så man enkelt kan gå in o följa eventuell diskussionstråd om innehållet. Keep up the good work!