serializable transaction problem

alter session set isolation_level = serializable;

change isolation level, so there is no need to explicitly issue locks anymore. Problem is, that "automatic" locks, locking whole table, so it decreasing concurency to make serializable transactions virtually unusable. I did following experiment with 7.3.4. Does all versions having the same problem, or is it getting better in 8?
With 7.3.4 behavior would using serialization kill almost all transactions and make server unusable, because of too many serialization conflicts. Imagine working with table with one million rows and any access to single row, would lock out access to rest of 999999 rows.

Behavior is:

One session do not see changes in database (DML) which are done by other session, even if those are commited. This sounds very cool, but I did some experiments:

it is true, that one session do not see any, even commited, changes from other sessions. It is true, that session could not issue any DML command which touching data modified by other session. In the other words:

Transaction one change one record and do not commit. Transaction two try to change the same record and this update immediately fail. This is very good, maybe better than GemStone aproach, because I have error immediatelly, so no problems with finding what was wrong later on commit.

Unfortunately, there are side effects:

If some other transaction than mine after my commit or rollback updated, inserted or deleted any record in table "a" and regardles if this transaction is commited, mine transaction could not do any insert, update, delete into table "a". Only chance is to commit or rollback and try again. But this need again interaction with user to check if user is still interested in this transaction. In the other words, serialization is implemented by some kind of underlined table level locking, which decreasing concurency a lot.

Stan Received on Thu Dec 23 1999 - 11:24:10 CST