Getting a Row Lock in SQL
Sam L.
in a file that interactive users may also be updating. It is a
legacy file and commitment control is probably not an option.
In native RPG, if I specify update on the F-spec, a record I
Chain to is locked against other updates until I release it.
Thus, I can verify that a record exists, make changes, then
update it, all the while knowing that someone else didn't change
it in the few milliseconds while my program was doing its work on
the record.
I'm writing the program using embedded SQL. How do I get a row
lock that is the equivalent of a Chain to an update file?
The only way I can see is to declare a cursor with "for Update",
then read the one record that I expect with a fetch, then do an
update "where current of". I believe this will lock the record
from being updated fror the fetch to the update, but it seems a
whole lot more work than a Chain.
Any thoughts?
Phil
Excerpt from Paul Conte's SQL “Crib Sheet” for RPG Programmers article.
Implementing "optimistic" concurrency for file updates
Applications that update files must protect against conflicting updates from
other jobs. Both RPG and SQL provide record locks when you read a record
from a file accessed for update. Depending on the level of commitment
control in effect, the record lock is held until a subsequent update
operation for the record, another read-for-update operation to the same
file, or a commit or rollback statement ends the transaction. To use record
locking in SQL, you simply add a For Update Of clause to the cursor
declaration and use a positioned Update statement (more on this in a moment)
after executing a Fetch statement to retrieve a record through the cursor.
When your application obtains a record lock and holds it while a user
browses and potentially changes data interactively, the application is using
what’s known as a pessimistic concurrency strategy — "pessimistic" because
your application expects another application to attempt a conflicting update
while the record is being browsed. Pessimistic concurrency strategies can
increase record access conflicts between jobs and, generally, are not the
best approach for transactions that involve user "think time." Pessimistic
concurrency also doesn’t work well with applications delivered over the Web
where database connections shouldn’t be held for a long duration.
A technique known as optimistic concurrency is a preferable alternative for
many interactive applications. In simple terms, your application initially
reads a record without a lock; but, before updating the record in the file,
your application checks to be sure the record hasn’t changed since it was
initially retrieved. In RPG IV, this is implemented with six basic steps:
1.. On the F-spec, declare the file as used for update operations (code a
U in position 17).
2.. Use the N (no lock) opcode extender on the Chain, Read, or other
initial input operation to read the record without a lock.
3.. Make a copy of the record as initially read.
4.. Before updating the record, reread it without the N opcode extender,
thus locking the record. (If the record is currently locked by another job
when you try to reread it, you can optionally retry this step one or more
times. If the record remains locked by another job, handle the situation as
an exception and restart the transaction. Note that lock conflicts should
rarely arise if all applications avoid holding locks for a long duration.)
5.. Compare the saved original copy and the reread record.
6.. If the two are identical, update the record that was retrieved with
the lock; otherwise, handle the exception and restart the transaction.
In SQL, you can implement this technique with a read-only cursor for step 2
and an updatable cursor for steps 4 and 6. But there’s a simpler alternative
available with SQL. First, read the record without a lock using a Select
Into statement or a read-only cursor. Either of these operations stores
column values in host variables. Then for the update operation, use a
searched Update statement such as the one in Figure 4.
Figure 4
Update statement to check for unchanged fields before update
Update Master
Set Fld1 = :NewFld1,
Fld2 = :NewFld2,
...
FldN = :NewFldN
Where KeyFld = :KeyFld
And Fld1 = :Fld1
And Fld2 = :Fld2
...
And FldN = :FldN
In this example, a Select Into or Fetch statement is used to retrieve a
record’s fields into the KeyFld, Fld1, Fld2, ..., FldN host variables.
The program places new values in the NewFld1, NewFld2, ..., NewFldN host
variables.
After the Update statement, the program should check the SQL state,
which will be set to SqlStateNoRow [ D SqlStateNoRow C
Const( '02000' ) ]
if any of the fields tested in the Where clause don’t still match their
values when the record was originally retrieved.
Note that with this technique, you should always retrieve the primary key
field (s) to uniquely identify the record in the Update statement. You don’t
have to retrieve and check all the record’s nonkey fields — just those that
are used in the transaction. That is, if you don’t care whether a field has
been changed before you complete the transaction, you don’t need to retrieve
or check it. Of course, you shouldn’t update any field that you don’t check
first...
HTH
Phil