Transcript
From Block to Lock – Lock Modes von der Theorie bis zur Praxis Tobias Deml Trivadis GmbH München
Schlüsselworte Performance, Troubleshooting, Datenmodell, Sperre, Lock, Constraints Einleitung Ein bekanntes Szenario für Datenbank Administratoren: Von den Anwendern wird ein "Lock“ in der Datenbank" gemeldet. Nun beginnt die Analyse der vorgefundenen Situation mit der Klassifizierung der Sperre und der Identifizierung des Blockers. Im produktiven Betrieb ist ein solcher Zustand meist kritisch und lässt daher oftmals wenig Zeit für diese Tätigkeiten. Aus diesem Grund ist es wichtig, die vorherrschende Sachlage richtig verstehen und einschätzen zu können. Diese Sperren sind ein elementarer Bestandteil der Sicherstellung von Regeln, die ein relationales Datenbanksystem ausmachen. Konzeptionelle Grundlage der Sperren Mit dem bekannten Schriftstück „A Relational Model of Data for Large Shared Data Banks“ legte der britische Mathematiker und Physiker Edgar F. Codd die Grundlagen eines modernen relationalen Datenbanksystems fest. Hiermit wurden Grundsätze festgelegt die noch immer Bestand und Anwendung finden. Einige dieser Regeln werden in einer Oracle Datenbank mit Sperren in der Datenbankstruktur sichergestellt. Eine davon ist die referenzielle Integrität, welche die Beziehungen (Constraint) zwischen Objekten in einer Datenbank regelt. So muss gesichert sein, dass ein entsprechend gegenstehender Primärschlüssel bereits vorhanden ist, wenn ein neuer Datensatz mit einem Fremdschlüssel in eine Datenbank geladen wird. Umgekehrt gilt, wenn Daten gelöscht werden, dürfen keine Daten mehr existieren, welche diesen Schlüssel referenzieren. Ein weiterer Grundsatz ist die Konsistenz in einer Datenbank. Es muss sichergestellt werden, dass die Daten zum Zeitpunkt der Abfrage immer den aktuell festgeschriebenen Zustand widerspiegeln. Ebenso genannt wird die Konkurrenz bzw. Nebenläufigkeit, welche Regeln für die simultane Bearbeitung der Daten von mehreren Parteien festlegt. Hierbei wird sichergestellt, dass die gleichzeitige Änderungsanforderung konsistent und fehlerfrei abläuft.
Arten von Sperren in einer Datenbank Um diesen divergierenden Anforderungen gerecht zu werden, gibt es eine Vielzahl von verschiedenen Sperren. Diese Locks können in drei Gruppen eingeteilt werden. Die folgende Abbildung zeigt die verschiedenen Arten und die jeweilig vorhandenen Untergruppierungen:
DML Locks Bei dieser Art unterscheidet man grundsätzlich zwei verschiedene Typen: - „Row Lock“(TX) - „Table Lock“(TM) Ein TX-Lock beschreibt die Sperrung einer oder mehrerer Zeilen in einer Tabelle durch eine DMLOperation (Data Manipulation Language). Diese Sperren sind immer exklusiv, also kann diese jeweils nur einmal pro Row gesetzt werden. Das Setzen des Locks geschieht über den Eintrag der jeweiligen Session-Informationen in den Header des entsprechenden Datenbankblocks. Wenn ein Benutzer eine solche Aktion durch INSERT, UPDATE oder DELETE auslöst, resultiert daraus immer eine Sperre auf die entsprechende Tabelle. Diese Sperre wird implizit gelöst, sobald die jeweilige Transaktion durch einen Commit oder einen Rollback abgeschlossen ist. Daneben gibt es die „Table Locks“ (TM), die eine Sperrung einer Tabelle beschreiben. Hier wird zwischen fünf verschiedenen Lock-Arten unterschieden: Row Shared Lock (SS/RSX) Diese Art von Sperre ist am wenigsten restriktiv und erlaubt somit die meiste Konkurrenz auf die entsprechende Tabelle. Dieser Lock zeigt an, dass eine Transaktion, welche diese Sperre auf der Tabelle hält, beabsichtigt, eine Zeile zu ändern. Row Exclusive Table Lock (SX/RX) Diese Variante zeigt an, dass eine Session Daten eine DML-Operation ausgelöst oder durch ein SELECT…FOR UPDATE hervorgerufen hat. Dieser Lockerlaubt jedoch SX-Sperren von weiteren Transaktionen. Somit ist es auch möglich, andere Datensätze der gleichen Tabelle zu bearbeiten.
Shared Table Lock (S) Dieser Lock erlaubt generell die Abfrage aller Daten. DML-Operationen(INSERT, UPDATE, DELETE) der Daten sind nur möglich, wenn nur ein Shared Lock auf der Tabelle existiert. Außerdem reicht das Setzen dieser Sperre nicht aus, um die Daten der Tabelle zu ändern. Shared Row Exclusive Table Lock (SSX/SRX) Etwas restriktiver als der S-Lock kann hier nur ein SSX pro Tabelle gehalten werden. Die Selektion der Daten ist für alle weiteren Sessions möglich, aber deren Änderung wird durch diese Sperre verhindert. Exclusive Lock (X) Dieser Lock ist der restriktivste seiner Art. Diese Sperre verhindert jegliches Ausführen von DMLOperation und Setzten eines anderen Lock-Modes. Jedoch ist die Selektion von Daten noch immer blockadefrei möglich. Hier eine Matrix mit den oben beschriebenen Lock-Modes und welche Operationen bzw. Sperren bei den jeweiligen Optionen noch möglich sind.
DDL Locks Diese Art von Sperren schützt während einer laufenden DDL-Operation die Definition von Datenbankobjekten, die in dem ausgeführten Statement referenziert werden. DDL Locks beziehen sich immer auf ein einzelnes Objekt und nie auf das gesamte Data Dictionary. Bei der Erstellung beispielsweise einer Procedure wird eine Sperre gesetzt, um während des Aufbaus des Objekts zu verhindern, dass eine referenzierte Tabelle strukturell geändert oder sogar entfernt wird. Bei dieser Gruppierung von Locks wird zwischen drei verschiedenen Typen unterschieden: Exclusive DDL Lock Dieser exklusive Lock verhindert die Ausführung jeglicher DDL oder DML-Operationen auf ein bestimmtes Objekt. Die meisten DDL-Operationen lösen eine solche exklusive Sperre aus, um einen alleinigen Zugriff auf das entsprechende Objekt zu gewährleisten. Beispielsweise ist es während einer DROP-Operation auf eine Tabelle nicht möglich, gleichzeitig deren Struktur durch ein ALTERStatement zu verändern. Eine exklusive DDL Sperre dauert während der Ausführung der entsprechenden Operation an und wird automatisch nach deren Beendigung wieder entfernt. Share DDL Lock Die Share DDL Sperre verhindert, dass ein exklusiver Lock gesetzt werden kann. Es dürfen auf der Tabelle DML und DDL-Operationen ausgeführt werden, solange diese keine exklusive Sperre auf das Objekt benötigen. Während eines CREATE PROCEDURE-Statement etwa darf die referenzierte Tabelle nicht strukturell geändert werden. Jedoch ist es möglich, dass eine weitere Session gleichzeitig eine Procedure erstellt, welche die selbige Referenz auf die genannte Tabelle besitzt. Wie ein exklusive Lock, dauert die Share DDL Sperre ebenfalls nur während der Laufzeit der jeweiligen Operation an. Nach Beendigung des Statements wird der Lock automatisch gelöst. Breakable Parse Lock Diese Art von Sperre hält einen Lock auf einen entsprechen Bereich der Shared SQL Area während des Parse-Vorgangs eines Objekts (View, Procedure…). In diesem Bereich sind Informationen zu den vorhandenen Abhängigkeiten auf weitere Datenbankobjekte hinterlegt. Die Sperre kann durchbrochen und folglich invalidiert werden, wenn eine kollidierende DDLOperation auf die referenzierenden Datenbankobjekte aufgeführt wird. Diese Art von Sperre wird gehalten, solange der oben genannte Bereich nicht aus dem Shared Pool gealtert ist.
System Locks Eine Oracle Datenbank verwendet eine Vielzahl von Sperren, um die interne Datenbank und Memory Struktur zu schützen. Diese Mechanismen sind dem Benutzer unzugänglich, da er keine Kontrolle über deren Auftreten oder Dauer hat. Latches Bei Latches handelt es sich um Serialisierungsmechanismen, welche die Zugriffe von mehreren Benutzern auf Datenbankobjekte, Dateien und geteilte Datenbankstrukturen regeln. Ebenfalls verhindern sie das Auftreten von Memory-Korruption, die aufgrund konkurrierender Zugriffe auftreten können. Im Speziellen unterbinden sie ebenfalls das Auftreten folgender Situationen: - Konkurrierende Modifikation der gleichen Daten von verschiedenen Sessions - Lesen von noch nicht festgeschriebenen Daten - Ausaltern von Daten aus der Memory-Struktur während diese verwendet werden Wenn beispielsweise mehrere Prozesse einer Instanz einen Speicherbereich im Shared Pool allokieren möchten, wird der neu reservierte Teil im Memory durch einen Latch geschützt. Somit wird verhindert, dass mehrere Prozesse gleichzeitig auf den entsprechenden Speicherbereich Zugriff bekommen. Ein Latch in einer Oracle Instanz bzw. Datenbank ist immer nur von sehr kurzer Dauer. Wenn beispielweise das Gehalt eines Mitarbeiters verändert wird, ist es nicht unüblich, dass während dieses Vorgangs mehrere tausend von Latches gesetzt und wieder gelöst werden. Die Dauer, die Größe des gesperrten Bereichs und die Wartezeit auf einen gesetzten Latch sind betriebssystemabhängig. Je größer die Anzahl der Sperren, desto geringer die Möglichkeit, ungehindert Daten zu manipulieren.
Zur Veranschaulichung: Wenn etwa die Anzahl von Hard Parses steigt, erhöht sich auch die Menge an Latches im Library Cache. Mutexes Ein Mutex verhindert die Ausalterung oder die Korruption von Objekten im Memory, solange diese im Zugriff sind. Diese Art von Lock hat eine Ähnlichkeit zum Latch. Der Unterschied besteht darin, dass dieser nicht eine Gruppe von Objekten sondern nur einen Teil davon schützt. Ein Mutex hat folgende Vorteile gegenüber des Latches: - Dieser reduziert die Konkurrenz verschiedener Sessions bzw. Prozesse, da er nur einzelne Objekte und nicht eine Gruppe davon schützt. Dies bewirkt, dass auf einem bestimmten Speicherbereich mehr Verbindungen konkurrenzfrei agieren können. - Ein Latch verbraucht mehr Memory. Je nach Betriebssystem wird für einen Latch ca. 100-200B benötigt, für einen Mutex lediglich ein Zehntel davon.
Internal Locks Diese haben eine höhere Komplexität als Latches oder Mutexes. Unter folgenden Typen wird unterschieden: - Dictionary cache locks Diese Sperren sind von sehr kurzer Dauer und werden während des Zugriffs oder der Manipulation des Data Dictionary verwendet. Hier wird wie bei den DDL Locks auch zwischen shared und exklusiven Sperren unterschieden. Das Verhalten ähnelt dem der Data Dictionary Locks, nur das diese im Speicher stattfinden. - File und Log Management Locks Diese Locks schützen diverse Dateien einer Oracle Datenbank. Beispielweise verhindert diese Art von Sperre den gleichzeitigen Zugriff mehrerer Prozesse auf ein Control File. Ebenfalls wird durch diesen Typus sichergestellt, dass ein Datafile nicht gleichzeitig an mehreren Datenbanken gemountet sein kann. - Tablespace and Undo Segment Locks Diese Sperre stellt den fehlerfreien Zugriff auf Tablespaces und die Undo Segmente sicher. Es wird damit geregelt, dass nur eine Instanz einer Datenbank in ein einzelnes Segment des Undo Tablespaces schreiben kann. Außerdem werden sie auch dazu verwendet, um über Instanzen hinweg zu bestimmen, ob ein Tablespace den Status Online oder Offline trägt. Locks und Foreign Key Constraints In einem Datenbanksystem wird die Integrität von Daten ebenfalls über Tabellen hinweg sichergestellt. Hierfür werden Beziehungen über Foreign Key Constraints realisiert. Das heißt wenn ein Datensatz in eine „Child“-Tabelle eingefügt wird, müssen bereits alle referenzierten Daten existieren. Wenn ein Mitarbeiten einer neuen Abteilung über eine ID-Spalte zugewiesen wird, muss bereits der entsprechende Satz der Abteilung inklusive ID vorhanden sein. Umgekehrt ist es ebenfalls der Fall: Wenn eine Abteilung gelöscht wird, darf es keinen zugewiesen Mitarbeiter mehr geben. Diese oben beschriebenen Regeln werden durch einen Lock auf die „Child“-Tabelle umgesetzt. Bei der Löschung einer Abteilung wird während der Ausführung des DELETE-Statements ein exklusiver Lock auf die referenzierte Tabelle Mitarbeiter gesetzt. Dadurch wird sichergestellt, dass keine neuen Mitarbeiter mit der zuvor gelöschten Abteilung eingefügt werden können. Oftmals wird für die Optimierung dieses Konstrukts ein Index auf die Foreign Key Spalte der Child Tabelle erstellt. Durch dieses zusätzliche Objekt muss die referenzierte Tabelle zur Sicherstellung der Integrität nicht mehr gesperrt werden. Stattdessen wird bei einer Löschung eines Parent-Datensatzes der gelöschte Wert im Foreign-Key-Index vorbelegt. Somit können keine weiteren Daten mit diesem Referenzschlüssel in die Child-Tabelle eingefügt werden. Die Regel der Index Erstellung auf Foreign Key Constraints kann und darf aber nicht generalisiert werden. Jeder Index, der auf der Child Tabelle angelegt wird, verschlechtert die Performance bei Insert- und Update Operationen. Falls sichergestellt werden kann, dass auf der Parent Tabelle selten oder generell keine Daten gelöscht werden, kann in Erwägung gezogen werden, den Index zugunsten der Performance nicht zu erstellen.
Deadlocks Ein Deadlock beschreibt eine Situation, in der zwei oder mehrere Verbindungen auf die Lösung einer Sperre warten, aber der jeweils andere diese inne hält. Diese Vorkommnisse werden durch die Oracle Datenbank automatisch erkannt und durch Beendigung einer der Verbindungen gelöst. Der betroffene Benutzer wird über eine entsprechende Meldung auf SQL Ebene informiert. Zusätzlich wird das Auftreten dieser Situation ins Alert.log der Datenbank protokolliert. Hier ein entsprechender Auszug nach dem Auftreten eines Deadlocks: Es wird ebenfalls der Pfad zum entsprechenden Trace-File angegeben, in dem man detailliertere Informationen über die gelöste Sperre findet. Im oberen Teil dieser Dateien befinden sich zunächst generelle Informationen über das System und genauere Merkmale der Session inklusive deren Service, Module, ClientID und Action-Angaben.
Darunter befindet sich der sogenannte „Deadlock Graph“, der Details über die involvierten Sessions aufzeigt. Außerdem sind hier alle gehaltenen und angeforderten Sperren der jeweiligen Verbindungen aufgeführt.
Hierbei werden betroffene Objekte mit deren ID in Hexadezimal-Format angegeben. Diese kann man nach einer Konvertierung zum Dezimal-Format als Filterprädikat auf die DBA_OBJECTS verwenden um somit das entsprechende Objekt zu ermitteln.
Im nächsten Teil befinden sich genauere Informationen über die weiteren Sessions, die in diesem Deadlock involviert waren. Hier sieht man ebenfalls die ausgeführten Statements inklusiv deren SQL_IDs.
Einem Deadlock liegt meistens ein Problem in der Applikation zugrunde. Ein mögliches Szenario könnten verschiedene Applikationsprozesse bilden, die einen gegenläufigen Abarbeitungszyklus besitzen und sich demzufolge gegenseitig bei der Abarbeitung ihrer Queues behindern würden. Da das Auftreten solch einer Situation nicht allein seitens der Datenbank unterbunden werden kann, ist hier eine Zusammenarbeit mit den entsprechenden Applikationsentwicklern gefordert.
Erkennung und Analyse von Lock-Situationen Zur Identifizierung und Klassifizierung der Sperren bei einem Problemfall stellt die Oracle Datenbank verschiedene hilfreiche Views zur Verfügung. Hier ein kurzer Auszug der Wichtigsten: GV$LOCK In dieser Abfrage werden alle aktuell gesetzten Sperren der Datenbank aufgelistet. Über die Spalte TYPE wird klassifiziert, um welchen Lock es sich handelt. Außerdem wird in der Spalte BLOCK dargestellt, ob dieser Vorgang einen weiteren Prozess blockiert. GV$LOCK_TYPE Hierin werden alle Typen dargestellt, welche die Spalte TYPE der GV$LOCK annehmen kann. Diese Klassen werden hier mit Beschreibungen und entsprechenden Abkürzungen gelistet. GV$LOCKED_OBJECT Es werden nur Ergebnisse angezeigt, wenn ein weiterer Prozess auf eine gesetzte Sperre wartet. Es werden ebenfalls Identifikationsmerkmal wie die SESSION_ID und die zugehörige OBJECT_ID aufgeführt. GV$LATCH Dort ist eine statistische Übersicht aller Latches in der Datenbank zu sehen. Gruppiert sind diese Daten nach den jeweiligen Typ. V$WAIT_CHAINS Bei der Selektion dieser View werden Informationen über blockierte Sessions anzeigt. Eine Wait Chain ist eine Kette von Verbindungen, die sich aneinander gereiht blockieren. Jede Zeile dieser Abfrage repräsentiert eine blockierte und eine blockende Session. DBA_DML_LOCKS Diese View zeigt alle DML Locks inklusive einiger Session Informationen an. DBA_BLOCKERS Hier werden Verbindungen angezeigt, wenn diese Sperren auf Datenbankobjekte inne halten und somit andere Session blockieren. Falls diese Verbindung ebenfalls auf einen Lock wartet und somit Teil einer Wait Chain ist, wird diese nicht aufgeführt. Bei RAC-Umgebungen werden hier nur Blocker der aktuellen Instanz angezeigt. Detaillierte Abfrage Um eine ganzheitliche Analyse eines Systems durchführen zu können und im Fehlerfall entsprechende Informationen erheben zu können, muss man über mehrere Views und Tabelle selektieren. Im Anhang befindet sich ein SQL-Statement, das bei einer Lock-Problematik diverse Informationen zurückgibt Unter anderem um welche Sperre es sich handelt und welche Objekte involviert sind. Darüber hinaus wird zusätzlich ein Statement ausgegeben, mit dem sich die blockierende Session beenden lässt.
Fazit Grundsätzlich sind Sperren in einer Datenbank nötig und werden zur Umsetzung diverser Regeln eines relationalen Datenbanksystem benötigt. Falls jedoch Tabelle-Sperren vermehrt auftreten und zum Problem werden, muss eine ausgiebige Ursachenanalyse betrieben werden. Hierbei kann es vielerlei Ursachen geben, wie Probleme bei der Serialisierung von Applikationsprozessen. Ebenfalls sind strukturelle Ungereimtheiten im Datenbankmodell, beispielsweise ein störender Constraint oder andererseits ein fehlender Index auf dem Foreign Key, eine potenzielle Problemquelle. Auch nachdem eine bestehende Lock-Problematik behoben scheint, kann davon ausgegangen werden, dass die Lösung von Dauer ist. Sobald sich die Bedingungen oder die Anforderungen an das Datenbanksystem ändern, verschiebt sich auch die Belastung. Daraus folgen die Verschiebung des Heatpoints eines Systems und auch die Bereiche für mögliches Auftreten von diversen Locks.
Kontaktadresse: Tobias Deml Trivadis GmbH Lehrer-Wirth-Str. 4 D-81928 München Telefon: Fax: E-Mail Internet:
+49 89 99 27 59 32 4 +49 89 99 27 59 59
[email protected] www.trivadis.com
Anhang
SELECT S1.sid "blocking SID", S1.username "blocking user", S1.module "blocking Module", TRIM(S1.action) "blocking Action", S2.sid "blocked SID", S2.module "blocked Module", TRIM(S2.action) "blocked Action", L2.ctime "lock waiting time [s]", S1.event "blocking session waits for", S2.event "blocked session waits for", DECODE(L1.lmode, 0, L1.lmode || ' - no lock', 1, L1.lmode || ' - N/A', 2, L1.lmode || ' - row shared lock (SS)', 3, L1.lmode || ' - row exclusive lock (SX)', 4, L1.lmode || ' - object shared lock (S)', 5, L1.lmode || ' - object shared / row exclusive lock (SSX)', 6, L1.lmode || ' - object exclusive / row exclusive lock (X)', L1.lmode) "lock mode blocking session", DECODE(L2.request, 0, L2.request || ' - no lock', 1, L2.request || ' - N/A', 2, L2.request || ' - row shared lock (SS)', 3, L2.request || ' - row exclusive lock (SX)', 4, L2.request || ' - object shared lock (S)', 5, L2.request || ' - object shared / row exclusive lock (SSX)', 6, L2.request || ' - object exclusive / row exclusive lock (X)', L2.request) "lock request blocked session", L1.type || ' - ' || LT.name "lock type", LT.description "lock type description", ( SELECT SQLT.sql_text FROM GV$SQLText SQLT WHERE SQLT.piece = 0 AND SQLT.address = S2.sql_address AND SQLT.hash_value = S2.sql_hash_value) "blocked session SQL-Text", ( SELECT LISTAGG(AO.object_name, ', ') WITHIN GROUP (ORDER BY AO.object_name) FROM All_Objects AO, GV$Locked_Object LO, GV$Lock L3 WHERE AO.object_id = LO.object_id AND LO.xidSqn = L3.id2 AND LO.session_id = L3.sid AND L3.block = 1 AND L3.sid = L1.sid) "locked object(s)", ( SELECT AO.object_name FROM All_Objects AO WHERE AO.object_id = S2.row_wait_obj#) "waiting object", ( SELECT 'ALTER SYSTEM KILL SESSION ''' || S3.sid || ',' || S3.serial# || ', @' || inst_id || ''' IMMEDIATE;' FROM GV$Session S3 WHERE S3.sid = S1.sid AND ROWNUM = 1) "Kill SQL" FROM GV$Session S2, GV$Lock L2, GV$Lock_Type LT, GV$Lock L1, GV$Session S1 WHERE S2.sid = L2.sid AND L2.request > 0 AND S2.blocking_session = S1.sid AND LT.type = L1.type AND L1.BLOCK = 1 AND L1.sid = S1.sid;
