Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kailash.

Asked: February 01, 2003 - 1:13 pm UTC

Answered by: Tom Kyte - Last updated: September 21, 2015 - 3:43 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

I tried the following 2 scenarios :

Case #1 : serializable

Session 1
=========
Set transaction isolation level serializable;
select * from a;
(displayed 2 rows)

Session 2
=========
delete from a;
commit;

Session 1
=========
select * from a;
(displayed the 2 rows again)
commit;
select * from a;
(no rows selected)

Can you explain me what happened actually?

2. Restored the table again

Case#2 : read committed

Session 1
=========
set transaction isolation level read committed
select * from a;
(displayed 2 rows)

Session 2
=========
delete from a;
(2 rows deleted)
commit

session 1
=========
select * from a;
(no rows selected)
commit
select * from a;
(no rows selected)

What happened in this case?

Case#2 is the usual way Oracle works, right? Can you explain me what is important about case #2 and what is difference between case#1 and case #2

I read through your book "Expert One On One", but not able to understand. I would appreciate if you could provide me an explanation with an example to make things more clear. Awaiting your response on this.

3. Also please explain me under what situations these might be useful


and we said...

1) umm -- well, you asked for serializable in session 1 which means that the database will appear to you to be frozen in time...

It will ensure that every single thing you see is "as of" the point in time you set "serializable please".

So, when you did the first select *, it checked to make sure the data didn't change since you set the transaction isolation (it hadn't, so it just read from the blocks). The second time, it checked the blocks again - found they HAD changes and went to the rollback segments to "undo" those changes -- to restore the block to the way it looked when you said "serializable"

2) well, the same thing as one actually EXCEPT that the queries were to return the data at the point in time they were submitted.

So, when you did the first select *, it checked to make sure the data didn't change since you submitted the query (it hadn't, so it just read from the blocks). The second time, it checked the blocks again - to make sure the data didn't change since you submitted the query (it hadn't, so it just read from the blocks).


Please reread some of the Expert one on one stuff -- redo, rollback, concurrency controls. I have more information in there, along with examples, then I could put here. Just read pages 123-132 a couple more times. If you have a concrete question -- be glad to try and address it.

3) for that -- read the whole book. The answer to this comes from understanding. When you understand what it does -- you will be able to apply it to your problem at hand.

and you rated our response

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

ORA-08177: can't serialize access for this transaction

May 08, 2003 - 10:06 am UTC

Reviewer: Sami from Bangalore,India

Dear Tom,

Thanks for your wonderful service. I need your help again..

How come value "2" has been inserted into SESSION2 but value "5" is giving error in the same session?


19:12:14 SESSION2>set transaction isolation level serializable;

Transaction set.

19:12:19 SESSION2>select * from t;
no rows selected

19:12:54 SESSION1>set transaction isolation level read committed;
Transaction set.

19:12:58 SESSION1>select * from t;

no rows selected

19:13:04 SESSION1>insert into t values(1,1,1,1);

1 row created.


19:13:09 SESSION2>insert into t values(2,2,2,2);

1 row created.

19:13:24 SESSION1>commit;

Commit complete.
19:13:44 SESSION2>insert into t values(3,3,3,3);

1 row created.






19:13:53 SESSION1>insert into t values(4,4,4,4);

1 row created.


19:14:00 SESSION2>insert into t values(5,5,5,5);
insert into t values(5,5,5,5)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction



19:14:24 SESSION1>commit;

Commit complete.

19:14:26 SESSION1>


19:14:36 SESSION2>insert into t values(5,5,5,5);
insert into t values(5,5,5,5)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction



How come value "2" has been inserted into SESSION2 but value "5" is giving error in the same session?
I went thru all the docs related to isolation but not able to understand.

Kindly provide your comments.
Thanks in adavance.

Tom Kyte

Followup  

May 08, 2003 - 6:11 pm UTC

an incomplete test that a simple dry run on my system cannot reproduce....


start with "drop table t"
"create table t ...."
"alter session....."


and show the entire progression.

May 08, 2003 - 6:50 pm UTC

Reviewer: Dave from Colorado

Can you just clarify one thing for me?

Is the point-in-time for consistency as-of the time that the isolation level was set to serializable, or at the time that the first serializable query started?

Tom Kyte

Followup  

May 09, 2003 - 1:07 pm UTC

easy enough to see:


ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set transaction isolation level serializable;

Transaction set.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2    pragma autonomous_transaction;
  3  begin
  4          update t set x = 555;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

         X
----------
         1

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> select * from t;

         X
----------
       555

ops$tkyte@ORA920>
ops$tkyte@ORA920>


from the time it was issued. 

ORA-08177: can't serialize access for this transaction

May 08, 2003 - 11:36 pm UTC

Reviewer: Sami from Bangalore,India

Dear Tom, Sorry for my previous incomplete test case.
Hope this will explain the problem clearly.

In short, Is it possible to manipulate data on a table from both "read committed" and "serializable" transaction?

C:\>sqlplus demo/demo@tamizh1

SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 9 08:50:51 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set sqlprompt SESSION1>
SESSION1>set time on
08:51:07 SESSION1>drop table t;

Table dropped.

08:51:16 SESSION1>create table t(a number,b number,c number,d number);

Table created.

08:52:05 SESSION1>alter table t add constraint t_pk primary key(a);

Table altered.

08:52:18 SESSION1>set transaction isolation level READ COMMITTED;

Transaction set.

08:53:09 SESSION1>insert into t values(1,1,1,1);

1 row created.

08:53:37 SESSION1>commit;

Commit complete.


                    08:54:41 SESSION2>show user
                    USER is "DEMO"
                    08:54:43 SESSION2>set transaction isolation level SERIALIZABLE;
                    
                    Transaction set.
                    
                    08:54:52 SESSION2>select * from t;
                    
                    no rows selected
                    
                    08:55:06 SESSION2>insert into t values(2,2,2,2);
                    
                    1 row created.
                    
                    08:55:17 SESSION2>insert into t values(3,3,3,3);
                    
                    1 row created.



08:55:31 SESSION1>insert into t values(4,4,4,4);

1 row created.
                
                    08:55:43 SESSION2>insert into t values(5,5,5,5);
                    insert into t values(5,5,5,5)
                                *
                    ERROR at line 1:
                    ORA-08177: can't serialize access for this transaction



08:56:03 SESSION1>commit;

Commit complete.
                    08:56:09 SESSION2>insert into t values(6,6,6,6);
                    insert into t values(6,6,6,6)
                                *
                    ERROR at line 1:
                    ORA-08177: can't serialize access for this transaction


08:56:28 SESSION1>
 

Tom Kyte

Followup  

May 09, 2003 - 1:19 pm UTC

your test doesn't compute.

At time 8:54:43, there should be a row in there (in T).


That and I quite simply cannot reproduce it as you have it. I tried -- really, but no go.

transaction isolation Vs Index

May 09, 2003 - 5:39 am UTC

Reviewer: Sami from Bangalore,India

Dear Tom,

Kindly ignore my previous test case and look at this one.
Does isolation depent on indexes?

/***************************
TEST CASE 1: WITH NO INDEX
****************************/
14:53:15 SESSION2>create table r1(a number,b number,c number,d number);

Table created.
14:53:20 SESSION1>set transaction isolation level read committed;

Transaction set.

14:53:42 SESSION2>set transaction isolation level serializable;

Transaction set.

14:53:48 SESSION2>select * from r1;

no rows selected

14:53:58 SESSION1>select * from r1;

no rows selected

14:54:06 SESSION1>insert into r1 values(1,1,1,1);

1 row created.


14:54:14 SESSION2>select * from r1;

no rows selected
14:54:34 SESSION1>commit;

Commit complete.

14:54:38 SESSION1>insert into r1 values(3,3,3,3);

1 row created.


14:54:48 SESSION2>insert into r1 values(2,2,2,2);

1 row created.

14:55:02 SESSION2>insert into r1 values(4,4,4,4);

1 row created.

14:55:12 SESSION1>insert into r1 values(5,5,5,5);

1 row created.


14:55:21 SESSION2>insert into r1 values(6,6,6,6);

1 row created.

14:55:31 SESSION1>commit;

Commit complete.

14:55:38 SESSION2>insert into r1 values(7,7,7,7);

1 row created.

14:55:42 SESSION2>commit;

Commit complete.

/***************************
TEST CASE 2: WITH INDEX(Primary Key)
****************************/



14:55:48 SESSION2>create table r2(a number,b number,c number,d number);

Table created.
14:55:51 SESSION1>set transaction isolation level read committed;

Transaction set.



14:56:06 SESSION2>alter table r2 add constraint r2_pk primary key(a);

Table altered.

14:56:34 SESSION2>set transaction isolation level serializable;

Transaction set.


14:56:43 SESSION2>select * from r2;

no rows selected

14:56:47 SESSION1>select * from r2;

no rows selected

14:57:07 SESSION1>insert into r2 values(1,1,1,1);

1 row created.

14:57:12 SESSION2>select * from r2;

no rows selected

14:57:26 SESSION1>commit;

Commit complete.

14:57:29 SESSION1>

14:57:32 SESSION2>insert into r2 values(2,2,2,2);
insert into r2 values(2,2,2,2)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


14:57:38 SESSION2>






Benefits of using read only transactions

October 19, 2004 - 6:26 pm UTC

Reviewer: A reader

Hi Tom, which is the real benefit from
executing this
DBMS_TRANSACTION.READ_ONLY
When you start a session you know definetively only will be read only.
Thanks.

Tom Kyte

Followup  

October 19, 2004 - 9:03 pm UTC

you freeze the database at that point of time for your session.

Every query you execute will return the answer that was in the database when you issued that statement -- regardless of what happens to the database tables.

it is good for generating a report "as of" a point in time that consists of multiple queries. All queries return answers consistent with regards to each other.

great....

October 19, 2004 - 9:43 pm UTC

Reviewer: mark from chicago, il

does it use the undo tablespace?

Tom Kyte

Followup  

October 20, 2004 - 7:00 am UTC

yes.

other benefict of read only transaction

October 20, 2004 - 10:20 am UTC

Reviewer: A reader

Thanks Tom, but there are other benefit?, for example avoid the use of some latches.

The question is would you suggest to set readonly the transaction when you know this is only read only, because it could improve the use of database resources, even a few?

If not, when you suggest to use it, or it is not a frequently useful feature.

Thanks.

Tom Kyte

Followup  

October 20, 2004 - 11:41 am UTC

use it when you need all queries to return an answer "as of a single point of time"

that is what it is for.

October 20, 2004 - 12:10 pm UTC

Reviewer: A reader

Thanks Tom,
then this means will be a good idea to set it in all reports?


Tom Kyte

Followup  

October 20, 2004 - 12:16 pm UTC

you tell me.

do you have a need for it to be so? if so, sure, if not, no. remember, the further the database deviates from where the database was the longer your queries will take to run (and the larger the odds of a 1555 over time).

like all things, understand it - use it when you need it, don't when you don't

October 20, 2004 - 12:32 pm UTC

Reviewer: A reader

Thanks Tom
This is what I was asking about
" ..database deviates from where the database was the LONGER your queries will take to run..."

usage of isolation level serializable

October 20, 2004 - 1:23 pm UTC

Reviewer: markus from austria

hi tom,

afaik, any transaction (X) with isolation level serializable will raise an "ORA-08177: can't serialize access for this transaction" exception as soon as ANY other transaction (Y) does an insert/update/delete + commit on the data used by (X) because (X) would not work on actual but historical data any longer.

i am not sure if anyone will get this ever working in an high concurrency environment. the transaction will probably never be finished but aborted every time. i would use table locks instead of that. if i get the locks i can get sure my work will be done.

is there really any practical example for this transaction type (in an concurrency environment)???


Tom Kyte

Followup  

October 20, 2004 - 4:49 pm UTC

on data TO BE MODIFIED by (x)

not on data used, data that X has not yet MODIFIED but will.

eg: you have to update something that I would want to update.


we run our TPC-C's (they are slightly "highly concurrent") in this mode.


You have to have people going after exactly the same rows -- same exact rows -- in a write fashion. in highly concurrent situations -- that is not what is happening.

October 20, 2004 - 5:45 pm UTC

Reviewer: David Aldridge from Colorado Springs

>> we run our TPC-C's ... in this mode.

Is that to satisfy a requirement of the TPC test, or for some other reason?


Tom Kyte

Followup  

October 20, 2004 - 8:54 pm UTC


It is a requirement of the TPC-C, the TPC-C has lots of "strange" requirements.

only modifications of the SAME ROWS ???

October 21, 2004 - 12:27 pm UTC

Reviewer: markus from austria

hi tom,

look at this ->

**********
SESSION A)
**********
17:30:27 SQL> create table test (col1 number);

Tabelle wurde angelegt.

17:30:35 SQL> insert into test values (1);

1 Zeile wurde erstellt.

17:30:40 SQL> insert into test values (2);

1 Zeile wurde erstellt.

17:30:46 SQL> commit;

Transaktion mit COMMIT abgeschlossen.

17:30:49 SQL> select * from test;

      COL1
----------
         1
         2


**********
SESSION B)
**********
17:31:00 SQL> set transaction isolation level serializable;

Transaktion wurde gesetzt.

17:31:10 SQL> select * from test;

      COL1
----------
         1
         2


**********
SESSION A)
**********
17:31:19 SQL> insert into test values (3);

1 Zeile wurde erstellt.

17:31:22 SQL> commit;

Transaktion mit COMMIT abgeschlossen.

17:31:25 SQL> select * from test;

      COL1
----------
         1
         2
         3


**********
SESSION B)
**********
17:31:45 SQL> update test set col1 = 0 where col1 <= 2;
update test set col1 = 0 where col1 <= 2
       *
FEHLER in Zeile 1:
ORA-08177: can't serialize access for this transaction


i inserted two rows (1,2) in session A, started a serialized transaction in session B, inserted a NEW row (3) in session A, tried to update the OLD UNTOUCHED rows (1,2) in session B but got ora-08177!?

i remember this mode is there to prevent for lost updates, phantom reads and stuff like this. 

 

Tom Kyte

Followup  

October 21, 2004 - 3:06 pm UTC

i cannot reproduce your findings, but yes, things can at a block level happen (we generally use initrans to help that out)

ops$tkyte@ORA9IR2> create table test (col1 number)  initrans 5 ;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into test values (1);
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into test values (2);
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select * from test;
 
      COL1
----------
         1
         2
 
ops$tkyte@ORA9IR2> set transaction isolation level serializable;
 
Transaction set.
 
ops$tkyte@ORA9IR2> select * from test;
 
      COL1
----------
         1
         2
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !echo 'exec insert into test values (3); commit; ' | sqlplus /
 
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 21 14:42:45 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA9IR2>
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from test;
 
      COL1
----------
         1
         2
 
ops$tkyte@ORA9IR2> update test set col1 = 0 where col1 <= 2;
 
2 rows updated.



<b>but in any case -- for short transactions (oltp), this is viable -- it works.  it gets an 8177 tons less then you would see "deadlock" in the shared read locking implementations such as sqlserver, informix, and db2..</b>
 

result differs

October 22, 2004 - 9:06 am UTC

Reviewer: markus from austria

that's funny. i cut + paste your things but i received ora-08117 instead:


SQL> create table test (col1 number)  initrans 5 ;

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

      COL1
----------
         1
         2

SQL> set transaction isolation level serializable;

Transaction set.

SQL> select * from test;

      COL1
----------
         1
         2

SQL> !echo 'exec insert into test values (3); commit; ' | sqlplus test/test

SQL*Plus: Release 9.2.0.3.0 - Production on Fr Okt 22 14:30:14 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> 
PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> select * from test;

      COL1
----------
         1
         2

SQL> update test set col1 = 0 where col1 <= 2;
update test set col1 = 0 where col1 <= 2
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


any idea why we got different results?

 

Tom Kyte

Followup  

October 22, 2004 - 5:54 pm UTC

I was running with less bits ? :)



No seriously, i used ASSM, it spread the rows out over many blocks.

but, since you would not be updating rows you looked at but didn't lock -- adding a simple "select for update" in the mix straightens it out even in the manual tablespace mode.

There are opportunities for the 8177 at the block level -- even across different sets of rows.

the way it works

October 28, 2004 - 1:38 pm UTC

Reviewer: markus from vianna, austria

tom, i am a little bit confused now ?:o

this isolation level is an ANSI compliant level which should prevent for phantoms, lost updates, bla, bla, bla.

i think this level should work in the same way all the time. it should not depend on the BLOCK LEVEL if this ISOLATION LEVEL is working correctly!? should it?

this looks like "update fails with oracle on windows but will succeed with oracle on solaris" ;)



Tom Kyte

Followup  

October 28, 2004 - 7:31 pm UTC

on other databases, you'll get deadlocks with serializable (lots of them). In Oracle you get fewer 8177's (tpc-c, we have to report how many we get, it is an expected side effect of the isolation level -- the tpc-c expects them, we have to report them)

ORA-08177 - is problmatic only in update/insert on the same table

February 07, 2005 - 5:15 am UTC

Reviewer: Amihay gonen from Israel

Hi ,
Just want to check if I've understand the problem with ORA-08177.

My situation is as the follow :

I need to summaries data which is insert into my table all the time into a table A (each record as insert time stamp).

Then do the following should work :
1. Set transaction isolation level serializable;
2. Do "insert as select" from table A to B. (FTS)
3. Fetch max timestamp .
4. Get all now added records (Index scan)select"


As far as I've understood , I won't get ORA-8177 because I'm writing to another table .

My only issue should be with ora-1555

Tom Kyte

Followup  

February 07, 2005 - 5:37 am UTC

well, if your transaction is steps 1..4, I don't know why you need serializable.

somethings missing from your problem description. or I don't understand what your step 4 is really.

ORA-08177 - explain

February 07, 2005 - 8:01 am UTC

Reviewer: A reader

Hi,
I applogize for the unclear discription .
I will try to explain it better.

I've table (partition by end time ) which get data all the time, and I've index on insert time field (non partition key).

I've another process (oracle job), which is selecting from this table each hour (per partition).

The idea is to do full table scan and aggerate data into other table , and then run again and get all the records add while the first run was executing.

1. Set transaction isolation level serializable;
2. Do "insert as select" from table A to B. (FTS) <=== First Run
3. Fetch max timestamp .
4. Commit (end transcation) (forget to put it before)
5. Get all now added records (Index scan) <=== Second Run
using select * from ... where insert_time > "value in step 3"


I hope this is more clear.
Thanks for the feed back.


Tom Kyte

Followup  

February 07, 2005 - 8:51 am UTC

uh oh.

think about it........

you cannot use that timestamp


you want to use a materialized view which has all of the logic, else you have to be prepared to process the same record twice at times (it is UNAVOIDABLE using a timestamp!!!)


Why?

At time t1 someone inserts but does not commit a record. It's timestamp is T1
At time t2, you start your process. You will not, CANNOT see that record above.
At time t3, they commit.


Now what? you will NEVER see their record! Never.

So, you have to go more like this:

a) find the timestamp of the oldest current transaction in your system (via v$transaction)

that is the timestamp you must use to pull with next time -- but it means you will see the same record twice - you will have to deal with it.

So, why not use materialized views, this is what they do! And they do it right (consistently)


Read this to fully understand:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:16998677475837 <code>

What is a XA transaction?

March 04, 2005 - 5:52 pm UTC

Reviewer: reader


Tom Kyte

Followup  

March 04, 2005 - 6:26 pm UTC

An XA transaction is a "global transaction" that may span multiple resources (oracle and non oracle).

It is a transaction control protocol that allows an external resource manager (RM) to manage a transaction over disparate data sources and ensure they either all commit or none to.

Just like our two phase commit across heterogenous databases.

my serializable behavior... as designed?

November 18, 2005 - 2:44 am UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

I've been playing around with the serializable isolation level, and reading about it quite a bit. I like the concept. But in my tests, I seem to be getting the ORA-08177 under circumstances that don't make much sense to me (which points to the fact that I might be the problem here...)

If I have 2 concurrent transactions in "serializable", should I be able to perform insert statements (different PKs) in the same table from both sessions without getting the 8177 error? I thought so, but have a look at my test case (Oracle 9.2.0.6.0 on Windows):

SESSION 1> alter session set isolation_level = serializable;

Session altered.

SESSION 1> create table test_table (
2 pk number(5) not null,
3 dummy varchar2(50)
4 ) initrans 10;

Table created.

SESSION 1> select to_char(dbms_metadata.get_ddl('TABLE','TEST_TABLE')) from dual;

TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE'))
----------------------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST_TABLE"
( "PK" NUMBER(5,0) NOT NULL ENABLE,
"DUMMY" VARCHAR2(50)
) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SANDBOX"



SESSION 1> alter table test_table
2 add constraint test_table_pk
3 primary key(pk)
4 using index initrans 10;

Table altered.

SESSION 1> select to_char(dbms_metadata.get_ddl('INDEX','TEST_TABLE_PK')) from dual;

TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','TEST_TABLE_PK'))
----------------------------------------------------------------------------------------------

CREATE UNIQUE INDEX "TEST"."TEST_TABLE_PK" ON "TEST"."TEST_TABLE" ("PK")
PCTFREE 10 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SANDBOX"



SESSION 1> select * from test_table;

no rows selected

SESSION 2> alter session set isolation_level = serializable;

Session altered.

SESSION 2> select * from test_table;

no rows selected

SESSION 1> insert into test_table (pk, dummy) values (1, 'insert row from first session');

1 row created.

SESSION 1> commit;

Commit complete.

SESSION 2> insert into test_table (pk, dummy) values (100, 'insert row from second session');
insert into test_table (pk, dummy) values (100, 'insert row from second session')
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

Is this as designed? Is this how "serializable" is supposed to work? or is this an Oracle-specific implementation side effect? windows implementation?

It seems to me that this type of "insert" scenario is quite typical and could not work very well in high concurrency situations.

Any help in clearing this up is very very appreciated.

Tom Kyte

Followup  

November 18, 2005 - 10:42 am UTC

please give us a better timeline here - if you committed and then go to session two, I cannot see this happening


give us a more explicit time line.

more precise timeline

November 18, 2005 - 11:28 am UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

Hi Tom, I appreciate you had a look...

I know... I wouldn't believe myself either if I were you, but I assure you that I executed all the statements one after the other in the exact order that I put them.

It basically boils down to this, and I can reproduce this every time at will:

10:40 am - Session 1 - starts transaction
10:41 am - Session 2 - starts transaction
10:42 am - Session 1 - inserts a new record in table A, and COMMITS.
10:43 am - Session 2 - also tries to insert a new record (with a different PK that would not violate the PK constraint), but fails with the "cannot serialize error")

So yes, my INSERT from session 2 happens AFTER the COMMIT from session 1. Do you believe me now :) ?

The fact that you expressed doubts that my test case is accurate, confirms that the behavior I'm getting is not right. But I don't seem to have been the only one with these kinds of problems. Markus from Austria, higher in this same thread, also seemed to experience the "cannot serialize error" in situations that you were not able to reproduce. And you'll notice that I purposefully set my INITRANS to something higher in my test case following your recommendations, in the hopes that it would make a difference. And you also mentioned that maybe he and you had different results because you were using ASSM. But I am using ASSM too. And though I can't swear on this one, I'm pretty sure I had experienced the same problem on a Solaris installation, not just a windows one. I can't really check again right now, I am away from work... Though I am very tempted to call someone up to try it for me, that's how much this little problem is bugging me.

Also, it's worth being noted that this weird behavior only happens when I have the unique index created on the table. Without the index, I don't run into the ORA-08177 error.

Somehow, I doubt that this is a bug. Especially since I am convinced that there have to be plenty of "real-life" systems out there that currently use serializable, and which cannot possibly be experiencing my behavior... that would be unacceptable. I just have a feeling that there is something in my setup/config that makes the world of a difference here, but I just cannot put my finger on it.

Any ideas? Thanks again.

Tom Kyte

Followup  

November 18, 2005 - 3:46 pm UTC

it is not that I was "amazed" or anything - 8177's happen frequently at the block level. The problem was - I wanted your simple concise time line.


I cannot reproduce.


anyone else?

more info: there is something about that index that's not right...

November 18, 2005 - 1:04 pm UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

I've been testing more scenarios, and it would seem that the 08177 error doesn't come up as often if the index on my table has been in use for a while (more on this below). At certain times, I even had what seemed like erratic behavior, where my second session was able to insert records fine, up until it would try to insert for an existing PK which of course blew up with the unique constraint violation, after which, any insert I would attempt would produce "cannot serialize" errors. But I have a hard time reproducing that one.

However, here is another simple test case that I am able to reproduce consistently which seems to indicate a problem at the index level, I think (I have it in chronological order):

-- Round #1 - I start from scratch... new table + new index
SESSION 1> alter session set isolation_level = serializable;

Session altered.

SESSION 1> create table test_table ( pk number(5) not null ) initrans 10;

Table created.

SESSION 1> alter table test_table
2 add constraint test_table_pk
3 primary key(pk)
4 using index initrans 10;

Table altered.

SESSION 1> select * from test_table;

no rows selected

SESSION 2> alter session set isolation_level = serializable;

Session altered.

SESSION 2> select * from test_table;

no rows selected

SESSION 1> insert into test_table values (1);

1 row created.

SESSION 1> commit;

Commit complete.

SESSION 2> insert into test_table values (2);
insert into test_table values (2)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

-- Round #2, I scrap the table's data, but not the index and start over
SESSION 2> truncate table test_table;

Table truncated.

SESSION 2> select * from test_table;

no rows selected

SESSION 1> select * from test_table;

no rows selected

SESSION 1> insert into test_table values (1);

1 row created.

SESSION 1> commit;

Commit complete.

SESSION 2> insert into test_table values (2);

1 row created.
-- Now it works ??????

SESSION 2> commit;

Commit complete.

-- Round #3, I scrap table and index, and try again...
SESSION 2> truncate table test_table;

Table truncated.

SESSION 2> alter table test_table drop constraint test_table_pk;

Table altered.

SESSION 2> alter table test_table
2 add constraint test_table_pk
3 primary key(pk)
4 using index initrans 10;

Table altered.

SESSION 2> select * from test_table;

no rows selected

SESSION 1> select * from test_table;

no rows selected

SESSION 1> insert into test_table values (1);

1 row created.

SESSION 1> commit;

Commit complete.

SESSION 2> insert into test_table values (2);
insert into test_table values (2)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

-- The error is back...because I dropped and re-created the index this time??

What do you make of this?

Thanks, I honestly don't know how you come up with the time to read everyone's posts...


Tom Kyte

Followup  

November 18, 2005 - 3:46 pm UTC

8177's frequently happen at the block level, it is not asssured to be at the individual row level.

This is exactly why serializable isolation is ...

November 18, 2005 - 3:11 pm UTC

Reviewer: Toon Koppelaars from Netherlands

This is exaclty why nobody uses serializable.
As soon as indexes are involved (and they always are...) you receive lots of (seemingly) injust 'cannot serialize' errors.

I logged a TAR for this quite a time ago. It has to do with index splits: them happening or not, in session1 or 2, ...
I will see if I can dig up the TAR, and post more info on this.


thanks Toon

November 18, 2005 - 3:36 pm UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

Actually, if you can pull up that info about the "index splits", I would really appreciate that. Anything that would allow me to understand it better really.

I don't care much if something doesn't work as I would like it to. But it is a bit tougher to deal with when it doesn't work as advertised or doesn't behave consistently. If it's a bug, then it's a bug. But if it's a feature, I would like to see it documented.

Thanks again

Tom Kyte

Followup  

November 18, 2005 - 3:59 pm UTC

It isn't a bug, it won't be documented, it changes over time as the feature "improves". Many things can cause it. You have to be aware that when in serializable - 8177's will happen. In other databases - they deadlock in this mode fequently, we 8177 instead.

This was the TAR at metalink...

November 18, 2005 - 4:00 pm UTC

Reviewer: Toon Koppelaars from Netherlands

My question:
- Two concurrent transactions update two different rows in the same table.
- Both transactions run in isolation_level serializable.
- If the updated column is indexed then I hit ora-8177
- If the updated column is not indexed then the two transactions serialize ok.

Testcase:
create table test(i number primary key);
insert into test values(1);
insert into test values(2);
commit;
Now create two sqlplus sessions and set isolation_level to serializable in
both.
session 1: select * from test;
session 2: select * from test;
session 1: update test set i=0 where i=1;
session 1: commit;
session 2: update test set i=3 where i=2;
==> ORA-8177

And here's the answer Oracle support gave:
Concurrent inserts into a table with index using serializable transactions could lead to an index split that the transaction tries to re-read and cause ORA-08177

This is Bug 1174981 closed as not a bug.

fix:
Insert smaller number of rows (e.g commit more often)
or
Catch the error code in the application and re-try the operation again
==================
Apart from the fact that I was updating, and not inserting, the answer did not help me. Apparently Oracle is aware of some problem (bug, if you ask me), but does not recognize it as such.

Serializable is about signalling (through 1877) when I try to change (TABLE!) data, that has been changed and committed by somebody else since the start of my TX. It does this by comparing timestamps (or rather system commit numbers, scn's) that are recorded against every row in a data block. If I try to change a record whose scn is more recent than the scn at start of my TX, then I get 1877.

I think what is happening here, is that Oracle is doing a current-read of the index block(s) to honor the where-clause in my update (it has to since I'm updating). It then signals that the block..(indexentry, leaf, branch, or whatever) has been changed (due to index split...) and committed by another transaction, and therefore raises 1877.
It sort of detects that the current state of the (part of the) index (that I'm about to change), does not equal the state of the index at start of my TX.

My feeling is that Oracle should not care about this (but I haven't thought this fully through yet). It should allow this when the INDEX is being scanned to honor the where-clause of an Update, or a Delete (cannot understand your insert scenario...), AS LONG AS the timestamp against the TABLE RECORD(s) that I am about to manipulate DO obey the serializable rules.

Tom Kyte

Followup  

November 18, 2005 - 4:04 pm UTC

Not all behaviour we don't agree with is a "bug", it is an implementation aspect.

could they avoid it? probably.
was the decision made not to for implemenation reasons? yes.

thanks

November 18, 2005 - 4:42 pm UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

Thank you both for having a look.

Toon, your explanation is what I was looking for. thanks.


Thanks Toon, Samuel, Markus and Tom

May 26, 2006 - 9:58 am UTC

Reviewer: malcolm from London

I am hitting a similar problem, getting ORA-08177 when my table has a unique index.

I realise this is an old thread, but was wondering which versions of Oracle were used in the test cases above, and whether anything had been done to improve the situation in later versions.

I'm on 9.2.0.5.0, but we will upgrading to 10gR2 in several months.

Tom Kyte

Followup  

May 27, 2006 - 9:19 pm UTC

You will always stand a chance of "false 8177's" with serializable, it is inherit in the implementation.

rowdepedencies setting seems to make serializable more predictible

September 17, 2006 - 12:32 am UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

Hi Tom,

About a year ago, I submitted a test case in this thread that showed how a session in serializable mode would consistently get 8177 errors upon inserting a new row in a table with a unique index. The conclusion was that either because of index splits or some other implementation reason, changes at the block level could cause seemingly unreasonable 8177 errors, and that there wasn't much we could do about it...

I recently read on an other thread about the use of the ROWDEPENDENCIES option on a CREATE TABLE statement. If I understood it correctly, that option enables more refined row-level tracking of changes by keeping track of an SCN at the row level, rather than at the block level, at the expense of a few extra bytes per row.

So that got me thinking about my SERIALIZABLE test case (a year later...). What if activating this rowdependencies option could help in avoiding 8177 errors caused by block level changes, since that option would cause changes to be truly tracked by row? Sure enough, when I retried my test case using a table created with rowdependencies, I stopped hitting the "unjustified" 8177 errors!

1. Do you think what I tried makes sense? Or did I just benefit from a bit of luck and some funny side effect of rowdependencies that made the SERIALIZABLE mode appear more "predictible"? In other words, I tried this in 9i... do you think that the solution would be consistent across Oracle releases, or did I get lucky this time because of 9i implementation specifics?

2. And if ROWDEPENDENCIES does truly help in this mode, do you see any down sides apart from slightly larger rows in the tables involved?

If this truly helps, like it seems to, I think it would be nice to have this advertised in the docs somewhere... If I was running in an environment where SERIALIZABLE was the way to go, I would want to do my very best to have as predictible behavior as possible, no?

Tom Kyte

Followup  

September 17, 2006 - 8:20 am UTC

I am not aware of any tie in between the two - what was your test case?

test case - serializable with and without rowdependencies

September 17, 2006 - 11:01 pm UTC

Reviewer: Samuel Stanojevic from Montreal, Canada

Well here is my test case. The statements appear top to bottom in the exact order that I execute them. I indented the 2nd concurrent session for clarity. I start by showing how I get the 8177 error, and then how I don't get it when I go through it a second time with the rowdependencies option. I ran this with Oracle 9.2.0.6.0 for both Windows and Linux with identical results. However, just as a reminder, last time I had ran the test case by you, you were not able to reproduce the 8177 error in the first place. Maybe we'll get lucky this time...

session-01> alter session set isolation_level = serializable;

Session altered.

session-01> drop table test_table;
drop table test_table
*
ERROR at line 1:
ORA-00942: table or view does not exist


session-01> create table test_table ( pk number(5) not null ) initrans 10;

Table created.

session-01> alter table test_table add constraint test_table_pk primary key(pk) using index initrans 10;

Table altered.

session-01> select * from test_table;

no rows selected

session-02> alter session set isolation_level = serializable;

Session altered.

session-02> select * from test_table;

no rows selected

session-01> insert into test_table values (1);

1 row created.

session-01> commit;

Commit complete.

session-02> insert into test_table values (2);
insert into test_table values (2)
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction



-- OK, so I got the error... Now, I try it again, the only difference this time being the "rowdependencies" in my "CREATE TABLE" stmt...

session-01> drop table test_table;

Table dropped.

session-01> create table test_table ( pk number(5) not null ) initrans 10 ROWDEPENDENCIES;

Table created.

session-01> alter table test_table add constraint test_table_pk primary key(pk) using index initrans 10;

Table altered.

session-01> select * from test_table;

no rows selected

session-02> select * from test_table;

no rows selected

session-01> insert into test_table values (1);

1 row created.

session-01> commit;

Commit complete.


session-02> insert into test_table values (2);

1 row created.

session-02> commit;

Commit complete.


... and there you have it. With "rowdependencies" being the only difference, I have eliminated the 8177 error. What do you think? Is it possible that it's an SCN at the row level vs block level thing?

Thanks!

ORA-08177 with nothing but a single (end user) session

August 24, 2012 - 5:21 am UTC

Reviewer: Max

Dear Tom,

can you imagine (or even "show") any scenario suffering from ORA-08177 when there 's nothing but *ONE* session (apart from "internal" sessions owned by Oracle)?
Tom Kyte

Followup  

August 29, 2012 - 11:48 am UTC

well, look up one review. there is an example there.

You may need to set initrans higher for these segments.

anything wrong with my code?

September 04, 2015 - 3:45 pm UTC

Reviewer: Hao from Stamford, CT USA

Hi,

I have following java code to set up a oracle connection:

private static Connection createConnection() throws SQLException, PublishException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String url = PublishingProperties.getDBConnString();
Connection conn = DriverManager.getConnection(url,PublishingProperties.getDBUser(),PublishingProperties.getDBPassword());
Statement stmt = conn.createStatement();
stmt.exec("alter session set currenct_schema=abc");
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
conn.setReadOnly(true);
return conn;
}

I always get "ora-08177" exception. I have read your articles and wanted to change isolation level to "Read only", which is only available in oracle. Is this the right direction to solve 08177 issue? If it is, should I get rid of following code:

conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setReadonly(true);

from above and put following code instead?

stmt.exec("ALTER SESSION SET ISOLATION_LEVEL READONLY");

Thank you in advance!
Chris Saxon

Followup  

September 21, 2015 - 3:43 am UTC

That error is seen when you try to get a record in current mode (ie, lock it or change it) and it has changed since the start of the transaction.

Normally, you would just wait until that other transaction completed, but in SERIALIZABLE mode it just falls over.

So you either need have all transactions complete very fast so there is less chance of it happening, and/or get all of your locks up front.