天天看點

MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址

一、鎖概念簡介

1、基礎描述

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

2、存儲引擎和鎖

  • MyISAM引擎:基于讀寫兩種模式,支援表級鎖 ;
  • InnoDB引擎:支援行級别讀寫鎖,跨行的間隙鎖,InnoDB也支援表鎖 ;

3、鎖操作API

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

二、MyISAM鎖機制

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='使用者資訊表';
           
  • 共享讀鎖
MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址

會話視窗一

-- 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 ;           

這裡驗證表鎖的共享讀機制。

  • 排它寫鎖
MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址

這裡驗證表鎖的排它寫機制。

  • 查詢鎖争用

通過下面語句檢視配置,

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存儲引擎,下面驗證案例會有不同。

  • 無索引結構表
MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、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 ;           
  • 索引結構表
MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址
-- 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');           
MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址
-- 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鎖

  • 基礎描述

兩個或者多個事務在同一個資源上互相占用,并請求鎖定對方占用的資源,進而導緻死循環現象,也就是死鎖。

MySQL基礎篇(06):事務管理,鎖機制案例詳解一、鎖概念簡介二、MyISAM鎖機制三、InnoDB鎖機制四、源代碼位址
-- 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