Physical Locks (Magic xpa 3.x)

Physical locks are managed by the database. The database manages the lock: opens it and releases it according to Magic xpa requests/statements.

When there is a physical lock, other users cannot change or lock the same record via the application nor via database tools.

Physical locks are always within a physical transaction. The lock starts only after the transaction has started and ends only when the transaction is ended by COMMIT or ROLLBACK.

There are two types of physical locking:

• Implicit Locking – DML commands cause the transaction to automatically lock until the end of the transaction. When Magic xpa sends UPDATE, DELETE, or INSERT commands, the database automatically locks all the rows in the DML results set without the user specifically asking for it. The duration of this lock will be from the DML command until the end of the transaction.

Note:

Even if you choose "No lock" in Magic xpa when a DML is issued, locks will automatically be issued in the database.

• Explicit Locking – Explicit locking is created by a user request, usually before a planned DML, in order to keep the data from being changed by other users.

The SELECT ... FOR UPDATE statement is available in Oracle, MSSQL, and DB2. These RDBMSs support row-level locking. Therefore, when a lock is requested according to the selected locking strategy, Magic xpa tells the gateway that the record should be read again with a lock. If a transaction has not been started, the gateway starts a transaction. Then the gateway reads the current record with the FOR UPDATE clause (except in MSSQL where it uses the UPDLOCK hint instead).

The procedure described above ensures that no application, including non-Magic xpa applications, will be able to update the record until the end of the transaction, which usually occurs after the update is done.

Process of Physical Locking

1. The Lock phase

The hook – Magic xpa checks that the record was not changed until the Lock phase. Magic xpa compares the values that were first read with the new values that the SELECT with lock retrieved.

2. The Update phase

In the Update phase, there is no check to see if the data was changed. This is because the record was locked and no other user could change it.

Magic xpa only locks tables that were accessed in Write mode. No locks will be issued for tables that were opened in Read access. This parameter is accessed in the Data Source repository (Ctrl+D) within the task.

Example

The current record is retrieved:

SELECT empnum, ename, deptno, rowid
FROM emp
WHERE rowid= 1111

Returned values: 1 , John, 30
The lock is requested:

SELECT empnum, ename, deptno, rowid
FROM emp
WHERE empnum= 1111
FOR UPDATE NO WAIT

Returned values: 1 , John, 30
Assume that the deptno was changed to 40:

UPDATE emp SET deptno= 40
WHERE rowid= 1111

In Batch tasks, when an Immediate locking strategy is used the gateway may be able to lock the whole data view, ahead of time.

In Oracle, a SELECT statement can be issued with a FOR UPDATE clause and an ORDER BY clause. Therefore, when the cursor is defined at the beginning of the task, it is declared with a FOR UPDATE clause. Then all the records are fetched from this cursor.

DB2 does not allow the procedure used by Oracle. Instead, the cursor is opened, and another cursor is opened with a FOR UPDATE clause for every record.