I am trying to serialize access to a table so that a SQL update
statement will either complete successfully, or if another user has the
row locked, fail immediately. I open a SQL Plus window and execute the
first statement "alter session set isolation_level=serializable". This
is supposed to cause DML statements to fail immdtly if the target row is
locked. Then I execute the update statement which updates one row. I
haven't committed this yet. Now I open another SQL Plus window and type
"alter session..." followed by the same update statement. It hangs. Not
until I type rollback in the first window does the other statement
complete. If I commit in the first window, the second returns with error
"ORA-08177: can't serialize access for this transaction" According to
the messages reference this error means data was changed after the start
of a serial transaction. But how would that happen if the other
transaction started from a SQL Plus window that wasn't even opened until
after the update statement in the first one completed?
The startup parameter COMPATIBLE=7.3.2 is in the database initialization
file. This supposedly enables serial transactions. But it seems not to
be having an effect.
I don't care so much whether the update statement succeeds or fails, as
long as I can get it to return immdtly.