Informix Guide to SQL: Syntax SQL Statements

Use the LOCK TABLE statement to control access to a table by other processes.

You can lock a table if you own the table or have the Select privilege on the table or on a column in the table, either from a direct grant or from a grant to PUBLIC. The LOCK TABLE statement fails if the table is already locked in exclusive mode by another process, or if an exclusive lock is attempted while another user has locked the table in share mode.

The SHARE keyword locks a table in shared mode. Shared mode allows other processes read access to the table but denies write access. Other processes cannot update or delete data if a table is locked in shared mode.

The EXCLUSIVE keyword locks a table in exclusive mode. Exclusive mode denies other processes both read and write access to the table.

Exclusive-mode locking automatically occurs when you execute the ALTER INDEX, ALTER TABLE, CREATE INDEX, DROP INDEX, RENAME COLUMN, RENAME TABLE, START VIOLATIONS TABLE, and STOP VIOLATIONS TABLE statements.

If your database was created with transactions, the LOCK TABLE statement succeeds only if it executes within a transaction. You must issue a BEGIN WORK statement before you can execute a LOCK TABLE statement.

Transactions are implicit in an ANSI-compliant database. The LOCK TABLE statement succeeds whenever the specified table is not already locked by another process.

The following guidelines apply to the use of the LOCK TABLE statement within transactions:

• You cannot lock system catalog tables.
• You cannot switch between shared and exclusive table locking within a transaction. For example, once you lock the table in shared mode, you cannot upgrade the lock mode to exclusive.
• If you issue a LOCK TABLE statement before you access a row in the table, no row locks are set for the table. In this way, you can override row-level locking and avoid exceeding the maximum number of locks that are defined in the database server configuration.
• All row and table locks release automatically after a transaction is completed. Note that the UNLOCK TABLE statement fails in a database that uses transactions.

The following example shows how to change the locking mode of a table in a database that was created with transaction logging:

BEGIN  WORK

LOCK  TABLE  orders  IN  EXCLUSIVE  MODE

 ...

COMMIT  WORK

BEGIN  WORK

LOCK  TABLE  orders  IN  SHARE  MODE

 ...

COMMIT  WORK  

In a database that was created without transactions, table locks set by using the LOCK TABLE statement are released after any of the following occurrences:

• An UNLOCK TABLE statement executes.
• The user closes the database.
• The user exits the application.

To change the lock mode on a table, release the lock with the UNLOCK TABLE statement and then issue a new LOCK TABLE statement.

The following example shows how to change the lock mode of a table in a database that was created without transactions:

LOCK  TABLE  orders  IN  EXCLUSIVE  MODE

 	.

	.

	.

UNLOCK  TABLE  orders  

	.

	.

	.

LOCK  TABLE  orders  IN  SHARE  MODE

Related statements: BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SET ISOLATION, SET LOCK MODE, and UNLOCK TABLE

For a discussion of concurrency and locks, see the Informix Guide to SQL: Tutorial.