SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—540440-003

2-156

LOCK TABLE Statement

Considerations for LOCK TABLE

Examples of LOCK TABLE

The LOCK TABLE statement locks a table (or the underlying tables of a view) and its

indexes, limiting other access to the table and its indexes while your process executes

DML statements. See Database Integrity and Locking on page 1-10.

LOCK TABLE is an SQL/MX extension.

table

is the name of the table or view to be locked. See Database Object Names on

page 6-12.

IN {SHARE | EXCLUSIVE} MODE

specifies the locking mode:

If you request a SHARE lock on a table locked with an EXCLUSIVE lock by

another user, your request waits until the EXCLUSIVE lock is released.

If you request an EXCLUSIVE lock on a table and any part of the table is locked by

another user, your request waits until the lock is released, or until your lock request

times out and an error message is returned.

Considerations for LOCK TABLE

Authorization Requirements

To lock a table, you must have authority to read the table. To lock a view, you must

have authority to read the view but not necessarily the tables underlying the view.

Modifying Default Locking

A SELECT statement automatically acquires SHARE locks unless you specify

EXCLUSIVE in the IN clause of the SELECT statement. The DELETE, INSERT, and

UPDATE statements automatically acquire EXCLUSIVE locks.

LOCK TABLE table IN {SHARE | EXCLUSIVE} MODE

SHARE Other processes can read, but not delete, insert, or update the table or

view.

EXCLUSIVE Other processes can read with READ UNCOMMITTED access, but

cannot read with READ COMMITTED or SERIALIZABLE access, and

cannot delete, insert, or update the table or view.