Limit on row inserts when using TRANSACTION_SERIALIZABLE?

From: Thomas Hyldgaard <hyldgaard_at_hotmail.com>
Date: 27 Jul 2001 03:53:32 -0700
Message-ID: <62b0a667.0107270253.b273063@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 Received on Fri Jul 27 2001 - 05:53:32 CDT