From Block to Lock – Lock Modes von der Theorie bis zur

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;

Download Report