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

一、鎖的分類(lèi)

從對(duì)數(shù)據(jù)的操作類(lèi)型來(lái)分，可以分為讀鎖和寫(xiě)鎖；從對(duì)數(shù)據(jù)操作粒度來(lái)分，可分為表鎖和行鎖。

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

• 寫(xiě)鎖(排他鎖)：當(dāng)前寫(xiě)操作沒(méi)有完成前，會(huì)阻斷其他寫(xiě)鎖和讀鎖；

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

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

二、表鎖

1. 介紹：

表鎖偏向MyISAM存儲(chǔ)引擎，開(kāi)銷(xiāo)小，加鎖快，無(wú)死鎖，粒度大，并發(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è)引擎是寫(xiě)優(yōu)先的，加了寫(xiě)鎖后，其他線(xiàn)程不能對(duì)被鎖的表做任何操作，即使是查詢(xún)，所以如果寫(xiě)操作很多，就會(huì)導(dǎo)致其他線(xiàn)程的讀操作難以執(zhí)行，大量的查詢(xún)sql被阻塞。

• 增加表鎖的語(yǔ)法：

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

• 查看表上加過(guò)的鎖;

show open tables;

• 給mylock表加讀鎖，tblA加寫(xiě)鎖：

lock table mylock read, tblA write;

• 釋放鎖：

unlock tables;

2. 表鎖演示：

讀鎖：

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

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

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

• 在session2中執(zhí)行select * from mylock;，結(jié)果也是可以查詢(xún)出數(shù)據(jù)。說(shuō)明讀鎖，大家都可以讀數(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)，說(shuō)明阻塞了，要直到mylock表解鎖才能成功。

表讀鎖總結(jié)：

寫(xiě)鎖：

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

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

3. 表鎖分析：

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

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

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

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

show status like 'table%';

三、行鎖

1. 介紹：

行鎖偏向InnoDB存儲(chǔ)引擎，開(kāi)銷(xiāo)大，加鎖慢，會(huì)出現(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;

接下來(lái)看看session1和session2的各種操作情況：

3. 分析行鎖：

我們可以通過(guò)如下sql查看行鎖的爭(zhēng)奪情況：

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í)長(zhǎng)
• 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ù)

四、索引失效行鎖變表鎖問(wèn)題

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

update col_lock set name = aa where id = 1;

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

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

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

五、間隙鎖的危害

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

我們?cè)谶@session1中執(zhí)行下面的update操作：

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

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

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

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

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

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