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.

The SQL Server Method
When someone begins modifying data in SQL Server, that data becomes locked. Locking prevents any other connection from accessing that data-even to query it. The data will only be accessible to other users when the transaction is either committed or rolled back.

I can demonstrate this behavior rather simply using the pubs sample database that comes with SQL Server. Open up two windows in Query Analyzer. In the first window, raise the price of all books in the pubs database by executing the following SQL:

use pubsgobegin tranupdate titlesset price = price * 1.05wheretitle_id = 'BU2075'

Because I haven’t yet executed a commit statement in the code the change is still not finished.

Now in the other window, I’ll try to query the titles table by executing:

select title_id,title,pricefrom titlesorder by title_id.

You will not get any results. Instead, the little globe on the bottom will keep spinning. Although I’m only updating one row, my select statement includes the one row that is being changed. Therefore, no data will be returned till the update is either committed or rolled back.

The solution chosen by SQL Server potentially lowers throughput and performance. The longer data is locked, or the larger the amount of data being locked, the more likely that other users will have to wait for their statements to execute. Therefore, as a programmer, it is important to keep transactions small and fast.

In more recent versions of the product, Microsoft has enabled SQL Server to lock smaller amounts of data at a time, which is a big improvement. In version 6.5 and below, the smallest lock was on a page. So even if you were only modifying one row out of 10 on a page, all 10 rows would be locked. This increased the likelihood of causing a wait for another connection till the modification was completed. In version 7, Microsoft introduced row-level locking, so now SQL Server locks only the rows that are actually being changed.

SQL Server’s solution sounds simple, but there’s a lot that has to happen behind the scenes to maintain sufficient performance. For example, if you are modifying many rows, SQL Server will escalate the lock to the page level or even the table level so as not to have to track and maintain separate locks for each record.

The Oracle Method
Now I’ll perform a similar experiment in Oracle and see how it behaves. First, I’ll open up one instance of SQLPlus and execute the following query (my example uses a sample schema that comes with 9i). I’ll call this instance of SQLPlus the Query Instance.

select first_name, last_name, salary from hr.employees where department_id = 20;

This code returns two rows, as follows:

FIRST_NAME              LAST_NAME                       SALARY-------------------- 	-------------------------       ----------Michael              	Hartstein                      	13000Pat                  	Fay                             6000

In another instance of SQLPlus (which I’ll call the Update Instance), I’ll execute the following command:

SQL> update hr.employees  2  set salary = salary * 1.05  3  where   4  department_id = 20  5  /

When this code executes, I receive a reply that two rows have been updated.

Note that I haven’t typed “begin tran” as I did in the SQL Server example. Oracle’s SQLPlus starts an implicit transaction for you. (You can set “autocommit to on,” which will automatically commit the transaction for you if you would like Oracle to mimic SQL Server behavior.) In the Update instance of SQLPlus I’ll execute the same Select statement as I did in the Query Instance.

FIRST_NAME           LAST_NAME                     SALARY-------------------- -------------------------     ----------Michael              Hartstein                     13650Pat                  Fay                           6300

My results clearly show that both Michael and Pat have received salary increases, even though these changes have not yet been committed.

Now, I’ll switch back to the Query Instance of SQLPlus and re-run the query, which returns the following:

FIRST_NAME              LAST_NAME                       SALARY-------------------- 	-------------------------       ----------Michael              	Hartstein                       13000Pat           	       	Fay                             6000

Oracle did not require me to wait until the data change in the Update instance was committed. It returned Michael and Pat’s information as requested, but has, in fact, returned a view of the data from before the update started!

I will only see the changes to the salary in the Query Instance if I commit the update in the Update Instance of SQLPlus by typing “commit.” If I do that and re-run the Select statement in the Query Instance Oracle will return the new salary values for Michael and Pat.

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.