Mit Reservable Columns wird ein neuer Locking Mechanismus eingeführt. Es soll damit das Handling von langen Transaktionen mit Updates auf numerischen Spalten erleichtert werden.
Typisches Beispiel: In einem Webshop befüllen viele Benutzer gleichzeitig ihren Warenkorb, die Änderungen am Restbestand sollten dabei nicht blockiert werden.
Als Reservable kann nur eine numerische Spalte deklariert oder geändert werden, NULL Werte sind nicht erlaubt.
Eine Tabelle kann bis zu 10 Reservable Columns haben. Ein Check Constraint auf eine Reservable Column ist möglich.
create table T
(ID number primary key,
NAME varchar2(32),
ANZAHL number,
RC_ANZAHL number reservable);
alter table T add constraint T_CONRC check (RC_ANZAHL>0);
alter table T add RC_ANZAHL2 number reservable;
Im Hintergrund wird für die Tabelle mit Reservable Column im selben Tablespace eine Journaltabelle mit dem Namen SYS_RESERVJRNL_<OBJECT_ID der Originaltabelle> angelegt.
Die neuangelegte Journaltabelle:
desc SYS_RESERVJRNL_703615
Name Null? Type
----------------------------------------- -------- ----------------------------
ORA_SAGA_ID$ RAW(16)
ORA_TXN_ID$ RAW(8)
ORA_STATUS$ CHAR(12)
ORA_STMT_TYPE$ CHAR(16)
ID NOT NULL NUMBER
RC_ANZAHL_OP CHAR(7)
RC_ANZAHL_RESERVED NUMBER
RC_ANZAHL2_OP CHAR(7)
RC_ANZAHL2_RESERVED NUMBER
insert into T values (1,'Orange',10,10,100);
insert into T values (2,'Banane',20,20,200);
commit;
alter table T modify RC_ANZAHL2 not reservable;
In der Journaltabelle werden dann entsprechende Spalten gelöscht:
desc SYS_RESERVJRNL_703615
Name Null? Type
----------------------------------------- -------- ----------------------------
ORA_SAGA_ID$ RAW(16)
ORA_TXN_ID$ RAW(8)
ORA_STATUS$ CHAR(12)
ORA_STMT_TYPE$ CHAR(16)
ID NOT NULL NUMBER
RC_ANZAHL_OP CHAR(7)
RC_ANZAHL_RESERVED NUMBER
Wenn die Tabelle keine Reservable Columns mehr beinhaltet, wird die Journaltabelle gelöscht.
Eine Session sieht in der Journaltabelle nur eigene Änderungen, diese werden nicht aggregiert:
update T set RC_ANZAHL=RC_ANZAHL+1 where id=1;
update T set RC_ANZAHL=RC_ANZAHL+2 where id=1;
update T set RC_ANZAHL2=RC_ANZAHL2+100 where id=1;
select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID,RC_ANZAHL_OP,RC_ANZAHL_RESERVED,
RC_ANZAHL2_OP,RC_ANZAHL2_RESERVED from SYS_RESERVJRNL_703615;
ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ID RC_ANZA RC_ANZAHL_RESERVED RC_ANZA RC_ANZAHL2_RESERVED
---------------- ------------ -------------- --- ------- ------------------ ------- -------------------
03000F00D5EB0000 ACTIVE UPDATE 1 + 1
03000F00D5EB0000 ACTIVE UPDATE 1 + 2
03000F00D5EB0000 ACTIVE UPDATE 1 + 100
Ein Verschieben der Journaltabelle ist nicht möglich:
alter table SYS_RESERVJRNL_703615 move tablespace USERS
ORA-55727: DML, ALTER, RENAME, and CREATE UNIQUE INDEX operations are not allowed on the reservation journal table "PNO"."SYS_RESERVJRNL_703615".
Non unique Index ist zwar möglich, würde ich aber nicht empfehlen.
create index SYS_RESERVJRNL_703615_IDX1 on SYS_RESERVJRNL_703615(ID)
Reservable Column kann nicht indiziert werden und ist nicht in IOTs, External und Temporary Tabellen erlaubt.
Beispiele der weiteren Restriktionen:
update T set RC_ANZAHL=2*RC_ANZAHL;
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.
update T set RC_ANZAHL=RC_ANZAHL+2 where name='Banane';
ORA-55732: Reservable column update should specify all the primary key columns in the WHERE clause.
update T set ANZAHL=10 ,RC_ANZAHL=10 , RC_ANZAHL2=100 where id=1;
ORA-55735: Reservable and non-reservable columns cannot be updated in the same statement.
drop table T;
ORA-55764: Cannot DROP or MOVE tables with reservable columns.
First run "ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE)" and then
DROP or MOVE the table.
create table T2 as select * from T;
ORA-55762: Reservable column property is not supported for columns of a view
Lock beim Update einer 'normalen' Spalte:
update t set RC_ANZAHL=RC_ANZAHL+2 where id=1;
1 row updated.
select sid,id1,id2,type,lmode,object_name,request from v$lock l
left outer join dba_objects o on o.object_id=l.id1 where type!='AE' and sid in (52,53) order by sid,object_name ;
Sid ID1 ID2 TY LMODE OBJECT_NAME REQUEST
------- -------------- -------------- -- -------------- ------------------------------ --------------
52 703616 0 TM 3 SYS_RESERVJRNL_703615 0
52 703615 0 TM 3 T 0
52 131099 60544 TX 6 0
53 703616 0 TM 3 SYS_RESERVJRNL_703615 0
53 703615 0 TM 3 T 0
53 196608 60541 TX 6 0
Deadlock passiert auch nicht.
Session 52:
update t set RC_ANZAHL=RC_ANZAHL+1 where id=1;
1 row updated.
Session 53:
update t set RC_ANZAHL=RC_ANZAHL+2 where id=2;
1 row updated.
update t set RC_ANZAHL=RC_ANZAHL+1 where id=1;
1 row updated.
Session 52:
update t set RC_ANZAHL=RC_ANZAHL+2 where id=2;
1 row updated.
select sid,id1,id2,type,lmode,object_name,request from v$lock l
left outer join dba_objects o on o.object_id=l.id1 where type!='AE' and sid in (52,53) order by sid,object_name ;
Sid ID1 ID2 TY LMODE OBJECT_NAME REQUEST
------- -------------- -------------- -- -------------- ------------------------------ --------------
52 703616 0 TM 3 SYS_RESERVJRNL_703615 0
52 703615 0 TM 3 T 0
52 458774 45521 TX 6 0
53 703616 0 TM 3 SYS_RESERVJRNL_703615 0
53 703615 0 TM 3 T 0
53 327693 60557 TX 6 0
Update einer ‚normalen‘ Spalte und Update einer Reservable Column - sie blockieren sich nicht .
Session 52:
update t set RC_ANZAHL=RC_ANZAHL+1 where id=1;
1 row updated.
Session 53:
update t set ANZAHL=2*ANZAHL where id=1;
1 row updated.
Session 54:
update t set RC_ANZAHL=RC_ANZAHL+1 where id=1;
1 row updated.
select sid,id1,id2,type,lmode,object_name,request from v$lock l
left outer join dba_objects o on o.object_id=l.id1 where type!='AE' and sid in (52,53,54) order by sid,object_name ;
Sid ID1 ID2 TY LMODE OBJECT_NAME REQUEST
------ -------------- -------------- -- -------------- ------------------------------ --------------
52 703616 0 TM 3 SYS_RESERVJRNL_703615 0
52 703615 0 TM 3 T 0
52 131074 71412 TX 6 0
53 703615 0 TM 3 T 0
53 327706 71423 TX 6 0
54 703616 0 TM 3 SYS_RESERVJRNL_703615 0
54 703615 0 TM 3 T 0
54 393235 71475 TX 6 0
Da eine Session andere Änderungen in der Journaltabelle nicht sieht, kann es ein bisschen irreführend sein.
Beim Standard Update muss man die Zeile exklusiv für sich haben, es wird der aktuelle Wert in der Tabelle geprüft.
Beim Update einer Reservable Column wird intern auch der Inhalt der Journaltabelle geprüft.
Check Constraint T_CONRC mit (RC_ANZAHL>0).
Session 52:
update T set RC_ANZAHL=RC_ANZAHL-5 where id=1;
Session 53:
select RC_ANZAHL from T where id=1;
RC_ANZAHL
--------------
10
update T set RC_ANZAHL=RC_ANZAHL-8 where id=1;
ERROR at line 1:
ORA-02290: check constraint (PNO.T_CONRC) violated
Im SQLTrace sieht man interne Statements wie:
SELECT NVL(((select NVL(sum(RC_ANZAHL_RESERVED), 0)
from
SYS_RESERVJRNL_703615 where ORA_STATUS$ = 'ACTIVE' and RC_ANZAHL_OP = '+'
and ORA_TXN_ID$ = :TXID and ID = :VAL1 ) - (select
NVL(sum(RC_ANZAHL_RESERVED), 0) from SYS_RESERVJRNL_703615 where
ORA_STATUS$ = 'ACTIVE' and RC_ANZAHL_OP = '-' and ID = :VAL1 )), 0) as
curr_reserv from dual
Im Unterschied zum Commit eines Update auf 'normalen' Spalten, sieht man bei Reservable Columns im SQLTrace nach dem Commit zusätzliche interne Statements - eigentliches Update mit den Werten aus der Journaltabelle und Löschen der Daten aus der Journaltabelle:
commit;
update (select B$.ID,B$.RC_ANZAHL,ORA_ESCR_AGG$.RC_ANZAHL_RESVAL from PNO.T
B$ inner join (select EJ$.ID,NVL(sum(case when RC_ANZAHL_OP = '+' then
RC_ANZAHL_RESERVED when RC_ANZAHL_OP = '-' then -1*RC_ANZAHL_RESERVED else
0 end), 0) as RC_ANZAHL_RESVAL from PNO.SYS_RESERVJRNL_703615 EJ$
where
ORA_TXN_ID$=:1 and ORA_STATUS$='ACTIVE' and ORA_SAGA_ID$ IS NULL group by
EJ$.ID order by EJ$.ID desc) ORA_ESCR_AGG$ on B$.ID=ORA_ESCR_AGG$.ID)
ORA_ESCR_JOIN$ set ORA_ESCR_JOIN$.RC_ANZAHL = ORA_ESCR_JOIN$.RC_ANZAHL +
ORA_ESCR_JOIN$.RC_ANZAHL_RESVAL
delete from PNO.SYS_RESERVJRNL_703615
where
ORA_TXN_ID$ = :1 and ORA_SAGA_ID$ IS NULL
Mit dem Einführen von Reservable Columns stellt Oracle einen neuen interessanten Mechanismus zum Reduzieren der Lock Contention in speziellen Situationen, zur Verfügung. Wie gefällt es Ihnen?
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.
