Table Level Locks in MySQL

MySQL Tutorials - Herong's Tutorial Examples

∟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

About This Book

Introduction of SQL

MySQL Introduction and Installation

Introduction of MySQL Programs

Perl Programs and MySQL Servers

PHP Programs and MySQL Servers

Java Programs and MySQL Servers

Datatypes and Data Literals

Operations and Expressions

Character Strings and Bit Strings