Project

General

Profile

Database Record Locking

The Progress programming model relies heavily on pessimistic record locking to maintain data integrity in concurrent applications. FWD emulates this locking behavior in the runtime environment, to ensure converted applications manage concurrent access to database records in exactly the same manner.

Locking is ignored for temp-tables, since in Progress temp-tables are private to a user context. As no sharing of data is possible in such a model, no locking is necessary. Consequently, locking operations on temp-tables are no-ops and this discussion of lock behavior will assume permanent tables are in use, unless otherwise indicated.

Lock Types and Characteristics

To implement pessimistic record locking, the Progress 4GL provides three types of locks: NO-LOCK, EXCLUSIVE-LOCK and SHARE-LOCK, where:

  • NO-LOCK represents the absence of a record lock. A session which obtains a record with NO-LOCK may read the record, but may neither edit nor delete it. Furthermore, other sessions may edit or delete the record while it is held with NO-LOCK, so the record's data may become stale while the session is using it.
  • SHARE-LOCK represents a "read" lock. Multiple sessions may simultaneously hold such a lock, allowing each session read-only access to the locked record. No session may acquire an EXCLUSIVE-LOCK while any other session holds a SHARE-LOCK. Thus, the data cannot be edited or deleted by another session while this lock is held, thereby guaranteeing the data is not stale while this lock is held. This lock may be acquired and held either outside or within a database transaction
  • EXCLUSIVE-LOCK represents a "write" lock. Only one session may hold such a lock at a time, which prevents any other session from acquiring either a SHARE-LOCK or an EXCLUSIVE-LOCK. The holder may edit or delete the locked record, and is guaranteed that the data will not become stale while this lock is held. This lock may be acquired and held only within a database transaction.

There are a number of ways to obtain record locks, both implicit and explicit. An attempt to edit a record upon which the current session holds a SHARE-LOCK will implicitly cause that lock to be upgraded to an EXCLUSIVE-LOCK. Locks commonly are obtained using data access language statements such as FIND, FOR, OPEN QUERY, etc. By default, a lock request (implicit or explicit) will block the session indefinitely if the lock is not available; however, locks also can be requested with the NO-WAIT option, which causes the data access request to fail if the requested lock is not immediately available. When failing, the NO-WAIT option will show an error message informing the client that the record could not be loaded; note that this is done unless the NO-ERROR option is specified for the statement which initiated the lock request.

When the data access statement has no lock type specified, the lock will always default to SHARE-LOCK. There is one exception to this rule: the CAN-FIND statement will default to NO-LOCK, if no lock option is specified.

During conversion, each time a lock and a NO-WAIT option is encountered with a data access statement, these clauses will be converted to a reference to a p2j.com.goldencode.p2j.persist.LockType constant. The following table shows how each lock maps to a LockType constant:

4GL Lock LockType Constant
NO-LOCK LockType.NONE
SHARE-LOCK LockType.SHARE
EXCLUSIVE-LOCK LockType.EXCLUSIVE
SHARE-LOCK NO-WAIT LockType.SHARE_NO_WAIT
EXCLUSIVE-LOCK NO-WAIT LockType.EXCLUSIVE_NO_WAIT

A lock can be released using the RELEASE statement or by calling the FIND CURRENT buffer NO-LOCK statement for the required buffer. In cases when the the record currently holds a lock of type EXCLUSIVE-LOCK and a transaction is still active - the lock will be downgraded to SHARE-LOCK until the transaction ends. Note that the RELEASE statement also will remove the record from the buffer (even if in some cases it remains locked). The syntax of this statement is:

RELEASE buffer [ NO-ERROR ].

where buffer is the buffer which needs to have its record released. When the NO-ERROR option is specified, any errors encountered during record release are suppressed - use the ERROR-STATUS function to check for errors.

The 4GL also provides the LOCKED function, which checks if a prior FIND ... NO-WAIT statement couldn't load the record as it is already locked by another session. For more details, please see the LOCKED Function section of the Database Field References chapter of this book.

Example 1:

find first person no-lock.

Converted code:

new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).first();

Details:

As the lock option is set to the FIND statement, a LockType.NONE parameter is passed to the FindQuery constructor.

Example 2:

find first person exclusive-lock no-wait.

Converted code:

new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.EXCLUSIVE_NO_WAIT).first();

Details:

This example shows how the NO-WAIT and lock options get combined into a single constructor parameter in the converted code.

Example 3:

find first person.

Converted code:

new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc").first();

Details:

In cases when the lock option is missing, the converted constructor for the data access statement will have no explicit lock specified as parameter: instead, the constructor will set the lock for this query to SHARE-LOCK.

Example 4:

for each person exclusive-lock no-wait:
...
end.

Converted code:

forEach(TransactionType.FULL, "loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;

   public void init()
   {
      RecordBuffer.openScope(person);
      query0 = new AdaptiveQuery(person, (String) null, null,
                                 "person.siteId asc, person.empNum asc",
                                 LockType.EXCLUSIVE_NO_WAIT);
   }

   public void body() { ... }
});

Details:

Here, note how the constructor for the converted query has as a parameter the LockType.EXCLUSIVE_NO_WAIT constant, which maps to both the EXCLUSIVE-LOCK and the NO-WAIT options.

Example 5:

for each person:
...
end.

Converted code:

forEach(TransactionType.FULL, "loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;

   public void init()
   {
      RecordBuffer.openScope(person);
      query0 = new AdaptiveQuery(person, (String) null, null,
                                 "person.siteId asc, person.empNum asc");
   }

   public void body() { ... }
});

Details:

In such cases, when the lock option is missing, the lock defaults to SHARE-LOCK and is automatically set by the query's constructor.

Example 6:

message can-find(first person share-lock).

Converted code:

message(new FindQuery(person, (String) null, null,
        "person.siteId asc, person.empNum asc",
        LockType.SHARE).hasAny());

Details:

Although the CAN-FIND function can take a lock type as a parameter, this does not mean it actually will load the record in the buffer. Instead, if a record is found but the specified lock can't be acquired, the CAN-FIND will block until the user presses CTRL-C or the lock is acquired. Note that only the SHARE-LOCK and NO-LOCK options can be set for this statement.

Example 7:

message can-find(first person share-lock no-wait).

Converted code:

message(new FindQuery(person, (String) null, null,
                      "person.siteId asc, person.empNum asc",
                      LockType.SHARE_NO_WAIT).hasAny());

Details:

When the NO-WAIT option is specified, the CAN-FIND will return false if the record was found and it could not be locked.

Example 8:

message can-find(first person no-lock).

is equivalent to

message can-find(first person).

Converted code:

message(new FindQuery(person, (String) null, null,
                      "person.siteId asc, person.empNum asc",
                      LockType.NONE).hasAny());

Details:

These two examples show that the default lock for the CAN-FIND function is NO-LOCK - thus, whether NO-LOCK is explicitly set or is omitted, the converted code looks the same. Note that in this case the converted code must explicitly specify the LockType.NONE parameter to the FindQuery constructor. If the constructor lock parameter is missing - as the FindQuery class is used to replace the FIND statement - the costructor will default the lock to SHARE-LOCK, which is incorrect for the CAN-FIND statement.

Lock State Transition Rules

Locks behave differently within and outside of transactions. Very specific rules apply to the release of locks, particularly when crossing a transaction boundary. This affects how the lock is downgraded, depending on the current lock and the transaction type.

Lock acquisition, use and release for the same record among several buffers within the same user context are communal and asymmetric:

  • Communal. If several buffers in the same context acquire different lock types on the same record, each buffer can access the record according to the rules of the most restrictive lock type acquired among all of the buffers, regardless of the lock type actually requested by each, individual buffer.
  • Asymmetric. The acquisition of a lock which is more restrictive than the level of lock held by any other buffer on the same record in that context escalates the communal lock type to that level for all the involved buffers. Likewise, any buffer is guaranteed to succeed in a request to lock a record that is as restrictive or less restrictive than the lock type held by any other buffer in the same context for the same record. Conversely, when releasing the lock, the most restrictive lock type held by any buffer sharing the communal lock governs the lock type held by all involved buffers. Essentially, each buffer maintains its own lock type to which it pins the communal lock. When the pinned lock type of all involved buffers drops to NO-LOCK, the communal lock is released.

The following code samples illustrate the lock type held on a single record through various state transitions. The color of the code indicates the lock type held after each statement is executed, according to the following legend.

Legend for Code Snippets - can be disabled on some platforms or browsers
No Record in Scope NO-LOCK SHARE-LOCK EXCLUSIVE-LOCK

Example 1:

001 do transaction:
002   find first person exclusive-lock.
003   person.emp-num = 100.
004   find current person no-lock.
005 end.

Converted code:

RecordBuffer.openScope(person);
 
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.EXCLUSIVE).first();
      person.setEmpNum(new integer(100));
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.NONE).current();
   }
});

Details:

At line 002 in the 4GL, the transaction a record is found with EXCLUSIVE-LOCK, then re-found with NO-LOCK at line 004. However, because we are inside a transaction, the lock is downgraded to SHARE-LOCK here, rather than released. After the transaction ends, the lock is released.

Example 2:

001 find first person no-lock.
002 do transaction:
003   find current person exclusive-lock.
004   person.emp-num = 100.
005   find current person no-lock.
006 end.
007 find current person no-lock.

Converted code:

RecordBuffer.openScope(person);
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).first();
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.EXCLUSIVE).current();
      person.setEmpNum(new integer(100));
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.NONE).current();
   }
});
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).current();

Details:

In this case, the buffer scope of the person buffer is larger than the transaction scope, because it begins at line 001, when the record is found with NO-LOCK. As in the previous example, it is downgraded rather than released at line 005, but the SHARE-LOCK remains even after the transaction ends at line 006. It is not until line 007 that the lock is finally released by the explicit find NO-LOCK statement.

Example 3:

001 find first person no-lock.
002 do transaction:
003   find current person exclusive-lock.
004   person.emp-num = 100.
005   undo, leave.
006 end.

Converted code:

RecordBuffer.openScope(person);
new FindQuery(person, (String) null, null,
               "person.siteId asc, person.empNum asc",
               LockType.NONE).first();
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.EXCLUSIVE).current();
      person.setEmpNum(new integer(100));
      undoLeave("blockLabel0");
   }
});

Details:

Even though the buffer scope is larger than the transaction scope in this case, the undo at line 005 causes the lock to revert at the end of the transaction to its state at the beginning of the transaction, NO-LOCK.

Example 4:

001 find first person no-lock.
002 do transaction:
003   repeat:
004     find current person exclusive-lock.
005     person.emp-num = 100.
006     undo, leave.
007   end.
008 end.

Converted code:

RecordBuffer.openScope(person);
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).first();
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      repeat(TransactionType.FULL, "loopLabel0", new Block()
      {
         public void body()
         {
            new FindQuery(person, (String) null, null,
                          "person.siteId asc, person.empNum asc",
                          LockType.EXCLUSIVE).current();
            person.setEmpNum(new integer(100));
            undoLeave("loopLabel0");
         }
      });
   }
});

Details:

In this case, the EXCLUSIVE-LOCK is acquired in a repeat block nested within the transaction scope at line 004. Even though the repeat block is undone at line 006, the exclusive lock is not released when the repeat block ends at line 007. It is not until the transaction exits at line 008 that the lock is released.

Note that even though the buffer scope is larger than the transaction scope, the lock is not downgraded to SHARE-LOCK when the transaction ends, unlike Example 2. The existence of the UNDO preempts this behavior and causes the lock instead to revert to its state at the beginning of the transaction, even though it remains at EXCLUSIVE-LOCK throughout the remainder of the transaction. Thus, had we initially found the record with SHARE-LOCK at line 001, it would still be locked with SHARE-LOCK at line 008.

Example 5:

001 find first person no-lock.
002 do transaction:
003   repeat:
004     find current person exclusive-lock.
005     person.emp-num = 100.
006     leave.
007   end.
008 end.
009 find current person no-lock.

Converted code:

RecordBuffer.openScope(person);
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).first();
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      repeat(TransactionType.FULL, "loopLabel0", new Block()
      {
         public void body()
         {
            new FindQuery(person, (String) null, null,
                          "person.siteId asc, person.empNum asc",
                          LockType.EXCLUSIVE).current();
            person.setEmpNum(new integer(100));
            leave("loopLabel0");
         }
      });
   }
});
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).current();

Details:

This example is similar to Example 4, however, without the UNDO, the EXCLUSIVE-LOCK is downgraded to SHARE-LOCK at the end of the transaction. It remains SHARE-LOCKED until it is explicitly released at line 009.

Example 6:

001 do transaction:
002   repeat:
003     find first person exclusive-lock.
004     person.emp-num = 100.
005     leave.
006   end.
007 end.

Converted code:

doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      repeat(TransactionType.FULL, "loopLabel0", new Block()
      {
         public void init()
         {
            RecordBuffer.openScope(person);
         }
 
         public void body()
         {
            new FindQuery(person, (String) null, null,
                          "person.siteId asc, person.empNum asc",
                          LockType.EXCLUSIVE).first();
            person.setEmpNum(new integer(100));
            leave("loopLabel0");
         }
      });
   }
});

Details:

In this case, the buffer scope is smaller than the transaction scope. Even though the record is no longer referenced after the end of the inner repeat loop, it remains locked through the end of the transaction, so that no other session can edit it before the transaction has been committed (or undone). However, the EXCLUSIVE-LOCK is downgraded to a SHARE-LOCK at the end of the buffer's scope. It is released entirely at the end of the transaction.

Example 7:

001 do transaction:
002   repeat:
003     find first person exclusive-lock.
004     person.emp-num = 100.
005     release person.
006   end.
007 end.

Converted code:

doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      repeat(TransactionType.FULL, "loopLabel0", new Block()
      {
         public void init()
         {
            RecordBuffer.openScope(person);
         }
 
         public void body()
         {
            new FindQuery(person, (String) null, null,
                          "person.siteId asc, person.empNum asc",
                          LockType.EXCLUSIVE).first();
            person.setEmpNum(new integer(100));
            RecordBuffer.release(person).
         }
      });
   }
});

Details:

Even though the record is released, its lock is not set to NO-LOCK until the transaction is committed - until then, the lock remains downgraded to SHARE-LOCK.

Example 8:

...
find first person share-lock.
person.emp-num = 1.
...

Converted code:

...
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.SHARE).first();
person.setEmpNum(new integer(1));
...

Details:

When the mutator for the person's emp-num field is executed, the lock for this record is automatically upgraded to EXCLUSIVE-LOCK, as the initial lock was SHARE-LOCK. Once this is done, no other session can obtain a share or exclusive lock on this record.

Example 9:

001 def buffer x-person for person.
002 find first x-person share-lock.
003 find first person no-lock.
004 do transaction:
005   find current person exclusive-lock.
006   person.emp-num = 100.
007   find current person no-lock.
008 end.
009 find current person no-lock.
010 find current x-person no-lock.

Converted code:

RecordBuffer.openScope(person, xPerson);
new FindQuery(xPerson, (String) null, null,
              "xPerson.siteId asc, xPerson.empNum asc",
              LockType.SHARE).first();
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).first();
doBlock(TransactionType.FULL, "blockLabel0", new Block()
{
   public void body()
   {
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.EXCLUSIVE).current();
      person.setEmpNum(new integer(100));
      new FindQuery(person, (String) null, null,
                    "person.siteId asc, person.empNum asc",
                    LockType.NONE).current();
   }
});
new FindQuery(person, (String) null, null,
              "person.siteId asc, person.empNum asc",
              LockType.NONE).current();
new FindQuery(xPerson, (String) null, null,
              "xPerson.siteId asc, xPerson.empNum asc",
              LockType.NONE).current();

Details:

When the two buffers reference the same record, once a buffer has acquired a SHARE-LOCK (in this case) or EXCLUSIVE-LOCK, any other buffer (within the same context) will not be able to downgrade the communal lock to NO-LOCK until all buffers holding the share or exclusive lock have relinquished it. Here, the SHARE-LOCK is finally relinquished on line 010, when the x-person buffer downgrades its lock to NO-LOCK.

FWD Implementation

FWD uses a runtime implementation of the pessimistic locking model. While this is not optimal, the reasons for using a runtime implementation instead of relying upon the backing database's pessimistic lock model are several:

  • The FWD pessimistic locking model is designed to emulate Progress' locking behavior exactly. Anything but an exact match will cause applications to behave differently than what the user expects in certain, concurrent situations. We cannot be certain the support of any given RDBMS vendor will match identically.
  • The persistence runtime requires information about the internal state of the locking implementation in order to do its work. Gathering this information from the RDBMS is not necessarily possible or practical.
  • While some RDBMS implementations offer the basic feature set needed upon which to build a locking implementation, Hibernate does not expose all of these features adequately. This is something we may remedy over time.

Consequently, external applications which update data in a converted system's database must be "good citizens", since there will be no database-level locks preventing them from editing data at inappropriate times. This can be cumbersome for new code or may introduce a difficult new requirement for existing, external applications. This also requires FWD to expose to external users APIs which access its application server's lock manager.

Low Level Interface

The low level interface is responsible for accessing the core locking implementation, which responds to requests to acquire and release locks. It is this interface's job to manage the locks held by each user context, and to respond to requests to lock, unlock, and query lock status for individual records, per context. The low level interface is not accessed directly by converted code, but rather is accessed internally by the persistence runtime used by converted code.

The LockManager Java interface was developed for the purpose of defining this interface. A particular concrete implementation is created at FWD server initialization - the implementation class to use is specified in the directory. The Persistence class uses the lock manager transparently to do its work; generally, the lock manager does not need to be accessed directly by application level code. There is one Persistence instance per physical database managed by a FWD application server. External applications can access a Persistence instance via remote method calls, or they may implement a remotable implemenation of the LockManager interface (see below). To date, a non-Java mechanism does not exist for external access.

The default implementation of the LockManager interface is InMemoryLockManager. An instance of this class resides in the server JVM. As the name suggests, all locking behavior is managed in the internal memory of the server process. Thus, coordinated locking is available only to objects which exist in the JVM server process. To maintain the integrity of concurrent record access, all lock requests must be routed through this object. If locking needs to be shared among external JVM instances, one of the mechanisms described above must be employed. The default mechanism for such coordination among separate FWD server instances is described below.

Each FWD server instance is responsible for managing record locks for the database instances to which it permanently connects (as configured in the FWD directory). Each such FWD server instance is said to be authoritative for those database instances, and each such database instance is said to be a local database with respect to that server. Databases which are accessed by a FWD server (server A) via an explicit connection request in business logic (i.e., the converted form of the Progress CONNECT statement), which are local to a different FWD server instance (server B), are said to be remote databases instances with respect to server A.

A FWD server instance (the authoritative server) is responsible for managing all record locks for its local database instances, including requests made by external FWD servers. Locking services for a remote database are accessed via a distributed implementation of the LockManager interface: RemoteLockManager. An instance of this class automatically is created for and used by a specialized Persistence instance, which is responsible for accessing a remote database. RemoteLockManager acts as a proxy for the remote server's corresponding, primary LockManager for the target database: as a method on the RemoteLockManager instance is invoked, the request is forwarded to the remote server and is handled there by a LockManagerMultiplexer implementation, which dispatches the request to the appropriate, local LockManager for the target database. A response is returned to the RemoteLockManager which initiated the request on the requesting server. Other than the increased latency to communicate between FWD servers, the use of the remote LockManager proxy mechanism is transparent, and is managed automatically by the FWD runtime environment. This mechanism presumes a direct, server-to-server connection can be established between the two FWD servers.

High Level Interface

The high level locking interface is provided by the RecordLockContext Java interface. It is intended for use only within the FWD runtime environment, to manage communal locks among RecordBuffer instances. A no-op implementation is used for temporary tables, and a package private, inner class implementation is used for permanent tables. When a RecordBuffer or query object needs to acquire or release a record lock, it does so through this interface. A RecordLockContext implementation uses the low level interface to perform the actual lock operations. This implementation, in collaboration with the RecordBuffer class, enforces the lock transition rules specified above. It is also responsible for ensuring the asymmetric rules of lock acquisition and release when multiple buffers within a user context access locks for the same record.

Usage from Business Logic

Locks can be acquired explicitly during record retrieval or implicitly during record retrieval or update. Explicit locking is managed by specifying a lock type when defining or using the various query implementations. Implicit locking occurs either when no lock type is specified (the various query implementations may elect default lock types), or when data model object (DMO) modification requires a lock upgrade.

Specifically, an implicit lock upgrade occurs when application code modifies the data within a DMO instance by invoking one of its mutator methods. This is achieved through the use of Java's dynamic proxy mechanism. When a DMO is defined by the application (via RecordBuffer.define or TemporaryBuffer.define), the application is given a proxy to the underlying DMO, rather than a direct reference. The proxy's invocation handler (implemented within RecordBuffer as an inner class) intercepts mutator method invocations on the DMO and upgrades the lock from SHARE to EXCLUSIVE if necessary. It is assumed a transaction is active when such an upgrade is attempted.

Usage from External Applications

External applications which need persistence access, unless they want to work with 4GL-compatible code, will use plain DMOs to access records. This is different than the first business logic case above, as the lock will not be upgraded automatically when the DMO mutator method is invoked, as there will be no anonymous proxy to intercept the call (RecordBuffer.define is not used in this case). Instead, the programmer must be careful when writing such code, as proper locks need to be acquired before any changes are performed to the record.

To ease the work with plain DMOs, FWD runtime provides mechanism to access the persistence framework (via methods the PersistenceFactory class). All persistence calls must be made using the Persistence instance returned by a PeristenceFactory.getInstace(Database) call. Once such as instance is provided, various methods are available to query, lock or find records. More details about how external applications can access the persistence framework can be found in the Accessing the Database from Hosted Services section of the FWD Developer Guide book, Integrating External Applications chapter.


© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.