RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Enhance Application Concurrency with Oracle 10g's ROWDEPENDENCIES

Oracle 10g introduced a new feature called ROWDEPENDENCIES, which made every row independent. Learn how to use this feature to improve application concurrency and avoid the dreaded ORA-8177 error.

he ORA-8177 error "Cannot serialize access" often frustrates developers and testers of Oracle database-driven applications that employ SERIALIZABLE isolation levels. These errors can be particularly vexing because they stem from rows that are not being modified by any other transaction (INSERT/UPDATE/DELETE) than the one currently underway. Not only are the errors sporadic and seemingly illogical, but they also limit concurrency. So what causes them?

The problem is the default block-level locking of Oracle's SERIALIZABLE isolation level. In the SERIALIZABLE isolation level, data "freezes" when a transaction starts. The data that the transaction is selecting cannot be modified by any other transaction; it is effectively locked for modification. Transactions then are "queued" one after another, and a transaction can proceed only when the transaction ahead of it has completed. Data readers are not blocked from selecting the data, however. In effect, every transaction locks every block it touches from further modification. Because the entire block is locked, all the rows in it are locked as well—even those that are not supposed to be.

Oracle 10g introduced a new feature called ROWDEPENDENCIES, which developers can leverage not only to avoid the dreaded ORA-8177 error but also to improve application concurrency. This article demonstrates two techniques for doing just that. First, let's examine some alternative solutions.

What Can You Do About It?

Developers do have the ability to manipulate block size and the number of concurrent transactions that can modify data within a block through DB_BLOCK_SIZE and INI_TRANS, respectively. Block sizes are derived from the DB_BLOCK_SIZE parameter (or tablespace blocksize), which usually is multiples of the OS block size. If the row length (the total bytes of each column in the row) is 2000 and the block size is 8000, 3 or 4 rows will fit in a block. Some space in the block is reserved for internal use. (Beginning in 10g, you can define block size at the tablespace level.)

Every table or index database block has a recorded value for the initial number of concurrent transactions that can modify data within the block. This value is called the INI_TRANS, and in high-transaction environments it is set high. INI_TRANS is also applicable to other objects, such as materialized views and clusters.

The Oracle (10g and 11g) documentation suggests that increasing the INI_TRANS parameter of the block will mitigate the "Cannot serialize access" error. To verify how effective this suggestion is, I constructed a test case. First, I reproduced the ORA-08177 error (see Listing 1) by creating tables, opening two SQL*Plus sessions, and executing an anonymous block in both sessions. The results are from runs on Oracle 10g Express Edition, but I tested the code on other editions of 10g as well.

Next, my test case demonstrates the effect of increasing the INI_TRANS parameter. For consistency, the following code creates the same tables as Listing 1. (Only new blocks allocated to the table or index will be affected by the ALTER statement.)

create table t1_initrans5 (c1 number) initrans 5 pctfree 5;

create index idx_t1_initrans5 on t1_initrans5(c1)  initrans 5 pctfree 5;

select table_name, ini_trans from dba_tables where table_name like '%INITRANS%';

select index_name, ini_trans from dba_indexes where table_name like '%INITRANS%';

FOR i IN 1..10000
    insert into t1_initrans5 values(i);

Repeating the exercise from the code in Listing 1 produces the same error:

FOR i IN 1..10000
    insert into t1_initrans5 values(i);
  6  end;
  7  /
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-06512: at line 4

As you can see, INI_TRANS has no effect. What then could be the solution?

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date