Oracle diary May5 2011

Oracle will never escalate a lock. Never. But it does practice lock conversion, or lock promotion,

Note The terms ʹlock conversionʹ and ʹlock promotionʹ are synonymous.

 

Types of lock:

 

DML locks - locks on a specific row of data, or a lock at the table level, which locks every row in the table.

                  TX locks : row level (Oracle does not store a master list of every row locked anywhere. To find out if a

                                                  row is locked, we must go to that row)

                  TM locks : table level

note : DML _LOCKS init.ora parameter specifies the maximum number of DML locks

 

DDL locks - protect the definition of the structure of objects.

                  Exclusive DDL locks : prevent other sessions from gaining a DDL lock or DML lock themselves

                  Share DDL locks : protect the structure of object, allows modifications to the data, like create view

                  Breakable Parse locks : dba_ddl_locks view is useful to you, as a developer, when it is found that some

                            piece of code wonʹt compile in the test or development system ‐ it hangs and eventually times out.

 

note : DDL will always commit, even if it is unsuccessful. (Except that there is a syntactic and semantic error)

note : create index t_idx on t(x) ONLINE; only TM lock, allow DML to occur, Oracle accomplishes this feat by keeping a record of modifications made to the table during the DDL statement,and applying these changes to the new index.

 

Internal locks and latches - For example, when Oracle parses a query and generates an optimized query plan, it will ʹlatchʹ the library cache in order to put that plan in there for other sessions to use. A latch is a lightweight low‐level serialization device employed by Oracle ‐ similar in function to a lock.

 

Distributed locks - used for OPS(Oracle Parallel Server),ensure that resources on the various
nodes remain consistent with respect to each other, held by a database instance, not by individual transactions.

 

PCM (Parallel Cache Management) Locks - used for OPS,protect one or more cached data blocks in the buffer cache across multiple instances.

 

Note: there are some other types of lock which are generally held for very short periods of time.

 

If Oracle had a traditional lock manager, the act of locking a row would resemble:

1. Find the address of the row you want to lock.
2. Get in line at the lock manager (must be serialized, it is a common in‐memory
structure.)
3. Lock the list.
4. Search through the list to see if anyone else has locked this row.
5. Create a new entry in the list to establish the fact that you have locked the row.
6. Unlock the list.

Now that you have the row locked, you can modify it. Later, as you commit your changes you must:
7. Get in line again.
8. Lock the list of locks.
9. Search through it and release all of your locks.
10. Unlock the list.

 

Oracle does not do it that way. Oracle does it more like this:
1. Find the address of the row you want to lock.
2. Go to the row.
3. Lock it (waiting for it if it is already locked, unless we are using the NOWAIT option).

 

Note:In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated.Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
The database ignores MAXTRANS when specified by users only for new objects created when the COMPATIBLE initialization parameter is set to 10.0 or greater.