#共享鎖(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
示列: select * from 表 where id = 3 or id=5 lock in share mode;
#排他鎖(X):SELECT * FROM table_name WHERE ... FOR UPDATE
示列: insert、update、delete寫操作自帶排他鎖
select * from 表 where id = 3 or id=5 for update;
【行級鎖示意圖】
18@MySQL中的鎖機制
【案列】(行級鎖)
SQL的執行為例,講解一下InnoDB對于單行資料的加鎖原理:
#示列:
update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
#sql注解:
第一條SQL使用主鍵查詢,隻需要在 id = 49 這個主鍵索引上加上鎖。
第二條 SQL 使用二級索引來查詢,那麼首先在 name = Tom 這個索引上加寫鎖,然後由于使用 InnoDB 二級索引還需再次根據主鍵索引查詢,是以還需要在 id = 49
sql :update user set age = 10 where id > 49;
sql注解:mysql Server 會根據 WHERE 條件讀取第一條滿足條件的記錄,然後 InnoDB 引擎會将第一條記錄傳回并加鎖,接着 MySQL Server 發起更新改行記錄的 UPDATE 請求,更新這條記錄。一條記錄操作完成,再讀取下一條記錄,直至沒有比對的記錄為止
意向鎖是 InnoDB 自動加的,不需要使用者幹預;
1)對于UPDATE、DELETE和INSERT語句,InnoDB會自動給涉及的資料集加上排他鎖;
2)對于普通的SELECT語句,InnoDB不會加任何鎖;事務可以通過以下語句顯示給記錄集添加共享鎖或排他鎖:
共享鎖(S):select * from table_name where ... lock in share mode 此時其他 session 仍然可以查詢記錄,并也可以對該記錄加 share mode 的共享鎖。但是如果目前事務需要對該記錄進行更新操作,則很有可能造成死鎖。
排他鎖(X):select * from table_name where ... for update 其他session可以查詢記錄,但是不能對該記錄加共享鎖或排他鎖,隻能等待鎖釋放後在加鎖
####################################### 兩種加鎖方式 ################################
1)#select **for update** (添加排他鎖)
在執行這個 select 查詢語句的時候,會将對應的索引通路條目加上排他鎖(X鎖),也就是說這個語句對應的鎖就相當于update帶來的效果;
#*使用場景*:
為了讓確定自己查找到的資料一定是最新資料,并且查找到後的資料值允許自己來修改,此時就需要用到select for update語句;
#*性能分析*:
select for update語句相當于一個update語句。在業務繁忙的情況下,如果事務沒有及時地commit或者rollback可能會造成事務長時間的等待,進而影響資料庫的并發使用效率。
2)#select lock **in share mode** (添加共享鎖)
in share mode 子句的作用就是将查找的資料加上一個share鎖,這個就是表示其他的事務隻能對這些資料進行簡單的 select 操作,而不能進行 DML 操作
#*使用場景*:
為了確定自己查詢的資料不會被其他事務正在修改,也就是確定自己查詢到的資料是最新的資料,并且不允許其他事務來修改資料。與select for update不同的是,本事務在查找完之後不一定能去更新資料,因為有可能其他事務也對同資料集使用了 in share mode 的方式加上了S鎖;
#*性能分析*:
select lock in
3、案列
#資料表準備:
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);
insert into employee(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龍'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('張野'),
('程咬金'),
('程咬銀'),
('程咬銅'),
('程咬鐵')
;
update employee set age = 18 where id <=3;
#驗證insert、update、delete是預設加排他鎖的
############################(事務一)#############################
#步驟一:
start transaction;
select name from employee where id = 1;
#步驟三:
-- 此處的update會阻塞在原地,因為事務二并未送出事務,即尚未釋放排他鎖
update employee set name = concat(name,"_NB") where id = 1;,
#步驟五:
-- 此處查詢到的結果為EGON_NB
select name from employee where id = 1;
#步驟六:
-- 送出之後,name持久化為EGON_NB
commit;
select name from employee where id = 1;
###########################(事務二)###############################
#步驟二:
-- 把小寫的egon變為大寫,此時的update會自動加鎖
update employee set name = "EGON" where id = 1;
立即檢視修改結果,name變為EGON,但此時還沒有commit
select name from employee where id = 1;
#步驟四:
-- 事務二一旦送出,事務一阻塞在步驟3的操作即會運作成功
commit;
-- 檢視修改結果,name變為大寫的EGON
select name from employee where id = 1;
4、共享鎖與排他鎖
特例:加過排他鎖的資料行在其他事務種是不能修改資料的,也不能通過for update和lock in share mode鎖的方式查詢資料,但可以直接通過select …from…查詢資料,因為普通select查詢沒有任何鎖機制
(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50)
);
#插入記錄
#三個部門:教學,銷售,營運
insert into emp(name,sex,age,hire_date,post) values
('egon','male',78,'20170301','老男孩駐沙河辦事處外交大使'), #以下是教學部
('zxx','male',19,'20150302','teacher'),
('wupeiqi','male',81,'20130305','teacher'),
('yuanhao','male',73,'20140701','teacher'),
('liwenzhou','male',28,'20121101','teacher'),
('jingliyang','female',18,'20110211','teacher'),
('jinxin','male',18,'19000301','teacher'),
('成龍','male',48,'20101111','teacher'),
('歪歪','female',48,'20150311','sale'),#以下是銷售部門
('丫丫','female',38,'20101101','sale'),
('丁丁','female',18,'20110312','sale'),
('星星','female',18,'20160513','sale'),
('格格','female',28,'20170127','sale'),
('張野','male',28,'20160311','operation'), #以下是營運部門
('程咬金','male',18,'19970312','operation'),
('程咬銀','female',18,'20130311','operation'),
('程咬銅','male',18,'20150411','operation'),
('程咬鐵','female',18,'20140512','operation')
;
create index idx_name on emp(name);
create index idx_age on emp(age);
#事務1與事務2同時運作,會出現死鎖
事務1
begin;
update emp set post="IT" where name="egon" or name="zxx"; -- 索引的有序性,'zxx'>'egon',是以在輔助索引中zxx對應的記錄在後
-- 即 ('egon',主鍵字段為1),('zxx',主鍵id為2),在鎖完輔助索引後鎖主鍵索引時,先鎖主鍵1對應的記錄再鎖2
事務2
begin;
select * from emp where age=78 or age = 19 for update;
#步驟分析一
1) #查出商品剩餘量
select quantity from items where id=100;
2)#如果剩餘量大于0,則根據商品資訊生成訂單
insert into orders(id,item_id) values(null,100);
3)# 修改商品的庫存
update Items set quantity=quantity-1 where id=100;
#步驟分析二
step1: #查出商品狀态
select quantity from items where id=100 for update;
step2: #根據商品資訊生成訂單
insert into orders(id,item_id) values(null,100);
step3: #修改商品的庫存
update Items set quantity=quantity-2 where id=100;
select…for update是MySQL提供的實作悲觀鎖的方式。此時在items表中,id為100的那條資料就被我們鎖定了,其它的要執行select quantity from items where id=100 for update的事務必須等本次事務送出之後才能執行。這樣我們可以保證目前的資料不會被其它事務修改
解決高并發帶來的問題,就可以利用樂觀鎖的概念來解決: 1》将上面中的第3個步驟中是sql語句改為(update task set count=count-1 where id=1 and count=1)當然,其中的count=1中的1是步驟2讀取出來的資料總數 2》可以給表加一個版本号version字段,預設為1,每次執行更新的時候自增1,并在where語句後帶上讀取到的版本号,以免再讀取和更新資料之間,有第三者更新了資料庫
18@MySQL中的鎖機制
#分析步驟二:
1》使用者請求搶任務接口
2》讀取資料庫剩餘數量
3》如果大于0,剩餘數量減1,更新資料庫剩餘數量(update task set count=count-1 where id=‘任務id’ and count=‘讀取到的剩餘數量’)
4》傳回資料
ps : 以上SQL其實還是有一定的問題的,就是一旦發上高并發的時候,就隻有一個線程可以修改成功,那麼就會存在大量的失敗
有一條比較好的建議,可以減小樂觀鎖力度,最大程度的提升吞吐率,提高并發能力,如下:
//修改商品庫存
update task set count=count-1 where id=‘任務id’ and count=‘讀取到的剩餘數量’ and count-1 >= 0;
以上SQL語句中,通過count-1>0的方式進行樂觀鎖控制,商品個數count至少要有1件才可以。
以上update語句,在執行過程中,會在一次原子操作中自己查詢一遍count的值,并将其扣減掉1。
沒錯!你參加過的天貓、淘寶秒殺、聚劃算,跑的就是上述這條SQL,通過挑選樂觀鎖,可以減小鎖力度,進而提升吞吐