Oracle Tips and Tricks — David Fitzjarrell

October 23, 2006

Serial, or Cereal?

Filed under: General — dfitzjarrell @ 15:47

An interesting issue regarding serialisable transaction behaviour has reared it’s head in the comp.databases.oracle.server newsgroup; the original situation is posted below:

Session 1 (isolation level read committed)

Insert into dd values(1);

Commit;

Insert into dd values(2);

Commit;

Session 2 (isolation level serializable)
Set transaction isolation level serializable
Select * from dd; (2 rows returned at this point)

Now issue another insert from session 1

Insert into dd values(3);
Commit;

Now run the select again from session2:

Select * from dd;
(still return 2 rows)

Now truncate the table from session1:

Truncate table dd;
(issue implicit commit)

Now again run the following select from session 2:

Select * from dd; (This should still show 2
rows as read data from rollback segments since in serializable mode, it need to show the pre-image before the truncate but it is showing 0 rows)

This, by the way, is in 10.2.0. This behaviour also occurs in 9.2.0.x for table truncates, but not for table drops:

— SESSION 1
— 1) create table test_table as select object_id myid, object_name myname from all_objects where rownum < 100;
— 2) select count(*) from test_table; — … 99
— 3) commit;
— 6 A) drop table test_table;
— 6 B) truncate table test_table;
— *********************************
— SESSION 2
— 4) commit
ALTER SESSION set isolation_level=serializable
–5) select count(*) from test_table — … result = 99
— 7) select count(*) from test_table
— A) … ORA-00942: table does not exist
— B) … result = 0 !!

This should be considered a bug in Oracle, and should be addressed. Truncate should result in the same behaviour as a drop table (both commands being DDL) and cause the transaction to fail with an Oracle error (it should return an ORA-08177, in my opinion) as it shouldn’t silently ‘fail’ by returning 0 rows.

If you feel so inclined recreate the example on your system and report it as a bug to Oracle. Hopefully the more requests this gets for attention the faster it will get corrected.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: