Logical Locks (Magic xpa 2.x)
Logical locking is a simulation of a lock: No real external lock is issued to the database. Instead of locks, Magic xpa provides software checks to ensure that changes to record data that was changed by another user since it was first read, will not be written to the database.
With Logical locks, you cannot prevent other users from updating the same records, but can only prevent your own update if the record was changed. Magic xpa checks the record twice: in the lock start point and in the update stage.
The logical locking assists in preventing lost updates.
When a lock is requested and Magic xpa asks the gateway to read the record with a lock, the gateway reads the record and keeps the values of the read record.
When the UPDATE statement is then issued in the Record Suffix, all the columns, are added to the WHERE clause.
If in that period of time the record, which was not locked, has been changed by another user, the gateway sends a message that the record has been changed by another user, and the UPDATE fails.
Logical locking can be used in two situations. In each situation, the implementation is different:
-
Deferred transaction – Deferred transactions are kept in the cache and not in the database. This means that it is not possible to issue a physical locking.
-
Physical transaction – Physical transactions are used when the database/gateway does not support locking at the row level.
There are two kinds of logical locking:
-
Logical locking within physical transactions – Physical transactions are used when the database/gateway does not support locking at the row level. In this kind of locking, no external locks are sent to the database. However, sending DML commands will cause implicit locking that will be held until the end of the transaction.
-
Logical locking within deferred transactions – All the changes in the deferred transaction are saved in the transaction cache and only at the end of the transaction are they physically written to the database. This means that it is not possible to issue a physical locking.
Logical locking is not managed by the database. Therefore, it is checked twice. The first time it is checked is when a lock is required (the Lock phase) and the second time is when the update is sent to the database.
1. The Lock phase
The hook – Magic xpa checks that the record was not changed until the lock request. Magic xpa compares the values that were first read with the new values that the additional select retrieved.
2. The Update phase
At the end of the deferred transaction, all of the DML commands are sent to the database. In this stage, a second check that the records have not been changed, is done. This check is needed since from the logical lock phase until the end of the transaction, other users could have changed the records. Magic xpa sends the UPDATE/DELETE commands with a WHERE clause that will include the record fields as was defined in the Identify Modified Row property.
MSSQL 7 and above supports both logical and physical locks. To work with logical locks, the flag SQL_PHYSICAL_LOCKING=N should be specified in the Database Information property in the Database Properties dialog box.
ODBC supports only page level locking, which may result in locking problems. Therefore a logical lock strategy is used in which the record is not actually locked. Instead, Magic xpa verifies, for integrity reasons, that no one changes the record from the moment the record was logically locked until the update.
The current record is retrieved:
SELECT empnum, ename, deptno
FROM emp
WHERE empnum= 1
values: 1 , John, 30
Lock is requested:
SELECT empnum, ename, deptno
FROM emp
WHERE empnum= 1
values: 1 , John, 30
Assume that the deptno was changed to 40:
UPDATE emp SET deptno= 40
WHERE
empnum= 1 AND ename= John’ AND deptno= 30
The ODBC gateway also uses logical locking behavior because it cannot assume that a record lock or a FOR UPDATE statement is available in the accessed database.
