Oracle and DB2, Comparison and Compatibility/Process Model/Locking/Oracle

Oracle

At the highest level, Oracle operates this way – readers do not block readers. If you are reading data, you don’t have to wait for other people who are reading the same data. Writers don’t block readers – if you are reading data, you don’t have to wait for users who are writing to the same block. Writers only block other writers who are writing to the same block.

The isolation level called SERIALIZABLE in Oracle is in fact snapshot isolation. Snapshot isolation works by taking a ‘snapshot’ of the data – a so-called read-consistent data image. All reads see this snapshot and the transaction will only commit successfully if no updates it has made conflict with any concurrent updates made since that snapshot.

Since Oracle do not guarantee that some serial ordering of transactions will result in the same outcome, this is a direct contradiction of the ANSI/ISO standard. The reason it is used is that it performs better than serializability, and avoids concurrency anomalies. Snapshot isolation is closely connected to multi version concurrency control (MVCC).

Oracle’s locking mechanisms are tightly coupled to its transaction control. The idea is that if you design your transactions properly, you don’t have to worry about locking since the DBMS will automatically obtain locks for you. Having said this Oracle does allow users to get locks manually if they so wish. Oracle locks work for user objects (tables and rows) and system objects not visible to users (shared data structures in memory and data dictionary rows.) Oracle attempts to get locks at the lowest level possible to ensure high levels of data concurrency.

Oracle has two levels of locking, exclusive and shared. Exclusive locking is used to modify data. The first transaction to get an exclusive lock is the only transaction that can alter that object until the lock is released. Shared locking allows the locked object to be shared (depending on the operations involved.) Multiple users reading data can share the data by holding share locks that would prevent a writer getting access to the same data, since it would need an exclusive lock.

All locks acquired within a transaction are held for the duration of the transaction, preventing such things as dirty reads, lost updates, and destructive DDL operations (i.e. a transaction dropping a table while another transaction is trying to insert into it.) The changes made by one transaction become visible only to other transactions that start after the first transaction is committed (read-consistent data image.)

Oracle releases locks on either commit or a transaction rollback (undo.) Locks are also released when rolling back to a prior save point. When rolling back to a save point, only transactions not waiting for the previously locked resources can get locks on the newly available resources. Transactions waiting at the start of a transaction that rolls back to a save point will continue to wait until the original transaction commits or rolls back completely.

Exclusive row locks are obtained for all rows inserted, updated, or deleted within a transaction. Row locks are acquired at the highest level of restriction so it not necessary to convert row locks once they are acquired. Oracle may convert Table locks however. A SELECT statement with the FOR UPDATE clause will acquire exclusive row locks and a row share table lock for the table. If the transaction later updates one or more of the locked rows, the row share table lock is automatically converted to a row exclusive table lock. This is an example of lock conversion.

Oracle never escalates locks. Lock escalation occurs when a database needs to raise locks held at one level of granularity (i.e. rows) to a higher level (i.e. table). An example of this would be if a user has several row locks in a table and the database raises the users row locks to a table lock. This reduces the number of locks, but the restrictiveness of what is being locked, and hence the likelihood of deadlock is increased.