Locking - SQL Considerations (Magic xpa 4.x)

Records are locked to preserve data integrity and to give each user a consistent view, while providing maximum concurrency. Locking prevents a record or group of records from being changed while a user is viewing or modifying them.

Magic xpa provides three kinds of locking:

• Magic xpa Locking – Magic xpa manages the locks using the MGLOCK file. We use this type of locking when we do not want or cannot use the database locking mechanism. It is mostly used with ISAM databases and very seldom used for SQL databases.

• Physical Locking – Magic xpa issues real locks in the database. The database manages the locks and prevents any other user in any interface (application or database tools) from changing the record.

• Logical Locking – 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.

Transactions and locking are tightly bound in RDBMSs. Because SQL databases run in multi-user environments, indiscriminate locking can cause an application to severely limit user access. Different levels of locking are available in all the RDBMSs.

In ISAM a record is locked for updating and then immediately released when you leave it. In an RDBMS the lock is enforced at the beginning of the transaction and released only by a COMMIT or ROLLBACK operation.

During a normal operation, the RDBMS locks the view structures. Implicit locking is performed automatically and protects the data without any user intervention. Overriding default locking is known as explicit locking.

Implicit locking occurs automatically when SQL statements are executed. For example, the statements INSERT, UPDATE, and DELETE cause implicit locking so that data consistency and integrity are maintained during transactions.

Some RDBMSs, such as Oracle, acquire locks at a record level. Other RDBMSs acquire only page-level locks, which cause other records belonging to the same page to also be locked.

Locking Levels

There are two major levels of locks in SQL:

• Exclusive – not allowing other users to even read the records. An exclusive lock, which is automatically generated when an UPDATE statement is issued.

• Shared – letting other users read the records without modifying them in any way. A shared lock, which can be issued by a SELECT statement or by adding the FOR UPDATE clause at the end of a SELECT statement. A shared lock tells the SQL server that you plan to update the record, and that no updates will be allowed until this lock is released. If another user tries to update the record or to send a SELECT ... FOR UPDATE statement, an error message that the record has been locked by another user is sent to that user.

Default Locking

Magic xpa includes Physical and Deferred transaction modes. The default locking behavior for the Magic xpa SQL gateways is as follows:

Transaction Mode	Default Behavior
Physical	All SQL gateways (except for ODBC and Cache) use physical locking as their default locking strategy. ODBC and Cache always use logical locking, regardless of the transaction mode.
Deferred	All SQL gateways use logical locking as their locking strategy.

Related Topics

Escalating a Lock to a Table Lock

Enforcing Locks

Locking Duration

Locking and Transaction Processing

Table Access and Share Mode