mysql之事务 锁(三)
目錄
- 事務(wù)
- 事務(wù)特性ACID
- 事務(wù)帶來的問題
- 事務(wù)隔離級(jí)別
- 鎖
- 表鎖 行鎖
- Innodb鎖類型
- 共享鎖 Shared Locks
- 排它鎖 Exclusive Locks
- 意向鎖 (IS 、IX)
- 自增鎖 AUTO-INC Locks
- 行鎖算法
- 臨鍵鎖 Next-key Locks
- 間隙鎖 Gap Locks
- 記錄鎖 Record Locks
- 死鎖
- 利用鎖解決事務(wù)問題
- 解決臟讀
- 解決不可重復(fù)讀
- 解決幻讀
事務(wù)
事務(wù):數(shù)據(jù)庫(kù)操作的最小工作單元,是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作;事務(wù)是一組不可再分割的操作集合(工作邏輯單元);
典型事務(wù)場(chǎng)景(轉(zhuǎn)賬):
mysql中如何開啟事務(wù):
begin 或者 start transaction -- 手工 commit / rollback -- 事務(wù)提交或回滾 set session autocommit = on/off; -- 設(shè)定事務(wù)是否自動(dòng)開啟示例:
--查看事務(wù)自動(dòng)提交狀態(tài) show VARIABLES like 'autocommit';-- autocommit 為 ON update teacher set name ='seven' where id =1; insert teacher (name,age) value ('james',22); delete from teacher where name = 'james';-- autocommit 為 ON 如何開啟事務(wù) BEGIN; START TRANSACTION;update teacher set name ='seven' where id =1; insert teacher (name,age) value ('james',22); delete from teacher where name = 'james';COMMIT; ROLLBACK;-- 將autocommit 改成OFF set session autocommit = OFF; update teacher set name ='seven' where id =1; insert teacher (name,age) value ('james',22); delete from teacher where name = 'james';commit; ROLLBACK;JDBC 編程:
connection.setAutoCommit(boolean);Spring 事務(wù)AOP編程:
expression=execution(com.gpedu.dao.*.*(..))
事務(wù)特性ACID
原子性(Atomicity)
最小的工作單元,整個(gè)工作單元要么一起提交成功,要么全部失敗回滾
一致性(Consistency)
事務(wù)中操作的數(shù)據(jù)及狀態(tài)改變是一致的,即寫入資料的結(jié)果必須完全符合預(yù)設(shè)的規(guī)則,
不會(huì)因?yàn)槌霈F(xiàn)系統(tǒng)意外等原因?qū)е聽顟B(tài)的不一致
隔離性(Isolation)
一個(gè)事務(wù)所操作的數(shù)據(jù)在提交之前,對(duì)其他事務(wù)的可見性設(shè)定(一般設(shè)定為不可見)
持久性(Durability)
事務(wù)所做的修改就會(huì)永久保存,不會(huì)因?yàn)橄到y(tǒng)意外導(dǎo)致數(shù)據(jù)的丟失
事務(wù)帶來的問題
臟讀:事務(wù)未提交對(duì)其他事務(wù)也是可見的
不可重復(fù)讀:一個(gè)事務(wù)開始之后,只能看到自己提交的事務(wù)所做的修改,也叫不可重復(fù)讀(nonrepeatable read)
幻讀:當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí),另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄 當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí),會(huì)產(chǎn)生幻行。
事務(wù)隔離級(jí)別
SQL92 ANSI/ISO標(biāo)準(zhǔn):
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Read Uncommitted(未提交讀):事務(wù)的修改,即使沒有提交 對(duì)其他事務(wù)也都是可見的 --事務(wù)可以讀取未提交的數(shù)據(jù),被稱為臟讀
Read Committed(提交讀) --解決臟讀問題
一個(gè)事務(wù)開始之后,只能看到自己提交的事務(wù)所做的修改,換句話說,一個(gè)事務(wù)從開始直到提交之前,所做的任何修改對(duì)其他事務(wù)都是不可見的。
Repeatable Read (可重復(fù)讀) --解決不可重復(fù)讀問題
可重復(fù)讀是在開始讀取數(shù)據(jù)(事務(wù)開始)時(shí) 不再允許修改操作 。
在同一個(gè)事務(wù)中多次讀取同樣的數(shù)據(jù)結(jié)果是一樣的,這種隔離級(jí)別未定義解決幻讀的問題。所謂幻讀 當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí),另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄 當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí),會(huì)產(chǎn)生幻行。Innodb和XtraDB存儲(chǔ)引擎通過多版本并發(fā)控制 解決幻讀問題。
**可重復(fù)讀是mysql的默認(rèn)事務(wù)隔離級(jí)別。**可就是說
Serializable(串行化) --解決所有問題
最高的隔離級(jí)別,通過強(qiáng)制事務(wù)的串行執(zhí)行,避免了前面說的幻讀問題。
Innodb對(duì)隔離級(jí)別的并發(fā)程度:
Innodb是怎么解決幻讀問題的呢?
加鎖的讀取是通過臨鍵鎖鎖住要讀取的行 不讓修改 不加鎖的讀取是通過MVCC 版本號(hào)控制 讀取比
隔離級(jí)別的實(shí)現(xiàn):鎖 MVCC
鎖
表鎖 行鎖
鎖是用于管理不同事務(wù)對(duì)共享資源的并發(fā)訪問
表鎖與行鎖的區(qū)別:
鎖定粒度:表鎖 > 行鎖
加鎖效率:表鎖 > 行鎖
沖突概率:表鎖 > 行鎖
并發(fā)性能:表鎖 < 行鎖
InnoDB存儲(chǔ)引擎支持行鎖和表鎖(另類的行鎖)
Innodb鎖類型
用到的表:
-- ---------------------------- -- Table structure for `users` -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(32) NOT NULL,`age` int(11) NOT NULL,`phoneNum` varchar(32) NOT NULL,`lastUpdate` datetime NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `idx_eq_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;-- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES ('1', 'seven', '26', '13666666666', '2018-12-07 19:22:51'); INSERT INTO `users` VALUES ('2', 'qingshan', '19', '13777777777', '2018-12-08 21:01:12'); INSERT INTO `users` VALUES ('3', 'james', '20', '13888888888', '2018-12-08 20:59:39'); INSERT INTO `users` VALUES ('4', 'tom', '99', '13444444444', '2018-12-06 20:34:10'); INSERT INTO `users` VALUES ('6', 'jack', '91', '13444444544', '2018-12-06 20:35:07'); INSERT INTO `users` VALUES ('11', 'jack1', '33', '13441444544', '2018-12-06 20:36:19'); INSERT INTO `users` VALUES ('15', 'tom2', '30', '1344444444', '2018-12-08 15:08:24'); INSERT INTO `users` VALUES ('19', 'iiii', '30', '1344444444', '2018-12-08 21:21:47');共享鎖 Shared Locks
共享鎖: 又稱為讀鎖,簡(jiǎn)稱S鎖,顧名思義,共享鎖就是多個(gè)事務(wù)對(duì)于同一數(shù)據(jù)可以共享一把鎖,都能訪問到數(shù)據(jù),但是只能讀不能修改;
加鎖釋鎖方式:
釋放方式:
commit/rollback示例:
-- 共享鎖加鎖 BEGIN select * from users WHERE id=1 LOCK IN SHARE MODE;rollback; commit; -- 其他事務(wù)執(zhí)行 --成功 select * from users where id =1; --失敗 update users set age=19 where id =1;排它鎖 Exclusive Locks
排他鎖: 又稱為寫鎖,簡(jiǎn)稱X鎖,排他鎖不能與其他鎖并存,如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖,其他事務(wù)就不能再獲取該行的鎖(共享鎖、排他鎖),只有該獲取了排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)行進(jìn)行讀取和修改,(其他事務(wù)要讀取數(shù)據(jù)可來自于快照)
加鎖方式:
釋鎖方式:
commit/rollback示例:
set session autocommit = OFF; update users set age = 23 where id =1; select * from users where id =1; update users set age = 26 where id =1;commit; ROLLBACK;-- 手動(dòng)獲取排它鎖 set session autocommit = ON; begin select * from users where id =1 for update; commit; -- 其他事務(wù)執(zhí)行 --等待 select * from users where id =1 lock in share mode; --等待 select * from users where id =1 for update; --成功 select * from users where id =1;InnoDB的行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的。
只有通過索引條件進(jìn)行數(shù)據(jù)檢索,InnoDB才使用行級(jí)鎖,否則,InnoDB將使用表鎖(鎖住索引的所有記錄)
表鎖:lock tables xx read/write;
示例:
users表的索引項(xiàng)是name
----ex1 set session autocommit = OFF; update users set lastUpdate=NOW() where phoneNum = '13666666666';rollback; -- 其他查詢執(zhí)行 --等待 update users set lastUpdate=NOW() where id =2; --等待 update users set lastUpdate=NOW() where id =1;排他鎖沒走索引 所有鎖住了整個(gè)表 導(dǎo)致其他事物的所有更新操作不能執(zhí)行
----- ex2 set session autocommit = OFF; update users set lastUpdate=NOW() where id = 1;rollback; -- 其他查詢執(zhí)行 --成功 update users set lastUpdate=NOW() where id =2; --等待 update users set lastUpdate=NOW() where id =1;排它鎖走了主鍵索引 導(dǎo)致主鍵id為1的數(shù)據(jù)
排它鎖走了主鍵索引 導(dǎo)致主鍵id為1的數(shù)據(jù)鎖住 行鎖級(jí)別 其他數(shù)據(jù)事務(wù)操作成功執(zhí)行
排它鎖走了索引name 鎖住name為seven的數(shù)據(jù) 同時(shí)也在對(duì)應(yīng)的主鍵索引加鎖 加兩把鎖
意向鎖 (IS 、IX)
意向共享鎖(Intention Shared Locks 簡(jiǎn)稱IS):表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖,即一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖,
意向共享鎖之間是可以相互兼容的
意向排它鎖(Intention Exclusive Locks 簡(jiǎn)稱IX):表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖,即一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖,
意向排它鎖之間是可以相互兼容的
意向鎖(IS 、IX) 是InnoDB 數(shù)據(jù)操作之前 自動(dòng)加的,不需要用戶干預(yù)
意義:
意向鎖相當(dāng)于一個(gè)全局flag 只要有一行記錄有鎖 flag就設(shè)置為true 當(dāng)事務(wù)想去進(jìn)行鎖表時(shí),可以先判斷意向鎖是否存在,存在時(shí)則可快速返回該表不能
啟用表鎖
等待因?yàn)檫@個(gè)操作不走索引 鎖全表 但上邊的操作已經(jīng)對(duì)id為1的行鎖住了 所以他無法執(zhí)行 處于等待狀態(tài)
自增鎖 AUTO-INC Locks
針對(duì)自增列自增長(zhǎng)的一個(gè)特殊的表鎖
show variables like 'innodb_autoinc_lock_mode';
默認(rèn)取值1 ,代表連續(xù),事務(wù)未提交ID永久丟失
行鎖算法
用到的表:
DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of t2 -- ---------------------------- INSERT INTO `t2` VALUES ('1', '1'); INSERT INTO `t2` VALUES ('4', '4'); INSERT INTO `t2` VALUES ('7', '7'); INSERT INTO `t2` VALUES ('10', '10');臨鍵鎖 Next-key Locks
區(qū)間左開右閉
命中區(qū)域所在的區(qū)間以及下一個(gè)區(qū)間都會(huì)被鎖住 即命中7 鎖住7鎖住的區(qū)間和下一個(gè)區(qū)間
begin; select * from t2 where id>5 and id<9 for update;ROLLBACK-- 其他事務(wù)執(zhí)行 set session autocommit=off; --執(zhí)行成功 select * from t2 where id=4 for update; --等待 select * from t2 where id=7 for update; --等待 select * from t2 where id=10 for update; --等待 INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');為什么Innodb選擇臨鍵鎖next-key作為行鎖的默認(rèn)算法?
防止幻讀
間隙鎖 Gap Locks
開區(qū)間
gap只在RR事務(wù)級(jí)別存在
begin; select * from t2 where id >4 and id <6 for update; -- 或者 select * from t2 where id =6 for update;ROLLBACK;-- 其他會(huì)話執(zhí)行 --等待 INSERT INTO `t2` (`id`, `name`) VALUES (5, '5'); --等待 INSERT INTO `t2` (`id`, `name`) VALUES (6, '6');兩條插入數(shù)據(jù)執(zhí)行不了 因?yàn)閰^(qū)間(4,7)被鎖住
記錄鎖 Record Locks
begin; select * from t2 where id =4 for update; rollback;-- 其他事務(wù)執(zhí)行 --成功 select * from t2 where id =7 for update; --等待 select * from t2 where id =4 for update;死鎖
多個(gè)并發(fā)事務(wù)(2個(gè)或者以上);
每個(gè)事務(wù)都持有鎖(或者是已經(jīng)在等待鎖);
每個(gè)事務(wù)都需要再繼續(xù)持有鎖;
事務(wù)之間產(chǎn)生加鎖的循環(huán)等待,形成死鎖。
避免死鎖:
1)類似的業(yè)務(wù)邏輯以固定的順序訪問表和行。
2)大事務(wù)拆小。大事務(wù)更傾向于死鎖,如果業(yè)務(wù)允許,將大事務(wù)拆小。
3)在同一個(gè)事務(wù)中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率。
4)降低隔離級(jí)別,如果業(yè)務(wù)允許,將隔離級(jí)別調(diào)低也是較好的選擇
5)為表添加合理的索引。可以看到如果不走索引將會(huì)為表的每一行記錄添加上鎖(或者說是表鎖)
利用鎖解決事務(wù)問題
解決臟讀
加上排它鎖(X鎖)后沒有 查詢的時(shí)候處于等待狀態(tài) 從而解決了臟讀問題
解決不可重復(fù)讀
加入共享鎖 其他事物不能進(jìn)行更新操作 因此解決了不可重復(fù)讀問題
解決幻讀
數(shù)據(jù)只有一條 區(qū)間分成了負(fù)無窮到16 16到正無窮 臨鍵鎖鎖住了負(fù)無窮到16和下一個(gè)區(qū)間16到正無窮 其他事物的新增和刪除操作無法執(zhí)行 查詢結(jié)果還是1條 從而解決幻讀問題
點(diǎn)關(guān)注不迷路:
總結(jié)
以上是生活随笔為你收集整理的mysql之事务 锁(三)的全部?jī)?nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 2021年12月苹果开发者证书配置
- 下一篇: 马云的电影,丁磊的饭局