MySQL主要有表鎖，行鎖和頁鎖，頁鎖用得少，本文主要介紹表鎖和行鎖。

一、鎖的分類

從對數(shù)據(jù)的操作類型來分，可以分為讀鎖和寫鎖；從對數(shù)據(jù)操作粒度來分，可分為表鎖和行鎖。

• 讀鎖(共享鎖)：針對同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會互相影響；

• 寫鎖(排他鎖)：當(dāng)前寫操作沒有完成前，會阻斷其他寫鎖和讀鎖；

• 表鎖：鎖住被操作的整張表；

• 行鎖：鎖住被操作表中的被操作行，其他行不受影響。

二、表鎖

1. 介紹：

表鎖偏向MyISAM存儲引擎，開銷小，加鎖快，無死鎖，粒度大，并發(fā)性差。下面建表演示表鎖的用法。

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20)
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

這里用了MyISAM引擎，這個(gè)引擎是寫優(yōu)先的，加了寫鎖后，其他線程不能對被鎖的表做任何操作，即使是查詢，所以如果寫操作很多，就會導(dǎo)致其他線程的讀操作難以執(zhí)行，大量的查詢sql被阻塞。

• 增加表鎖的語法：

lock table 表名1 read(write), 表名2 read(write) ……;

• 查看表上加過的鎖;

show open tables;

• 給mylock表加讀鎖，tblA加寫鎖：

lock table mylock read, tblA write;

• 釋放鎖：

unlock tables;

2. 表鎖演示：

讀鎖：

首先給mylock表加上讀鎖，然后打開兩個(gè)session，暫且將左邊的稱為session1，右邊的稱為session2，如下：

然后進(jìn)行如下操作：

• 在session1中執(zhí)行lock table mylock read，然后執(zhí)行select * from mylock;，結(jié)果是可以查詢出數(shù)據(jù)。即自己加了讀鎖，自己是可以查的；

• 在session2中執(zhí)行select * from mylock;，結(jié)果也是可以查詢出數(shù)據(jù)。說明讀鎖，大家都可以讀數(shù)據(jù)；

• 在session1中執(zhí)行update mylock set name = 'aa' where id = 1;，結(jié)果報(bào)了如下錯(cuò)誤：

ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

• session1給mylock表加了讀鎖，那么session1能讀其他的表嗎？我現(xiàn)在執(zhí)行select * from tblA;，結(jié)果是不行的，報(bào)了如下的錯(cuò)誤：

ERROR 1100 (HY000): Table 'tblA' was not locked with LOCK TABLES

• session2能讀tblA表嗎？執(zhí)行select * from tblA;，結(jié)果是可以的。

• session2中執(zhí)行update mylock set name = 'aa' where id = 1;，結(jié)果如下：

一直卡著不動(dòng)，說明阻塞了，要直到mylock表解鎖才能成功。

表讀鎖總結(jié)：

寫鎖：

給mylock表加上寫鎖，lock table mylock write，然后在session1和session2中對當(dāng)前表和其他表進(jìn)行讀寫操作，最后結(jié)論如下：

對于表讀鎖和表寫鎖，總結(jié)起來就是加了讀鎖，當(dāng)前session只能讀當(dāng)前表，其他session只有寫當(dāng)前表會被阻塞；加了寫鎖，當(dāng)前session只能對當(dāng)前表進(jìn)行讀寫，其他session對當(dāng)前表的讀寫都會被阻塞。所以表鎖一般偏讀，也就是一般不會加表寫鎖，加寫鎖可能會導(dǎo)致大量的查詢被阻塞。

3. 表鎖分析：

MySQL中有兩個(gè)變量，可以記錄表的鎖定情況，如下：

• Table_locks_immediate：表示可以立即獲取鎖的查詢次數(shù)，每次加1；

• Table_locks_waited：出現(xiàn)表級鎖爭用而發(fā)生等待的次數(shù)，每次加1；

查看這兩個(gè)變量的值的sql：

show status like 'table%';

三、行鎖

1. 介紹：

行鎖偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現(xiàn)死鎖，粒度小，并發(fā)性好。InnoDB支持事務(wù)，而MyISAM是不支持事務(wù)的，InnoDB默認(rèn)采用的也是行鎖，下面建表演示表鎖的用法。

create table col_lock(
    id int not null primary key auto_increment,
    name varchar(20)
) engine innodb;

insert into col_lock(name) values('a');
insert into col_lock(name) values('b');
insert into col_lock(name) values('c');
insert into col_lock(name) values('d');
insert into col_lock(name) values('e');

2. 行鎖總結(jié)：

innodb支持事務(wù)，并且默認(rèn)是自動(dòng)提交，為了演示行鎖，先執(zhí)行下面的sql把自動(dòng)提交關(guān)閉。

set autocommint = 0;

接下來看看session1和session2的各種操作情況：

3. 分析行鎖：

我們可以通過如下sql查看行鎖的爭奪情況：

show status like 'innodb_row_lock%';

執(zhí)行結(jié)果是：

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 57446 |
| Innodb_row_lock_time_avg      | 28723 |
| Innodb_row_lock_time_max      | 51618 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+

• Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量
• Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)長
• Innodb_row_lock_time_avg：每次等待所花的平均時(shí)間
• Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在獲取鎖等待最久的一次花的時(shí)間
• Innodb_row_lock_waits：系統(tǒng)啟動(dòng)到現(xiàn)在獲取鎖等待的總次數(shù)

四、索引失效行鎖變表鎖問題

這個(gè)是比較隱蔽的問題，很難發(fā)現(xiàn)，但確實(shí)存在。比如之前說的varchar類型的沒加單引號，會導(dǎo)致索引失效，那么這時(shí)候行鎖就會變?yōu)楸礞i。比如col_lock表的name字段是varchar類型的，先在name字段加索引，然后關(guān)閉自動(dòng)提交，執(zhí)行下面的語句：

update col_lock set name = aa where id = 1;

然后再另一個(gè)session中執(zhí)行：

update col_lock set name = 'bb' where id = 2;

本來操作的是不同的行，即使第一條語句還沒commit，第二條應(yīng)該也能執(zhí)行，但實(shí)際上不行，因?yàn)閍a沒加單引號，索引失效了，行鎖變成了表鎖。

五、間隙鎖的危害

有個(gè)tblA表，age字段是加了索引的，數(shù)據(jù)如下：

我們在這session1中執(zhí)行下面的update操作：

update tblA set birth = now() where age > 20 and age < 25;

其實(shí)也就是3條記錄都會被更新。執(zhí)行后，先不提交，在session2中執(zhí)行如下語句：

insert tblA(age,birth) values(22,now());

表中沒有age為22的，那現(xiàn)在就插入一條age為22的記錄，行鎖，兩邊操作不同的行，應(yīng)該不會有任何影響的，但是現(xiàn)在情況如下：

直接等待鎖都超時(shí)了，這就是間隙鎖。session1中commit了之后，session2中的insert語句才能執(zhí)行成功。

• 間隙：當(dāng)我們使用范圍條件檢索數(shù)據(jù)，請求共享或排他鎖時(shí)，innodb會給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖，對于在條件范圍內(nèi)但是不存在的記錄，比如age為22在age > 20 and age <25這個(gè)范圍內(nèi)，但是不存在這條記錄，這個(gè)就叫做間隙。innodb會對這個(gè)間隙加鎖，這就叫間隙鎖。