MYSQL高并發與鎖機制-有解無憂

MySQL常用存盤引擎的鎖機制

MyISAM和MEMORY采用表級鎖(table-level locking)

BDB采用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖

InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖

innodb和mysam

目前大家用的mysql中表的engine都是innodb,很少會用mysam了,就是因為在更新資料的時候mysam會鎖表,導致并發更新的時候只能一個個排隊更新,對效率影響很大,而innodb則是行鎖,大家只更新自己的那條,沖突的時候才排隊,這樣效率會高很多.

事務處理中行鎖的實體體驗：

set autocommit = 0;
update fa_admin set nickname = 'cyy' where id = 2;
commit;

innodb的索引與行級鎖

如果更新一個欄位,然后在篩選的時候where后面的欄位沒有加索引,就會把整個表的所有記錄都鎖定.

在where后面的篩選欄位加上索引后就不會鎖表只會鎖行,即使這個篩選范圍欄位只是組合索引里的其中一個

這里我經過實際操作發現了個現象,當更新的時候如果以主鍵為where條件,并且主鍵型別是varcahr的但是你在條件里寫成int的了(都是數字,型別不同),第一個用戶沒有提交事務,會阻塞后面的用戶更新另一條記錄.
所以這里要注意,更新的時候一定要注意看清欄位的型別寫sql

索引對表鎖的影響：

alter table的陳述句是很危險的(其實他的危險其實是未提交事物或者長事務導致的)，在操作之前最好確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯陳述句，如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設定好超時時間，避免長時間的metedata鎖等待，

加索引要避免鎖表，需要先確定此時沒有慢查詢事務未提交，如果這個查詢卡了30分鐘，那么整個表的所有業務都會卡30分鐘，這是很變態的，

所以大表加索引還是在沒人用的時間加比較安全，要不就是先創建副本，再將表名改掉，

由此得出一個結論，當一張表資料量很大時，不要輕易添加索引，會導致表被鎖死！如果非要添加，那么應該先把資料表進行備份，然后進行空表添加索引，

查詢范圍對mysql鎖的影響：

set autocommit=0;
update fa_admin set nickname='cyy4' where id>1;

這種情況下，執行insert操作，會存在阻塞，因為新增的id也在更新的范圍內；

set autocommit=0;
update fa_admin set nickname='cyy4' where id<3;

這種情況下，不存在阻塞；

建議每次行鎖的范圍越小越好，這樣對其他資料的阻塞就越小；

高并發下商城秒殺悲觀鎖的應用技巧：

當select XXXX for update就會鎖住這一行記錄,別人只能查不能更新,即加了寫鎖；

demo如下：

set autocommit=0;
select * from fa_admin where id = 1 for update;
update fa_admin set username = 'cyy6' where id = 1;
commit;

使用樂觀鎖控制高并發下商品下單：

方式1：使用資料版本（Version）記錄機制實作，這是樂觀鎖最常用的一種實作方式，何謂資料版本？即為資料增加一個版本標識，一般是通過為資料庫表增加一個數字型別的 “version” 欄位來實作，當讀取資料時，將version欄位的值一同讀出，資料每更新一次，對此version值加一，當我們提交更新的時候，判斷資料庫表對應記錄的當前版本資訊與第一次取出來的version值進行比對，如果資料庫表當前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期資料，

set autocommit=0;
select * from fa_admin where id = 1;
update fa_admin set nickname = 'cyy7',version = version + 1 where id = 1 and version = 0;
commit;

方式2：樂觀鎖定的第二種實作方式和第一種差不多，同樣是在需要樂觀鎖控制的table中增加一個欄位，名稱無所謂，欄位型別使用時間戳（timestamp）, 和上面的version類似，也是在更新提交的時候檢查當前資料庫中資料的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突，

讀鎖與寫鎖的運轉：

讀鎖：只能讀，不能寫；select成功，insert失敗，

加鎖:lock table XX read
釋放鎖:unlock table
加上讀鎖后大家都可以查詢,對于當前加鎖的這個用戶來說,也只能查詢(讀),更新插入(寫)的時候就會報錯
對于其他用戶來說可以查詢,但是更新(寫)操作會被阻塞，直到加鎖的用戶釋放這個讀鎖

lock table fa_admin read;
select * from fa_admin;
insert into fa_admin(username) values('cyy8');
unlock tables;

寫鎖：既不能讀，也不能寫

在當前會話中可以讀寫,別的會話不能讀也不能寫,都會被阻塞,直到當前會話釋放這個鎖

加鎖:lock table XXX write;
解鎖:unlock table;

在實際操作寫鎖的時候發現navicat一個查詢標簽在加了寫鎖后會把自己也鎖住,這操作騷的一批.

使用表鎖應用高并發下單實體： 首先建個測驗表結構如下：

隨便整點資料

不建議表鎖

lock table goods write;
update goods set num = num - 200 where id = 1;
-- 寫單 發送 扣除余額等 --
unlock tables;

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

標籤：MySQL

上一篇：MySQL 之索引進階

下一篇：MySQL 之索引進階