Database Reference
In-Depth Information
You must take these facts into consideration when developing your application and you must also realize that
this policy is unique to Oracle; every database has subtle differences in its approach to locking. Even if you go with
lowest common denominator SQL in your applications, the locking and concurrency control models employed
by each vendor assure something will be different. A developer who does not understand how his or her database
handles concurrency will certainly encounter data integrity issues. (This is particularly common when a developer
moves from another database to Oracle, or vice versa, and neglects to take the differing concurrency mechanisms into
account in the application.)
Preventing Lost Updates
One of the side effects of Oracle's nonblocking approach is that if you actually want to ensure that no more than one
user has access to a row at once, then you, the developer, need to do a little work yourself.
A developer was demonstrating to me a resource-scheduling program (for conference rooms, projectors, etc.)
that he had just developed and was in the process of deploying. The application implemented a business rule to
prevent the allocation of a resource to more than one person for any given period of time. That is, the application
contained code that specifically checked that no other user had previously allocated the time slot (at least the
developer thought it did). This code queried the
SCHEDULES
table and, if no rows existed that overlapped that time slot,
inserted the new row. So, the developer was basically concerned with two tables:
EODA@ORA12CR1> create table resources
2 ( resource_name varchar2(25) primary key,
3 other_data varchar2(25)
4 );
Table created.
EODA@ORA12CR1> create table schedules
2 ( resource_name varchar2(25) references resources,
3 start_time date,
4 end_time date
5 );
Table created.
And, right after inserting a room reservation into
SCHEDULES
, and before committing, the application would query:
EODA@ORA12CR1> select count(*)
2 from schedules
3 where resource_name = :resource_name
4 and (start_time < :new_end_time)
5 AND (end_time > :new_start_time)
6 /
It looked simple and bulletproof (to the developer anyway); if the count came back as one, the room was yours.
If it came back greater than one, you could not reserve it for that period. Once I knew what his logic was, I set up
a very simple test to show him the error that would occur when the application went live—an error that would be
incredibly hard to track down and diagnose after the fact. You'd be convinced it
must
be a database bug.
