Re: serializable transaction problem

I think it works for you, because your table is created with initrans and freelists parameter higher than my one. Maybe you have in tablespace different initrans and freelists. I had default one and when I created table with value 20, this example with updates works. I have no time to test deletes and inserts. Can you let me know what was your setup?

select ini_trans, freelists, freelist_groups from all_tables where table_name like 'A';

Thanks,

        Stan

Message from: tkyte_at_us.oracle.com
Content-Type: text/plain; charset="iso-8859-1"

   Content-Transfer-Encoding: 7bit

   cannot reproduce. i've attached test.sql and test2.sql that I ran in 7.3.4.

   if you use 2 sessions and run test and when it pauses, run test2 in the    other session and when it pauses hit enter in test and then hit enter in    test2. send me the output (test.lst and test2.lst) and i'll have a look    see....

   beware -- it drops and creates a table T.

> -----Original Message-----
> From: Stanislav Benda [mailto:si_bendovi_at_hotmail.com]
> Sent: Thursday, December 23, 1999 4:04 PM
> To: tkyte_at_us.oracle.com
> Subject: Re: serializable transaction problem
>
>
> Here is test case:
>
> /* session 1 time 1 */
> SQL> alter session set isolation_level = serializable;
> Session altered.
>
> /* session 2 time 1 */
> SQL> alter session set isolation_level = serializable;
> Session altered.
>
> /* session 1 time 2 */
> SQL> update a set a = 'aa' where id = 1;
>
> 1 row updated.
>
> SQL> select * from a;
>
> ID A
> --------- ----------
> 1 aa
> 2 b
> 3 c
>
> /* session 2 time 2 */
> SQL> update a set a = 'bb' where id = 2;
> update a set a = 'bb' where id = 2
> *
> ERROR at line 1:
> ORA-08177: can't serialize access for this transaction
>
>
> SQL> delete from a where id = 2;
> delete from a where id = 2
> *
> ERROR at line 1:
> ORA-08177: can't serialize access for this transaction
>
>
> SQL> insert into a values ( 4, 'd');
>
> 1 row created.
>
> /* session 1 time 3 */
> SQL> commit;
>
> Commit complete.
>
> /* session 2 time 3 */
> SQL> commit;
>
> Commit complete.
>
> /* session 1 time 4 */
> SQL> insert into a values ( 6, 'f');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> /* session 2 time 4 */
> SQL> insert into a values ( 7, 'g');
> insert into a values ( 7, 'g')
> *
> ERROR at line 1:
> ORA-08177: can't serialize access for this transaction
>
> /* end of test case */
>
> As you can see, if one sesion update record, other session could not
> update
> or delete other record in the same table. In some circumstances is
still

> possible insert into table, but in some other circumstances not. Insert
> is
> misterious, because in the same scenario I was once able insert from
> other
> sesion, and in second time not. I am maybe missing something. I would
> greatly appreciate explanation of this behavior or statement, that in
> Oracle
> 8 it works fine. I have no chance to proof if it works fine in Oracle 8
> by
> myself.
>
> Thanks,
>
> Stan
>
> Thomas Kyte wrote in message ...
> >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
>

   Content-Type: application/octet-stream; name="test.sql"
   Content-Transfer-Encoding: 7bit
   Content-Disposition: attachment;filename="test.sql"