# LOCK TABLE statement

The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction.

To lock a table, you must either be the database owner or the table owner.

Explicitly locking a table is useful to:

- Avoid the overhead of multiple row locks on a table (in other words, user-initiated lock escalation)
- Avoid deadlocks

You cannot lock system tables with this statement.

#### Syntax

    LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE

After a table is locked in either mode, a transaction does not acquire any subsequent row-level locks on a table. For example, if a transaction locks the entire `Flights` table in share mode in order to read data, a particular statement might need to lock a particular row in exclusive mode in order to update the row. However, the previous table-level lock on the `Flights` table forces the exclusive lock to be table-level as well.

If the specified lock cannot be acquired because another connection already holds a lock on the table, a statement-level exception is raised (SQLState X0X02) after the deadlock timeout period.

#### Examples

To lock the entire `Flights` table in share mode to avoid a large number of row locks, use the following statement:

    LOCK TABLE Flights IN SHARE MODE;
    SELECT *
    FROM Flights
    WHERE orig_airport > 'OOO';

You have a transaction with multiple UPDATE statements. Since each of the individual statements acquires only a few row-level locks, the transaction will not automatically upgrade the locks to a table-level lock. However, collectively the UPDATE statements acquire and release a large number of locks, which might result in deadlocks. For this type of transaction, you can acquire an exclusive table-level lock at the beginning of the transaction. For example:

    LOCK TABLE FlightAvailability IN EXCLUSIVE MODE;
    UPDATE FlightAvailability
    SET economy_seats_taken = (economy_seats_taken + 2)
    WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-03-31');

    UPDATE FlightAvailability
    SET economy_seats_taken = (economy_seats_taken + 2)
    WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-11');

    UPDATE FlightAvailability
    SET economy_seats_taken = (economy_seats_taken + 2)
    WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-12');

    UPDATE FlightAvailability
    SET economy_seats_taken = (economy_seats_taken + 2)
    WHERE flight_id = 'AA1265' AND flight_date = DATE('2004-04-15');

If a transaction needs to look at a table before updating the table, acquire an exclusive lock before selecting to avoid deadlocks. For example:

    LOCK TABLE Maps IN EXCLUSIVE MODE;
    SELECT MAX(map_id) + 1 FROM Maps;
    -- INSERT INTO Maps . . .

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause