Informix database lock technology

INFORMIX uses the lock technology to solve the concurrency control problem of accessing the same object when multiple users access the database. INFORMIX supports complex and scalable locking technologies.

Lock type

INFORMIX has three different types of locks. They are used in different cases.

1. SHARED lock

The SHARED lock only retains the readability of objects. When a lock exists, the object cannot be changed. Multiple programs can apply the SHARED lock to the same object.

2. EXCLUSIVE lock

It can only be used by a single program. Used when the program wants to change the object. When other locks exist, EXCLUSIVE locks cannot be used. When E is used
After the XCLUSIVE lock, other locks cannot be used for the same object.

3. PROMOTABLE lock

To achieve the goal of updating. The PROMOTABLE lock can be stored in a record with a SHARED lock, but not in a place with a PROMOTABLE lock or EXCLUSIVE lock. When there is no other lock including SHARED lock on the record), when the program is preparing to change the lock record, the PROMOTABLE lock can be upgraded to the EXCLUSIVE lock. If the PROMOTABLE lock is set on the existing SHARED lock record, you need to delete the SHARED lock before the PROMOTABLE lock can be promoted to the EXCLUSIVE lock. The PROMOTABLE lock can only be supported in INFORMIX Universal Server.

Lock range

INFORMIX provides three different data locking methods, ranging from large to small: database, table, and record-level locks. The timing depends on the application status.

1. Database-level locks

You can use CONNECT, DATABASE, or create database statements to open a DATABASE. When the database is opened, the SHARED lock is set on the database. As long as the program opens a database, the SHARED lock will prevent other programs from deleting the database or setting the EXCLUSIVE lock on the database. You can use the statement DATABASE name EXCLUSIVE to lock the entire database. If another user is using the database, an error is returned.

Once the EXCLUSIVE lock is set, other programs cannot open the database, because a SHARED lock should be placed when it is opened. The database lock is released only when the database is closed. You can use DISCONNECT or CLOSE the DATABASE for display processing, or run other DATABASE statements for implicit processing. Generally, database-level EXCLUSIVE locks exclusively occupy database resources to prevent other programs from accessing the database. It makes the program very simple and does not produce concurrent results. It is often used in non-peak hours to change a large amount of data, such as the database backup process.

2. Table-Level Lock

INFORMIX provides two table-level locks: exclusive mode and share mode. You can lock the entire table. In some cases, this operation is performed automatically. When INFORMIX processes the following statements, it usually locks the entire TABLE: alter index, alter table, create index, drop index, rename column, and rename table. The lock is released when the statement ends or the transaction ends. In some query statements, INFORMIX also automatically locks the entire table.

You can use the lock table statement to display the whole TABLE. This statement allows you to set EXCLUSIVE or SHARED locks for the entire table. When your program reads data from the table, the SHARED lock prevents data updates in the table. INFORMIX Universal Server achieves more concurrent data protection by setting the isolation level.

Table-level EXCLUSIVE lock prevents concurrent use of the same table. Therefore, if many other programs want to use this table, the system performance will be seriously affected. Similar to database-level EXCLUSIVE locks, table-level EXCLUSIVE locks are often used to change a large amount of data during off-peak periods. For example, some applications do not update Tables during peak hours, and they can be updated on a regular basis in batches during off-peak periods.

Unlock table name is used to UNLOCK a table. When a transaction exists, the lock is lifted when the transaction ends.

3. Record-level, page-level, and key-word-level locks

A record in a table is the smallest object that can be locked. A program can lock a set of records or records, while other programs can operate on other records in the same table. The Universal Server stores data in disk pages. A disk page contains one or more records. In
In some cases, page-level locks are better than individual locks. Other database servers may not have page-level or key-word-level locks.

On the Universal Server, when creating a table, you can choose to use record-level locks or page-level locks. This option is not available for other database servers.
Page-level and record-level locks have the same effect. When the Universal Server needs to lock a record, it locks the record or record according to the lock mode during table creation.
Page. Under certain circumstances, the database server needs to lock a non-existent reCord. The effect is equivalent to placing a lock where the record will exist. When a table uses a record lock, it uses a key-word lock for the hypothetical record. When a table uses a page-Level Lock, an index page containing or possibly containing key words will be set with a key-Level Lock.

Lock Period

The period in which the program controls database-level locks. When the database is closed, the database lock level is released. Table-level, record-level, and index-level locks depend on the SQL statements used and whether transactions are used. If the database does not use transactions, that is, the transaction log does not exist and you have not used the commit work statement, the TABLE lock is released when the unlock table statement is run. When a transaction is used, the transaction ends and the table-level, record-level, and index-level locks are released.

Lock processing during Modification

When the database server retrieves a record through an update cursor, it sets a PROMOTABLE lock on the record. If this action succeeds, the database server knows that other programs cannot change this record. Because the PROMOTABLE lock is not exclusive, other programs can continue to read this record. It may take some time before the program that obtains the record executes the UPDATE or DELETE statement or simply retrieves the next record. This improves the performance. When it changes a record, the database server sets an EXCLUSIVE lock on this record. If it already has a PROMOTABLE lock, it changes the lock to the EXCLUSIVE state.

The period of the EXCLUSIVE lock depends on whether transactions are used. If transactions are not used, the modified record will be released when it is written to the disk. When a transaction is used, the lock is kept until the transaction ends. This action prevents other programs from rolling back records in the original state.

When a transaction is used, you only need to delete the record key-Level Lock. Use A key lock to solve the following errors: program A deletes A record, and program B inserts A record with the same key. Program A rolls back the transaction and restores the deleted records on the database server. What should I do with the records inserted by program B? By locking indexes, the database server inserts records only when program A commits transactions.

Because the Universal Server database Server manages its own locks, it can provide different types of locks. Other database servers implement locks by operating system features, so they cannot provide multiple options. Some operating systems provide lock functions externally through operating system services. In these systems, the database supports the set lock mode statement. Some operating systems do not support kernel-level features. In this case, the database locks by generating small files in the database directory. These files are suffixed with. lok. If your program uses a single SELECT statement or a cursor that does not use the for update statement to extract a record, this record will be extracted immediately no matter whether it is locked by an unfinished transaction or not. This will produce the best performance. When you use the cursor declared by for update, it locks the current record before extraction. If the current record already has a lock, the program will wait or return an error as the selection mode is different. When the next record is retrieved, the database checks whether the CURRENT record is updated using an UPDATE with where current.

Lock mode

The lock mode determines what results will be generated when the program encounters the locked data. When a program needs to extract or modify a lock record, there are the following situations:

1. The database immediately returns an error code to the program through the SQLCODE variable or SQLSTATE structure.

2. Before data is unlocked, the database suspends the program.

3. The database suspends the program for a period of time. If the lock has not been resolved, the database returns an error code to the program.

You can select the above result through the set lock mode.

If you like program waiting, this is the best choice for most programs), run the following statement: set lock mode to wait.

When the lock mode is set, the program often ignores the existence of other concurrent programs. If the program needs to access a record locked by another program, it waits for another program to unlock and then continues. The delay is often unpredictable.

The disadvantage of waiting for unlocking is that it may take a long time. If you cannot accept a long delay, the program can run the following statement: set lock mode to not wait to choose not to wait. When the program requires a lock record, it immediately returns an error code and the current SQL statement ends. At this time, the program must roll back the current transaction and try again. When the program starts, the database is initially set to not waiting.

When you use the universal server, you have another option. You can set the maximum wait time for the database. You can use the following statement: setlock mode to wait 18, giving the database a maximum WAIT time of 18 seconds. If the lock has not been unlocked yet, the error code is returned.

When each program selects the lock wait mode, a deadlock may occur. Deadlocks are mutual blocking between programs. Each Program sets a lock on the objects to be accessed by other programs. The universal server immediately detects deadlocks on a single network SERVER. If the program chooses the lock wait mode and returns the error code to the program, you will know that you have encountered a deadlock. In the case of multiple database servers, the universal server cannot detect them immediately. Each database server has a lock wait limit. If the timeout occurs, the database server considers a deadlock and returns the relevant error code. The database administrator can set and modify the maximum wait time.