DML locks in Oracle

From: Igor Tatarinov <tatarino_at_prairie.nodak.edu>
Date: 1997/03/06
Message-ID: <Pine.OSF.3.91.970306153906.12267A-100000@prairie.nodak.edu>#1/1

Hi!

Can anybody tell me why Oracle needs so many different types of DML locks?

Here is what I have found so far:

if SERIALIZABLE=FALSE (default) only RX and RS are used. RX is used by INSERT/UPDATE/DELETE and RS is used by SELECT FOR UPDATE.

Why does Oracle need RS if it behaves exactly like RX: neither RX nor RS can be set on the table if the row being used has already been locked.

2. if SERIALIZABLE=TRUE (non-default) Oracle uses 3 types of locks: S, RX, and SRX.

RX is used by INSERT, SRX - by UPDATE/DELETE and S by SELECT/SELECT FOR UPDATE. Here everything seems to be clear: having no mechanism to enforce read locks on rows Oracle locks the whole table in share mode (no updates allowed) whenever the table is being read. This obviously means only a paranoid or a CS student like me would run Oracle in this mode.

Is my understanding correct?
If yes, what RS is for? RX could well be used instead.

Thanks,
Igor Received on Thu Mar 06 1997 - 00:00:00 CST