Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Enhance Application Concurrency with Oracle 10g's ROWDEPENDENCIES : Page 2

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.


advertisement

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; begin FOR i IN 1..10000 LOOP insert into t1_rd values(i); END LOOP; end; /* session 2*/ alter session set isolation_level=serializable; begin FOR 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.

<perform some transactions here on t1 and t1_rd - INSERT/UPDATE, but do not commit> 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.



Shirish Joshi is a senior developer at Persistent Systems Ltd. in Pune, India.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap