Locks And Deadlocks

Part:   1   2 

This chapter describes:

• What are the types and levels of locks?
• How table level locks work in MySQL?
• How row level locks work in MySQL?

All notes in chapter are based MySQL 5.0 server.

Types and Levels of Locks

There are two types of locks:

• Read Lock - The locked data is reserved for read by the current session. Other sessions can read the locked data. But they can not write (update) the locked data. A read lock is also called a shared lock.
• Write Lock - The locked data is reserved for write by the current session. Other sessions can not read and write the locked data. A write lock is also called an exclusive lock.

There are three levels of locks:

• Table Lock - The lock is set at the table level. All rows in the locked table are locked.
• Row Lock - The lock is set at the row level. Some rows of a table are locked. But other rows are not locked.
• Column Lock - The lock is set at the column level. Some columns of a row are locked. But other columns are not locked.

Table Level Locks 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:

c:\>\mysql\bin\mysql test
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;
                                  c:\>\mysql\bin\mysql test
                                  mysql> PROMPT >;
                                  >SELECT * FROM User;
                                  +----+------+
                                  |  2 | bill |
                                  +----+------+
                                  >INSERT INTO User VALUES (3,
                                  >   'jack');
                                  (waiting for the lock to release)
>UNLOCK TABLES;
                                  (insert done)
>-- Test 2;
>LOCK TABLES User WRITE;
                                  >SELECT * FROM User;
                                  (waiting for the lock to release)
>UNLOCK TABLES;
                                  (select done)
                                  +----+------+
                                  |  2 | bill |
                                  |  3 | jack |
                                  +----+------+

(Continued on next part...)

Part:   1   2