文章目錄
-
- 一、表鎖和行鎖概念
- 二、表鎖執行個體剖析
-
-
- 開啟兩個會話，對表添加讀鎖
- 開啟兩個會話，對表添加寫鎖
-
- 三、行鎖執行個體剖析
-
-
- 開啟兩個會話，示範行鎖
-
最近小白也是在深度學習mysql，這幾天在研究mysql的表鎖與行鎖，今天寫部落格記錄一下。
一、表鎖和行鎖概念
mysql中按照鎖的粒度來區分，分為表級鎖和行級鎖
- 表鎖：對于整張表進行鎖定，如果兩個會話對某張表的做修改，那麼必須等第一個會話結束完成之後才可以對此張表進行修改。粒度比較大，在高并發情況下效率較低。
- 行鎖：mysql的innodb是采用的行鎖，指的是兩個會話對某張表中的某一行資料進行修改，粒度較小，容易出現死鎖，在高并發情況下支援較好。
二、表鎖執行個體剖析
建立一張表，存儲引擎類型為MyISAM：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`age` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
為表中插入資料：
INSERT INTO user (`id`, `name`, `sex`, `age`) VALUES ('1', 'hello', '男', '25');
INSERT INTO user (`id`, `name`, `sex`, `age`) VALUES ('2', 'hi', '女', '30');
開啟兩個會話，對表添加讀鎖
會話A：
-- 會話A
-- 為user表添加讀鎖
LOCK TABLE user read;
-- 查詢表資料
SELECT * FROM user;
會話B：
-- 會話B
SELECT * FROM user;
運作之後，發現會話A和會話B中都可以進行讀的操作，可以查詢到資料
如圖所示：
繼續執行
會話A：
-- 添加資料
INSERT INTO user(name,sex,age) VALUES('走在自己的時區裡','no',45);
-- 釋放鎖
-- UNLOCK TABLES;
運作會話A後，發現目前會話内不能對該表進行寫的操作，如下圖所示：
會話B：
-- 添加資料
INSERT INTO user(name) VALUES('美女');
運作會話B後，發現其他會話内不能對該表進行寫的操作，該操作被阻塞了，如下圖所示：
總結：
對表添加讀鎖，目前會話和其他會話中都可以進行讀的操作；而在目前會話中進行寫的操作會提示該表被添加讀鎖，其他會話中則寫的操作會被阻塞。
開啟兩個會話，對表添加寫鎖
會話C：
-- 會話C
-- 為表添加寫鎖
LOCK TABLE userinfo WRITE;
-- 查詢該資料
SELECT * FROM userinfo;
會話D：
-- 會話D
-- 查詢該資料
SELECT * FROM userinfo;
運作完成之後，發現會話C中可以進行該表的讀操作，會話D中發現該查詢操作被阻塞了，如下圖所示：
繼續執行
會話C：
INSERT INTO userinfo(name,sex,age) VALUES('大刀王五','牛逼',30);
-- 釋放鎖
-- UNLOCK TABLES;
會話D：
運作完成之後，發現會話C中，成功插入資料；而會話D中該插入操作被阻塞了，如下圖：
總結：
對表添加寫鎖，目前會話中可以進行讀寫的操作；而在其他會話中則讀寫的操作則會被阻塞等待。
一旦對表釋放鎖之後，被阻塞的操作會立即執行。
三、行鎖執行個體剖析
建立一張表，存儲引擎類型為InnoDB：
CREATE TABLE `shop_food` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`food_name` varchar(20) DEFAULT NULL,
`food_price` decimal(4,2) DEFAULT NULL,
`food_type` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
為表中插入資料：
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('1', '洋芋絲', '20.50', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('2', '巴沙魚', '30.55', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('3', '豆芽粉條', '15.00', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('4', '可樂', '4.00', '2');
特别注意：行鎖是對索引添加的鎖，并不是對表中的記錄。前提是索引是有效的，否則會由行鎖更新成表鎖
開啟兩個會話，示範行鎖
會話A：
-- 事務A
START TRANSACTION;
SELECT * FROM shop_food;
UPDATE shop_food SET food_name = '酸辣洋芋絲' WHERE id=1;
會話B：
-- 事務B
SELECT * FROM shop_food;
前提這個表的存儲引擎是InnoDB類型
運作完畢之後，發現事務A查詢到了目前會話修改的值，但在事務B中查詢到的還是修改之前的資料
繼續執行
會話B：
這時，我們和事務A中一樣，修改同一行id為1的資料，結果發現在事務B中該操作被阻塞了，如下圖：
擷取不到行鎖的情況下，會自動斷開，并且不斷重試，如圖：
這時，我們在事務B中修改id為2的行，發現修改成功，如下：
會話B：
會話A：
-- 事務A
START TRANSACTION;
SELECT * FROM shop_food;
UPDATE shop_food SET food_name = '酸辣洋芋絲' WHERE id=1;
COMMIT;
這時我們在事務A中執行commit，發現事務B中被阻塞的立即釋放被執行了。
總結：
可以看出，會話A在未送出的情況下：
- 會話B中對同一行資料進行修改的時候，會被阻塞，阻塞之後會一直進行等待，如果長時間沒有等到釋放則自己會自動斷開；在會話B中更新表中的其他行是可以成功的
- 會話A中的事務一旦commit，事務B中被阻塞的會立即釋放作相應的修改
InnoDB中是對索引加的行鎖，那麼我們來驗證一下如果在不是索引的列上面修改會發生什麼？
如上所建立的表中，隻有主鍵id，其餘列沒有添加索引
會話A：
START TRANSACTION;
SELECT * FROM shop_food;
-- 更新沒有索引的列 food_name
UPDATE shop_food SET food_name='雪碧' WHERE food_name='透心涼，渴釋放';
會話B：
SELECT * FROM shop_food;
UPDATE shop_food SET food_name='酸辣豆芽粉條' WHERE food_price=30.55;
事務A執行完成之後，修改food_name的列成功；而事務B執行完成之後，發現被阻塞了。這是因為從行鎖更新到了表鎖。
總結：
InnoDB的行鎖是對索引的列有效果的，如上所述，修改的是food_name的列，該列上沒有建立索引，這裡執行完事務A就更新成表鎖了，是以在事務B中修改資料的時候發現是被阻塞到了。
這是我自己對于mysql的表鎖和行鎖的了解與實踐，如果有不對的地方還請大家多多指正，溝通交流。