鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)線程訪問同一個(gè)系統(tǒng)資源的機(jī)制

鎖的分類：

• 從數(shù)據(jù)的操作類型分為：讀鎖和寫鎖
• 從數(shù)據(jù)的操作粒度分為：行鎖和表鎖

表鎖

偏讀，MyISAM存儲(chǔ)引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，并發(fā)度最低

添加鎖

lock table 數(shù)據(jù)表名 read/write，數(shù)據(jù)表2 read/write...

-- 比如我要給mylock表加讀鎖，給book表加寫鎖
lock table mylock read,book write;

查看那些表被加了鎖

show open tables;

第三列In_use字段如果為1則表示加鎖了

釋放所有表的鎖

unlock tables;

執(zhí)行完該命令后在執(zhí)行show open tables; 命令就會(huì)發(fā)現(xiàn)所有數(shù)據(jù)表的in_use字段都為0了。

加鎖對(duì)我們的數(shù)據(jù)操作和系統(tǒng)性能有什么影響

結(jié)論：

如果在會(huì)話1中給某個(gè)數(shù)據(jù)表加了讀鎖，其他會(huì)話就只能查看該表的數(shù)據(jù)，會(huì)話1不能對(duì)當(dāng)前表進(jìn)行修改操作，會(huì)報(bào)錯(cuò)；會(huì)話1也不能查詢其他未加鎖的數(shù)據(jù)表。而其他會(huì)話如果對(duì)該數(shù)據(jù)表進(jìn)行修改操作會(huì)一直阻塞，但不會(huì)報(bào)錯(cuò)，直到會(huì)話1執(zhí)行unlock tables; 命令將鎖釋放掉 才會(huì)解除阻塞狀態(tài)進(jìn)而執(zhí)行成功修改操作。其他會(huì)話還是可以查詢其他表的數(shù)據(jù)。

如果在會(huì)話1中給某個(gè)數(shù)據(jù)表加了寫鎖，會(huì)話1能對(duì)當(dāng)前表進(jìn)行查詢修改操作，但不能查詢其他未加鎖的數(shù)據(jù)表，其他會(huì)話不能對(duì)加了寫鎖的數(shù)據(jù)表進(jìn)行查詢修改操作，會(huì)阻塞住。

簡而言之，讀鎖會(huì)阻塞寫操作，但不會(huì)阻塞讀操作；寫鎖會(huì)阻塞其他會(huì)話的增刪改查操作。

具體步驟如下：

首先是讀鎖，

首先在當(dāng)前會(huì)話1 對(duì)mylock數(shù)據(jù)表加讀鎖

lock table mylock read;

然后查看表的當(dāng)前數(shù)據(jù)

另一個(gè)會(huì)話，另一個(gè)命令行窗口也能查看mylock數(shù)據(jù)表內(nèi)容。

然后在會(huì)話1中 查詢其他未鎖定的表的數(shù)據(jù)，發(fā)現(xiàn)還是報(bào)錯(cuò)了

但會(huì)話2 還是可以查詢其他表的數(shù)據(jù)，

這個(gè)時(shí)候在會(huì)話1 中對(duì)mylock數(shù)據(jù)表進(jìn)行修改操作

update mylock set name='aaa' where id=1;

執(zhí)行就會(huì)發(fā)現(xiàn)報(bào)錯(cuò)了，這是因?yàn)榧恿俗x鎖后不能再對(duì)數(shù)據(jù)表進(jìn)行修改操作了。

會(huì)話2 也如果對(duì)mylock表進(jìn)行修改操作，會(huì)一直阻塞住，但不會(huì)報(bào)錯(cuò)。如果這個(gè)時(shí)候會(huì)話1執(zhí)行unlock tables;進(jìn)行解鎖，會(huì)話2的修改操作就會(huì)立刻執(zhí)行成功。

然后是寫鎖

在會(huì)話1中對(duì)mylock表加寫鎖

lock table mylock write;

會(huì)話1中讀取自己當(dāng)前鎖的表，發(fā)現(xiàn)能讀

但是會(huì)話2 讀取mylock數(shù)據(jù)表就會(huì)出現(xiàn)阻塞，也不會(huì)報(bào)錯(cuò) 就一直等待，當(dāng)會(huì)話1釋放鎖后就能讀取到數(shù)據(jù)了。如果在會(huì)話1釋放鎖之前 會(huì)話2也能讀取到數(shù)據(jù)，那原因是mysql有緩存，如果之前查詢過一次，第二次查詢就會(huì)從緩存中取數(shù)據(jù)。

會(huì)話1 對(duì)mylock表進(jìn)行修改操作，發(fā)現(xiàn)也能成功

但會(huì)話2 連查詢都不可以 修改操作也肯定不行。

會(huì)話1 查詢其他未鎖的表，會(huì)報(bào)錯(cuò)

會(huì)話2 查詢其他表，能正常查詢。

分析表的鎖定

show status like 'table%';

這里兩個(gè)狀態(tài)變量記錄MySql內(nèi)部表級(jí)鎖定的情況，兩個(gè)變量分析如下：

• Table_locks_immediate ： 產(chǎn)生表級(jí)鎖定的次數(shù)，表示可以立即獲取鎖的查詢次數(shù)，每立刻獲取鎖加1
• Table_locks_waited: 鎖的等待情況，每等待一次，值加1，如果該數(shù)值很高則表示存在嚴(yán)重的表級(jí)鎖爭用情況

此外：MyISAM存儲(chǔ)引擎讀寫鎖調(diào)度是寫優(yōu)先，也就是說它不適合做為寫為主的表的存儲(chǔ)引擎。因?yàn)閷戞i后，其他線程就不能進(jìn)行任何操作，會(huì)阻塞。

行鎖

偏向于InnoDB存儲(chǔ)引擎，開銷大、加鎖慢、會(huì)出現(xiàn)死鎖、鎖定粒度最小、發(fā)生鎖沖突的概率最低，并發(fā)度最高。

MyISAM和InNoDB存儲(chǔ)引擎最大的區(qū)別是：事務(wù)是否支持，表鎖與行鎖

接下里就是會(huì)行鎖的理解，首先是建一個(gè)數(shù)據(jù)表，并且為這個(gè)數(shù)據(jù)表的兩列都創(chuàng)建了單值索引

create index idx_til_a on text_innodb_lock(a);
create index idx_til_a on text_innodb_lock(b);

然后查詢一次數(shù)據(jù)

首先還是一樣開兩個(gè)會(huì)話，mysql事務(wù)的隔離級(jí)別默認(rèn)是可重復(fù)讀。兩個(gè)會(huì)話都想將自動(dòng)提交關(guān)閉set autocommite=0;, 然后會(huì)話1對(duì)一個(gè)表進(jìn)行修改操作，但沒有提交，會(huì)話2查詢這個(gè)表的數(shù)據(jù)能看到的是會(huì)話1修改前的值，然后會(huì)話1會(huì)話2都提交，會(huì)話2再讀就寫修改后的值了。這是之前事務(wù)的知識(shí)。

假如這兩個(gè)會(huì)話，會(huì)話1先對(duì)這個(gè)數(shù)據(jù)表a=1的數(shù)據(jù)進(jìn)行了修改，但沒有提交，這個(gè)時(shí)候會(huì)話2也對(duì)a=1的數(shù)據(jù)進(jìn)行修改操作，會(huì)話2的修改操作就會(huì)阻塞住，但不會(huì)報(bào)錯(cuò)。然后會(huì)話1提交，這時(shí)候會(huì)話2的修改操作也就解除阻塞了，然后會(huì)話2再提交

也就是兩個(gè)會(huì)話 不能對(duì)同一行數(shù)據(jù)進(jìn)行操作，會(huì)話2的更新操作會(huì)阻塞，需要等到會(huì)話1提交事務(wù)才能解除阻塞。

但如果兩個(gè)會(huì)話 不是操作同一行數(shù)據(jù)則不會(huì)阻塞。

索引失效，行鎖變表鎖

在我們的SQL如果導(dǎo)致了索引失效，那么就會(huì)把本來的行鎖變?yōu)楸礞i

上面的數(shù)據(jù)表text_innodb_lock中 a字段是數(shù)字型，b字段是字符型

首先還是開兩個(gè)會(huì)話，

關(guān)閉自動(dòng)提交set autocommit=0;

會(huì)話1執(zhí)行update tets_innodb_lock set b='4001' where a=4; 對(duì)a=4進(jìn)行修改操作，但還沒有提交

然后會(huì)話2執(zhí)行update tets_innodb_lock set b='9001' where a=9; 對(duì)a=9進(jìn)行修改操作，因?yàn)椴皇遣僮魍恍袛?shù)據(jù)，所以不會(huì)阻塞。

但如果索引失效，行鎖就會(huì)變?yōu)楸礞i

會(huì)話1 對(duì)第三行進(jìn)行修改操作，但未提交，這里故意將b=‘4000’ 寫成了b=4000 使索引失效

然后會(huì)話2 對(duì)其他行進(jìn)行修改，就阻塞住了

間隙鎖

現(xiàn)在表的數(shù)據(jù)如下

為了做云計(jì)算和大數(shù)據(jù)分析，數(shù)據(jù)最好是連續(xù)的，可是上面并沒有a=2的記錄。

這個(gè)時(shí)候會(huì)話1 進(jìn)行一個(gè)范圍性的修改操作，回車后，未提交

update test_innodb_lock set b='aaa' where a>1 and a<6;

會(huì)話2 進(jìn)行一個(gè)新增操作，插入一個(gè)a=2的記錄。 這邊就會(huì)被阻塞住。

然后會(huì)話1提交后 會(huì)話2的阻塞才會(huì)被解除。

當(dāng)我們使用范圍條件而不是相等條件檢索數(shù)據(jù)，innodb會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖，對(duì)于符合范圍查詢條件但并不存在的記錄就交間隙。

危害就是：它會(huì)鎖定整個(gè)范圍內(nèi)的所有索引鍵值，即使這個(gè)鍵值不存在，某些場景下會(huì)對(duì)系統(tǒng)的性能造成傷害。

如何手動(dòng)鎖定一行

首先在命令行輸入begin;

然后要鎖的哪一行，就先查詢那一行，在where條件后面加上for update

這個(gè)時(shí)候這一行的數(shù)據(jù)就被鎖了 你只需要進(jìn)行你的操作即可，然后你再提交

在上面的表鎖 可以通過show status like 'table%'命令來查看表的一些鎖定情況，

當(dāng)然 行鎖也有show status like 'innodb_row_lock%'

這各個(gè)參數(shù)的意思是：

• Innodb_row_locak_current_waits：當(dāng)前正在等待鎖定的數(shù)量
• Innodb_row_locak_time：從系統(tǒng)啟動(dòng)到現(xiàn)在，鎖定總時(shí)間長度
• Innodb_row_locak_time_avg：每次等待所花的平均時(shí)間
• Innodb_row_locak_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在，等待時(shí)間最長的一次時(shí)間
• Innodb_row_locak_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在，總共等待的次數(shù)

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論