| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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 16:12:11 -0600, you wrote:
>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';
>
nope, i'm 100% default:
Connected to:
Oracle7 Server Release 7.3.4.0.1 - Production
With the distributed, parallel query and Spatial Data options
PL/SQL Release 2.3.4.0.0 - Production
scott_at_ORA734.WORLD> select ini_trans, freelists, freelist_groups from all_tables whe 2 re table_name = 'T';
INI_TRANS FREELISTS FREELIST_GROUPS
---------- ---------- ---------------
1 1 1
SVRMGR> show parameter block_size
NAME TYPE VALUE ----------------------------------- ------- ------------------------------ db_block_size integer 8192SVRMGR> did you run my test?
>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"
>
--
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 - 19:47:52 CST
 |
 |