MySQL Tutorials - Herong's Tutorial Examples - Version 4.20, by Dr. Herong Yang
Table Level Locks in MySQL
A tutorial example is provided on how to use LOCK TABLES statements to locks at table level in MySQL.
The LOCK TABLES statement sets table level locks on specified tables. It also unlocks all previously locks:
LOCK TABLES tbl_name {READ | WRITE}, tbl_name {READ | WRITE}, ...;
The UNLOCK TABLES statement unlocks all previously locks:
UNLOCK TABLES;
The following test shows that how a locked table in one session affects its usage in another session:
Session 1: Session 2: mysql> PROMPT >; >DROP TABLE IF EXISTS User; >CREATE TABLE User (ID INT > PRIMARY KEY, Name CHAR(8)); >INSERT INTO User VALUES (2, > 'bill'); >-- Test 1; >LOCK TABLES User READ; mysql> PROMPT >; >SELECT * FROM User; +----+------+ | 2 | bill | +----+------+ >INSERT INTO User VALUES (3, > 'jack'); (waiting for the lock to release to be able to write) >UNLOCK TABLES; (insert done) >-- Test 2; >LOCK TABLES User WRITE; >SELECT * FROM User; (waiting for the lock to release to be able to read) >UNLOCK TABLES; (select done) +----+------+ | 2 | bill | | 3 | jack | +----+------+
We all know that multiple locks in multiple sessions could cause deadlocks. Let's see if we can create a deadlock in MySQL between two sessions:
Session 1: Session 2: mysql> PROMPT >; >DROP TABLE IF EXISTS User; >CREATE TABLE User (ID INT > PRIMARY KEY, Name CHAR(8)) > ENGINE=InnoDB; >INSERT INTO User VALUES (2, > 'bill'); >LOCK TABLES User WRITE; mysql> PROMPT >; >DROP TABLE IF EXISTS System; >CREATE TABLE System (ID INT > PRIMARY KEY, Name CHAR(8)); >INSERT INTO System VALUES (2, > 'black'); >SELECT * FROM System; +----+-------+ | 2 | black | +----+-------+ >LOCK TABLE System WRITE; >SELECT * FROM User; ERROR Table 'user' was not locked with LOCK TABLES
MySQL's behavior on my "SELECT * FROM User" is interesting. It errored out instead of putting the session into a waiting status. I think it is doing this in order to avoid the possibility of deadlocks.
After some additional tests, I found that MySQL's rule is very conservative: If a session is using LOCK TABLES, it can only work on the locked tables.
Last update: 2015.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
PHP Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures