mysql的鎖機制

2024-07-24 12:35:51

字體：大中小

來源：轉載

供稿：網友

　　鎖概述
　　mysql鎖機制的特點：不同存儲引擎支持不同的鎖機制。
　　MyISAM和MEMORY存儲引擎支持表級鎖；
　　BDB存儲引擎采用頁面鎖；
　　InnoDB存儲引擎支持行級鎖。
　　表級鎖：
　　開銷小，加鎖快，不會出現死鎖，鎖定粒度大，加鎖沖突概率最高，并發度最低；
　　適用于以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；
　　行級鎖
　　開銷大，加鎖慢，會出現死鎖，鎖定粒度小，發生鎖沖突的概率最低，并發度最高；
　　適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統。
　　頁面鎖
　　開銷和加鎖時間介于表鎖和行鎖之間，會出現死鎖，鎖定粒度介于表鎖和行鎖之間，并發度一般；
　　MyISAM表鎖
　　查詢表級鎖的爭用情況
　　mysql> show status like 'table%';
　　+-----------------------+-------+
　　| Variable_name | Value |
　　+-----------------------+-------+
　　| Table_locks_immediate | 19 |
　　| Table_locks_waited | 0 |
　　+-----------------------+-------+
　　2 rows in set (0.00 sec)

　　table_locks_waited的值越大，表級鎖的爭用情況越嚴重
　　表級鎖的鎖模式
　　表共享讀鎖
　　表共享寫鎖
　　MyISAM表讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
　　MyISAM表的寫操作，會阻塞其他用戶對同一表的讀請求和寫請求。
　　MyISAM表的讀操作和寫操作之間，以及寫操作之間是串行的

　　如何加表鎖
　　MyISAM表在執行SELECT語句之前，會自動給涉及到的表加讀鎖，在執行UPDATE、DELETE、INSERT之前，自動給涉及到的表加寫鎖。

　　當然也可以顯示手動加鎖，用來模擬事務操作。
　　mysql> lock table tbl_name1 read/write；
　　mysql> lock tables tbl_name1 read [local], tbl_name2 write [local];

　　mysql> unlock tables;

　　注意：對表加鎖之后，使用該表的別名也是不允許的，需要對別名也進行鎖定，如下：
　　mysql> lock table tbl_name1 as tb1 read;
　　mysql> select a.id from tbl_name tb1;

　　MyISAM的并發插入
　　MyISAM的讀寫操作是串行的，但一定程度上，也是支持查詢和插入的并發進行，但不可以刪除和更新。

　　在MyISAM引擎中有一個系統變量 concurrent_insert ，專門用來控制并發插入的行為，有三種取值：
　　0-> 不允許并發插入；
　　1-> 在MyISAM表沒有孔洞的前提下，允許在讀的同時，另一個進程從尾部插入記錄（默認）
　　2-> 不論有無空洞，都可以在表尾并發插入

　　整理空間碎片：
　　mysql> optimize table tbl_name;

　　MyISAM的鎖調度
　　已經知道，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的，但是，及時讀請求先到達等待隊列，寫請求后到達等待隊列，寫鎖也會插入到讀鎖之前，因為MySQL認為寫操作比讀操作重要。

　　同時，這也是MyISAM表不適合有大量更新和查詢操作的原因，因為大量的更新和查詢操作會占據鎖等待隊列，讀鎖會被長時間等待。

　　為了解決這個問題，我們有一些參數設置來調節MyISAM的調度行為。

　　啟動low-priority-updates,使得MyISAM 默認基于讀請求以優先的權利；
　　降低更新請求的的優先級
　　mysql> set LOW_PRIORITY_UPDATES=1

　　指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級
　　一種折中的方案：給系統參數max_write_locl_count設置一個合適的值，當表的讀操作達到該值之后，MySQL就暫時將寫請求的優先級降低。
　　InnoDB鎖
　　InnoDB與MyISAM的最大不同支出在于：1、支持事務；2、采用了行級鎖。

　　查詢InnoDB行鎖的爭用情況
　　mysql> show status like 'innodb_row_lock%';
　　+-------------------------------+-------+
　　| Variable_name | Value |
　　+-------------------------------+-------+
　　| Innodb_row_lock_current_waits | 0 |
　　| Innodb_row_lock_time | 0 |
　　| Innodb_row_lock_time_avg | 0 |
　　| Innodb_row_lock_time_max | 0 |
　　| Innodb_row_lock_waits | 0 |
　　+-------------------------------+-------+
　　5 rows in set (0.00 sec)

　　Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，表級鎖的爭用情況越嚴重
　　InnoDB行鎖模式
　　共享鎖（S）
　　允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖
　　排他鎖（X）
　　允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖
　　為了行鎖表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖，這兩種意向鎖都是表鎖
　　意向共享鎖（IS）
　　事務在給一個數據行加共享鎖之前，必須先取得該表的IS鎖
　　意向排他鎖（IX）
　　事務在給一個數據行加排他鎖之前，必須先取得該表的IX鎖
　　意向鎖是InnoDB自動添加的，不需要用戶干預
　　對于UPDATE、DELETE、INSERT語句，InnoDB自動給涉及數據集加排他鎖；對于SELECT語句，InnoDB不會加任何鎖。

（編輯：武林網）

上一篇：二進制安裝mysql 5.7 mariadb 附yum安裝方式

下一篇：Mysql清理二進制日志的技巧