Informix Database Lock Technology

Informix uses lock technology to resolve concurrent control issues with access to the same object in the case of multiuser access to the database. INFORMIX supports complex, scalable locking techniques.

Type of lock

Informix has three different kinds of locks. They are used in different situations.

1. Shared lock

Shared locks only preserve the readability of objects. The object cannot be changed when the lock is present. Multiple programs can add shared locks to the same object.

2. Exclusive lock

Can only be used by a single program. Used when the program wants to change the object. The exclusive lock is not available when other locks exist. When using the E

After Xclusive locks, other locks cannot be used on the same object.

3. Promotable Lock

The purpose of implementing the update. Promotable locks can be placed on records that already have shared locks, but not in places where promotable locks and exclusive locks are already in place. When there are no other locks (including shared locks) on the record, the promotable lock can be promoted to a exclusive lock when the program is ready to change the record of the lock. If you set a promotable lock on a record that already has a shared lock, you need to remove the shared lock before the promotable lock can be elevated to the exclusive lock. Promotable locks can only be supported in Informix Universal server.

Range of the lock

Informix provides three different ways of data locking, ranging from a large to a small database, table, record-level lock. The time to use depends on the application situation.

1. Database-Level Locks

You can open the database using Connect, database, or CREATE database statements. Opening the database sets a shared lock on the database. Shared locks prevent other programs from deleting the database or setting exclusive locks on the database as long as the program opens a database. You can lock the entire database with the statement database database name exclusive. If another user is using the database at this time, the operation returns an error.

Once the exclusive lock is set, the other program cannot open the database because a shared lock is placed when it is opened. Database locks are released only when the database is closed. You can use disconnect or close database to display the processing, or you can run other database statements implied processing. General database-level exclusive locks are exclusive database resources that prevent other programs from accessing the database. It makes the program very simple and does not produce concurrent effects. Frequently used during off-peak periods to change large amounts of data, such as database backup processes.

2. Table-Level Locks

Informix offers two modes of table-level locks: EXCLUSIVE mode and share mode. You can lock the whole table. In some cases, this operation is done automatically. When Informix processes the following statements, it generally locks the entire table: Alter INDEX, ALTER TABLE, CREATE Index, DROP INDEX, RENAME COLUMN, RENAME table. The end of the statement or the end of the transaction releases the lock. In some query statements, Informix also automatically locks the entire table.

You can use the Lock table statement to display the lock on the entire table. This statement allows you to set a exclusive lock or shared lock on the entire table. When you read data from a table, a shared lock prevents the data from being updated in the table. INFORMIX Universal Server enables greater concurrency data protection by setting the isolation level.

Table-Level exclusive locks prevent concurrent use of the same table. Therefore, system performance can be severely affected if many other programs use the table. Similar to database-level exclusive locks, table-level exclusive locks are often used to change large amounts of data during off-peak periods. For example, some applications do not update tables during peak periods, and they can be updated periodically in batches during off-peak hours.

Unlock by unlock table table name. When a transaction is present, the lock is unlocked at the end of the transaction.

3. Record level, page level, key word level lock

A record of a table is the smallest object that can be set to a lock. A program can lock a collection of records or records, while other programs can manipulate other records of the same table. The universal Server stores data in Disk pages (page). A disk page contains one or more records. In some cases, page-level locks are better than individual locks. There may not be page-level, key-level locks on other database servers.

On universal server, when you create a table, you can choose to use record-level or page-level locks. Other database servers do not offer this option.

Page-level, record-level locks have the same effect. When universal server needs to lock a record, lock the page where the record or record is located, based on the lock mode when the table was created. In some cases, the database server needs to lock a record that does not exist. The effect is equivalent to putting a lock on the record where it will be. When a table uses a record lock, a key word lock is used for the imaginary record. When a table uses page-level locks, index pages that contain or may contain key words are set to key-level locks.