Follow Us

Views from the Lab

Data consistency problems in RDBMS applications

The 2007 New York Times Bestseller, The Black Swan: The Impact of the Highly Improbable by Naseem Nicholas Taleb covers events characterised as 1) being outliers beyond normal expectation, 2) having extreme impact, and 3) being able to be rationalised after the fact. Are data consistency problems in RDBMS applications black swans in IT?
A common misconception of applications built on the top of a Relational Database Management System (RDBMS) is that they are virtually free of data consistency problems. This assumption hinges on the condition that applications use RDBMS’s transaction support correctly. But evidently that’s not quite the case. 
A recent study by researchers from Purdue University found that more than one hundred transaction-related data-consistency defects in popular web modules such as OpenCart and phpBB.  OpenCart is a widely available shopping cart module with over a million hits. One key function is managing coupons to ensure they are used no more than a maximum number of times. 

The study finds that the code where the coupon’s usage count is read from the database, and the code where the count is updated, reside in a two separate transactions. As a result, an over selling of coupons can happen when two or more users use the coupon at the same time. To perform correctly, the code should have been encapsulated in a single transaction.

This cross-transaction data flow issue is not new and it is a fairly common topic discussed in basic database courses. However, the study by Purdue indicates that this issue is more pervasive than many anticipated. Such a defect could be disastrous for some systems and is difficult to detect. 

It is not easy to spot during manual code review, as different database queries are often encapsulated in different classes and methods. It is also difficult to catch during the testing phase as it requires precise timing for interleaving of transactions to produce a wrong result.

The key in discovering these defects is to locate data dependencies between different database queries. If the value used in an update query comes from a read query, chances are these queries ought to be enclosed in the same transaction. We are not aware of any readily-available tools that can detect this defect automatically. 

However, we can configure dynamic tainting tools, such as Python tainting mode, CORE GRASP etc., to do so. Dynamic tainting tools mark inputs that percolate through the system and can locate data dependencies between two variables in a piece of code. 

Many of these tools are originally designed for detecting security vulnerabilities such as SQL injection by tracing the data flow of raw user input from the GUI into SQL query strings without going through any security check.
To locate data-consistency defects mentioned above, dynamic tainting tools have to be manually reconfigured by explicitly specifying the location of each read and update queries. This manual effort is not for the faint of heart, especially for large systems.  

But one can mitigate this issue by focusing on critical modules where defects like this can have significant implications. Ultimately, it is the developer’s responsibility to ensure the database transactions are implemented correctly.

Data consistency problems do occur in RDBMS applications.  They are difficult to detect and become increasingly so as we incorporate and reuse software modules by others.  

But, depending on the consequences of failure, identifying the vulnerability is worth the effort to “turn the Black Swans white.”  Taleb states that what may be a black swan surprise for a turkey is not a black swan surprise to its butcher.  So “avoid being the turkey” and consider data consistency issues in your RDBMS applications.

By Teresa Tung, Manager, Accenture Technology Labs and Chen Fu, researcher, Accenture Technology Labs

Enhanced by Zemanta

Tags: applications, database, relational database management system, sql

RSSSubscribe to this blog

More from Techworld

More relevant IT news

Contact Us

For editorial queries:
Mike Simons

For website issues:

For commercial queries
Russell Kearney

For more contact details click here.

Email this to a friend

* indicates mandatory field

Techworld White Papers

Optimising data protection for virtual environments

VM environments require the same level of data protection as does the physical server environment. Companies may use data protection tools built for the physical environment in the virtual world, but this has serious disadvantages.

Download Whitepaper

PCI Compliance: Are UK businesses ready?

Exploring the results of a recent survey, including: ? Levels of understanding of the standard ? Current perceptions of actual compliance status ? Attitudes toward addressing compliance

Download Whitepaper

Mobility Management for Dummies

Your complete guide to managing and securing mobile devices such as laptops and smartphones.

Download Whitepaper

Magic Quadrant for midrange and high-end NAS solutions

It is difficult to find one midrange or high-end NAS product that can cater to all needs. File systems embedded in NAS are often designed to solve one major pain point, with additional features being added later to broaden use cases and benefits.

Download Whitepaper

Techworld UK - Technology - Business

Oracle Video

Enabling agile and intelligent businesses

 Changing markets, competitive pressures and evolving customer needs are placing increasing pressure on IT to deliver greater flexibility and speed. Explore truly flexible SOA foundations with this Oracle video.

COLT White Paper

IT Misuse Survey

Complete this survey and you could win a Nexus One

Techworld are running a short survey to discover how UK businesses are managing Internet and email misuse in the Enterprise.

Complete Survey

Complete our survey and you could win a Sony E-book Reader.
Techworld have teamed up with HP to compile a survey relating to server virtualisation. Complete the short survey and you could be the lucky winner of a Sony E-book reader.

Complete the survey here

Site Map