Migrating Record Locking/Unlocking to MSSQL: Best Practices
Estimated reading time: 4 minutes
Overview
Although DGL tries to make SQL Server look like DB2 for IBM i, for some operations record locking behaves differently under DGL.
The record locking problem arises in three areas:
- The
Unlock
operation loses the ‘current’ position. - The
Update
operation keeps the record locked. - There is no support for the
Access(*NoLock)
keyword on read operations (CHAIN, READ, READP, READE, READPE).
Some background on record locking
DataGate for IBM i
DG/400 determines the type and duration of record locks depending on how the file is opened.
-
For read-only files, when a record is read, there is no lock requested on it. Even if some other application has the record lock, the reading application does not block on the lock, that is, the record is read in spite of being locked by somebody else (a dirty read is performed).
-
For files open for update, every time a record is read it is write-locked so that other updating applications cannot read it. The write lock is held until the record is updated or explicitly unlocked by the application or when the program uses the file to position or read another record.
Additionally for files opened for update, the read op-codes have an optionAccess(*NoLock)
1 to avoid locking the record.
DataGate Linear for SQL Server
DGL (using server cursors) also determines the locking characteristics based on how the file is opened.
-
For read-only files, DGL behaves like DG/400, that is, locks are neither placed nor considered on records being read.
-
The behavior of DGL when the file is opened for update is similar to DG/400 but with two significant differences:
- Update Operation: Updating a record does not release the lock on the record.
- Unlock Opeartion: Explicitly unlocking a record causes the ‘current record position’ to be lost, meaning you can not perform a subsequent sequential read (next/previous) without repositioning the file with a SET (SETLL/SETGT) or CHAIN.
Read - Update/Unlock Sequences
In a Loop
Tight loops involving an initial Chain/SetLL/SetGT
operation followed by sequential reads (Read/ReadP/ReadE/ReadPE
) with either Update/Unlock opearations in the loop can have the Unlock opearation removed from the loop. If the reads were done via Read/ReadP
within the loop, then add an Unlock
at the end of the loop. Alternatively, the loop could be converted to use Range operations.
Out of a Loop
The most demanding change is the one requiring segments of code involving CHAIN-UPDATE
. Combinations have to be studied and possibly modified.
- If the
CHAIN-UPDATE
happens in a tight loop, then at the end of the loop anUNLOCK
should be issued to release the last record updated. Note however that the record position will be lost after theUNLOCK
. - If the
CHAIN-UPDATE
is sprinkled throughout the code, then each case has to be closely studied to see if the current position is used in a subsequent read operation.
Access(*NoLock) Option on Reads
DGL uses SQL Server Cursors to implement file access. When a file is opened for update, it is not possible to tell SQL Server to not lock the record on a read, so this option is not valid for files opened for Update. If a read operation uses the Access(*NoLock)
1 option a runtime error will be rised.
You have two options to solve this problem:
-
A first alternative is to remove the
Access(*NoLock)
and follow theREAD' or 'CHAIN
with anUNLOCK
operation. The challenge with this approach is the side efect thatUnlock
has of loosing the file’s current position. -
Another approach is is to declare and open the file twice, once for input only and the other for update. Where the read appears with the
Access(*NoLock)
option, the file should be substituted with the one open for input only. By doing this, the application can take advantage of network blocking - yielding better performance. The challenge here is that it may be necessary to keep both files synchronized, particularly whenever aSETLL
orSETGT
(generically a seek) opeartion is done.