| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: "read only" lock in Oracle?
Go ahead and buy Thomas's book,
http://www.amazon.com/exec/obidos/ASIN/1861004826/
..where this is discussed at length. Other than that, I'm not sure where
you'd find a good discussion
of multi-versioning..
--steve
"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
news:a6bic10vi0_at_drn.newsguy.com...
> 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.
>
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
 |
 |