Re Returning result of a simple "select * " from a stored procedure without using cursor

Home -> Community -> Usenet -> c.d.o.server -> Re Returning result of a simple "select * " from a stored procedure without using cursor

From: VC <boston103_at_hotmail.com>
Date: 21 Oct 2003 05:16:02 -0700
Message-ID: <31e0625e.0310210416.35acf036@posting.google.com>

Hello Joe,

[VC]
> > > Please elaborate why you are calling a rather simple tool (select for
> > > update) a 'work-around'. The statement is used for what is known as
> > > pessimistic locking. One can resort to a technique called optimistic
> > > locking, in WEB environment, in order to avoid using 'select for
> > > update' if need be.
> >

[JW]
> > I call it a workaround because it requires non-standard SQL, which our our
> > product handles in code generation for EJBs etc. If I were boss, I'd ask
> > that oracle implemented SERIALIZABLE isolation by locking read data.

Please elaborate on why it's necessary to "lock read data" in the SERIALIZABLE mode.
Obviously, the standard is not adequate to solve various practical transaction issues. Otherwise, why would a locking scheduler have a plethora of non-standard, so-called locking hints (HOLDLOCK, NOLOCK, PAGLOCK, READPAST, ROWLOCK, ... etc.) ? One cannot seriously hope to design a real OLTP system without using some of those. Would not you agree that Oracle's 'non-standard' select for update is a more elegant way to modify transactional behaviour in comparison to the above ?
As I've already mentioned, in a stateless environment (Web applications), a correct approach would be using optimistic locking thereby make the 'select for update non-standardness' a moot issue.

[VC]
> > > Oracle currently does not implement the SERIALIZABLE isolation level
> > > in the strict sense of the word. What is known as the Oracle
> > > serializable isolation level, is commonly considered to be the
> > > SNAPSHOT IL.
> >

[JW]
> > I agree, but many oracle customers seem to be surprised when I describe
> > what actually happens, and the odd bit of dissembling that oracle employs
> > in accomplishing a repeatable read.

Please clarify what you mean by the above. Oracle's SERIALIZABLE does ensure repeatable reads without resorting to 'select for update'.

[VC]
> > > This was true under some older Oracle versions. However, the bug was
> > > fixed in May 2002 (patchset 8.1.7.4) and it's hardly fair to discuss
> > > the database behaviour in the context of a fixed software bug. If
> > > you're saying that the bug is still there, please post (or send me
> > > via e-mail) a reproducible test case. I'd very much interested to
> > > learn from your experience.
> >
> > Ok! It was in all oracle versions we'd dealt with from '96 till it was fixed,
> > and it was known for years. I believe the recent "fix" is simply to throw an
> > exception, failing the transaction in that case. The failure message is not so
> > explicit as to say "we can't complete this tx because your insert caused an
> > index block to split, and we won't be able to find the row(s) you inserted".
> > We get the generic 8177 error. I will attach a SQL-PLUS script and jdbc
> > program that you can use to duplicate the problem, and please let me know
> > what it does for you. Doing it in Java using oracle's thin driver did give
> > an 8177 on the 110th transaction. This is the "cannot serialize this transaction"
> > message, which I have to explain to customers when they ask why the DBMS
> > cannot serialize a tiny transaction that is just like the other hundreds they've done
> > successfully, especially when there's only one user logged in to the DBMS.
> > I'm not sure what fairness we're talking about. My discussing it demonstrated
> > a history of experience with Oracle, long, and in this case painful, but truth,
> > though anecdotal, can't be unfair.

I ran both the original version verbatim and the more compact electrons-saving one, and could not reproduce under Oracle 9.2:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 21 06:41:35 2003

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

SQL> create table isolationtest (id char(250) primary key, val float);

Table created.

SQL> begin
2 for i in 1..200 loop

  3      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  4      insert into isolationtest (id,val) values (i,100);
  5      update isolationtest set val = 200 where id = i;
  6      COMMIT;

  7 end loop;
  8 end;
  9
10 /

PL/SQL procedure successfully completed.

SQL> select count(*) from isolationtest;

COUNT(*)

200

SQL> quit
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

[VC]
> >
> > > Whilst I am unhappy myself about the Oracle level of 'bugginess' and
> > > the amount of time it takes to fix bugs, I was bitten more often by
> > > the locking scheduler behaviour in the READ COMMITTED mode. It comes
> > > to one as quite a surprise when one discovers that, under the locking
> > > scheduler, aggregate statements can return inconsistent data in the
> > > RC mode, especially if one is accustomed to always seeing statement
> > > level consistent data
> >

[JW]
> > I understand. There are many innocent expectations dashed when tested
> > against something other than one's 'mother DBMS'. After enough pain
> > and experience going one way and the other, one tempers one's initially
> > religious ferver in the defense of one's first DBMS, and develops a
> > careful palette of mixed trust and dismay for such products.

Let us skip the 'surprise' part and allow me to re-phrase my original statement. In the READ COMMITTED mode, the locking scheduler aggregate functions (sum(), avg(), etc.) may return incorrect data, e.g. 'select sum(value) from t1' may produce an incorrect result. As far as I know, there is no satisfactory solution to this problem. An obvious one causes severe concurrency degradation and I'd be very much interested to know whether alternative approaches exist.

Rgds.

> > Joe
> >
> >
Received on Tue Oct 21 2003 - 07:16:02 CDT