Concurrency issues - when do modifications to different rows of a table block each other?

Oracle's implementation of row level locking offers a high level of concurrency since you usually don't encounter blocking issues from concurrent modifications to different rows of a table, that could be caused e.g. by lock escalations (row locks to "page" or "block" locks, "block" locks to "table locks" etc.) that some DBMS products perform if too many low level locks are requested and a centralized lock management unit (usually a "lock manager") is unable to cope with the amount of locks. Since Oracle stores the "row level lock" information right in the block itself, it doesn't suffer from such scalability issues.

But there are certain scenarios in Oracle which still can lead to locking issues, even if different rows of a table are modified concurrently.

The following scenarios should be briefly outlined and demonstrated here along with possible remedies and supporting information how to identify if such an issue is encountered:

- Same unique key written by different sessions
- Unindexed foreign keys and modifications to parent table primary keys
- Insufficient block space and ITL slot shortage
- Bitmap indexes
- Segments with low MAXTRANS settings (Only pre-10g)

Same unique key written by different sessions

If multiple sessions attempt to write the same unique key but haven't yet completed the transactions, they will block each other, because depending on the completion of the transactions (commit or rollback) some might succeed or fail. This can be caused by updates, inserts and deletes.

The following test case run against 11.1.0.7 demonstrates the issue:

SQL>
SQL> create table unique_test as
2 select object_id as id, object_name as name
3 from all_objects
4 where rownum <= 10;

Table created.

SQL>
SQL> alter table unique_test
2 add constraint pk_unique_test
3 primary key (id);

Table altered.

SQL> -- perform this in session 1
SQL> insert into unique_test (id, name) values (100, 'NASE');

1 row created.

SQL>
SQL> pause

SQL> -- perform this in session 2
SQL> insert into unique_test (id, name) values (100, 'NASE');

Diagnosis:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 134 589845 1681 6 0 TX
Waiter: 140 589845 1681 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'row lock contention';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX row lock contention

SQL>
SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 2

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>

There is no easy remedy in this case, and if this happens frequently then it could be called a design flaw of the application (or it is intended behaviour). One potential workaround could be the definition of the primary key as deferred, but this has other significant implications, e.g. direct-path inserts might not work (silently fall back to conventional inserts, depending on the Oracle version) and the cost based optimizer treats deferrable constraints differently which could cause execution plan changes.

Unindexed foreign keys and modifications to parent table primary keys

If referential constraints have been defined between tables and the foreign key columns in the child table are not indexed, then modifications to the parent table's key cause locks on the child table, and if the foreign key columns are not indexed this can cause contention.

The following example run against 11.1.0.7 demonstrates the issue:

SQL>
SQL> create table parent_table
2 as
3 select distinct object_type
4 from all_objects;

Table created.

SQL>
SQL> create table child_table
2 as
3 select object_id, object_type, object_name
4 from all_objects
5 where rownum <= 10000;

Table created.

SQL>
SQL> alter table parent_table
2 add constraint pk_parent_table
3 primary key (object_type);

Table altered.

SQL>
SQL> alter table child_table
2 add constraint pk_child_table
3 primary key (object_id);

Table altered.

SQL>
SQL> alter table child_table
2 add constraint fk_child_table_parent_table
3 foreign key (object_type) references parent_table
4 on delete cascade;

Table altered.

SQL>
SQL> select count(*), object_type
2 from child_table
3 group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
1 EDITION
52 INDEX PARTITION
96 SEQUENCE
63 TABLE PARTITION
58 PROCEDURE
2 OPERATOR
468 PACKAGE
305 PACKAGE BODY
124 LIBRARY
44 TYPE BODY
1 TRIGGER
880 TABLE
972 INDEX
2849 SYNONYM
3278 VIEW
60 FUNCTION
10 CLUSTER
737 TYPE

18 rows selected.

SQL>
SQL> -- perform this in session 1
SQL> delete from parent_table where object_type = 'EDITION';

1 row deleted.

SQL>

-- and this in session 2
delete from parent_table where object_type = 'OPERATOR';

Although different rows in the parent and child table are affected by the two concurrent operations, session 2 is blocked.

Diagnosis:

SQL>
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 140 74233 0 3 0 TM
Waiter: 144 74233 0 0 5 TM

SQL>

SQL>
SQL> select event, total_waits from v$system_event
2 where event = 'enq: TM - contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TM - contention 1

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TM - contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TM - contention 1

SQL>

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TM';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
DML TM contention

The remedy in this case is simple, create an index on the foreign key columns of the child table:

SQL> -- indexing the foreign key column in the child table
SQL> -- solves the issue
SQL> create index fk_child_table_parent_table on child_table(object_type);

Index created.

SQL> -- perform this in session 1
SQL> delete from parent_table where object_type = 'EDITION';

1 row deleted.

SQL>

SQL> -- and this in session 2
SQL> delete from parent_table where object_type = 'OPERATOR';

1 row deleted.

SQL>

As you can see session 2 is now no longer blocked.

Insufficient block space and ITL slot shortage

If multiple transactions attempt to modify the same block, they can block each other if the following conditions are fulfilled:

- There is no free ITL ("Interested Transaction List") slot available. Oracle records the lock information right in the block and each transactions allocates an ITL entry.

- Insufficient space in the block left to add a new ITL slot. Since each ITL entry requires a couple of bytes a new one cannot be created if the block doesn't have sufficient free space.

The INITRANS and MAXTRANS settings of a segment control the initial and maximum number of ITL slots per block. The default of INITRANS in recent Oracle releases is 1 resp. 2 for indexes and the default value for MAXTRANS is 255 since the 10g release.

The following example demonstrates the issue. A block is almost full and several transactions attempt to manipulate different rows that all reside in this block.

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL>
SQL> create table t1 (c1 varchar2(1336)) pctfree 10;

Table created.

SQL>
SQL> insert into t1 values ('a');

1 row created.

SQL>
SQL> insert into t1 values ('b');

1 row created.

SQL>
SQL> insert into t1 values ('c');

1 row created.

SQL>
SQL> insert into t1 values ('d');

1 row created.

SQL>
SQL> insert into t1 values ('e');

1 row created.

SQL>
SQL> insert into t1 values ('e');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- increase the size of the rows
SQL> -- so that no space is left in the block
SQL> update t1 set c1 = rpad(c1, 1335, c1)
2 where length(c1) = 1;

6 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select substr(c1,1,1) from t1 where c1 like 'a%' for update nowait;

S
-
a

SQL> -- second session
SQL> select substr(c1,1,1) from t1 where c1 like 'b%' for update nowait;

S
-
b

SQL>

SQL> -- third session
SQL> select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait;
select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL>
SQL> update t1 set c1 = c1
2 where c1 like 'c%';

Diagnosis:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 138 393224 1330 6 0 TX
Waiter: 140 393224 1330 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'allocate ITL entry';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX allocate ITL entry

SQL>

SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - allocate ITL entry';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - allocate ITL entry 2

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - allocate ITL entry';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - allocate ITL entry 2

SQL>

Note that you can also query V$SEGMENT_STATISTICS for STATISTIC_NAME = 'ITL waits'.

If one of the rows is shrunk so that space is available, the third update will succeed:

SQL> -- shrink one row to release some space
SQL> -- in the block
SQL> -- and repeat the exercise
SQL> update t1 set c1 = substr(c1, 1, 1)
2 where rownum <= 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select substr(c1,1,1) from t1 where c1 like 'a%' for update nowait;

S
-
a

SQL>

SQL> -- session 2
SQL> select substr(c1,1,1) from t1 where c1 like 'b%' for update nowait;

S
-
b

SQL>

SQL> -- session 3
SQL> select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait;

S
-
c

SQL>

The remedy in this case requires a rebuild of the table segment using a higher INITRANS setting, so that initially more ITL slots are created. Note this reduces the space available for row data, so the size of the segment might increase.

Note that a rebuild using an appropriate PCTFREE setting also alleviates the problem but still a unsuitable row growth of rows by updates can again cause the issue. So the only change that helps independently of the row growth is an appropriate INITRANS setting.

Bitmap indexes

A single bitmap index entry can cover a lot of rows (rowid ranges), this is one of reasons why a bitmap index can be much smaller than a corresponding b*tree index.

But in terms of concurrency this can cause issues if different sessions attempt to modify the same bitmap index entry:

SQL>
SQL> create table fact_table2 as
2 select seq as fact_pk,
3 round(dbms_random.value(0, 1001)) as measure1,
4 round(dbms_random.value(0, 1001)) as measure2,
5 round(dbms_random.value(0, 1001)) as measure3,
6 case
7 when mod(seq, 20) >= 0 and mod(seq, 20) <= 10
8 then 1
9 else mod(seq, 20) - 9
10 end as dim_fk,
11 case when seq = 1 then 1 else 2 end as dim_fk2
12 from (
13 select level as seq from dual connect by level <= 1000
14 );

Table created.

Elapsed: 00:00:00.14
SQL>
SQL> create bitmap index fact_table2_dim_fk_idx on fact_table2(dim_fk);

Index created.

Elapsed: 00:00:00.11
SQL>
SQL> -- perform this in session 1
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1001, 1);

1 row created.

Elapsed: 00:00:00.03
SQL>

SQL> -- session 2
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1002, 1);

Diagnosis:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 138 589824 1682 6 0 TX
Waiter: 144 589824 1682 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'row lock contention';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX row lock contention

SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>

Two simple inserts block each other, a quite significant reason why bitmap indexes are not suitable for OLTP like applications.

There is no simple remedy in this case. Using a b*tree index instead of the bitmap index apparently doesn't show the same contention issue:

SQL>
SQL> drop index fact_table2_dim_fk_idx;

Index dropped.

Elapsed: 00:00:00.07
SQL>
SQL> -- the same using a b*tree index doesn't block each other
SQL> create index fact_table2_dim_fk_idx on fact_table2(dim_fk);

Index created.

Elapsed: 00:00:00.05
SQL>
SQL> -- perform this in session 1
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1001, 1);

1 row created.

Elapsed: 00:00:00.01
SQL>
SQL> pause

SQL>
SQL> -- perform this in session 2
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1002, 1);

1 row created.

Elapsed: 00:00:00.01

Segments with low MAXTRANS settings (Only pre-10g)

This scenario only applies to pre-10g databases where the MAXTRANS setting can be set to a lower value than the default of 255. 10g and later ignore the MAXTRANS value if specified and use 255 anyway.

The issue is basically the same as in the previous case: An ITL slot needs to be allocated, but cannot be created. In this case this is due to the artificial limitation caused by the low MAXTRANS setting.

SQL>
SQL> CREATE TABLE TAB1
2 ( COL1 NUMBER,
3 COL2 VARCHAR2(200))
4 INITRANS 1 MAXTRANS 1
5 --tablespace TEST_8K_ASSM
6 /

Table created.

SQL>
SQL> DECLARE
2 I NUMBER;
3 BEGIN
4 FOR I IN 1..10000 LOOP
5 INSERT INTO TAB1 VALUES
6 (I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');
7 END LOOP;
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- use three different sessions to perform these updates
SQL> -- the second or the at least the third one will be blocked
SQL> -- Session 1
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;

1 row updated.

SQL>
SQL> -- session 2
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;

1 row updated.


SQL> -- session 3
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 3;

Diagnosis:

SQL> --diagnosis
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 13 589842 976 6 0 TX
Waiter: 14 589842 976 0 4 TX

Note that the same can be shown using indexes with a low MAXTRANS setting.

The appropriate remedy is of course to use a suitable MAXTRANS setting which 10g and later applies by default.