All I did was get someone else to use the terminal next to him.
They both navigated to the same screen and, on the count of
three, each hit the Go button and tried to reserve the same room
for the exact same time. Both people got the reservation —
the logic, which worked perfectly in isolation, failed in a
multi-user environment. The problem in this case was caused by
Oracle's non-blocking reads. Neither session ever blocked the
other session. Both sessions simply ran the above query and then
performed the logic to schedule the room. They could both run the
query to look for a reservation, even if the other session had
already started to modify the schedules table (the change
wouldn't be visible to the other session until commit, by which
time it was too late). Since they were never attempting to modify
the same row in the schedules table, they would never block each
other and, thus, the business rule could not enforce what it was
intended to enforce.
The developer needed a method of enforcing the business rule in a
multi-user environment, a way to ensure that exactly one person
at a time made a reservation on a given resource. In this case,
the solution was to impose a little serialization of his own
— in addition to performing the count(*) above, the
developer must first:
select * from resources
where resource_name = :room_name
FOR UPDATE;
A little earlier in the chapter, we discussed an example where
use of the FOR UPDATE clause caused problems, but here it is what
makes this business rule work in the way intended. What we did
here was to lock the resource (the room) to be scheduled
immediately before scheduling it, in other words before we query
the Schedules table for that resource. By locking the resource we
are trying to schedule, we have ensured that no one else is
modifying the schedule for this resource simultaneously. They
must wait until we commit our transaction — at which point,
they would be able to see our schedule. The chance of overlapping
schedules is removed. The developer must understand that, in the
multi-user environment, they must at times employ techniques
similar to those used in multi-threaded programming. The FOR
UPDATE clause is working like a semaphore in this case. It
serializes access to the resources tables for that particular row
— ensuring no two people can schedule it simultaneously.
This is still highly concurrent as there are potentially
thousands of resources to be reserved — what we have done
is ensure that only one person modifies a resource at any time.
This is a rare case where the manual locking of data you are not
going to actually update is called for. You need to be able to
recognize where you need to do this and, perhaps as importantly,
where not to (I have an example of when not to below).
Additionally, this does not lock the resource from other people
reading the data as it might in other databases, hence this will
scale very well.
Issues such as the above have massive implications when
attempting to port an application from database to database (I
return to this theme a little later in the chapter), and this
trips people up time and time again. For example, if you are
experienced in other databases, where writers block readers and
vice versa then you may have grown reliant on that fact to
protect you from data integrity issues. The lack of concurrency
is one way to protect yourself from this — that is how it
works in many non-Oracle databases. In Oracle, concurrency rules
supreme and you must be aware that, as a result, things will
happen differently (or suffer the consequences).
For 99 percent of the time, locking is totally transparent and
you need not concern yourself with it. It is that other 1 percent
that you must be trained to recognize. There is no simple
checklist of 'if you do this, you need to do this' for this
issue. This is a matter of understanding how your application
will behave in a multi-user environment and how it will behave in
your database.
» 
Comment and Contribute
