Multiversioning

Today I came across one of those situations where a developer needs to be aware of Oracle's multiversion concurrency architecture. A table was created as a lookup table to generate surrogate keys for dimension tables in a data warehouse. Whenever a new row for a dimension was to be loaded, a piece of code was called to find the current maximum key value and add 1 to it. The problem with this is that due to Oracle's multiversioning, if two processes execute this query at the same time, they will both get the same value for the next key. This is obviously not what we want in this situation. The solution is to serialize access to the table by executing a select for update statement prior to retrieving the next key. Or a better solution is to use sequences and bypass the need for serialization at all.

Here is a simple shell script that I put together that illustrates this happening. It executes two sqlplus sessions concurrently in the background, and both sessions select from the lookup table and get the same key.

#!/bin/sh
sqlplus username/password <<endsql
drop table key_lookup;
create table key_lookup(table_name varchar2(30),key number);
insert into key_lookup(table_name, key) values ('DIM_CUSTOMER', 5);
exit
endsq

lfor i in 1 2
do
FILE=`echo $i`
(sqlplus username/password <<endsql
begin for x in ( select nvl(max(key), 0) + 1 as new_key
from key_lookup
where table_name = 'DIM_CUSTOMER' )
loop
insert into key_lookup (table_name, key) values ('DIM_CUSTOMER', x.new_key);
end loop;
end;
/
exit
endsql ) > $FILE 2>&1 &
done

echo Sleep for 10 seconds to wait for process to finish
sleep 10
sqlplus username/password <<endsql
spool lookup.lst
select table_name, key from key_lookup order by table_name, key;
spool off
exit
endsql

The end result is:
ora92> select table_name, key from key_lookup order by table_name, key;
TABLE_NAME KEY
------------------------------ ----------
DIM_CUSTOMER 5
DIM_CUSTOMER 6
DIM_CUSTOMER 6