本文原始碼：GitHub·點這里 || GitEE·點這里

一、鎖概念簡介

1、基礎描述

鎖機制核心功能是用來協調多個會話中多執行緒并發訪問相同資源時，資源的占用問題，鎖機制是一個非常大的模塊，貫徹MySQL的幾大核心難點模塊：索引，鎖機制，事務，這里是基于MySQL5.6演示的幾種典型場景，對面MySQL這幾塊問題時，有分析流程和思路是比較關鍵的，在MySQL中常見這些鎖概念：共享讀鎖、排它寫鎖 ; 表鎖、行鎖、間隙鎖，

2、存盤引擎和鎖

MyISAM引擎：基于讀寫兩種模式，支持表級鎖 ;
InnoDB引擎：支持行級別讀寫鎖，跨行的間隙鎖,InnoDB也支持表鎖 ;

3、鎖操作API

LOCK TABLE name [READ,WRITE] ;加表鎖
UNLOCK TABLES ; 釋放標所

二、MyISAM鎖機制

1、基礎描述

MySQL的表級鎖有兩種模式：共享讀鎖（Read-Lock）和排它寫鎖（Write-Lock），針對MyISAM表的讀操作，不會阻塞其他執行緒對同一表的讀請求，但阻塞對同一表的寫請求；針對MyISAM表的寫操作，會阻塞其他執行緒對同一表的讀和寫操作；MyISAM引擎讀寫操作之間，以及寫與寫操作之間是串行化，當一次會話執行緒獲取表的寫鎖后，只有當前持有鎖的會話執行緒可以對表進行操作，其它執行緒的讀、寫操作都會等待，直到鎖被釋放為止，

2、驗證案例

基于上面的表鎖機制特點，使用下面兩個案例驗證，

基礎表結構

CREATE TABLE `dc_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用戶表';
CREATE TABLE `dc_user_info` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `city` varchar(20) DEFAULT NULL COMMENT '城市',
  `country` varchar(20) DEFAULT NULL COMMENT '國家',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用戶資訊表';

共享讀鎖

會話視窗一

-- 1、加讀鎖
LOCK TABLE dc_user READ ;
-- 2、當前會話查詢，OK
SELECT * FROM dc_user ;
-- 4、當前會話寫入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 6、查詢其他表,Error
SELECT * FROM dc_user_info ;
-- 7、釋放鎖
UNLOCK TABLES ;

會話視窗二

-- 3、其他會話查詢，OK
SELECT * FROM dc_user ;
-- 5、其他會話寫入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 8、再次執行寫入讀取，OK
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
SELECT * FROM dc_user ;

這里驗證表鎖的共享讀機制，

排它寫鎖

這里驗證表鎖的排它寫機制，

查詢鎖爭用

通過下面陳述句查看配置，

show status like 'table%';

Table_locks_waited的值越大，鎖爭用情況越嚴重，效率則越低下，

3、并發寫入問題

針對排它寫鎖的測驗案例再說明：在一定條件下，MyISAM表也支持查詢和插入操作的并發執行，通過配置系統變數concurrent_insert的值[0,1,2]，可以實作并發寫入，

concurrent_insert=0，禁止并發寫入;
concurrent_insert=1，默認配置AUTO，在MyISAM表中沒有空洞，即表的中間沒有被洗掉的行，例如[1,2,3],洗掉2之后[1,,3]，則允許在讀表的同時，另一個執行緒從表尾寫入記錄，
concurrent_insert=2，無論MyISAM表中有沒有空洞，都允許在表尾并發插入記錄，
在下面的例子中，session_1獲得了一個表的READ LOCAL鎖，該執行緒可以對表進行查詢操作，但不能對表進行更新操作；其他的執行緒（session_2），雖然不能對表進行洗掉和更新操作，但卻可以對該表進行并發插入操作，這里假設該表中間不存在空洞，

4、優先級問題

MyISAM存盤引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的，但是當一個讀操作和寫操作同時請求，寫資料會優先獲得鎖，這一機制可以通過配置修改，指定配置引數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利，
通過執行命令SET

LOW_PRIORITY_UPDATES=1，使該會話的寫操作優先級降低，
指定INSERT、UPDATE、DELETE陳述句的LOW_PRIORITY屬性，降低該陳述句的優先級，

5、表鎖應用

資料一致性校驗問題，比如銷售量+剩余庫存=貨品總量，在校驗時就要在一次會話中同時鎖住訂單表和庫存表，免得在讀取訂單表的時候，庫存表被修改，導致資料誤差出現，

三、InnoDB鎖機制

1、事務基礎概念

事務概念

事務是指作為單個邏輯作業單元執行的一系列操作（SQL陳述句），這些操作要么全部成功，要么全部不成功，

事務特性ACID

原子性(Atomicity)：事務中的多個操作要么都成功要么都失敗

一致性(consistency)：事務的執行的前后資料的完整性保持一致

隔離性(isolation)：事務執行的程序中,不應該受到其他事務的干擾

持久性(durability)：事務一旦結束,資料就持久到資料庫

事務問題

臟讀：一個事務讀到另一個事務沒有提交的資料

不可重復讀：一個事務前后多次讀取相同資料，資料內容不一致，update場景問題

虛讀(幻讀)：一個事務前后多次讀取，資料總量不一致，insert場景問題

隔離級別

read uncommitted:事務可以讀取另一個未提交事務的資料，

read committed：事務要等另一個事務提交后才能讀取資料,解決臟讀，

repeatable read：在開始讀取資料時,事務開啟,不再允許修改操作,解決：臟讀、不可重復讀，

serializable：最高事務隔離級別，事務串行化順序執行，解決臟讀、不可重復讀、幻讀，但是效率低下，耗資料庫性能，

2、鎖機制描述

InnoDB與MyISAM的最大不同有兩點：一是支持事務TRANSACTION,二是采用了行級鎖，行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來新問題：并發，死鎖等，

共享鎖：又稱讀鎖，允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖，若事務T對資料物件A加上共享鎖，則事務T可以讀A但不能修改A，其他事務只能再對A加共享鎖，而不能加寫鎖，直到T釋放A上的共享鎖，這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改，
排他鎖：又稱寫鎖，允許獲取排他鎖的事務更新資料，阻止其他事務取得相同的資源的共享讀鎖和排他鎖，若事務T對資料物件A加上寫鎖，事務T可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到T釋放A上的寫鎖，

3、驗證案例

基礎表結構

CREATE TABLE `dc_user_in01` (
  `id` int(11) DEFAULT NULL COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';

CREATE TABLE `dc_user_in02` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用戶表';

注意結構：表dc_user_in01主鍵沒有索引，表dc_user_in02主鍵有索引，但是都使用INNODB存盤引擎，下面驗證案例會有不同，

無索引結構表

會話視窗一

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=1，OK
SELECT * FROM dc_user_in01 WHERE id=1 ;
-- 3、添加寫鎖失敗
SELECT * FROM dc_user_in01 
WHERE id=1 FOR UPDATE ;
-- 4、恢復事務提交
SET AUTOCOMMIT = 1 ;

會話視窗二

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=2，OK
SELECT * FROM dc_user_in01 WHERE id=2 ;
-- 3、寫入失敗(等待)
INSERT INTO dc_user_in01 (id,user_name,tell_phone) 
VALUES (3,'lock01','13267788998');
-- 4、寫鎖失敗(等待)
SELECT * FROM dc_user_in01 
WHERE id=2 FOR UPDATE ;
-- 5、恢復事務提交
SET AUTOCOMMIT=1 ;

索引結構表

會話視窗一

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=1，OK
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 3、添加寫鎖成功
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 執行到這里，再執行視窗2
-- 4、恢復事務提交
SET AUTOCOMMIT = 1 ;

會話視窗二

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=2，OK
SELECT * FROM dc_user_in02 WHERE id=2 ;
-- 3、查詢id=1，OK，加讀鎖
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 4、寫入成功
INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 5、加寫鎖成功，id為2的
SELECT * FROM dc_user_in02 
WHERE id=2 FOR UPDATE ;
-- 6、加寫鎖失敗(等待)，占用id為1的
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 7、恢復事務提交
SET AUTOCOMMIT=1 ;

索引失效問題

這里要注意索引是否被使用問題，在很多查詢中，可能因為種種原因導致索引不執行，

explain SELECT * FROM dc_user_in02 WHERE id=1 ;

查詢鎖爭用

show status like 'innodb_row_lock%';

Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，鎖爭用情況越嚴重，效率則越低下，

4、Next-Key鎖

官方檔案說明

為了防止幻讀，InnoDB使用了一種名為Next-Key鎖定的演算法，它將記錄鎖和間隙鎖定結合在一起即：InnoDB在執行行級鎖的時候，會用這種方式-掃描索引記錄，會在符合索引條件的記錄上加共享鎖或者獨占鎖，

[Next-Key]=[Record-lock]+[Gap-lock]

如果說上面的幾種鎖機制給人的感覺是昏天暗地，那個這個Next-Key演算法就會叫人懷疑人生，

驗證案例

這里主要驗證Gap-lock間隙鎖的存在機制，

CREATE TABLE `dc_gap` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `id_index` int(11) NOT NULL COMMENT 'index',
  PRIMARY KEY (`id`),
  KEY `id_index` (`id_index`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='間隙表';
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('1', '2');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('3', '4');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('6', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('8', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('9', '9');

會話視窗一

-- 1、開始事務
START TRANSACTION ;
-- 3、鎖定id_index=7的兩條記錄
SELECT * FROM dc_gap 
WHERE id_index=7 FOR UPDATE ;
-- 9、提交
COMMIT ;

會話視窗二

-- 2、開始事務
START TRANSACTION ;
-- 4、寫入等待,id_index=6
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '6');
-- 5、寫入等待,id_index=4
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '4');
-- 6、寫入成功,id_index=3
INSERT INTO `dc_gap` (`id`, `id_index`) 
VALUES ('4', '3');
-- 7、寫入等待,id_index=9
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '9');
-- 8、寫入成功,id_index=10
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '10');

7向上到4有間隙，7向下到9有間隙，所以間隙鎖定[4,9]，且包含首尾值，

5、Dead-Lock鎖

基礎描述

兩個或者多個事務在同一個資源上相互占用，并請求鎖定對方占用的資源，從而導致死回圈現象，也就是死鎖，

驗證案例

會話視窗一

-- 1、開啟事務
START TRANSACTION ;
-- 3、占用id=6的資源
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;
-- 5、占用id=9的資源等待
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;

會話視窗二

-- 2、開啟事務
START TRANSACTION ;
-- 4、占用id=9的資源
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;
-- 6、占用id=6的資源拋死鎖
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;

補刀一句：資料庫實作各種死鎖檢測機制，或者死鎖超時等待結束，InnoDB存盤引擎在檢測到死鎖后，會立即回傳錯誤，不然兩個事務會隔空對望，一眼萬年，

注意：死鎖在事務型業務中，是無法絕對避免的，鎖定資源少，粒度細，盡量避免該情況出現，

四、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/68447.html

標籤：MySQL

上一篇：Linux安裝和卸載MySQL5.7

下一篇：mysql varchar int 123 走索引嗎？

MySQL基礎篇(06)：事務管理，鎖機制案例詳解

一、鎖概念簡介

1、基礎描述

2、存盤引擎和鎖

3、鎖操作API

二、MyISAM鎖機制

1、基礎描述

2、驗證案例

3、并發寫入問題

4、優先級問題

5、表鎖應用

三、InnoDB鎖機制

1、事務基礎概念

2、鎖機制描述

3、驗證案例

4、Next-Key鎖

5、Dead-Lock鎖

四、源代碼地址