“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.

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.

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