RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Concurrency Handling in Oracle: Preventing Locked Data

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.

ne of the most challenging requirements of a database engine is to react appropriately when two users want the same piece of data at the same time. A well functioning database engine can successfully service thousands of users concurrently. A poor solution will experience noticeable performance degradation as more people use the system.

While this is a challenge for any database solution, there are various approaches for solving it. SQL Server and Oracle each has a dramatically different method for handling concurrency. In this article we'll look at each solution and analyze the practical differences between them.

The Issue of Concurrent Access
There are several different scenarios in which concurrency becomes problematic. The simplest scenario is when more than one user wants to query the same data at the same time. In this case, there are no big decisions to be made: The database should serve the data to both users as quickly as possible. Both SQL Server and Oracle are multithreaded, which means they can handle more than one request at a time.

But when users are modifying data, the concurrency issue gets more complicated. For obvious reasons, databases generally allow only one person to modify any particular piece of data at a time. Once a user begins to modify a piece of data, both SQL Server and Oracle immediately lock the data, preventing other users from updating it until the first user has committed his transaction. But what happens when one user wants to query the information while another user is modifying it? What should the database do? Oracle and SQL Server choose very different solutions to this problem.

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