-
DDL Locks- Locking and Latching-2
A view that is invaluable for looking at this information is DBA_DDL_LOCKS. There is no V$ view. The DBA_DDL_LOCKS view is built on the more…
-
Latch “Spinning”- Locking and Latching
One thing I’d like to drive home with regard to latches is this: latches are a type of lock, locks are serialization devices, and serialization…
-
Blocked Merges, Updates, and Deletes- Locking and Latching-1
In an interactive application—one where you query some data out of the database, allow an end user to manipulate it, and then put it back…
-
Optimistic or Pessimistic Locking? – Locking and Latching
So which method is best? In my experience, pessimistic locking works very well in Oracle (but perhaps not so well in other databases) and has…
-
Blocked Inserts- Locking and Latching
There are a few times when an INSERT will block. The most common scenario is when you have a table with a primary key or…
-
TX (Transaction) Locks- Locking and Latching-2
Here is a small example showing how this happens, using three V$ tables: •\ V$TRANSACTION, which contains an entry for every active transaction.•\ V$SESSION, which…
-
Lock Escalation- Locking and Latching
When lock escalation occurs, the system is decreasing the granularity of your locks. An example would be the database system turning your 100 row-level locks…
-
Blocked Merges, Updates, and Deletes- Locking and Latching-2
These full table locks are a short-term occurrence in Oracle 9i and above, meaning they need to be taken for the duration of the DML…
-
Blocked Merges, Updates, and Deletes- Locking and Latching-3
This script works on foreign key constraints that have up to eight columns in them (if you have more than that, you probably want to…
-
TX (Transaction) Locks- Locking and Latching-1
A TX lock is acquired when a transaction initiates its first change. The transaction is automatically initiated at this point (you don’t explicitly start a…