Re: "read only" lock in Oracle?

Tom can't say this here but I can ... by Tom's book "expert one-on-one Oracle." In the book there is an excellent presentation on multiversioning, locking, and transactions that are absolutely essential material for anyone wishing to port an RDBMS application to Oracle.

Dan Morgan

Thomas Kyte wrote:

> In article <1c6a0599.0203081505.45d0b1d4_at_posting.google.com>, junkmbox_at_yahoo.com
> says...
> >
> >Hello to the Oracle database gurus out there!
> >
> >We are porting our app. from Informix to Oracle. Our problem is the
> >differences in locking behavior between the two databases. In
> >particular, the following example demonstrates how Informix places a
> >"read-only" type of lock.
> >
> >Suppose, we create a table with two rows (and we are in row-locking
> >mode, as opposed to page-locking):
> >
> >CREATE TABLE A (I INT);
> >INSERT INTO A VALUES (22);
> >INSERT INTO A VALUES (33);
> >COMMIT;
> >
> >** The example:
> >
> >***Session 1:
> >SELECT * FROM A WHERE I = 22;
> >//success, places read-lock
> >
> >***Session 2:
> >SELECT * FROM A WHERE I = 22;
> >//success, places read-lock
> >
> >UPDATE A SET I = 25 WHERE I = 22;
> >//fails or waits due to lock owned by session1
> >
> >DELETE FROM A WHERE I = 22;
> >//fails or waits due to lock owned by session1
> >
> >UPDATE A SET I = 35 WHERE I = 33;
> >// success, because this row had no locks
> >
> >***Session 1:
> >UPDATE A SET I = 25 WHERE I = 22;
> >//fails or waits due to lock owned by session2
> >
> >DELETE FROM A WHERE I = 22;
> >//fails or waits due to lock owned by session2
> >
> >This is Informix' default behavior. The questions is, what would be
> >the SQL statements for the given example that reproduce the same
> >locking behavior in Oracle.
> >
>
> There is no such locking behavior in Oracle -- this is what truly sets databases
> apart, locking and concurrency.
>
> In Oracle -- the above situation is not possible, we have no such thing as a
> "read lock" used by other databases to provide consistent reads -- we use
> multi-versioning.
>
> You will need to alter the way your application processes -- in Oracle reads do
> not block writes and (even better) writes do not block reads. Its a whole
> different ball game.
>
> >Any help would be highly appreciated.
> >Serge
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Mon Mar 11 2002 - 11:16:26 CST