Oracle Concepts and Architecture – Part 5 – Extract(Read) > Transform(Test&Apply)

Part 1 subjects –

1- Flashback Versions Query, Drop Table and Recyclebin Management
2- How shared pool works in Oracle and binding example
3- The Storage Hierarchy Summary in an Oracle Database
4- Concurrency and Isolation Levels Concepts
5- Automated Checkpoint Tuning and Mean time to recover(MTTR) Concepts
6- Concurrency and Consistency Concepts
7- Latch and Lock Concepts

Part 2 subject –

8- Oracle Database Architecture: Overview

Part 3 subjects –

9- How Atomicity is implemented on Oracle
10- NULL values and Oracle

Part 4 subject –

11- Overview of Transaction Management Internals

12- Concurrency versus Locking Concepts, Understanding Lock Contention with Examples

“Performance is interested with avoiding unnecessary work on limited system resources, where as Scalability is interested with avoiding blocking others doing same operations(serialization). These need different attentions, especially second is very important for heavy loaded OLTP systems. Sometimes you may sacrifice in one to gain in other.” Memory Management and Latching Improvements in Oracle9i and 10g Presentation by Tanel Põder

Concurrency and Locking are like Fenerbahce(imitates concurrency here of course :) and Galatasaray(imitates locking :), they have an immortal competition. It is very critical to understand Oracle’s answers for building highly-concurrent OLTP applications, you paid a lot for it right :) I mentioned the importance of testing for concurrency before, here with this post I want to focus on some conditions which reduce concurrency.

In Oracle you can develop highly concurrent database applications because;
1- Readers do not wait for readers,
2- Writers do not wait for readers,
3- Writers only waits for writers if they attempt to change the same rows at the same time,
4- Oracle automatically locks data at the lowest level of restrictiveness.

Below example demonstrates how updates and inserts on same row creates blocking locks – Code Listing 147a – Locks on same row update and insert example

There are some situations to be aware of –

A. Below example demonstrates that an update on row 101 waits for an update on row 100 of hr.employees table – Code Listing 147b – Lock acquisition and blocking lock example

Because lock acquisition works under a FIFO policy the SX lock can not be granted until the S lock has been released in the example.

B. Below example demonstrates that locks are converted under certain circumstances – Code Listing 147c – Lock conversion and blocking lock example

First session in the example has an SX lock both parent and child tables, but for the third session this time it has a SS lock on the parent table. After the update statement on the first session SX lock on the child table is not allowed to convert it and it hangs. The third session did not release the SX lock in order to get an SSX one, a conversion takes place.

C. Below example demonstrates how bitmap index causes blocking locks on different rows – Code Listing 147d – Bitmap index blocking locks on different rows example

Bitmap indexes are best for OLAP. But for OLTP, because of this locking behavior this option is an important subject to concurrent testing. Also check this related reference Bitmap Indexes by Jonathan Lewis

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Refences Used :
Oracle® Database Concepts 10g Release 2 (10.2)
Chapter 4 Transaction Management
Chapter 13 Data Concurrency and Consistency