Omnis Technical Note TNSQ0006
Non-Visual DB2 DAM and Record Locking
For Omnis Studio
By Gary Ashford
As with other databases, DB2 supports a variety of locking mechanisms
to prevent access to records which are potentially being updated by other
users. This article describes how to implement table and record level
locking within Omnis Studio. An example library is included which demonstrates
record locking using a set of simple data - see bottom of page for download.
Contents:
What to do at the server
What to do at the client machines
What to do in Studio
A note about isolation levels
What to do at the server:
Ensure that all DB2 users are logged out before proceeding. From the DB2
Control Center, navigate to the required database. Right-click and select
Configure. Select the Applications tab and change the value of 'Lock timeout'
to 1 (or some other suitable value.) Confirm this change.
From the Start Menu, or from the DB2\BIN folder, start the DB2 Command
Line Processor. Enter the following commands:
set client connect 2
connect to <db> user db2admin using db2admin
change isolation to rs
This sets client connections to be type 2 connects and establishes the
rules for *application-directed distributed units of work. It also permits
the DB2 isolation level to be changed.
* See the DB2 SQL Reference manual, Administration Guide and Connect Users's
Guide for more information on type 2 connections.
What to do at the client
machines:
From the Client configuration assistant > Advanced settings, ensure
that the hostname to be used is using the RS isolation level. (Read Stability.)
Note: This step is un-necessary when using DB2 Run Time Client 7.1 or
later.
What to do in Studio:
After logging-on to DB2 from a session object variable, change the transaction
mode to kSessionTranManual.
Do tSession.$transactionmode.$assign(kSessionTranManual) Returns #F
This enables the DAM to retain a lock on a table/record whilst another
statement operates on the table. To implement a lock, use a spare statement
object to perform a SELECT ... FOR UPDATE on the table, followed by a
$fetch() method. This action blocks further update operations on the records
addressed by the SELECT statement.
For record-level locking, a PRIMARY KEY column should be specified in
the SELECT, otherwise the behaviour defaults to table-level locking.
Calculate lockCursor
as tSession.$newstatement('LockStat')
; Create a new statement for the lock
Calculate SQL as con('select * from test where col1 = ',cRow.cCol1,' for
update')
Do lockCursor.$execdirect(SQL) Returns #F
Do lockCursor.$fetch(tempRow) ;; This action locks the record if successful
If len(lockCursor.$nativeerrortext)
; A 'timeout' error is returned if the record is already locked
OK
message Sorry {This record is currently locked by another user.}
Quit method
End If
When any updates required are complete, the session is committed (or rolled back). Since both statements are based on this session object, this also frees the lock held by the 'lockCursor'.
Do tSession.$commit() Returns #F ;; Commits any pending updates
DB2 UDB supports five types of isolation level, summarised below. These are more formally described in the DB2 SQL Reference, DB2 Call Level Interface Manual and DB2 Command Reference.
Repeatable Read (RR)
Any row read during a unit of work is not changed by another user, regardless
of their commitment strategy until the unit of work is complete. Any row
changed (or a row that is currently locked with an UPDATE row lock) by
another user cannot be read until it is committed.
Read Stability (RS)
Any row read during a unit of work is not changed by another user, regardless
of their commitment strategy until the unit of work is complete. Any row
changed (or a row that is currently locked with an UPDATE row lock) by
another user cannot be read until it is committed. RS does not completely
isolate the transaction from the effects of other concurrent transactions
that use a different commitment strategy.
Cursor Stability (CS)
Similar to RR and RS, except that level CS only ensures that the current
row of every updateable cursor is not changed by other concurrent transactions
using different commitment strategies. Thus, the rows that were read during
a unit of work can be changed by other users.
Uncommitted Read (UR)
For a SELECT INTO, a FETCH with a read-only cursor, subquery, or subselect
used in an INSERT statement, level UR allows:
Any row read during the unit of work to be changed by other transactions
that run under a different commitment definition.
Any row changed (or a row that is currently locked with an UPDATE row
lock) by another transaction running under a different commitment definition
to be read even if the change has not been committed.
For other operations, the rules of level CS apply.
No Commit (NC)
For all operations, the rules of level UR apply except: Commit and rollback
operations have no effect on SQL statements. Prepared statements are not
discarded, cursors are not closed, and LOCK TABLE locks are not released.
However, connections in the release-pending state are ended. Any changes
are effectively committed at the end of each successful change operation
and can be immediately accessed or changed by other transactions using
different commitment strategies.
Download:
Example Library and Word Document - tnsq0006.zip