LOCK TABLE statement

Allows a user to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction.

Explicitly locking a table is useful for:
  • avoiding the overhead of multiple row locks on a table (in other words, user-initiated lock escalation)
  • avoiding deadlocks

You cannot lock system tables with this statement.

Syntax

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

Once a table is locked in either mode, a transaction does not acquire any subsequent row-level locks on a table. Replace line 13 with this: 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 Hotels 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.

-- lock the entire table in share mode to avoid
-- a large number of row locks
LOCK TABLE Flights IN SHARE MODE;
SELECT *
FROM Flights
WHERE orig_airport > 'OOO';
-- lock the entire table in exclusive mode
-- for a transaction that will update many rows,
-- but where no single statement will update enough rows
-- acquire an exclusive table lock on the table.
-- In a row-level locking system, that transaction would
-- require a large number of locks or might deadlock.
LOCK TABLE HotelAvailability IN EXCLUSIVE MODE;
UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-10');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-11');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');

UPDATE HotelAvailability
SET rooms_taken = (rooms_taken + 2)
WHERE hotel_id = 194 AND booking_date = DATE('1998-04-12');
-- if a transaction needs to look at a table before
-- updating it, acquire an exclusive lock before
-- selecting to avoid deadlocks
LOCK TABLE People IN EXCLUSIVE MODE;
SELECT MAX(person_id) + 1 FROM PEOPLE;
-- INSERT INTO PEOPLE . . .
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE 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