You can apply locks to entire databases, entire tables, disk pages, single rows, or index-key values. The size of the object that is being locked is referred to as the scope of the lock (also called the lock granularity). In general, the larger the scope of a lock, the more concurrency is reduced, but the simpler programming becomes.
You can lock an entire database. The act of opening a database places a shared lock on the name of the database. A database is opened with the CONNECT, DATABASE, or CREATE DATABASE statements. As long as a program has a database open, the shared lock on the name prevents any other program from dropping the database or putting an exclusive lock on it.
The following statement shows how you might lock an entire database exclusively:
DATABASE database_one EXCLUSIVE
This statement succeeds if no other program has opened that database. After the lock is placed, no other program can open the database, even for reading, because its attempt to place a shared lock on the database name fails.
A database lock is released only when the database closes. That action can be performed explicitly with the DISCONNECT or CLOSE DATABASE statements or implicitly by executing another DATABASE statement.
Because locking a database reduces concurrency in that database to zero, it makes programming simple; concurrent effects cannot happen. However, you should lock a database only when no other programs need access. Database locking is often used before applying massive changes to data during off-peak hours.
You can lock entire tables. In some cases, the database server performs this action automatically. You can also use the LOCK TABLE statement to lock an entire table explicitly.
The LOCK TABLE statement or the database server can place the following types of table locks:
No users can write to the table. In shared mode, the database server places one shared lock on the table, which informs other users that no updates can be performed. In addition, the database server adds locks for every row updated, deleted, or inserted.
No other users can read from or write to the table. In exclusive mode, the database server places only one exclusive lock on the table, no matter how many rows it updates. An exclusive table lock prevents any concurrent use of the table and, therefore, can have a serious effect on performance if many other programs are contending for the use of the table. However, when you need to update most of the rows in a table, place an exclusive lock on the table.
A transaction tells the database server to use table-level locking for a table with the LOCK TABLE statement. The following example shows how to place an exclusive lock on a table:
LOCK TABLE tab1 IN EXCLUSIVE MODE
The following example shows how to place a shared lock on a table:
LOCK TABLE tab2 IN SHARE MODE
The database server always locks an entire table while it performs operations for any of the following statements:
Completion of the statement (or end of the transaction) releases the lock. An entire table can also be locked automatically during certain queries.
You can avoid table locking when you CREATE or DROP an index using the ONLINE keyword. While the index is being created or dropped online, no DDL operations on the table are supported, but operations that were concurrent when the CREATE INDEX or DROP INDEX statement was issued can be completed. The specified index is not created or dropped until no other processes are concurrently accessing the table. Then locks are held briefly to write the system catalog data associated with the index. This increases the availability of the system, since the table is still readable by ongoing and new sessions. The following statement shows how to use the ONLINE keyword to avoid automatic table locking with a CREATE INDEX statement:
CREATE INDEX idx_1 ON customer (lname) ONLINE;
Extended Parallel Server allows you to lock a table with either the LOCK TABLE statement or the TABLE lock mode of a LOCK MODE clause in a CREATE TABLE statement. All transactions that access a table whose lock mode is set to TABLE acquire a table lock for that table, if the isolation level for the transaction requires the transaction to acquire any locks at all. The following statement shows how to use the TABLE lock mode when you create a table:
CREATE TABLE tab1 ( col1 ... ) LOCK MODE TABLE
You can use the ALTER TABLE statement to switch a table from one lock mode to any other lock mode (TABLE, PAGE, or ROW).
Whether you specify the TABLE lock mode for the LOCK MODE clause of a CREATE TABLE or ALTER TABLE statement, or use a LOCK TABLE statement to acquire a table lock, the effect is the same.
The TABLE lock mode is particularly useful in a data-warehousing environment where query efficiency increases because, instead of acquiring (or trying to acquire, depending on the isolation level) page- or row-level locks, the transaction acquires table locks. Table-level locks can significantly reduce the number of lock requests. The disadvantage of table locks is that they radically reduce update concurrency, but in a data warehousing environment this reduction is generally not a problem.
You can lock one row of a table. A program can lock one row or a selection of rows while other programs continue to work on other rows of the same table.
Row and key locking are not the default behaviors. You must specify row-level locking when you create the table. The following example creates a table with row-level locking:
CREATE TABLE tab1 ( col1... ) LOCK MODE ROW;
If you specify a LOCK MODE clause when you create a table, you can later change the lock mode with the ALTER TABLE statement. The following statement changes the lock mode on the reservations table to page-level locking:
ALTER TABLE tab1 LOCK MODE PAGE
In certain cases, the database server has to lock a row that does not exist. To do this, the database server places a lock on an index-key value. Key locks are used identically to row locks. When the table uses row locking, key locks are implemented as locks on imaginary rows. When the table uses page locking, a key lock is placed on the index page that contains the key or that would contain the key if it existed.
When you insert, update, or delete a key (performed automatically when you insert, update, or delete a row), the database server creates a lock on the key in the index.
Row and key locks generally provide the best performance overall when you update a relatively small number of rows because they increase concurrency. However, the database server incurs some overhead in obtaining a lock.
The database server stores data in units called disk pages. A disk page contains one or more rows. In some cases, it is better to lock a disk page than to lock individual rows on it. For example, with operations that require changing a large number of rows, you might choose page-level locking because row-level locking (one lock per row) might not be cost effective.
If you do not specify a LOCK MODE clause when you create a table, the default behavior for the database server is page-level locking. With page locking, the database server locks the entire page that contains the row. If you update several rows that are stored on the same page, the database server uses only one lock for the page.
Dynamic Server allows you to set the lock mode to page-level locking or row-level locking for all newly created tables for a single user (per session) or for multiple users (per server). You no longer need to specify the lock mode every time that you create a new table with the CREATE TABLE statement.
If you want every new table created within your session to be created with a particular lock mode, you have to set the IFX_DEF_TABLE_LOCKMODE environment variable. For example, for every new table created within your session to be created with lock mode row, set IFX_DEF_TABLE_LOCKMODE to ROW. To override this behavior, use the CREATE TABLE or ALTER TABLE statements and redefine the LOCK MODE clause.
Set the single-user lock mode if all of the new tables that you create in your session require the same lock mode. Set the single-user lock mode with the IFX_DEF_TABLE_LOCKMODE environment variable. For example, for every new table created within your session to be created with row-level locking, set IFX_DEF_TABLE_LOCKMODE to ROW. To override this behavior, use the CREATE TABLE or ALTER TABLE statements and redefine the LOCK MODE clause. For more information on setting environment variables, see the IBM Informix: Guide to SQL Reference.
Database administrators can use the multiple-user lock mode to create greater concurrency by designating the lock mode for all users on the same server. All tables that any user creates on that server will then have the same lock mode. To enable multiple-user lock mode, set the IFX_DEF_TABLE_LOCKMODE environment variable before starting the database server or set the DEF_TABLES_LOCKMODE configuration parameter.
Locking mode for CREATE TABLE or ALTER TABLE has the following rules of precedence, listed in order of highest precedence to lowest:
When you change the lock mode of an index from normal to coarse lock mode, index-level locks are acquired on the index instead of item-level or page-level locks, which are the normal locks. This mode reduces the number of lock calls on an index.
Use the coarse lock mode when you know the index is not going to change; that is, when read-only operations are performed on the index.
Use the normal lock mode to have the database server place item-level or page-level locks on the index as necessary. Use this mode when the index gets updated frequently.
When the database server executes the command to change the lock mode to coarse, it acquires an exclusive lock on the table for the duration of the command. Any transactions that are currently using a lock of finer granularity must complete before the database server switches to the coarse lock mode.
Locks on a CLOB or BLOB column are separate from the lock on the row. Smart large objects are locked only when they are accessed. When you lock a table that contains a CLOB or BLOB column, no smart large objects are locked. If accessed for writing, the smart large object is locked in update mode, and the lock is promoted to exclusive when the actual write occurs. If accessed for reading, the smart large object is locked in shared mode. The database server recognizes the transaction isolation mode, so if Repeatable Read isolation level is set, the database server does not release smart-large-object read locks before end of transaction.
When the database server retrieves a row and updates a smart large object that the row points to, only the smart large object is exclusively locked during the time it is being updated.
You can lock a range of bytes for a smart large object. Byte-range locks allow a transaction to selectively lock only those bytes that are accessed so that writers and readers simultaneously can access different byte ranges in the same smart large object.
For information about how to use byte-range locks, see your IBM Informix: Performance Guide.
Byte-range locks support deadlock detection. For information about deadlock detection, see Handling a Deadlock.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]