Tutorial Addendum on SQL - Locks And Deadlocks

This affiliate describes:

    

    
• What are the types and levels of locks?
  
      



    
• How table akin locks plan in MySQL?
  
      



    
• How row akin locks plan in MySQL?
  
      



    

All addendum in affiliate are based MySQL 5.0 server.

    

Types and Levels of Locks

    

There are two types of locks:

    

    
• Read Lock - The bound data is aloof for apprehend by the accepted session.
  
      

  Other sessions can apprehend the bound data. But they can not address (update) the bound data.
  
      

  A apprehend lock is aswell alleged a aggregate lock.
  
      



    
• Write Lock - The bound data is aloof for address by the accepted session.
  
      

  Other sessions can not apprehend and address the bound data. A address lock is aswell alleged
  
      

  an absolute lock.
  
      



    

There are three levels of locks:

    

    
• Table Lock - The lock is set at the table level. All rows in the bound table
  
      

  are locked.
  
      



    
• Row Lock - The lock is set at the row level. Some rows of a table are locked.
  
      

  But additional rows are not locked.
  
      



    
• Column Lock - The lock is set at the cavalcade level. Some columns of a row are locked.
  
      

  But additional columns are not locked.
  
      



    

Table Akin Locks in MySQL

    

The LOCK TABLES account sets table akin locks on defined tables. It aswell unlocks all

    

previously locks:

    

    
LOCK TABLES tbl_name {READ | WRITE}, tbl_name {READ | WRITE}, ...; 

    

The Alleviate TABLES account unlocks all ahead locks:

    

    
UNLOCK TABLES;

    

The afterward analysis shows that how a bound table in one affair affects

    

its acceptance in addition session:

    

    
Session 1:                        Affair 2:

    

 

    
c:>mysqlinmysql test

    
mysql> Alert >;

    
>DROP TABLE IF EXISTS User;

    
>CREATE TABLE User (ID INT 

    
>   PRIMARY KEY, Name CHAR(8)); 

    
>INSERT INTO User Ethics (2, 

    
>    bill );

    
>-- Analysis 1;

    
>LOCK TABLES User READ;

    
                                  c:>mysqlinmysql test

    
                                  mysql> Alert >;

    
                                  >SELECT * FROM User;

    
                                  +----+------+

    
                                  |  2 | bill |

    
                                  +----+------+

    
                                  >INSERT INTO User Ethics (3,

    
                                  >    jack );

    
                                  (waiting for the lock to release)

    
>UNLOCK TABLES;

    
                                  (insert done)

    
>-- Analysis 2;

    
>LOCK TABLES User WRITE;

    
                                  >SELECT * FROM User;

    
                                  (waiting for the lock to release)

    
>UNLOCK TABLES;

    
                                  (select done)

    
                                  +----+------+

    
                                  |  2 | bill |

    
                                  |  3 | jack |

    
                                  +----+------+