-
Manual Locking and User-Defined Locks- Locking and Latching
So far, we have looked mostly at locks that Oracle places for us transparently. When we update a table, Oracle places a TM lock on…
-
Without Bind Variables- Locking and Latching
In the first instance, our PL/SQL code will not use bind variables, but rather will use string concatenation to insert data: To automate this, the…
-
TX (Transaction) Locks- Locking and Latching-4
This output shows that we were able to lock 37 rows but ran out of transaction slots for the 38th row. For this given block,…
-
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…
-
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…
-
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…
-
Optimistic Locking Using a Version Column – Locking and Latching
This is a simple implementation that involves adding a single column to each database table you wish to protect from lost updates. This column is…
-
What Are Locks?- Locking and Latching-2
Exactly the same issue affected concurrent updates (since an UPDATE was really a DELETE followed by an INSERT in SQL Server). Perhaps this is why…
-
Optimistic Locking Using a Checksum – Locking and Latching
This is very similar to the previous version column method, but it uses the base data itself to compute a “virtual” version column. I’ll quote…
-
What Are Locks?- Locking and Latching-1
Locks are mechanisms used to regulate concurrent access to a shared resource. Note how I used the term “shared resource” and not “database row.” It…