Re: serializable transaction problem

A copy of this was sent to "Stanislav Benda" <si_bendovi_at_hotmail.com> (if that email address didn't require changing) On Thu, 23 Dec 1999 11:24:10 -0600, you wrote:

>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.
>

alter session set isolation_level = serializable;

does *not* lock tables. setting

serializable=true

in the init.ora would -- allowing only for 1 updater at a time per table.

isolation_level = serializable is highly concurrent (we use it for our TPC-C's)

>
>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.
>

can you post a test case... some scripts to run in sqlplus?

>Stan
>
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 23 1999 - 12:06:15 CST