LOCK TABLE

Use the LOCK TABLE statement to set up a lock that lasts for the duration of the current transaction, i.e. up to the next COMMIT or ROLLBACK. To hold the lock for any length of time, you have to be running in Transaction Mode.

Usage

LOCK TABLE table IN {SHARE | EXCLUSIVE} MODE

Notes

The command can be used if you need to perform a complex transaction containing several UPDATE or DELETE queries, and want to make sure that all the objects being manipulated are locked as early as possible within the transaction.

Example - how LOCK TABLE effects sessions


The following SQL should be run in 2 seperate sessions. Consider Session 1 - set to run in transaction mode and Session 2 in auto commit mode (default):

-- Session 1                         --     Session 2
SET MODE TRANSACTION;
LOCK TABLE mjbt1 IN EXCLUSIVE MODE;

                                     SELECT * FROM mjbt1;
                                     -- Result: LOCK TIMEOUT

COMMIT;

                                     SELECT * FROM mjbt1;
                                     -- Result: OK

LOCK TABLE mjbt1 IN SHARE MODE;

                                     SELECT * FROM mjbt1;
                                     -- Result: OK

                                     DELETE FROM mjbt1;
                                     -- Result: LOCK TIMEOUT

COMMIT;
                                     DELETE FROM mjbt1;
                                     -- Result: OK

If a lock is held for a long time, or a session or query is aborted whilst waiting for locks, then the locks will be dumped to a locks log file for future investigation.

The parameter da_dump_lkti sets how long a lock has to be held for in seconds before triggering the dump; the default period is 10 minutes, to disable the functionality set the parameter to 0.