Re: Limit on row inserts when using TRANSACTION_SERIALIZABLE?

Can I clarify this:

you set isolation_level = serializable
you insert several thousand rows into table 1 without a commit you insert into table 2 'select rows from table 1' without a commit. you commit.

You find that rows that SHOULD have been inserted into table2 have not been inserted.

Are these rows that you inserted into table 1 in the previous step, or are they rows that pre-existed in table 1 before you did the 'set isolation_level = serializable' ?

When you insert rows into table 1 is it an array insert, or a series of single row inserts, or an 'insert select ...' ?

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Thomas Hyldgaard wrote in message
<62b0a667.0107270253.b273063_at_posting.google.com>...


>I have a encountered a problem when using TRANSACTION_SERIALIZABLE

>from JDBC. The problem appears on both 8.1.6.0 and 9i databases.

>

>Details of the problem:

>An data import like tool inserts rows into different tables where

>(which have foreign key constraint between them). I can't post the

>actual code here, but in principle the following happens within a

>single transaction:

>1) All tables are created

>2) Data is inserted into table1 (INSERT INTO ....)

>3) Data is inserted into table2 which uses subselects from table1

>INSERT INTO table2(...) VALUES(SELECT c FROM TABLE1 WHERE ...))

>4) commit

>

>This goes on very well as long as the number of inserted rows are low

>(less than 1000). However if this is extended to several thousands

>records then the subselect in step 2 fails (returns NULL).

>

>If TRANSACTION_READ_COMMITTED is used instead then the problem

>disappears - thus when TRANSACTION_SERIALIZABLE is used then some

>records previously inserted can't be read within the same transaction.

>

>The test setup is single threaded with just a single connection to the

>database.

>

>There are not generated any SQLExceptions nor any SQLWarnings which

>can explain the problem.

>

>So to me this looks like a bug in Oracle (or at least the JDBC driver

>fails to throw a SQLException when some internal limit on the number

>of row inserts is reached for TRANSACTION_SERIALIZABLE).

>

>As I can read from other appends then Oracle does not handle

>TRANSACTION_SERIALIZABLE in a "standard" way but this seems to be

>related to "select for update".

>

>Is there other non-standard behaviour one should be aware of when

>using TRANSACTION_SERIALIZABLE with Oracle?

>

>Kind regards

>Thomas