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%';beginFOR i IN 1..10000 LOOP insert into t1_initrans5 values(i); END LOOP;end;Repeating the exercise from the code in Listing 1 produces the same error:
beginFOR i IN 1..10000 LOOP insert into t1_initrans5 values(i); END LOOP; 6 end; 7 /begin*ERROR at line 1:ORA-08177: can't serialize access for this transactionORA-06512: at line 4As you can see, INI_TRANS has no effect. What then could be the solution?
Enter ROWDEPENDENCIES
Oracle 10g introduced a new feature called ROWDEPENDENCIES, which made every row independent. The primary objective of this feature was to aid row-level replication of tables at the transaction level, per row. Since it was introduced, experts such as Tom Kyte and Oracle bloggers have also demonstrated how ROWDEPENDENCIES can be used to provide optimistic locking, a very useful feature to have in web applications.
This article describes two ways to use tables with ROWDEPENDENCIES for enhanced application concurrency.
Overcome Concurrency Limitations
Here is how you can solve the concurrency-limiting problem demonstrated in my test case using ROWDEPENDENCIES. The following code creates a new set of tables, opens two SQL*Plus sessions, and executes an anonymous block:
/* create the tables for the test case */create table t1_rd (c1 number) rowdependencies pctfree 5;create index idx_t1_rd on t1_rd(c1) pctfree 5;/* now open 2 SQL*Plus sessions and cut-paste this code in both *//* session 1*/alter session set isolation_level=serializable;beginFOR i IN 1..10000 LOOP insert into t1_rd values(i); END LOOP;end;/* session 2*/alter session set isolation_level=serializable;beginFOR i IN 1..10000 LOOP insert into t1_rd values(i); END LOOP;end;/* now in both sessions, execute the anonymous block*//* session 1 */SQL>//* session 2*/SQL>/ Here is the output from both sessions:
SQL> alter session set isolation_level=serializable;Session altered.SQL> begin 2 FOR i IN 1..10000 3 LOOP 4 insert into t1_rd values(i); 5 END LOOP; 6 end; 7 /PL/SQL procedure successfully completed.The code produced no errors. Now you can commit or rollback to end the transaction. After commit, you can see the rows inserted in the table.
Determine Which Rows Have Been Committed
You can use ROWDEPENDENCIES to determine which rows have been committed and which rows haven’t been committed yet by the same session.
INSERT INTO t1_rd VALUES (100);INSERT INTO t1_rd VALUES (101);SELECT c1, ORA_ROWSCN FROM t1_rd WHERE ORA_ROWSCN IS NULL;INSERT INTO t1 values (1000);INSERT INTO t1 values (1001);SELECT c1, ORA_ROWSCN FROM t1; In tables with ROWDEPENDENCIES, the ORA_ROWSCN column is NULL for uncommitted rows.
With these techniques, you can improve application concurrency and avoid that dreaded ORA-8177 error.
