Browse DevX
Sign up for e-mail newsletters from DevX


Concurrency Handling in Oracle: Preventing Locked Data : Page 4

SQL Server and Oracle each have very different methods for maintaining data constancy when accessed by concurrent users. Oracle maintains a snapshot of the data, which prevents queries from hanging without stooping to "dirty reads." Find out how it works.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Storing a Data Snapshot
So how does Oracle allow data to be modified while displaying the previous version of the data to other users? Whenever a user starts a transaction to modify data, the before image of the data is written to a special storage area. This before image is used to provide a consistent view of the database to anyone who queries. In my test case I was able to see the unchanged salaries of the employees even while another user was in the process of modifying them.

Where is this special area? Well, it depends what version of Oracle you are using. In Oracle 8i and below, special rollback segments are created just for this purpose. However, this puts an added burden on the DBA to manage and tune the segments properly. For example, the DBA has to determine how many such segments are needed and how large they should be. Without properly configured rollback segments it is possible for transactions to queue up as they wait for the necessary space in a rollback segment.

In 9i, Oracle's latest release, Oracle has implemented a new feature-an undo tablespace-that is supposed to eliminate this management complexity. While rollback segments can still be used, the DBA has the option of instead creating an undo tablespace and letting Oracle manage the complexities involved in allocating the space properly for the before images.

Oracle's approach also has implications for programmers. Because rollback space is not infinite, the data snapshots for newer transactions replace images from previous transactions. Therefore, it's possible that a long running query might receive a "snapshot too old error" if the required rollback segment has been overwritten by an image from some other transaction.

Here's a scenario where this might occur. At 11:59 AM an employee begins a transaction that updates John Doe's account balance. This transaction is committed at 12:01 PM. Meanwhile, at 12:00 PM an accounting manager begins a query that reports on all the customer balances and the total billing for the month. Because there are many customers, this query takes some time but regardless of how long it takes, all of the data it retrieves will be the way the data existed at 12:00 PM. If the rollback space containing the before image of John Doe's account has been overwritten by the time the query reaches his name, an error will be returned.

Oracle's solution certainly makes sense and in the abstract should provide better concurrency then SQL Server. It is refreshing not to have to worry that a longer running query will lock an important transaction from occurring. However, it's hard to prove that Oracle really does provide better concurrency in real life situations as both engines have benchmarks supporting thousands of users.

Joseph Lax is the principal of DB Directions, a company specializing in all aspects of database architecture. He has spent the last 12 years providing expertise on performance optimization, test design, and backup and recovery strategies for banks, telecommunication firms, and manufacturing companies.
Comment and Contribute






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



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