Enhance Application Concurrency with Oracle 10g’s ROWDEPENDENCIES

Enhance Application Concurrency with Oracle 10g’s ROWDEPENDENCIES

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 4

As 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.

devx-admin

devx-admin

Share the Post:
Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security.

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private,

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists

AI Tool

Unleashing AI Power with Microsoft 365 Copilot

Microsoft has recently unveiled the initial list of Australian clients who will benefit from Microsoft 365 (M365) Copilot through the exclusive invitation-only global Early Access Program. Prominent organizations participating in

Microsoft Egnyte Collaboration

Microsoft and Egnyte Collaboration

Microsoft has revealed a collaboration with Egnyte, a prominent platform for content cooperation and governance, with the goal of improving real-time collaboration features within Microsoft 365 and Microsoft Teams. This

Best Laptops

Top Programming Laptops of 2023

In 2023, many developers prioritize finding the best laptop for programming, whether at home, in the workplace, or on the go. A high-performing, portable, and user-friendly laptop could significantly influence

Renaissance Gaming Magic

AI Unleashes A Gaming Renaissance

In recent times, artificial intelligence has achieved remarkable progress, with resources like ChatGPT becoming more sophisticated and readily available. Pietro Schirano, the design lead at Brex, has explored the capabilities

New Apple Watch

The New Apple Watch Ultra 2 is Awesome

Apple is making waves in the smartwatch market with the introduction of the highly anticipated Apple Watch Ultra 2. This revolutionary device promises exceptional performance, robust design, and a myriad

Truth Unveiling

Unveiling Truths in Bowen’s SMR Controversy

Tony Wood from the Grattan Institute has voiced his concerns over Climate and Energy Minister Chris Bowen’s critique of the Coalition’s support for small modular nuclear reactors (SMRs). Wood points

Avoiding Crisis

Racing to Defy Looming Financial Crisis

Chinese property developer Country Garden is facing a liquidity challenge as it approaches a deadline to pay $15 million in interest associated with an offshore bond. With a 30-day grace

Open-Source Development

Open-Source Software Development is King

The increasingly digital world has led to the emergence of open-source software as a critical factor in modern software development, with more than 70% of the infrastructure, products, and services

Home Savings

Sensational Savings on Smart Home Security

For a limited time only, Amazon is offering massive discounts on a variety of intelligent home devices, including products from its Ring security range. Running until October 2 or while

Apple Unleashed

A Deep Dive into the iPhone 15 Pro Max

Apple recently unveiled its groundbreaking iPhone 15 Pro and iPhone 15 Pro Max models, featuring a revolutionary design, extraordinary display technology, and unrivaled performance. These new models are the first

Renewable Crypto Miners

Crypto Miners Embrace Renewable Energy?

As the cryptocurrency sector deals with the fallout from the FTX and Celsius exchange collapses, Bitcoin miners are increasingly exploring alternative energy sources to reduce expenses and maintain profitability. Specialists

Laptop Savings

The HP Omen 16 is a Gamer’s Dream

Best Buy is currently offering an unbeatable deal on the HP Omen 16 gaming laptop, giving potential buyers the chance to save a significant $720 on their purchase. Originally priced

How to Check for Vulnerabilities in Exchange Server

It is imperative to keep your systems and infrastructure up-to-date to mitigate security issues and loopholes, and to protect them against any known vulnerabilities and security risks. There are many