LOCK TABLE statement LOCK TABLE statementTableslocking Lock escalationby user

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