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

鎖的分類：

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

表鎖

偏讀，MyISAM存儲引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，并發(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; 命令就會發(fā)現(xiàn)所有數(shù)據(jù)表的in_use字段都為0了。

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

結(jié)論：

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

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

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

具體步驟如下：

首先是讀鎖，

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

lock table mylock read;

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

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

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

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

這個時候在會話1 中對mylock數(shù)據(jù)表進行修改操作

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

執(zhí)行就會發(fā)現(xiàn)報錯了，這是因為加了讀鎖后不能再對數(shù)據(jù)表進行修改操作了。

會話2 也如果對mylock表進行修改操作，會一直阻塞住，但不會報錯。如果這個時候會話1執(zhí)行unlock tables;進行解鎖，會話2的修改操作就會立刻執(zhí)行成功。

然后是寫鎖

在會話1中對mylock表加寫鎖

lock table mylock write;

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

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

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

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

會話1 查詢其他未鎖的表，會報錯

會話2 查詢其他表，能正常查詢。

分析表的鎖定

show status like 'table%';

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

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

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

行鎖

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

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

接下里就是會行鎖的理解，首先是建一個數(shù)據(jù)表，并且為這個數(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ù)

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

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

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

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

索引失效，行鎖變表鎖

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

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

首先還是開兩個會話，

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

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

然后會話2執(zhí)行update tets_innodb_lock set b='9001' where a=9; 對a=9進行修改操作，因為不是操作同一行數(shù)據(jù)，所以不會阻塞。

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

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

然后會話2 對其他行進行修改，就阻塞住了

間隙鎖

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

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

這個時候會話1 進行一個范圍性的修改操作，回車后，未提交

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

會話2 進行一個新增操作，插入一個a=2的記錄。 這邊就會被阻塞住。

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

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

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

如何手動鎖定一行

首先在命令行輸入begin;

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

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

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

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

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

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

總結(jié)

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

最新評論