我們首先需要知道的一個(gè)大前提是：mysql的鎖是由具體的存儲引擎實(shí)現(xiàn)的。所以像Mysql的默認(rèn)引擎MyISAM和第三方插件引擎 InnoDB的鎖實(shí)現(xiàn)機(jī)制是有區(qū)別的。可根據(jù)不同的場景選用不同的鎖定機(jī)制。

Mysql有三種級別的鎖定：表級鎖定、頁級鎖定、行級鎖定

一、定義

每次鎖定的是一張表的鎖機(jī)制就是表級別鎖定(table-level)。它是MySQL各存儲引擎中粒度最大的鎖定機(jī)制。

二、優(yōu)缺點(diǎn)

1. 優(yōu)點(diǎn)

實(shí)現(xiàn)邏輯簡單，開銷小。

獲取鎖和釋放鎖的速度快。

由于表級鎖一次會將整個(gè)表鎖定，所以能很好的避免死鎖問題。

2. 缺點(diǎn)

由于鎖粒度最大，因此出現(xiàn)爭用被鎖定資源的概率也會最高，致使并發(fā)度十分低下。

三、支持存儲引擎

使用表級鎖定的主要有MyISAM，MEMORY，CSV等一些非事務(wù)性存儲引擎。

四、表級鎖類型

MySQL的表級鎖有兩種類型：表共享讀鎖(Table Read Lock)和表獨(dú)占寫鎖(Table Write Lock)。

鎖模式的兼容性：

對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫操作；

對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；

MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的。當(dāng)一個(gè)線程獲得對一個(gè)表的寫鎖后，只有持有鎖的線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

五、如何加表鎖

在執(zhí)行查詢語句(select)前，會自動給涉及的所有表加讀鎖

在執(zhí)行更新操作(update、delete、insert等)前，會自動給涉及的表加寫鎖。這個(gè)過程并不需要用戶干預(yù)，因此不需要直接用lock table命令給MyISAM表顯式加鎖。

顯示加寫鎖：

// 當(dāng)一個(gè)線程獲得對一個(gè)表的寫鎖后，只有持有鎖的線程可以對表進(jìn)行更新操作。

// 其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

// test表將會被鎖住，另一個(gè)線程執(zhí)行select * from test where id = 3;將會一直等待，直到test表解鎖

LOCK TABLE test WRITE;

顯示加讀鎖

// test表將會被鎖住，另一個(gè)線程執(zhí)行select * from test where id = 3;不會等待

// 執(zhí)行UPDATE test set name='peter' WHERE id = 4;將會一直等侍，直到test表解鎖

LOCK table test READ;

顯示釋放鎖：

UNLOCK TABLES;

需要注意的是，在同一個(gè)SQL session里，如果已經(jīng)獲取了一個(gè)表的鎖定，則對沒有鎖的表不能進(jìn)行任何操作，否則會報(bào)錯(cuò)。

// 鎖定test表

LOCK table test WRITE;

// 操作鎖定表沒問題

SELECT * from test where id = 4;

// 操作沒有鎖的表會報(bào)錯(cuò)

SELECT * from bas_farm where id =1356

報(bào)錯(cuò)：[Err] 1100 - Table 'bas_farm' was not locked with LOCK TABLES。這是因?yàn)镸yISAM希望一次獲得sql語句所需要的全部鎖。這也正是myisam表不會出現(xiàn)死鎖的原因。

當(dāng)然，你也不必?fù)?dān)心，MyISAM引擎的默認(rèn)方式是會給同一個(gè)session里的所有表都加上鎖的，不會麻煩你自己顯示操作的。

六、查看表級鎖爭用情況

執(zhí)行：show status like ‘table%’;

mysql> show status like 'table%';

+----------------------------+-----------+

| Variable_name | Value |

+----------------------------+-----------+

| Table_locks_immediate | 20708 |

| Table_locks_waited | 0 |

+----------------------------+-----------+

Table_locks_immediate：產(chǎn)生表級鎖定的次數(shù)；

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

如果Table_locks_waited狀態(tài)值比較高，那么說明系統(tǒng)中表級鎖定爭用現(xiàn)象比較嚴(yán)重，就需要進(jìn)一步分析為什么會有較多的鎖定資源爭用了。

七、優(yōu)化表級鎖定

優(yōu)化表級鎖時(shí)的最大問題是：提高并發(fā)度

###1. 通過減少查詢時(shí)間縮短鎖定時(shí)間

縮短鎖定時(shí)間的總體原則是：讓Query執(zhí)行時(shí)間盡可能的短。

盡量減少大的、復(fù)雜的Query，將復(fù)雜Query分拆成幾個(gè)小的Query分步執(zhí)行；

盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速；

盡量讓MyISAM存儲引擎的表只存放必要的信息，控制字段類型；

利用合適的機(jī)會優(yōu)化MyISAM表數(shù)據(jù)文件。

###2. 設(shè)置可并發(fā)插入：concurrent_insert=2

MyISAM的表鎖雖是讀寫互相阻塞的，但依然能夠?qū)崿F(xiàn)并行操作。MyISAM存儲引擎有一個(gè)控制是否打開Concurrent Insert(并發(fā)插入)功能的參數(shù)選項(xiàng)：concurrent_insert，取值范圍為0，1，2。

concurrent_insert=0，不允許并發(fā)插入。

concurrent_insert=1，如果MyISAM表中沒有空洞(即表的中間沒有被刪除的行)，MyISAM允許在一個(gè)線程讀表的同時(shí)，另一個(gè)線程從表尾插入記錄。這是MySQL的默認(rèn)設(shè)置；

concurrent_insert=2，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄；

所以，我們可通過設(shè)置concurrent_insert=2，同時(shí)定期在系統(tǒng)空閑時(shí)段執(zhí)行optimize table tableName語句來整理空間碎片，收回因刪除記錄而沒有真正釋放的空間，從而提高并發(fā)。optimize參考：mysql中OPTIMIZE TABLE的作用及使用

###3. 合理設(shè)置讀寫優(yōu)先級

MyISAM存儲引擎默認(rèn)是寫優(yōu)先級大于讀優(yōu)先級。即使是寫請求后到，寫鎖也會插到讀鎖請求之前。

但是，有時(shí)像修改文章點(diǎn)擊數(shù) 操作是不那么重要的，我們希望的是讀更快，此時(shí)我們可以這樣：

UPDATE LOW_PRIORITY article SET click_num=134 WHERE id = 823

LOW_PRIORITY使得系統(tǒng)認(rèn)為update操作優(yōu)化級比讀操作低，如果同時(shí)出現(xiàn)讀操作和上面的更新操作，則優(yōu)先執(zhí)行讀操作。

MySQL提供了幾個(gè)語句調(diào)節(jié)符，允許你修改它的調(diào)度策略：

LOW_PRIORITY關(guān)鍵字應(yīng)用于：DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

HIGH_PRIORITY關(guān)鍵字應(yīng)用于：SELECT、INSERT語句。

delayed(延遲)關(guān)鍵字應(yīng)用于：INSERT、REPLACE語句。

如果你希望所有支持LOW_PRIORITY選項(xiàng)的語句都默認(rèn)地按照低優(yōu)先級來處理，那么可能使用**low-priority-updates**選項(xiàng)來啟動服務(wù)器。然后可通過使用insert HIGH_PRIORITY table.....來把個(gè)別我們希望的INSERT語句提高到正常的寫入優(yōu)先級。

總結(jié)

以上是生活随笔為你收集整理的表级锁的mysql读写_Mysql的表级锁的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。