Database Reference
In-Depth Information
Then, in order to get a new key, you'd have to execute the following code:
EODA@ORA12CR1> update id_table
2 set id_value = id_value+1
3 where id_name = 'MY_KEY';
1 row updated.
EODA@ORA12CR1> select id_value
2 from id_table
3 where id_name = 'MY_KEY';
ID_VALUE
----------
1
Looks simple enough, but the outcomes (notice plural) are as follows:
•
Only one user at a time may process a transaction row. You need to update that row to
increment a counter, and this will cause your program to serialize on that operation. At best,
one person at a time will generate a new value for this key.
•
In Oracle (and the behavior might be different in other databases), all but the first user to
attempt to concurrently perform this operation would receive the error “ORA-08177: can't
serialize access for this transaction” in the
SERIALIZABLE
isolation level.
For example, using a serializable transaction (which is more common in the J2EE environment, where many tools
automatically use this as the default mode of isolation, often unbeknownst to the developers), you would observe the
following behavior. Notice that the SQL prompt contains information about which session is active in this example:
ops$tkyte session(419,269)> set transaction isolation level serializable;
Transaction set.
ops$tkyte session(419,269)> update id_table
2 set id_value = id_value+1
3 where id_name = 'MY_KEY';
1 row updated.
ops$tkyte session(419,269)> select id_value
2 from id_table
3 where id_name = 'MY_KEY';
ID_VALUE
----------
7
Now, we'll go to another SQL*Plus session and perform the same operation, a concurrent request for a unique id:
ops$tkyte session(6,479)> set transaction isolation level serializable;
Transaction set.
ops$tkyte session(6,479)> update id_table
2 set id_value = id_value+1
3 where id_name = 'MY_KEY';
