RR與RC隔離級別下索引和鎖的測試腳本示例代碼
更新時間：2018年12月04日 15:00:54 作者：stuartlau
這篇文章主要給大家介紹了關(guān)于RR與RC隔離級別下索引和鎖的測試腳本的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
基本概念
當前讀與快照讀
在MVCC中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。 快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，并且對返回的記錄，都會加上鎖，保證在事務(wù)結(jié)束前，這條數(shù)據(jù)都是最新版本。
快照讀：簡單的select操作，屬于快照讀，不加鎖(Serializable除外)。
select * from table where ?;
當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，需要加鎖?！?br />
select * from table where ? lock in share mode; select * from table where ? for update; insert into table values (); update table set ? where ?; delete from table where ?;
隔離級別與加鎖機制
- Read Uncommitted 會發(fā)生臟讀，不考慮。
- Read Committed (RC) 針對當前讀，RC隔離級別保證對讀取到的記錄加鎖 (Gap Locking)，存在幻讀現(xiàn)象。
- Repeatable Read (RR) 針對當前讀，RR隔離級別保證對讀取到的記錄加鎖 (Record Locking)，同時保證對讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (Gap Locking)，不存在幻讀現(xiàn)象。
- Serializable 所有的讀操作均為退化為當前讀，讀寫沖突，因此并發(fā)度急劇下降，不考慮。
測試腳本
-- 基本操作 --
-- 查詢事務(wù)隔離級別，默認是RR
show variables like '%isolation%';
-- 設(shè)置事務(wù)隔離級別為RC
set session transaction isolation level read committed;
-- 數(shù)據(jù)初始化 --
begin;
drop table if exists user;
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
`address` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_email` (`email`),
KEY `idx_age` (`age`)
);
insert into user (email, age, address) values ("test1@elsef.com", 18, "address1");
insert into user (email, age, address) values ("test2@elsef.com", 20, "address2");
insert into user (email, age, address) values ("test3@elsef.com", 20, "address3");
commit;
select * from user;
-- 一、trx_id示例
begin;
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
select * from user;
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
SHOW ENGINE INNODB STATUS;
update user set age = 22 where id = 3;
-- 查詢事務(wù)id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
-- INNODB 引擎狀態(tài)
SHOW ENGINE INNODB STATUS;
commit;
-- 二、可重復(fù)讀、不可重復(fù)讀示例
-- session1
set session transaction isolation level read committed;
begin;
-- session2
set session transaction isolation level repeatable read;
begin;
-- session1
select * from user;
-- session2
select * from user;
-- session3
begin;
insert into user (email, age, address) values ("test4@elsef.com", 30, "address4");
commit;
-- session1 這里因為是RC，所以可以讀到trx3提交的新數(shù)據(jù)，這里如果是證明不可重復(fù)讀的話應(yīng)該使用update而不是insert
select * from user;
commit;
-- session2 這里因為是RR，所以不會讀到trx3提交的新數(shù)據(jù)
select * from user;
commit;
-- 三、快照讀幻讀示例
-- session1
set session transaction isolation level repeatable read;
begin;
-- 這里使用快照讀
select * from user;
-- session2
begin;
insert into user (email, age, address) values ("test4@elsef.com", 30, "address4");
commit;
select * from user;
-- session1
select * from user; -- 這里讀不到test4@的數(shù)據(jù)，因為是RR
-- 這里發(fā)生了幻讀
insert into user (email, age, address) values ("test4@elsef.com", 30, "address4"); -- 插入失敗因為email唯一索引沖突
commit;
-- 四、當前讀幻讀示例
-- RC
-- session1
set session transaction isolation level read committed;
begin;
-- 這里會對所有滿足條件的age=20的記錄加鎖，因為是RC，所以沒有GAP鎖
delete from user where age = 20;
select * from user;
-- session2
set session transaction isolation level read committed;
begin;
-- 因為trx1沒有加GAP鎖，所以之類可以插入age=20的記錄
insert into user (email, age, address) values ("test4@elsef.com", 20, "address4");
select * from user; -- 可以查到4條數(shù)據(jù)，可以讀到trx1的刪除數(shù)據(jù)，因為是RC，trx1未提交所以沒影響trx2
commit;
-- session1
select * from user; -- 可以讀到trx2新插入的數(shù)據(jù)，雖然trx1是當前讀，但是并未添加相應(yīng)的next-key鎖，沒有阻止trx2的新數(shù)據(jù)插入
commit;
--RR
-- session1
set session transaction isolation level repeatable read;
begin;
delete from user where age = 20;
select * from user;
-- session2
begin;
-- 這里會阻塞，因為trx1在age=20周圍加了GAP鎖
-- 非唯一索引，首先，通過索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖；
-- 然后讀取下一條，重復(fù)進行。直至進行到第一條不滿足條件的記錄，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結(jié)束。
insert into user (email, age, address) values ("test4@elsef.com", 20, "address4");
-- 直到超時，ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 此時如果查詢可以看到3條記錄
commit;
-- session1
-- 此時只能看到1條記錄，另外兩條被刪除了
select * from user;
commit;
-- 唯一索引+RC
-- session1
set session transaction isolation level read committed;
begin;
delete from user where email = "test3@elsef.com";
-- session2
begin;
-- 可以讀到，因為trx1是RC
select * from user where email = "test3@elsef.com";
-- 嘗試更新這個記錄的age，會阻塞直到超時，因為email是唯一索引已經(jīng)被trx1鎖住了，同時也會在對應(yīng)的主鍵索引上加鎖
-- 注意這里操作的id=3就是trx1中操作的email的同一行記錄
update user set age = 40 where id = 3;
-- session1
commit;
-- session2
commit;
-- 無索引+RC
-- session1
set session transaction isolation level read committed;
begin;
-- 由于address字段無索引，所以Innodb會對所有行進行加鎖，由MySQL server進行判斷并釋放鎖
delete from user where address = "address3";
-- session2
set session transaction isolation level read committed;
begin;
-- 這一行會成功，因為這一行沒有加鎖（先加了后釋放了）
update user set age = 10 where address = "address2";
-- 這一行同樣會被阻塞，原因是它已經(jīng)被trx1的語句加了鎖了，全部符合條件的都加鎖了
update user set age = 10 where address = "address3";
-- session1
commit;
-- session2
commit;
-- 非唯一索引+RR
-- session1
set session transaction isolation level repeatable read;
begin;
delete from user where age = 20;
-- session2
set session transaction isolation level repeatable read;
begin;
-- 這里會阻塞，因為trx1中已經(jīng)鎖住了age=20的記錄以及加上了GAP鎖，所以這里18已經(jīng)落入鎖區(qū)間
insert into user (email, age, address) values ("test4@elsef.com", 18, "address4");
-- session1
commit;
-- session2
commit;
-- 無索引RR
-- session1
set session transaction isolation level repeatable read;
begin;
-- 沒有索引，那么會鎖上表中的所有記錄，同時會鎖上主鍵索引上的所有GAP，杜絕所有的并發(fā)更新操作
delete from user where address = "address3";
-- session2
set session transaction isolation level repeatable read;
begin;
-- 這里會阻塞，原因是主鍵已經(jīng)被加上了GAP鎖，所以新的插入不能執(zhí)行成功
insert into user (email, age, address) values ("test4@elsef.com", 18, "address4");
-- session1
commit;
-- session2
commit;
-- 死鎖 簡單示例
-- session1
begin;
delete from user where id = 1;
-- session2
begin;
delete from user where id = 3;
-- session1
delete from user where id = 3;
-- seession2
-- 這里MySQL判斷發(fā)生了死鎖，中斷了一個trx
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
delete from user where id = 1;
-- session1
rollback;
-- session2;
rollback;
-- 五、死鎖 insert示例
drop table if exists t1;
begin;
create table t1 (
`id` bigint not null auto_increment,
primary key (`id`)
);
insert into t1 values(1);
insert into t1 values(5);
commit;
select * from t1;
-- session1
begin;
insert into t1 values (2);
-- sessioin2
begin;
-- 這里會阻塞
insert into t1 values (2);
-- session3
begin;
-- 這里會阻塞
insert into t1 values (2);
-- session1;
-- 此時回滾，trx2和trx3收到通知，MySQL自動中斷一個trx，因為發(fā)生了死鎖
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
rollback;
--session2;
rollback;
--session3;
rollback;
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
相關(guān)文章
mysql查詢字符串替換語句小結(jié)(數(shù)據(jù)庫字符串替換)
有時候我們需要對mysql的字符串進行替換，我們就可以通過sql語句直接實現(xiàn)了，不過對于大數(shù)據(jù)量的字段不建議使用此方法2012-07-07
mysql出現(xiàn)ERROR 1819 (HY000)的解決方法
這篇文章主要為大家詳細介紹了mysql出現(xiàn)ERROR 1819 (HY000)的解決方法,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-10-10
Mysql存儲引擎InnoDB和Myisam的六大區(qū)別
這篇文章主要介紹了Mysql存儲引擎InnoDB和Myisam的六大區(qū)別,本文從構(gòu)成上、事務(wù)處理、SQL操作、自動ID、表行數(shù)等方面講解了它的區(qū)別,需要的朋友可以參考下2015-02-02
MYSQL必知必會讀書筆記第六章之過濾數(shù)據(jù)
本文給大家分享MYSQL必知必會讀書筆記第六章之過濾數(shù)據(jù)的相關(guān)知識，非常實用，特此分享到腳本之家平臺，供大家參考2016-05-05
完整性約束是對字段進行限制，從而符合該字段達到我們期望的效果比如字段含有默認值，不能是NULL等如果插入的數(shù)據(jù)不滿足限制要求，數(shù)據(jù)庫管理系統(tǒng)就拒絕執(zhí)行操作2013-01-01
這篇文章主要介紹MySQL時間類型和模式 MySQL會在存儲時將數(shù)據(jù)值轉(zhuǎn)換為UTC標準時間來存儲，讀取時再轉(zhuǎn)為當前時間。如果你的時區(qū)沒有發(fā)生改變，則該值就是你存儲的值，如果你改變了時區(qū)，讀取到的值就會發(fā)生變化。這個特性不會對DATETIME生效,需要的朋友可以參考一下2021-09-09
下面小編就為大家?guī)硪黄猰ysql 列轉(zhuǎn)行的技巧(分享)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
mysql 5.6 從陌生到熟練之_數(shù)據(jù)庫備份恢復(fù)的實現(xiàn)方法
下面小編就為大家?guī)硪黄猰ysql 5.6 從陌生到熟練之_數(shù)據(jù)庫備份恢復(fù)的實現(xiàn)方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-10-10
MySQL兩個查詢?nèi)绾魏喜⒊梢粋€結(jié)果詳解
利用union關(guān)鍵字,可以給出多條select語句,并將它們的結(jié)果組合成單個結(jié)果集,下面這篇文章主要給大家介紹了關(guān)于MySQL兩個查詢?nèi)绾魏喜⒊梢粋€結(jié)果的相關(guān)資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2022-08-08
本篇文章是對Mysql中OPTIMIZE TABLE的作用進行了詳細的分析介紹，需要的朋友參考下2013-06-06