Understanding Row Lock Contention in Oracle During Insert

Row lock contention can significantly impact the performance of insert operations in Oracle databases, particularly when dealing with tables that have bitmap indexes.

In this blog post, we will explore the concept of row lock contention using a simplified example with the creation of a sales table and a bitmap index. We will then demonstrate how row lock contention can occur during insert operations performed by two different sessions.

CREATE TABLE sales 
? sale_id NUMBER,
? product_id NUMBER,
? customer_id NUMBER,
? sale_date DATE
);        

Create Bitmap Index on product_id:

CREATE BITMAP INDEX sales_product_idx ON sales(product_id);        

Connect to 1 session:

SQL> col CURRENT_SESSION_ID for a2
SQL> SELECT SYS_CONTEXT('USERENV', 'SID') AS current_session_id 
     FROM DUAL;

CURRENT_SESSION_ID
--------------------
2840

SQL> INSERT INTO sales (sale_id, product_id, customer_id, sale_date) 
     VALUES (1, 100, 500, SYSDATE);
  
 1 row created.        

Connect to second session without committing the first 1:

the insert statement in second session did not complete, for end use looks like waiting ...

SQL> col CURRENT_SESSION_ID for a2
SQL> SELECT SYS_CONTEXT('USERENV', 'SID') AS current_session_id FROM DUAL;

CURRENT_SESSION_ID
--------------------
6207

SQL> INSERT INTO sales (sale_id, product_id, customer_id, sale_date)
     VALUES (2, 100, 600, SYSDATE);
        

checking the waiting event, shows that second session is blocked by the first one, due to bitmap index maintaining.

dropping the bitmap index, or recreating it as normal index solved the issue.

In conclusion, bitmap indexes might not be the ideal option for OLTP (Online Transaction Processing) systems, particularly when dealing with concurrent insert operations performed by different sessions. While bitmap indexes offer advantages in certain scenarios, there are considerations that make them less suitable for OLTP environments with high concurrent inserts:

1. Increased Row Lock Contention: Bitmap indexes can lead to increased row lock contention during concurrent insert operations. When multiple sessions attempt to insert rows with the same indexed value, they might contend for the same bitmap index block, potentially causing performance degradation and delays due to locking conflicts.
2. Suboptimal Insert Performance: Bitmap indexes are optimized for read-intensive workloads rather than write-intensive operations. As a result, the overhead of maintaining and updating the bitmap index during insert operations can impact overall insert performance, especially in highly concurrent environments.
3. Index Maintenance Overhead: Bitmap indexes require additional maintenance overhead as the index needs to be updated for each insert, update, or delete operation. This overhead can impact the overall system performance, especially in scenarios where frequent DML (Data Manipulation Language) operations occur.

Instead, alternative indexing options such as B-tree indexes might be more suitable for OLTP systems with high concurrent inserts. B-tree indexes provide efficient insert performance and handle concurrent operations more effectively, resulting in reduced row lock contention and improved overall system performance.

Ultimately, the choice of index type should be based on the specific requirements, workload characteristics, and performance considerations of the OLTP system. It is recommended to thoroughly analyze and benchmark different indexing strategies to determine the most appropriate option for your specific use case.

the following link, is nice to read about differences between B-Tree and Bitmap indexes:

last but not least, inserting into table with unique index using same values, will cause also a row lock contention, however now a days this rarely to see as many applications are using sequences or similar technique that avoids regenerate or duplicate of the same key.

So, why Bitmap indexes causes this lock:

reason I found is because of: Shared Bitmap Blocks: Bitmap indexes use shared bitmap blocks to represent the indexed values.

When multiple sessions concurrently insert rows with the same indexed value, they may contend for the same bitmap block.

As a result, they might need to acquire row-level locks on the bitmap block to update the bitmap index.