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 . . .
