Browse DevX
Sign up for e-mail newsletters from DevX


Concurrency Handling in Oracle: Preventing Locked Data : Page 3

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

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 13000 Pat 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 13650 Pat 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 13000 Pat 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!

Author's Note: Those of you familiar with SQL Server might think that you can achieve the same effect by specifying (NOLOCK) in your query. However, in SQL Server, you can't retrieve the before image of the data. By specifying (NOLOCK) you instead get the uncommitted data-in essence, a dirty read. Oracle's approach provides a consistent view of the data; all the information shown is from the same point in time.

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.

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