Sperren auf Tabellen in einer Oracle Datenbank

03.
August
2009
Veröffentlicht von: Bernhard Koch

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.

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 <link blog-detailansicht sessions-transaktionen-und-sperren.html external-link-new-window internal link in current>Opens external link in new windowTransaktion 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.
-- 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:

-- 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 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

-- 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

-- 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
---------- -------- ----- ------------- ----------------
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.

ModusWird von Oracle auch genanntAbkürzung in MODE_HELD
ROW EXCLUSIVEsubexclusive table lockRow-X (SX)
ROW SHAREsubshare table lockRow-S (SS)
SHARE-Share
SHARE ROW EXCLUSIVEshare-subexclusive tableS/Row-X (SSX)
EXCLUSIVElockExclusive

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.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.