Re: "read only" lock in Oracle?

Gentlemen,

Thanks for your comments. I will definitely check out Thomas' book.

Below I'm providing an exceprt from Oracle documentation - actually, taken from another long thread in this newsgroup: http://groups.google.com/groups?hl=en&threadm=7ivn28%24t4p%241%40nnrp1.deja.com&rnum=4&prev=/groups%3Fq%3Dread%2Block%2Bgroup:comp.databases.oracle.server%26hl%3Den%26selm%3D7ivn28%2524t4p%25241%2540nnrp1.deja.com%26rnum%3D4

Unfortunately, that thread does not provide any answers on *exactly* how to solve the problem.

This is the very problem we are facing (the parent-child referential integrity or other types of table relationships/references). But we have not had such a problem with Informix. In this parent-child example provided in the excerpt, it would work perfectly fine in Informix, because Transaction A would place a shared lock on the parent row, while reading it (i.e. by doing a SELECT on that row), so Transaction B will fail to delete the parent row. However, to make this example work properly in Oracle, Transaction A has to do a SELECT..FOR UPDATE, thereby placing an exclusive lock on the parent row. Alright, for the purposes of the example it would be sufficient, because Transaction B will fail to delete the parent row. But what if at the same time there is another Transaction C running, that tries to do exactly the same task as Transaction A - i.e. read parent, then insert another child. In Oracle, Transaction C will fail or will have to wait for Transaction A to complete, although it does not try to do anything conflicting and could proceed in parallel to Transaction A, if it weren't for the exclusive lock. In Informix (and I believe, some other databases) a mere shared row lock on the parent row will prevent deletion of that row, but will not prevent Transactions A and C to work in parallel. In our system we have multiple "Transaction A" types of operations running all at the same time. If they all have to exclusively lock the same parent row, this will bring the system down, pretty much.

And I have no idea how to solve that, and still need your advice.

Thank you all,
Serge

the Oracle doc excerpt follows:

> --- begin excerpt ---
> Consider two different transactions that perform application-level
> checks to maintain the referential integrity parent/child relationship
> between two tables. One transaction reads the parent table to determine
> that a row with a specific primary key value exists before inserting
> corresponding child rows. The other transaction checks to see that no
> corresponding detail rows exist before proceeding to delete a parent
> row. In this case, both transactions assume (but do not ensure) that
> data they read will not change before the transaction completes.
>
> Time T1: Transaction A: Read parent (it exists)
> Time T2: Transaction B: Read child rows (not found)
> Time T3: Transaction A: Insert child row(s)
> Time T4: Transaction B: Delete parent
> Time T5: Transaction A: Commit work
> Time T6: Transaction B: Commit work
>
> Note that the read issued by transaction A does not prevent transaction
> B from deleting the parent row. Likewise, transaction B's query for
> child rows does not prevent the insertion of child rows by transaction
> A. Therefore the above scenario leaves in the database a child row with
> no corresponding parent row. This result would occur even if both A and
> B are SERIALIZABLE transactions, because neither transaction prevents
> the other from making changes in the data it reads to check
> consistency.
>
> As this example illustrates, for some transactions, application
> developers must specifically ensure that the data read by one
> transaction is not concurrently written by another. This requires a
> greater degree of transaction isolation than defined by SQL92
> SERIALIZABLE mode.
>
> --- end excerpt ---

ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message news:<a8aed4.0203082226.5a606b7e_at_posting.google.com>...
> y <y_at_y.y> wrote in message news:<3C897FCA.5A8BA0F1_at_y.y>...
> > 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.
> >
> > Ok, I saw the multi-versioning. I just want to know if session 1 inserts some rows,
> > for example like thomas'senario, before this transaction end (commit or rollback),
> > if session 2 wants to select row 22 or 33, it have to issue the request again and
> > again?
>
> Just Repeating what Tom Told....
>
> * Writers Dont Block Readers and Readers Dont Block Writers *
>
> When Stmt 1 Is Issuing a Insert and Session B Selects from the table
> it will still be able to see the All the Records that have been
> Commited excluding session A's new insert.
>
> HTH
>
> Regards,
> Ganesh R
>
> > Regards,
> >
> >
> > >
> > >
> > > >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 Sat Mar 09 2002 - 18:58:30 CST