一. 背景知識(shí)
[事務(wù)（Transaction）、隔離級(jí)別、傳播機(jī)制]
二. 步入正題：表鎖和行鎖
1.1. 表鎖 vs 行鎖
在 MySQL 中鎖的種類有很多，但是最基本的還是表鎖和行鎖：表鎖指的是對(duì)一整張表加鎖，一般是 DDL 處理時(shí)使用，也可以自己在 SQL 中指定；而行鎖指的是鎖定某一行數(shù)據(jù)或某幾行，或行和行之間的間隙。行鎖的加鎖方法比較復(fù)雜，但是由于只鎖住有限的數(shù)據(jù)，對(duì)于其它數(shù)據(jù)不加限制，所以并發(fā)能力強(qiáng)，通常都是用行鎖來處理并發(fā)事務(wù)。表鎖由 MySQL 服務(wù)器實(shí)現(xiàn)，行鎖由存儲(chǔ)引擎實(shí)現(xiàn)，常見的就是InnoDb，所以通常我們?cè)谟懻撔墟i時(shí)，隱含的一層意義就是數(shù)據(jù)庫的存儲(chǔ)引擎為 InnoDb ，而 MyISAM 存儲(chǔ)引擎只能使用表鎖。
1.2. 表鎖
表鎖由 MySQL 服務(wù)器實(shí)現(xiàn)，所以無論你的存儲(chǔ)引擎是什么，都可以使用。一般在執(zhí)行 DDL 語句時(shí)，譬? ??? ??? ??? ?如?ALTER TABLE?就會(huì)對(duì)整個(gè)表進(jìn)行加鎖。在執(zhí)行 SQL 語句時(shí)，也可以明確對(duì)某個(gè)表加鎖。
1.2.1 操作演示（user_test創(chuàng)建表腳本見2.4.1）
-- 事物A中 顯示對(duì) user_test 加上讀鎖locktableuser_test read;select*fromuser_testwhereid=3;? -- 此時(shí)事物B中對(duì)其進(jìn)行update或者write操作start TRANSACTION;-- 手動(dòng)開啟事物insertintouser_test(age,name)values(18,'Tom');
結(jié)論：此時(shí)事物B一致處于等待事物A釋放鎖的狀態(tài)，最終會(huì)回去鎖超時(shí)
2.1. 行鎖（以下驗(yàn)證是在默認(rèn)的隔離級(jí)別（可重復(fù)讀的事務(wù)隔離級(jí)）下操作）
InnoDB? NEXT-KEY Locks，解決了在可重復(fù)讀的事務(wù)隔離級(jí)別下出現(xiàn)幻讀的問題。
什么是幻讀？
幻讀是在可重復(fù)讀的事務(wù)隔離級(jí)別下會(huì)出現(xiàn)的一種問題，簡(jiǎn)單來說，可重復(fù)讀保證了當(dāng)前事務(wù)不會(huì)讀取到其他事? ? ? ?務(wù)已提交的 UPDATE 操作。但同時(shí)，也會(huì)導(dǎo)致當(dāng)前事務(wù)無法感知到來自其他事務(wù)中的 INSERT 或 DELETE 操? ???? 作，這就是幻讀。
2.2. 關(guān)于行鎖我們要知道的
行鎖在 InnoDB 中是基于索引實(shí)現(xiàn)的，所以一旦某個(gè)加鎖操作沒有使用索引，那么該鎖就會(huì)退化為表鎖。
2.3 行鎖分類
2.3.1 從加鎖范圍劃分
a）記錄鎖（Record Locks）：存在與唯一索引包括主鍵索引 顧名思義，記錄鎖就是為某行記錄加鎖，它封鎖該行的索引記錄：
b）間隙鎖（Gap Locks）：
存在與非唯一索引中，鎖定開區(qū)間范圍內(nèi)的一段間隔，它是基于臨鍵鎖實(shí)現(xiàn)的。
間隙鎖基于非唯一索引，它鎖定一段范圍內(nèi)的索引記錄。間隙鎖基于下面將會(huì)提到的Next-Key Locking 算法，請(qǐng)務(wù)必牢記：使用間隙鎖鎖住的是一個(gè)區(qū)間，而不僅僅是這個(gè)區(qū)間中的每一條數(shù)據(jù)。
? 是一種加在兩個(gè)索引之間的鎖，或者加在第一個(gè)索引之前，或最后一個(gè)索引之后的間隙。有時(shí)候又稱為范圍（Range Locks），這個(gè)范圍可以跨一個(gè)索引記錄，多個(gè)索引記錄，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個(gè)范圍內(nèi)插入或修改記錄，保證兩次讀取這個(gè)范圍內(nèi)的記錄不會(huì)變，從而不會(huì)出現(xiàn)幻讀現(xiàn)象。很顯然，間隙鎖會(huì)增加數(shù)據(jù)庫的開銷，雖然解決了幻讀問題，但是數(shù)據(jù)庫的并發(fā)性一樣受到了影響，所以在選擇數(shù)據(jù)庫的隔離級(jí)別時(shí)，要注意權(quán)衡性能和并發(fā)性，根據(jù)實(shí)際情況考慮是否需要使用間隙鎖，大多數(shù)情況下使用 read committed 隔離級(jí)別就足夠了，對(duì)很多應(yīng)用程序來說，幻讀也不是什么大問題。
產(chǎn)生間隙鎖的條件（RR事務(wù)隔離級(jí)別下）：
3.1.? 使普通索引鎖定；
3.2.? 使用多列唯一索引；
3.3.? 使用唯一索引鎖定多行記錄。
c）臨鍵鎖（Next-Key Locks）臨鍵鎖存在于非唯一索引中（主鍵中不存在臨鍵鎖），該類型的每條記錄的索引上都存在這種鎖，它是一種特殊的間隙鎖，鎖定一段左開右閉的索引區(qū)間。臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區(qū)間。
注意: Next-Key 可以理解為一種特殊的間隙鎖，也可以理解為一種特殊的算法。通過臨建鎖可以解決幻讀的問題。 每個(gè)數(shù)據(jù)行上的非唯一索引列上都會(huì)存在一把臨鍵鎖，當(dāng)某個(gè)事務(wù)持有該數(shù)據(jù)行的臨鍵鎖時(shí)，會(huì)鎖住一段左開右閉區(qū)間的數(shù)據(jù)。需要強(qiáng)調(diào)的一點(diǎn)是，InnoDB 中行級(jí)鎖是基于索引實(shí)現(xiàn)的，臨鍵鎖只與非唯一索引列有關(guān)，在唯一索引列（包括主鍵列）上不存在臨鍵鎖 , 但是存在間隙鎖。臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務(wù)的隔離級(jí)別降級(jí)為RC，臨鍵鎖則也會(huì)失效。
2.3.2? 從兼容性和存在形態(tài)劃分
2.4. 操作演示
2.4.1 創(chuàng)建測(cè)試表并初始化數(shù)據(jù)
CREATETABLE`user_test`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT COMMENT'自增id',`age`int(11)unsignedNOTNULLCOMMENT'年齡',`name`varchar(16)NOTNULLCOMMENT'姓名',PRIMARY KEY(`id`)COMMENT'主鍵')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行鎖測(cè)試表';INSERTINTO`user_test`(`id`,`age`,`name`)VALUES(1,10,'Lee');INSERTINTO`user_test`(`id`,`age`,`name`)VALUES(2,24,'Ted');INSERTINTO`user_test`(`id`,`age`,`name`)VALUES(3,32,'Zed');INSERTINTO`user_test`(`id`,`age`,`name`)VALUES(4,45,'Talon');
2.4.2Record lock 記錄鎖驗(yàn)證
執(zhí)行一下sql
-- 事物A 中更新數(shù)據(jù) sql如下：-- 手動(dòng)開啟事物或者 beginSTART TRANSACTION;-- 根據(jù)非唯一索引列 UPDATE 某條記錄? (只會(huì)產(chǎn)生記錄鎖，不會(huì)產(chǎn)生間隙鎖)UPDATEuser_testSETname='LISHI'WHEREid=2;-- 等觀看完下面事物B在開啟事物commit;rollback;-- 事物B 也同時(shí)更新統(tǒng)一條記錄START TRANSACTION;UPDATEuser_testSETname='ZHANGSAN'WHEREid=2;commit;rollback;
此時(shí)事物B會(huì)出現(xiàn)如圖結(jié)果
事物A commit 或者 rollback 此時(shí)事物B會(huì)拿到鎖執(zhí)行成功；事物B更新其他id記錄不受影響：
START TRANSACTION;UPDATEuser_testSETname='ZHANGSAN'WHEREid=1;commit;
2.4.2Next-Key Locks 臨鍵鎖驗(yàn)證
首先在age字段上創(chuàng)建普通索引
ALTERTABLE`user_test` ADD INDEX `index_age`(`age`);
此時(shí)該表中 age 列潛在的臨鍵鎖有：
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],
事物A 中更新數(shù)據(jù) sql如下：
START TRANSACTION;-- 根據(jù)非唯一索引列 UPDATE 某條記錄select*fromuser_testwhereage=10forupdate-- 事物B此時(shí)插入或者更新age小于10大于1的一條記錄-- 插入一條記錄為age=7的記錄start TRANSACTION;insertintouser_test(age,name)values(7,'Tom');COMMIT;
-- 事物B更新其中一條記錄age為8也會(huì)被阻塞(雖然條件id是主鍵索引，更新的字段是普通索引，因此也會(huì)加上間隙鎖)start TRANSACTION;UPDATEuser_testSETage=8WHEREid=2;COMMIT;
此時(shí)事物B會(huì)出現(xiàn)如圖結(jié)果
事物A commit 或者 rollback 此時(shí)事物B會(huì)拿到鎖執(zhí)行成功
2.4.3. Gap Locks 間隙鎖驗(yàn)證
打開間隙鎖設(shè)置 首先查看 innodb_locks_unsafe_for_binlog 是否禁用：
show variableslike'innodb_locks_unsafe_for_binlog';
innodb_locks_unsafe_for_binlog：默認(rèn)值為OFF，即啟用間隙鎖。因?yàn)榇藚?shù)是只讀模式，如果想要禁用間隙鎖，需要修改 my.cnf（windows是my.ini） 重新啟動(dòng)才行。
默認(rèn)mac是沒有my.cnf文件的，因此要在 etc文件下創(chuàng)建 my.cnf文件（etc/my.cnf）
my.cnf內(nèi)容如下（主要添加：)
innodb_locks_unsafe_for_binlog=1
3.my.cnf內(nèi)容如下：
# Example MySQL config file formediumsystems.? #? # Thisisfor a system with little memory(32M-64M)whereMySQL plays# an important part,orsystems up to128MwhereMySQLisused together with# other programs(suchasa web server)? #? # MySQL programs look for option filesinasetof# locations which dependonthe deployment platform.? # You can copy this option file to one of those? # locations. For information about these locations,see:# http://dev.mysql.com/doc/mysql/en/option-files.html? #? #Inthis file,you can use alllongoptions that a program supports.# If you want to know which options a program supports,run the program# with the"--help"option.? # The following options will be passed to all MySQL clients? [client]default-character-set=utf8#password=your_passwordport=3306socket=/tmp/mysql.sock? # Here follows entries for some specific programs? ? # The MySQL server? [mysqld]character-set-server=utf8init_connect='SET NAMES utf8? port? ? ? ? = 3306? ? socket? ? ? = /tmp/mysql.sock? ? skip-external-locking? ? key_buffer_size = 16M? ? max_allowed_packet = 1M? ? table_open_cache = 64? ? sort_buffer_size = 512K? ? net_buffer_length = 8K? ? read_buffer_size = 256K? ? read_rnd_buffer_size = 512K? ? myisam_sort_buffer_size = 8M? ? character-set-server=utf8? ? init_connect='SETNAMES utf8'? innodb_locks_unsafe_for_binlog = 1# Don't listenona TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld runonthe same host.# All interaction with mysqld must be made via Unix socketsornamed pipes.# Note that using this option without enabling named pipesonWindows#(via the"enable-named-pipe"option)will render mysqld useless!#? #skip-networking # Replication Master Server(default)# binary loggingisrequired for replicationlog-bin=mysql-bin # binary logging format-mixed recommendedbinlog_format=mixed # required unique idbetween1and2^32-1# defaults to1if master-hostisnotset# but willnotfunctionasa master if omittedserver-id=1 # Replication Slave(comment out master section to use this)? ? #? # To configure this hostasa replication slave,you can choosebetween# two methods:? ? #? #1)Use the CHANGE MASTER TO command(fully describedinour manual)-#? ? the syntaxis:? ? #? #? ? CHANGE MASTER TO MASTER_HOST=<host>,MASTER_PORT=<port>,#? ? MASTER_USER=<user>,MASTER_PASSWORD=<password>;? ? #? #whereyou replace<host>,<user>,<password>byquoted stringsand#<port>bythe master's port number (3306 by default).? ? ? #? ? ? #? ? Example:? ? ? #? ? ? #? ? CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,? ? ? #? ? MASTER_USER='joe', MASTER_PASSWORD='secret';? ? ? #? ? ? # OR? ? ? #? ? ? # 2) Set the variables below. However, in case you choose this method, then? ? ? #? ? start replication for the first time (even unsuccessfully, for example? ? ? #? ? if you mistyped the password in master-password and the slave fails to? ? ? #? ? connect), the slave will create a master.info file, and any later? ? ? #? ? change in this file to the variables'valuesbelow will be ignoredand#? ? overriddenbythe content of the master.infofile,unless you shutdown#? ? the slave server,deletemaster.infoandrestart the slaver server.#? ? For that reason,you may want to leave the lines below untouched#(commented)andinstead use CHANGE MASTER TO(see above)? ? #? # required unique idbetween2and2^32-1#(anddifferentfromthe master)# defaults to2if master-hostisset# but willnotfunctionasa slave if omitted#server-id=2? ? #? # The replication master for this slave-required#master-host=<hostname>? ? #? ? ? # The username the slave will use for authentication when connecting? # to the master-required#master-user=<username>? ? #? ? ? # The password the slave will authenticate with when connecting to? # the master-required#master-password=<password>? ? #? mysqld# The port the masterislisteningon.# optional-defaults to3306#master-port=<port>? ? #? # binary logging-notrequired for slaves,but recommended#log-bin=mysql-bin? ? ? # Uncomment the following if you are using InnoDB tables? #innodb_data_home_dir=/usr/local/mysql/data#innodb_data_file_path=ibdata1:10M:autoextend#innodb_log_group_home_dir=/usr/local/mysql/data# You canset.._buffer_pool_size up to50-80%? ? ? # of RAM but beware of setting memory usage too high? #innodb_buffer_pool_size=16M#innodb_additional_mem_pool_size=2M#Set.._log_file_size to25%of buffer pool size#innodb_log_file_size=5M#innodb_log_buffer_size=8M#innodb_flush_log_at_trx_commit=1#innodb_lock_wait_timeout=50 [mysqldump]? ? ? ? quick? max_allowed_packet=16M [mysql]no-auto-rehash# Remove the next comment character if you arenotfamiliar with SQL#safe-updatesdefault-character-set=utf8 [myisamchk]key_buffer_size=20Msort_buffer_size=20Mread_buffer=2Mwrite_buffer=2M [mysqlhotcopy]interactive-timeout
此時(shí)再次查看間隙鎖是否開啟
唯一索引的間隙鎖
-- 創(chuàng)建test表并插入一些數(shù)據(jù)CREATETABLE`test`(`id`int(1)NOTNULLAUTO_INCREMENT,`name`varchar(8)DEFAULTNULL,PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERTINTO`test`VALUES('1','小羅');INSERTINTO`test`VALUES('5','小黃');INSERTINTO`test`VALUES('7','小明');INSERTINTO`test`VALUES('11','小紅');
在進(jìn)行測(cè)試之前，我們先來看看test表中存在的隱藏間隙：
(-∞, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +∞]
-- 執(zhí)行事物1如下START TRANSACTION;-- 查詢 id 在 5 - 11 范圍的數(shù)據(jù)并加記錄鎖 SELECT*FROM`test`WHERE`id`BETWEEN5AND11FORUPDATE;-- 事務(wù)B進(jìn)行插入數(shù)據(jù)操作 --? 注意：以下的語句不是放在一個(gè)事務(wù)中執(zhí)行，而是分開多次執(zhí)行，每次事務(wù)中只有一條添加語句START TRANSACTION;-- 事務(wù)2插入一條 id = 3，name = '小張1' 的數(shù)據(jù) INSERTINTO`test`(`id`,`name`)VALUES(3,'小張1');# 正常執(zhí)行-- 事務(wù)3插入一條 id = 4，name = '小白' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(4,'小白');# 正常執(zhí)行-- 事務(wù)4插入一條 id = 6，name = '小東' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(6,'小東');# 阻塞-- 事務(wù)5插入一條 id = 8， name = '大羅' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(8,'大羅');# 阻塞-- 事務(wù)6插入一條 id = 9， name = '大東' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(9,'大東');# 阻塞-- 事務(wù)7插入一條 id = 11， name = '李西' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(11,'李西');# 阻塞-- 事務(wù)8插入一條 id = 12， name = '張三' 的數(shù)據(jù)INSERTINTO`test`(`id`,`name`)VALUES(12,'張三');# 正常執(zhí)行-- 提交事務(wù)1，釋放事務(wù)1的鎖 COMMIT;
從上面我們可以看到，(5, 7]、(7, 11] 這兩個(gè)區(qū)間，都不可插入數(shù)據(jù)，其它區(qū)間，都可以正常插入數(shù)據(jù)。所以我們可以得出結(jié)論：當(dāng)我們給 (5, 7] 這個(gè)區(qū)間加鎖的時(shí)候，會(huì)鎖住 (5, 7]、(7, 11] 這兩個(gè)區(qū)間。
我們?cè)賮頊y(cè)試如果我們鎖住不存在的數(shù)據(jù)時(shí)，會(huì)怎樣：
-- 開啟事務(wù)1 START TRANSACTION;--? 查詢 id = 3 這一條不存在的數(shù)據(jù)并加記錄鎖 SELECT*FROM`test`WHERE`id`=3FORUPDATE;-- 延遲30秒執(zhí)行，防止鎖釋放 SELECTSLEEP(30);-- # 注意：以下的語句不是放在一個(gè)事務(wù)中執(zhí)行，而是分開多次執(zhí)行，每次事務(wù)中只有一條添加語句--? 事務(wù)2插入一條 id = 3，name = '小張1' 的數(shù)據(jù) INSERTINTO`test`(`id`,`name`)VALUES(2,'小張1');# 阻塞--? 事務(wù)3插入一條 id = 4，name = '小白' 的數(shù)據(jù) INSERTINTO`test`(`id`,`name`)VALUES(4,'小白');# 阻塞-- 事務(wù)4插入一條 id = 6，name = '小東' 的數(shù)據(jù) INSERTINTO`test`(`id`,`name`)VALUES(6,'小東');# 正常執(zhí)行-- 事務(wù)5插入一條 id = 8， name = '大羅' 的數(shù)據(jù) INSERTINTO`test`(`id`,`name`)VALUES(8,'大羅');# 正常執(zhí)行-- 提交事務(wù)1，釋放事務(wù)1的鎖 COMMIT;
我們可以看出，指定查詢某一條記錄時(shí)，如果這條記錄不存在，會(huì)產(chǎn)生間隙鎖。
結(jié)論
對(duì)于指定查詢某一條記錄的加鎖語句，如果該記錄不存在，會(huì)產(chǎn)生記錄鎖和間隙鎖，如果記錄存在，則只會(huì)產(chǎn)生記錄鎖，如：WHERE `id` = 5 FOR UPDATE;
對(duì)于查找某一范圍內(nèi)的查詢語句，會(huì)產(chǎn)生間隙鎖，如：WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
總結(jié)：
上述文檔是自己及其團(tuán)隊(duì)小伙伴們一起討論的結(jié)果，自己做了一下總結(jié)，很感謝團(tuán)隊(duì)成員劉昌力、劉明遠(yuǎn)、朱文彬、祁世松、桑萌萌、石偉男以及彭紹翔等他們的討論支持。
此文檔是對(duì)行鎖和表鎖的一個(gè)粗略的認(rèn)識(shí)，存在一定的不足、知識(shí)點(diǎn)的缺失、不完善等問題。希望大家能夠一起再完善一下，共同學(xué)習(xí)進(jìn)步，掌握知識(shí)技能，更好的正確高效的運(yùn)用到平時(shí)的工作當(dāng)中去，才是我們分享技術(shù)文檔的意思所在。
