select * from table where ?;

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 ?;

-- 基本操作 ---- 查詢事務(wù)隔離級(jí)別，默認(rèn)是RRshow variables like '%isolation%';-- 設(shè)置事務(wù)隔離級(jí)別為RCset 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 ("[email protected]", 18, "address1");insert into user (email, age, address) values ("[email protected]", 20, "address2");insert into user (email, age, address) values ("[email protected]", 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ù)idSELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();-- INNODB 引擎狀態(tài)SHOW ENGINE INNODB STATUS;commit;-- 二、可重復(fù)讀、不可重復(fù)讀示例-- session1set session transaction isolation level read committed;begin;-- session2set session transaction isolation level repeatable read;begin;-- session1select * from user;-- session2select * from user;-- session3begin;insert into user (email, age, address) values ("[email protected]", 30, "address4");commit;-- session1 這里因?yàn)槭荝C，所以可以讀到trx3提交的新數(shù)據(jù)，這里如果是證明不可重復(fù)讀的話應(yīng)該使用update而不是insertselect * from user;commit;-- session2 這里因?yàn)槭荝R，所以不會(huì)讀到trx3提交的新數(shù)據(jù)select * from user;commit;-- 三、快照讀幻讀示例-- session1set session transaction isolation level repeatable read;begin;-- 這里使用快照讀select * from user;-- session2begin;insert into user (email, age, address) values ("[email protected]", 30, "address4");commit;select * from user;-- session1select * from user; -- 這里讀不到test4@的數(shù)據(jù)，因?yàn)槭荝R-- 這里發(fā)生了幻讀insert into user (email, age, address) values ("[email protected]", 30, "address4"); -- 插入失敗因?yàn)閑mail唯一索引沖突commit;-- 四、當(dāng)前讀幻讀示例-- RC-- session1set session transaction isolation level read committed;begin;-- 這里會(huì)對(duì)所有滿足條件的age=20的記錄加鎖，因?yàn)槭荝C，所以沒(méi)有GAP鎖delete from user where age = 20;select * from user;-- session2set session transaction isolation level read committed;begin;-- 因?yàn)閠rx1沒(méi)有加GAP鎖，所以之類可以插入age=20的記錄insert into user (email, age, address) values ("[email protected]", 20, "address4");select * from user; -- 可以查到4條數(shù)據(jù)，可以讀到trx1的刪除數(shù)據(jù)，因?yàn)槭荝C，trx1未提交所以沒(méi)影響trx2commit;-- session1select * from user; -- 可以讀到trx2新插入的數(shù)據(jù)，雖然trx1是當(dāng)前讀，但是并未添加相應(yīng)的next-key鎖，沒(méi)有阻止trx2的新數(shù)據(jù)插入commit;--RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20;select * from user;-- session2begin;-- 這里會(huì)阻塞，因?yàn)閠rx1在age=20周圍加了GAP鎖-- 非唯一索引，首先，通過(guò)索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖；-- 然后讀取下一條，重復(fù)進(jìn)行。直至進(jìn)行到第一條不滿足條件的記錄，此時(shí)，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結(jié)束。insert into user (email, age, address) values ("[email protected]", 20, "address4");-- 直到超時(shí)，ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction-- 此時(shí)如果查詢可以看到3條記錄commit;-- session1-- 此時(shí)只能看到1條記錄，另外兩條被刪除了select * from user;commit;-- 唯一索引+RC-- session1set session transaction isolation level read committed;begin;delete from user where email = "[email protected]";-- session2begin;-- 可以讀到，因?yàn)閠rx1是RCselect * from user where email = "[email protected]";-- 嘗試更新這個(gè)記錄的age，會(huì)阻塞直到超時(shí)，因?yàn)閑mail是唯一索引已經(jīng)被trx1鎖住了，同時(shí)也會(huì)在對(duì)應(yīng)的主鍵索引上加鎖-- 注意這里操作的id=3就是trx1中操作的email的同一行記錄update user set age = 40 where id = 3;-- session1commit;-- session2commit;-- 無(wú)索引+RC-- session1set session transaction isolation level read committed;begin;-- 由于address字段無(wú)索引，所以Innodb會(huì)對(duì)所有行進(jìn)行加鎖，由MySQL server進(jìn)行判斷并釋放鎖delete from user where address = "address3";-- session2set session transaction isolation level read committed;begin;-- 這一行會(huì)成功，因?yàn)檫@一行沒(méi)有加鎖（先加了后釋放了）update user set age = 10 where address = "address2";-- 這一行同樣會(huì)被阻塞，原因是它已經(jīng)被trx1的語(yǔ)句加了鎖了，全部符合條件的都加鎖了update user set age = 10 where address = "address3";-- session1commit;-- session2commit;-- 非唯一索引+RR-- session1set session transaction isolation level repeatable read;begin;delete from user where age = 20;-- session2set session transaction isolation level repeatable read;begin;-- 這里會(huì)阻塞，因?yàn)閠rx1中已經(jīng)鎖住了age=20的記錄以及加上了GAP鎖，所以這里18已經(jīng)落入鎖區(qū)間insert into user (email, age, address) values ("[email protected]", 18, "address4");-- session1commit;-- session2commit;-- 無(wú)索引RR-- session1set session transaction isolation level repeatable read;begin;-- 沒(méi)有索引，那么會(huì)鎖上表中的所有記錄，同時(shí)會(huì)鎖上主鍵索引上的所有GAP，杜絕所有的并發(fā)更新操作delete from user where address = "address3";-- session2set session transaction isolation level repeatable read;begin;-- 這里會(huì)阻塞，原因是主鍵已經(jīng)被加上了GAP鎖，所以新的插入不能執(zhí)行成功insert into user (email, age, address) values ("[email protected]", 18, "address4");-- session1commit;-- session2commit;-- 死鎖 簡(jiǎn)單示例-- session1begin;delete from user where id = 1;-- session2begin;delete from user where id = 3;-- session1delete from user where id = 3;-- seession2-- 這里MySQL判斷發(fā)生了死鎖，中斷了一個(gè)trx-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactiondelete from user where id = 1;-- session1rollback;-- 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;-- session1begin;insert into t1 values (2);-- sessioin2begin;-- 這里會(huì)阻塞insert into t1 values (2);-- session3begin;-- 這里會(huì)阻塞insert into t1 values (2);-- session1;-- 此時(shí)回滾，trx2和trx3收到通知，MySQL自動(dòng)中斷一個(gè)trx，因?yàn)榘l(fā)生了死鎖-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionrollback;--session2;rollback;--session3;rollback;