Als PDF Downloaden!
Werbung
Tipps & Tricks: August 2009 Bereich: DBA, SQL Erstellung: 07/2009 BK Versionsinfo: 11.1 Letzte Überarbeitung: 07/2009 BK Sperren auf Tabellen in einer Oracle Datenbank In jeder Oracle Datenbank mit mehr als einer Session kann es zu Konflikten mit Sperren kommen. Sperren, in der Datenbanksprache Locks genannt, verhindern unter anderem, dass mehrere Benutzer zur gleichen Zeit die gleiche Zeile einer Tabelle verändern können. Innerhalb einer Transaktion können eine oder mehrere Zeilen, sowie ganze Tabellen gesperrt werden. Oracle versucht automatisch (implizit) zu Beginn einer Transaktion die betroffenen Zeilen auf niedrigstem Level zu sperren. Jeder Benutzer kann auch manuell (explizit) eine Sperre auf Tabellen Ebene setzen. Es gibt unterschiedliche Modi, wie Tabellen und Zeilen gesperrt werden. Die einzelnen Sperrmodi und welcher Zugriff dabei für andere Benutzer noch möglich ist, wird in diesem Monatstipp etwas genauer beleuchtet. ROW SHARE Ist die am wenigsten restriktive Sperrmethode LOCK TABLE tabelle IN ROW SHARE MODE; Erlaubt anderen Transaktionen gleichzeitigen Zugriff auf die gesperrte Tabelle. Select, Update, Insert, Delete Operationen, sowie row share, row exclusive, share und share row exclusiv Sperren sind erlaubt. Verhindert, dass andere Transaktionen die Tabelle für exklusiven Zugriff sperren: LOCK TABLE tabelle IN EXCLUSIVE MODE; Beispiel: -- Session 1 (Scott): lock table emp in row share mode; Tabelle(n) wurde(n) gesperrt. -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- --------------- --------------141 SCOTT EMP Row-S (SS) Not Blocking -- Session 2 (Scott): update emp set job = 'BOSS' where empno = 7788; 1 Zeile wurde aktualisiert. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 1 von 7 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- --------------141 SCOTT EMP Row-S (SS) Not Blocking 133 SCOTT EMP Row-X (SX) Not Blocking -- Session 2 (Scott): lock table emp in exclusive mode; -- Wartet auf rollback oder commit der Session 1 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- --------------141 SCOTT EMP Row-S (SS) Blocking 133 SCOTT EMP None Not Blocking Erläuterung: Nach dem Lock Table und Update Statement halten beide Sessions Sperren auf der Tabelle EMP. Wird mit der zweiten Session versucht, die Tabelle exklusiv zu sperren, wird dieses verhindert, indem die 1. Session die 2. Session blockiert. Gleichzeitig wechselt der Status der Sperre der 1. Session in der Spalte BLOCKING_OTHERS in der View DBA_DML_LOCKS von Not Blocking auf Blocking. Aufgelöst wird diese Situation, indem die blockierende Session die Transaktion mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden. ROW EXCLUCIVE Diese Sperrmethode wird automatisch von Oracle für DML Operationen verwendet. Insert INTO TABLE tabelle; Update TABLE tabelle; Delete FROM tabelle; Select for update; LOCK TABLE tabelle IN ROW EXCLUSIVE MODE; Erlaubt anderen Transaktionen gleichzeitigen Zugriff auf die gesperrte Tabelle. Select, Update, Insert, Delete Operationen, sowie row share und row exclusive Sperren sind erlaubt. Wird versucht, auf eine im row exclusive Mode gesperrte Zeile eine weitere Schreiboperation (UPDATE) durchzuführen, funktioniert das jeweilige Statement erst, wenn die Sperre aufgehoben wird. Verhindert, dass andere Transaktionen die Tabelle für exklusiven Zugriff sperren und verhindert auch, dass die Tabelle im SHARE Mode gesperrt wird: LOCK TABLE tabelle IN SHARE MODE; LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN EXCLUSIVE MODE; Beispiel: Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 2 von 7 -- Session 1(Scott): update emp set job = 'BOSS' where empno = 7788; 1 Zeile wurde aktualisiert. -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP Row-X (SX) Not Blocking -- Session 2 (Scott): update emp set job = 'BOSS' where empno = 4711; 1 Zeile wurde aktualisiert. -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP Row-X (SX) Not Blocking 133 SCOTT EMP Row-X (SX) Not Blocking -- Session 2 (Scott): lock table emp in share mode; -- Wartet auf rollback oder commit der Session 1 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP Row-X (SX) Blocking 133 SCOTT EMP None Not Blocking Erläuterung: Auch in diesem Beispiel wird klar, dass alle nicht erlaubten Sperrversuche einen Statuswechsel von BLOCKING_OTHERS auslösen. Aufgelöst wird diese Situation, indem die blockierende Session die Transaktion mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden. SHARE Diese Sperrmethode ist erforderlich, wenn ein Index auf einer Tabelle erstellt werden soll und wird automatisch von Oracle gesetzt, wenn ein CREATE INDEX Statement abgesetzt wird. Create Index name ON tabelle(spalte); LOCK TABLE tabelle IN SHARE MODE; Erlaubt anderen Transaktionen gleichzeitige lesende Zugriffe auf die Tabelle. Auch eine andere Transaktion kann auf die gleiche Tabelle einen LOCK TABLE IN SHARE MODE absetzen. In einem solchen Fall kann keine der Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 3 von 7 Erlaubt anderen Transaktionen gleichzeitige lesende Zugriffe auf die Tabelle. Auch eine andere Transaktion kann auf die gleiche Tabelle einen LOCK TABLE IN SHARE MODE absetzen. In einem solchen Fall kann keine der beiden Transaktionen Schreiboperationen auf der Tabelle durchführen. Schreiboperationen (siehe Beispiel update) warten bis die Sperre aufgehoben ist. Verhindert gleichzeitig Schreiboperationen auf Tabellenebene und Sperren in folgenden Modi: LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN EXCLUSIVE MODE; Beispiel: -- Session 1(Scott): create index idx_big on big_emp(hiredate); -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ------- ------------- ---------------141 SCOTT BIG_EMP Share Not Blocking -- Session 2 (Scott): update big_emp set comm = 100 where empno = 4711 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ------- ------------- ---------------141 SCOTT BIG_EMP Share Blocking 133 SCOTT BIG_EMP None Not Blocking -- Session 1(Scott): Index wurde erstellt. -- Session 2(Scott): 1048576 Zeilen wurden aktualisiert. -- Session 1(Scott): lock table emp in share mode; Tabelle(n) wurde(n) gesperrt. -- Session 2(Scott): lock table emp in share mode; Tabelle(n) wurde(n) gesperrt. -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP Share Not Blocking 133 SCOTT EMP Share Not Blocking -- Session 2 (Scott): insert into emp(empno) values(2222); -- Session 2 wartet Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 4 von 7 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP Share Blocking 133 SCOTT EMP Share Blocking Erläuterung: Aufgelöst wird diese Situation, indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden. SHARE ROW EXCLUSIVE Dieser Modus wird verwendet um eine komplette Tabelle unverändert zu lesen: LOCK TABLE tabelle IN SHARE ROW EXCLUXIVE MODE; Erlaubt anderen Transaktionen die Tabelle zu lesen. Verhindert dass andere Transaktionen die Tabelle im SHARE Modus sperren, oder Änderungen durchführen dürfen: LOCK TABLE tabelle IN SHARE MODE; LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN EXCLUSIVE MODE; Beispiel: -- Session 1(Scott): lock table emp in share row exclusive mode; -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP S/Row-X (SSX) Not Blocking -- Session 2 (Scott): update emp set comm = 100 where empno = 4711; -- Session 2 wartet -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP S/Row-X (SSX) Blocking 133 SCOTT EMP None Not Blocking -- Session 2 (Scott): lock table emp in share mode; -- Session 2 wartet Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 5 von 7 -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------141 SCOTT EMP S/Row-X (SSX) Blocking 133 SCOTT EMP None Not Blocking Erläuterung: Aufgelöst wird diese Situation indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden. EXCLUSIVE Das ist die restriktivste Methode eine Tabelle zu sperren. Dieser Sperrmodus erlaubt nur der eigenen Transaktion exklusiven Schreibzugriff auf die Tabelle. LOCK TABLE tabelle IN EXCLUSIVE MODE; DROP TABLE tabelle; ALTER TABLE tabelle; TRUNCATE TABLE tabelle; Erlaubt anderen Transaktionen lesenden Zugriff auf die gesperrte Tabelle. Nur eine Transaktion kann eine exklusive Sperre auf eine Tabelle setzen. Weitere Schreiboperationen warten darauf, dass die Sperre aufgehoben wird. Verhindert jeglichen schreibenden Zugriff auf irgendeine Zeile durch andere Transaktionen, sowie manuelle Sperren in folgenden Modi: LOCK TABLE tabelle IN ROW SHARE MODE; LOCK TABLE tabelle IN SHARE MODE; LOCK TABLE tabelle IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN ROW EXCLUSIVE MODE; LOCK TABLE tabelle IN EXCLUSIVE MODE; Beispiel: -- Session 1(Scott): lock table emp in exclusive mode; -- Session 3 (Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS ---------- -------- ----- ------------- ---------------163 SCOTT EMP Exclusive Not Blocking -- Session 2(Scott): lock table emp in row share mode; -- Session 2 wartet -- Session 3(Sys) select session_id, owner, name, mode_held, blocking_others from dba_dml_locks; SESSION_ID OWNER NAME MODE_HELD BLOCKING_OTHERS Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 6 von 7 ---------- -------- ----- ------------- ---------------163 SCOTT EMP Exclusive Blocking 138 SCOTT EMP None Not Blocking Erläuterung: Aufgelöst wird diese Situation indem die erste Session die Sperre mit COMMIT oder ROLLBACK beendet. Jede Sperre kann durch einen COMMIT oder ROLLBACK wieder aufgehoben werden. An dieser Stelle kommt noch eine kurze Aufschlüsselung der Abkürzungen in der Spalte MODE_HELD der View DBA_DML_LOCKS. Die View DBA_DML_LOCKS wird von dem Skript ORACLE_HOME\rdbms\ADMIN\catblock.sql angelegt. Modus Wird von Oracle Abkürzung in MODE_HELD auch genannt ROW subexclusive Row-X EXCLUSIVE (SX) table lock ROW subshare Row-S SHARE (SS) table lock SHARE -Share SHARE share-subexclusive S/Row-X ROW(SSX) EXCLUSIVE table lock EXCLUSIVE -Exclusive Dieser Monatstipp enthält nur einen kleinen Ausschnitt der in Oracle möglichen Sperren. Die unterschiedlichen Sperrmöglichkeiten werden u.a. in der View V$LOCK_TYPE gelistet. Dort findet sich auch eine kurze Beschreibung zu den jeweiligen Sperren. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 7 von 7
