Database Reference
In-Depth Information
Locking Issues
Before we discuss the various types of locks that Oracle uses, it is useful to look at some locking issues, many of
which arise from badly designed applications that do not make correct use (or make no use) of the database's locking
mechanisms.
Lost Updates
A lost update is a classic database problem. Actually, it is a problem in all multiuser computer environments. Simply
put, a lost update occurs when the following events occur, in the order presented here:
1.
A transaction in Session1 retrieves (queries) a row of data into local memory and displays
it to an end user, User1.
2.
Another transaction in Session2 retrieves that same row, but displays the data to a different
end user, User2.
3.
User1, using the application, modifies that row and has the application update the
database and commit. Session1's transaction is now complete.
4.
User2 modifies that row also, and has the application update the database and commit.
Session2's transaction is now complete.
This process is referred to as a lost update because all of the changes made in Step 3 will be lost. Consider,
for example, an employee update screen that allows a user to change an address, work number, and so on. The
application itself is very simple: a small search screen to generate a list of employees and then the ability to drill down
into the details of each employee. This should be a piece of cake. So, we write the application with no locking on our
part, just simple SELECT and UPDATE commands.
Then an end user (User1) navigates to the details screen, changes an address on the screen, clicks Save, and
receives confirmation that the update was successful. Fine, except that when User1 checks the record the next day to
send out a tax form, the old address is still listed. How could that have happened? Unfortunately, it can happen all too
easily. In this case, another end user (User2) queried the same record just after User1 did—after User1 read the data,
but before User1 modified it. Then, after User2 queried the data, User1 performed her update, received confirmation,
and even re-queried to see the change for herself. However, User2 then updated the work telephone number field
and clicked Save, blissfully unaware of the fact that he just overwrote User1's changes to the address field with the old
data! The reason this can happen in this case is that the application developer wrote the program such that when one
particular field is updated, all fields for that record are refreshed (simply because it's easier to update all the columns
instead of figuring out exactly which columns changed and only updating those).
Note that for this to happen, User1 and User2 didn't even need to be working on the record at the exact same
time. They simply needed to be working on the record at about the same time.
I've seen this database issue crop up time and again when GUI programmers with little or no database training
are given the task of writing a database application. They get a working knowledge of SELECT , INSERT , UPDATE , and
DELETE and set about writing the application. When the resulting application behaves in the manner just described,
it completely destroys a user's confidence in it, especially since it seems so random, so sporadic, and totally
irreproducible in a controlled environment (leading the developer to believe it must be user error).
Many tools, such as Oracle Forms and APEX (Application Express, the tool we used to create the AskTom web
site), transparently protect you from this behavior by ensuring the record is unchanged from the time you query it,
and locked before you make any changes to it (known as optimistic locking ); but many others (such as a handwritten
Visual Basic or a Java program) do not. What the tools that protect you do behind the scenes, or what the developers
must do themselves, is use one of two types of locking strategies: pessimistic or optimistic .
 
Search WWH ::




Custom Search