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 withNO-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 withNO-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 anEXCLUSIVE-LOCK
while any other session holds aSHARE-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 transactionEXCLUSIVE-LOCK
represents a "write" lock. Only one session may hold such a lock at a time, which prevents any other session from acquiring either aSHARE-LOCK
or anEXCLUSIVE-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 toNO-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 PersistenceFactory.getInstance(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.