Developers

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

A Note about Isolation Levels

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