Automatic Transaction Rollback

Einleitung

So gut wie jeder DBA hat es schon einmal in seinem Leben erlebt, dass sich ein User beschwert, weil ein kurzes Update plötzlich ewig dauert oder hängt. Nach meist kurzer Analyse entdeckt man, dass das besagte Update auf die Freigabe eines Locks wartet.

In der Regel ergeben sich dann drei mögliche Szenarios:

1. Der Applikationsprozess oder Nutzer, der die Session mit dem Lock hält, ist „steuerbar“ und er kann darüber informiert werden, dass er die Transaktion abschließen soll.
2. Der Applikationsprozess oder Nutzer, der die Session mit dem Lock hält, ist nicht „steuerbar“, aber es ist die Laufzeit bekannt. In diesem Fall heißt es den Nutzer, der auf sein kurzes Update wartet, zu vertrösten.
3. Der Applikationsprozess oder Nutzer, der die Session mit dem Lock hält, ist nicht „steuerbar“ und die Laufzeit ist unbekannt. In diesem Fall wird meist ermittelt, „was“ und „warum“ der Blocker sperrt und es muss gegeneinander abgewogen werden. Nachdem es hier keinen sanften Weg gibt, kommt es dann meist zu einem Session Kill des „Blockers“.

Mit dem Feature Automatic Transaction Rollback kann dem ganzen Szenario vorgebeugt werden. Natürlich sollten Sie dies vorher ausgiebig planen und testen.
Grundlage ist die einfache Logik, dass man einer Transaktion eine Priorität zuordnen und in Kombination mit einer „Wait Time“ dafür sorgen kann, dass Transaktionen mit einer höheren Priorität andere Transaktionen mit einer niedrigeren Priorität automatisch zum Rollback zwingen können.

Relevante Parameter

Grundsätzlich gibt es drei (vier) Parameter die zu setzen sind, wenn Sie Automatic Transaction Rollback“ benutzen wollen.
(Es handelt sich um vier Parameter, wobei zwei ddavon nahezu identisch sind.)

txn_priority

Das ist ein Parameter auf Session-Ebene und steuert die Priorität, mit der die Transaktionen gestartet werden. Er kann den Wert HIGH,,MEDIUM, und LOW haben und lässt sich einfach über ALTER SESSION wie im folgenden Beispiel setzen:

ALTER SESSION SET "txn_priority" = "HIGH";

txn_auto_rollback_*_priority_wait_target

Das ist ein Parameter auf Systemebene und gibt die Wartezeit in Sekunden an. Es gibt ihn in der Variante txn_auto_rollback_high_priority_wait_target und txn_auto_rollback_medium_priority_wait_target.
Gesetzt werden kann der Parameter normal über ALTER SYSTEM:

ALTER SYSTEM SET txn_auto_rollback_high_priority_wait_target = 15;

Diese Einstellung würde bedeuten, dass eine „High Priority“ Transaktion 15 Sekunden wartet, bis sie eine „Medium Priority“ oder „Low Priority“ Transaktion automatisch zurückrollt.

txn_auto_rollback_mode

Das ist ein weiterer Parameter auf Systemebene. Er kann zwei Werte haben: TRACK und ROLLBACK. ROLLBACK ist der Default-Wert und damit wird die Session auch tatsächlich zurückgerollt. Bei TRACK würde nur eine Aufzeichnung stattfinden. Der Parameter ist über ALTER SYSTEM zu setzen:

ALTER SYSTEM SET "txn_auto_rollback_mode"="ROLLBACK";

Mit dem Wert „TRACK“ können Sie arbeiten, wenn Sie sich nicht sicher sind, wie weit die Auswirkungen der Änderungen gehen würden. Über Wait Events können Sie dann ermitteln, wie lang derzeit die Wartezeiten sind. Dafür gibt es auch drei neue Wait Events

• enq:TX - row lock contention (HIGH pri)
• enq: TX - row lock contention (MEDIUM pri)
• enq: TX - row lock contention (LOW pri)

Beispiel

Folgendes einfaches Beispiel zur Veranschaulichung:

Vorbereitung

SQL>  ALTER SYSTEM SET txn_auto_rollback_high_priority_wait_target = 15;

System altered.

SQL> ALTER SYSTEM SET txn_auto_rollback_medium_priority_wait_target = 15;

System altered.

SQL>
CREATE USER ASDF IDENTIFIED BY asdf DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER ASDF QUOTA UNLIMITED ON USERS;
GRANT CREATE TABLE TO ASDF;
GRANT CREATE SESSION TO ASDF;
GRANT CONNECT TO ASDF;
CREATE TABLE test (
    id NUMBER not null,
    text varchar(50) not null
 );
INSERT INTO test (id, text) VALUES (1,'asdf1');
INSERT INTO test (id, text) VALUES (2,'asdf1');
INSERT INTO test (id, text) VALUES (3,'asdf1');
INSERT INTO test (id, text) VALUES (4,'asdf1');
INSERT INTO test (id, text) VALUES (5,'asdf1');
INSERT INTO test (id, text) VALUES (6,'asdf2');
INSERT INTO test (id, text) VALUES (7,'asdf2');
INSERT INTO test (id, text) VALUES (8,'asdf');
INSERT INTO test (id, text) VALUES (9,'asdf3');
INSERT INTO test (id, text) VALUES (10,'asdf');
INSERT INTO test (id, text) VALUES (11,'asdf');
commit;
select * from asdf.test;
        ID TEXT
---------- --------------------------------------------------
         1 asdf1
         2 asdf1
         3 asdf1
         4 asdf1
         5 asdf1
         6 asdf2
         7 asdf2
         8 asdf
         9 jkl2
        10 jkl
        11 asdf

11 rows selected

Durchführung

Für die Durchführung brauchen Sie zwei Sessions des Nutzers asdf. In der ersten Session wird der TXN_PRIORITY auf Medium gesetzt und in der zweiten Session auf High:

Session 1:

SQL> ALTER SESSION SET "txn_priority" = "MEDIUM";

Session altered.

SQL>

Session 2:

SQL> ALTER SESSION SET "txn_priority" = "HIGH";

Session altered.

SQL>

Nun starten Sie mit der ersten Session die erste Transaktion und beenden diese nicht:

Session 1:

SQL> UPDATE asdf.test SET text = 'jkl' WHERE id=9;

1 row updated.

SQL>

In einer dritten Session als DBA User können Sie jetzt über die View V$TRANSACTION sehen, dass es eine Transaction mit der Priorität Medium gibt:

SQL> select TXN_PRIORITY, TXN_PRIORITY_WAIT_TARGET from V$TRANSACTION;

TXN_PRI TXN_PRIORITY_WAIT_TARGET
------- ------------------------
MEDIUM                        15

SQL>

Wenn Sie jetzt in der zweiten Session (Prio: High) eine Transaktion auf dieselbe Zeile starten, wird nach 15 Sekunden die Transaktion von Session eins zurückgerollt:

Session 2:

SQL> set timing on
SQL> UPDATE asdf.test SET text = 'jkl2' WHERE id=9;

1 row updated.

Elapsed: 00:00:15.00
SQL>

Da die Tabelle sehr klein ist, entspricht die Ausführungszeit nahezu nur der Target Wait Time.

Danach sehen Sie auch nur noch eine Transaktion in V$TRANSACTION

SQL> select TXN_PRIORITY, TXN_PRIORITY_WAIT_TARGET from V$TRANSACTION;
TXN_PRI TXN_PRIORITY_WAIT_TARGET
------- ------------------------
HIGH                          15

Leider fällt an dieser Stelle auch, das meiner Meinung nach größte Problem dieses Features auf. Es wird nicht nur die Transaktion zurückgerollt, sondern die ganze Session gekillt:

Session 1 (Komplett):

SQL> ALTER SESSION SET "txn_priority" = "MEDIUM";

Session altered.

SQL> UPDATE asdf.test SET text = 'jkl' WHERE id=9;

1 row updated.

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 60404
Session ID: 41 Serial number: 43201

SQL>

Fazit

Automatic Transaction Rollback ist eine interessante Möglichkeit, die Steuerung des Transaktionsablauf zu erweitern. Wie so oft ist es aber nötig, dass Sie umfassendes Wissen bezüglich Ihrer Applikation haben sollten, um das Feature nutzbringend einzusetzen, da Sie sonst eventuell wichtige Transaktionen unterbrechen und sich damit mehr Probleme machen als Sie lösen.
Abschließend sei noch zu erwähnen, dass es sich um eine Developer Version handelt und sich dies natürlich noch bis zum Release ändern kann.