8.11.1 Internal Locking Methods

8.11.1 Internal Locking Methods

8.11.2 Table Locking Issues

8.11.3 Concurrent Inserts

8.11.4 Metadata Locking

8.11.5 External Locking

MySQL 使用locking 管理表記憶體的沖突:

内部locking 是通過MySQL server 本身來管理表内容的沖突,這種鎖是内部的,因為它完全由伺服器執行,沒有涉及其他程式。

外部鎖發生在伺服器和其他程式lock MyISAM 表檔案在它們之間協調,程式能通路表在任何時間。

8.11.1 Internal Locking Methods 内部鎖方法:

本章讨論内部locking，即,locking 在MySQL server 本身管理表内容在多個會話下的沖突。

這種類型的locking 是内部的,它完全是有伺服器執行的,并且沒有調用其他程式。

Row-Level Locking 行級别鎖定

MySQL 使用行級鎖對于InnoDB 表來支援并發的讀寫,使它們适用于多個使用者,高并發和OLTP應用。

為了避免死鎖 當執行多個并發寫操作在一個單獨的InnoDB 表上,

獲得必要的locks 在事務的開始通過執行SELECT .. FOR UPDATE語句對于每個期待修改的行。

即使DML語句在事務後面,如果事務修改或者lock 多個表,執行适當的語句在相當的順序在每個事務中。

Deadlocks 影響性能相比表現出一個嚴重的錯誤,因為InnoDB 自動檢測死鎖條件,復原其中一個受影響的事務。

行級鎖的優點:

1.當不同的會話通路不同的記錄,鎖沖突越少

2.對于復原變的更少

3.鎖定一條記錄很長時間

Table-Level Locking 表級鎖

MySQL 使用表級鎖對于MyISAM,MEMORY和MERGE 引擎表,

值允許一個會話同一時間更新那些表,讓它們處于隻讀模式,或者單使用者應用。

這些存儲引擎避免死鎖通過總是請求所有需要的鎖在查詢開始和總是以相同的順序鎖住表。

這種折衷是直接降低了沖突, 其他的會話需要修改表必須等待目前的DML完成。

MySQL 授于表寫鎖如下:

1.如果在表上沒有鎖, 放一把寫鎖在上面

2.否則,把鎖請求放入write lock queue：

MySQL 授予表讀鎖如下:

1.如果沒有寫鎖在表上, 放置一個read lock 在上面

2.否則,把鎖請求在read lock 隊列

表更新被給定一個更高的優先級相比表檢索,是以,當一個鎖被釋放,鎖變得可用對于請求在寫鎖隊列,

然後請求在read lock 隊列。確定了更新一個表不是被 “starved”,即使有嚴重的SELECT 活動。

然而, 如果你在表上有很多的更新,SELECT 語句等待直到這裡沒有更多的更新。

你可以分析表的lock 争用在你的系統上通過檢查Table_locks_immediate和Table_locks_waited 變量,

這表明請求表鎖的時間和等待的數量：

mysql> SHOW STATUS LIKE ‘Table%’;

+———————–+———+

| Variable_name | Value |

| Table_locks_immediate | 1151552 |

| Table_locks_waited | 15324 |

MyISAM 存儲引擎支援并發的插入來降低沖突在讀和寫對于一個給定的表,

如果MyISAM 表沒有足夠的空閑快在資料檔案中,行總是被插入到資料檔案尾部。

在這種情況下,你可以自由的混合并發的INSERT和SELECT 語句對于MyISAM表而不需要鎖,

也就說說, 你可以插入記錄到一個MyISAM 表的同時其他用戶端讀取它們。

如果你會的一個表鎖明确的使用LOCK TABLES,你可以請求一個READ LOCAL lock 而不是READ lock

讓其他session 執行一個并發的插入當你的表被鎖的時候。

執行很多的INSERT 和SELECT 操作在一個表 real_table 當并發的INSERT 是不可能的,

你可以insert 記錄到一個臨時表temp_table,用臨時表的資料來更新real 表上

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM temp_table;

mysql> DELETE FROM temp_table;

mysql> UNLOCK TABLES;

表級鎖的優點:

1.需要相對少的記憶體

當使用大部分表 ,因為隻有一個鎖被調用

3.如果你經常使用GROUP BY 操作在大資料表上或者你必須頻繁的掃描整個表

InnoDB 支援表級鎖和行級鎖,MyISAM 隻支援表級鎖

通常表級鎖使用于下面的情況:

1.表中的大多數語句都是讀

2.表的語句是一個混合的讀寫，在這裡寫的是更新或删除一個單獨的行，可以拿來一個鍵讀：

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

DELETE FROM tbl_name WHERE unique_key_col=key_value;