Developing Successful Oracle Applications - Expert Oracle Database Architecture - page 40

This will block at this point, as only one transaction at a time can update the row. This demonstrates the first
possible outcome—we would block and wait for the row. But since we're using SERIALIZABLE in Oracle, we'll observe
the following behavior as we commit the first session's transaction:
ops$tkyte session(419,269)> commit;
Commit complete.
The second session will immediately display the following error:
ops$tkyte session(6,479)> update id_table
2 set id_value = id_value+1
3 where id_name = 'MY_KEY';
update id_table
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
That error would occur regardless of the ordering of the preceding commit statement. All it takes is for your
transaction to attempt to modify any record that was modified by some other session since your transaction began.
So, that database-independent piece of logic really isn't database independent at all. It may not even perform reliably
in a single database, depending on the isolation level! Sometimes we block and wait; sometimes we get an error message.
To say the end user would be upset in either case (wait long time, or wait long time to get error) is putting it mildly.
This issue is compounded by the fact that our transaction is much larger than just outlined. The UPDATE and
SELECT in the example are only two statements of potentially many other statements that make up the transaction.
We have yet to insert the row into the table with this key we just generated, and do whatever other work it takes to
complete this transaction. This serialization will be a huge limiting factor in scaling. Think of the ramifications if this
technique was used on web sites that processed orders, and this was how we generated order numbers. There would
be no multiuser concurrency, so we would be forced to do everything sequentially.
The correct approach to this problem is to use the best code for each database. In Oracle 12 c this is as follows
(assuming the table that needs the generated primary key is T ):
EODA@ORA12CR1> create sequence s;
Sequence created.
EODA@ORA12CR1> create table t
2 ( x number
3 default s.nextval
4 constraint t_pk primary key,
5 other_data varchar2(20)
6 )
7 /
Table created.
Alternatively, you could use the IDENTITY attribute and skip the sequence generation:
EODA@ORA12CR1> create table t
2 ( x number
3 generated as identity
4 constraint t_pk primary key,
5 other_data varchar2(20)
6 )
7 /
Table created.