 |
The HP e3000--For Complete Novices,
Part 31: IMAGE/SQL Application
Logic--Locking Strategies
by George Stachnik
Welcome to part 31 of this series
of articles on the HP e3000. We are nearly at an end of our discussion
of the IMAGE/SQL database management system. We've been discussing
the various intrinsics that are used to read and write data to
and from an IMAGE/SQL database. More recently, we began looking
at ways of preventing database corruption. This time, we're going
to begin looking at one of the biggest causes of database corruption--poor
locking strategy.
To understand what a locking strategy is, consider the simple data entry
shown in Figure 1. This figure shows a record layout that's about as
simple as you can get. There are only two fields: a savings account
balance, and a checking account balance. Let's assume that this
record contains the account balances for a married couple--we'll
call them George and Monica.
| Figure 1: Basic Record |
|
 |
Now, watch what happens when
George and Monica go to two different ATM machines at the same
time. George and Monica have a total of $1,500 dollars in the
bank--$1,000 in savings, and $500 in checking. George is going
to transfer $100 from savings to checking. Monica is going to
withdraw $100 from checking. When both transactions are complete,
there should be $900 in savings, and $500 in checking, for a
total of $1,400.
Figure 2 shows what happens
inside this poorly designed ATM system when George puts his ATM
card into the machine and enters his PIN number. At the top of
the figure, we see the balances as they are represented in the
database. At the bottom, we see that a copy of the record has
been retrieved from the database into the ATM's own internal
memory. George has entered his transaction and the ATM has dutifully
debited the savings account by $100 (leaving a balance of $900),
and credited the checking account by the same amount (yielding
a balance of $600).
| Figure 2: George Uses the ATM |
|
 |
But while this has been going
on, Monica has inserted her ATM card into another machine
on the other side of town. Figure 3
shows the data that's in the database (at the top), the data
that's in George's ATM's memory (lower left), and the data that's
in Monica's ATM's memory (lower right). Note that the data in
Monica's ATM's memory reflects the contents of the database.
In other words, Monica's ATM doesn't know that George is in the
middle of transferring $100 from savings to checking. This, as
we'll soon see, is going to cause a lot of trouble.
| Figure 3: Monica Inserts Her Card |
|
 |
Figure 4 shows what happens
next. Monica tells her ATM to withdraw $100 cash. Dutifully,
her ATM machine subtracts $100 from the checking account balance.
Meanwhile, over in the lower left hand corner of the figure,
George has completed his transaction, and written the updated
balances back to the database. The database now thinks that there
are $900 in savings and $600 in checking.
| Figure 4: The Transactions Conflict |
|
 |
In Figure 5, Monica has completed her transaction, and written
her data record back to the database. At this point, there should
be $900 in savings, and $500 in checking, for a total of $1,400.
Instead, we see savings with a balance of $1,000, and checking
with a balance of $400. George's transfer has simply been wiped
out, as if it never happened.
| Figure 5: George's Transfer Wiped Out |
|
|
What went wrong? Each ATM machine
was processing a transaction without any knowledge of what the
other one was up to. Both ATMs were acting on the same data in
the same record, but there was no mechanism in place to coordinate
the two transactions and keep them out of each other's hair.
In IMAGE/SQL, the mechanism for
coordinating database transactions is called locking,
and here's how it works. In the ATM example, the problems really
started in Figure 3. That's
where two different copies of the data in the database were created,
with no mechanism to keep them in sync. A good IMAGE/SQL application
would not allow the scenario shown in Figure 3
to happen.
When an IMAGE/SQL application
retrieves a record from a database for the purpose of updating
it, it will typically call for a lock on that data. Application
programs do this by calling an intrinsic called DBLOCK. DBLOCK
can be used to lock database data at any of three different levels:
- at the RECORD level: By locking
data at the record level, an application program effectively
prevents other programs (or other users running the same program)
from acting on the same record at the same time.
- at the DATASET level: By locking
data at the dataset level, an application program effectively
prevents other programs (or other users running the same program)
from acting on any record in the same dataset at the same time.
- at the DATABASE level: By locking
data at the database level, an application program effectively
prevents other programs (or other users running the same program)
from acting on any record in the same database at the same time.
To understand how locking works,
let's walk through a scenario. Let's go back to
Figure 2. George has retrieved a copy of the data in the
database, but this time, let's suppose that he has also used
the DBLOCK intrinsic to call for a record-level lock on the record
in the database. By holding that lock, George is effectively
saying, "I'm using this record. If anybody else comes along
wanting to use this record, they're going to have to wait until
I'm done."
Now suppose that Monica comes
along and asks to update the record (before George is done).
But this time, Monica asks to lock the record before she tries
to update it. Her request to lock the record will be denied because
George is already holding the lock. What happens next depends
on how the application that George and Monica are using is coded.
The programmer could set it up so that Monica will simply queue
up behind George and wait until George completes his transaction
before she can begin hers. Or, the programmer could display an
error message on Monica's ATM (your account is in use--try again
later...).
The important thing to understand
is that IMAGE/SQL uses locks to prevent George and Monica from
getting into the kind of situation that we explained at the beginning
of this article. Without locking, database corruption cannot
help but result. However, locking comes with its own set of problems.
The first, and most important
thing to understand about locking is that it's a "gentleman's
agreement." It doesn't actually prevent anybody from updating
the database while another transaction is in progress. In other
words, suppose that George and Monica are running two different
applications. George's application dutifully locks the database
record before it begins updating it, when Monica's application
comes along. Now suppose that the programmer who designed Monica's
application didn't know about locking (or didn't understand how
important it was). Monica's program could retrieve the record
that George has locked, update it, and write it back to the database--all
while George was holding the lock!
The important thing to understand
here is that the fact that George is holding a lock on the data
in the database does not stop Monica (or anybody else) from reading,
writing, updating, or even deleting that data. It only stops
Monica (or anybody else) from locking the data. If an
errant application program acts on data without locking it, IMAGE/SQL
will do nothing to stop it.
The second thing to understand
about locking is that it can have severe performance consequences
if it's not used properly. One important rule to remember is
this: "Never lock around a user dialog." This means
that programs should not prompt the user for information while
holding a lock. Let's see why.
Suppose George inserts his ATM
card into the machine and tells the machine that he wants to
transfer money from savings to checking. The ATM machine's application
dutifully finds George's record, locks it (using DBLOCK), retrieves
it (using DBGET), and displays the account balances on the ATM
before asking George how much money he wants to transfer. Now
suppose that George sees the amount in his savings account and
drops dead of a heart attack (presumably he thought he had more
money in the bank). The ATM, having no way of knowing about George's
untimely demise, will simply wait for him to enter an amount.
Meanwhile, across town, Monica
is inserting her ATM card into another machine, and requesting
the same record. When the ATM application tries to lock the record,
it won't be able to (because poor dead George is holding the
lock across town). If the application has been designed to wait
until the lock becomes available, poor Monica could wind up stuck
at that ATM machine until the coroner's office sends somebody
across town to the ATM machine that George has been using.
To prevent this kind of deadlock,
it's good programming practice to avoid locking around user dialog.
George's program should retrieve the record--and engage George
in a dialog to see what he wants to do. Only after it has been
determined that George wants to transfer $100 from savings to
checking should the program at George's ATM lock the record,
retrieve it again (to ensure that it hasn't been updated during
the dialog), update it, and unlock it. The idea is to hold the
lock for the shortest possible length of time, in order to avoid
hanging up other users who are waiting for locks.
The performance impact of poorly
conceived locking strategies can be devastating. If an application
program is going to be making changes to a number of records,
it may be a good idea to ask for a dataset-level lock--or even
a database-level lock. A user who is holding a dataset-level
lock will prevent other users (or other programs) from locking
any of the records in that dataset, or from locking the
dataset itself. Dataset-level locking effectively single-threads
transactions, so that only one transaction can be in progress
against a particular dataset at a time. Database-level locking
is even worse--ensuring that only one transaction can be in progress
against a particular database at a time.
You can see that the effects
of database-level locking can be very severe. If a program holding
a database-level lock engages in some kind of lengthy user dialog,
it will effectively prevent any other transaction from being
applied against that database until the dialog is complete and
the lock freed up. Yet as bad as database-level locking can be,
there is an alternative that can be even worse.
Let's imagine an application
that needs to update two different records in order to complete
a transaction. We'll call them record A and record B. Now let's
assume that two different users are running the application at
the same time. We'll call them USER1 and USER2. Let's see what
could happen if our two users decide to try to update the same
two records at the same time. Remember, both USER1 and USER2
want to lock record A and record B.
Suppose that USER1 issues a request
to lock record A, and gets it. But when USER1 tries to lock record
B, he finds it's already locked by somebody else--so he waits.
Now suppose that record B is locked by USER2, who has issued
a request also to lock Record A. Of course, USER2 cannot lock
record A because USER1 is already holding that lock.
This scenario is called a "deadly
embrace" because the two users are waiting on one another.
USER1 cannot lock record B because USER2 has it. USER2 cannot
unlock record B because he's waiting for record A, which is locked
by USER1. USER1 cannot unlock record A because he's waiting
for record B, which is locked by USER2. The two users will literally
sit there waiting for one another until doomsday (or until somebody
reboots the 3000--whichever comes first).
The best way to avoid deadly
embrace situations is to avoid scenarios in which your application
requests multiple locks. If this isn't possible (and it often
isn't), then your application needs to request locks in a specific
order--and everyone needs to observe the same order. The problem
we just explained was caused because USER1 and USER2 were trying
to lock the two different records in different orders:
USER1 had locked record A and was trying to lock record B.
USER2 had locked record B and was trying to lock record A.
If both users had tried to lock
the two different records in the same order, (first A,
then B), they wouldn't have gotten themselves into the
deadly embrace situation.
Locking is a necessary part of
any IMAGE/SQL application in which multiple users are updating
data simultaneously. Here are some of the rules we've learned
about locking:
- Always lock at the lowest level
possible--record-level locking is better than dataset-level,
which is better than database-level.
- Dataset-level locking may be
necessary in situations in which you are updating multiple records
(as is the case, for example, when you're updating a chain of
records).
- Never hold a lock while engaging in a user dialog.
- Use caution when holding multiple
locks to avoid deadly embrace situations.
In the next article, we'll look
at some programming examples of locking.
George Stachnik works in Hewlett-Packard's
Commercial Systems Division in Cupertino, California. He is responsible
for the development and delivery of e3000 training.
|
![[3khat]](/img/3khat16.ico)
3kRanger