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 their update, received confirmation, and even requeried to see the change for themselves. However, User2 then updated the work telephone number field and clicked Save, blissfully unaware of the fact that they 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 website), 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.
Leave a Reply