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.
You cannot lock system tables with this statement.
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.
LOCK TABLE Flights IN SHARE MODE; SELECT * FROM Flights WHERE orig_airport > 'OOO';
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');
LOCK TABLE Maps IN EXCLUSIVE MODE; SELECT MAX(map_id) + 1 FROM Maps; -- INSERT INTO Maps . . .