==============================================================================

按照非索引列更新

在可重復(fù)讀的事務(wù)隔離級(jí)別下，在非索引列上進(jìn)行更新和刪除會(huì)對(duì)所有數(shù)據(jù)行進(jìn)行加鎖，阻止其他會(huì)話對(duì)邊進(jìn)行任何數(shù)據(jù)的增刪改操作。

如果更新或刪除條件為c3=4且c3列上沒有索引則：1、不允許其他會(huì)話插入任意記錄，因?yàn)樗杏涗浀闹麈I索引上存在X排他鎖，無(wú)法申請(qǐng)插入意向X鎖(lock_mode X insertintention waiting Record lock)2、不允許其他會(huì)話刪除任意記錄，因?yàn)樗杏涗浀闹麈I索引上存在X排他鎖3、不允許其他會(huì)話更新任意記錄。因?yàn)樗杏涗浀闹麈I索引上存在X排他鎖

##=========================================##

測(cè)試數(shù)據(jù)：CREATE TABLE`tb4001` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`c1`int(11) DEFAULT NULL,

`c2`varchar(200) DEFAULT NULL,

`c3`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`idx_c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into tb4001(c1,c2,c3) values(2,2,2);insert into tb4001(c1,c2,c3) values(4,4,4);insert into tb4001(c1,c2,c3) values(7,7,7);insert into tb4001(c1,c2,c3) values(8,8,8);

##=========================================##

##測(cè)試1：在沒有索引的列上更新

##事務(wù)隔離級(jí)別：RR

會(huì)話1：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=777 where c3=7;

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(9,9,9);

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 75 page no 3 n bits 80 index PRIMARY of table `test1`.`tb4001` trx id 10573 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; ascsupremum;;---------------------TRANSACTION 10571, ACTIVE 404 sec

2 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1201 127.0.0.1admin

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=888 where c3=8;

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 75 page no 3 n bits 80 index PRIMARY of table `test1`.`tb4001` trx id 10573lock_mode X waiting

Record lock, heap no2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 8; hex 8000000000000001; asc;;1: len 6; hex 00000000293c; asc )

2 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1201 127.0.0.1admin

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;delete from tb4001 where c3=8;

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 75 page no 3 n bits 80 index PRIMARY of table `test1`.`tb4001` trx id 10573lock_mode X waiting

Record lock, heap no2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

0: len 8; hex 8000000000000001; asc;;1: len 6; hex 00000000293c; asc )

2 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1201 127.0.0.1 admin

View Code

==============================================================================

按照非唯一索引更新

在可重復(fù)讀的事務(wù)隔離級(jí)別下，按照非主鍵非唯一索引進(jìn)行更新和刪除，會(huì)對(duì)滿足條件的行加行鎖+滿足條件的區(qū)域加gap鎖

如果更新或刪除條件為c1=4且c1列上有非唯一索引則：1、允許其他會(huì)話插入c1<>4的記錄，但不允許插入c1=4的記錄2、允許其他會(huì)話更新c1<>4的記錄，但不允許將記錄更新為c1=4的記錄3、允許其他會(huì)話刪除c1<>4的記錄。4、允許插入\刪除\修改在c1列索引上與c1=4相鄰的記錄，雖然操作會(huì)影響gap鎖的邊界值。

##=========================================##

測(cè)試數(shù)據(jù)：CREATE TABLE`tb4001` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`c1`int(11) DEFAULT NULL,

`c2`varchar(200) DEFAULT NULL,

`c3`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`idx_c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into tb4001(c1,c2,c3) values(2,2,2);insert into tb4001(c1,c2,c3) values(4,4,4);insert into tb4001(c1,c2,c3) values(7,7,7);insert into tb4001(c1,c2,c3) values(8,8,8);

##=========================================##

##測(cè)試1：在沒有索引的列上更新

##事務(wù)隔離級(jí)別：RR

會(huì)話1：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=777 where c1=7;

##=========================================##

會(huì)話2:begin;SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(9,9,9);

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(7,7,7);

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息---TRANSACTION 10600, ACTIVE 7 sec inserting

mysql tables in use 1, locked 1LOCK WAIT2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id53, OS thread handle 140674620851968, query id 1317 127.0.0.1 admin update

insert into tb4001(c1,c2,c3) values(7,7,7)------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 75 page no 4 n bits 72 index idx_c1 of table `test1`.`tb4001` trx id 10600 lock_mode X locks gap before rec insertintention waiting

Record lock, heap no5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000008; asc;;1: len 8; hex 8000000000000004; asc;;---------------------TRANSACTION 10598, ACTIVE 270 sec

4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1306 127.0.0.1admin

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;delete from tb4001 where c1=8;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=888 where c1=8;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

會(huì)話2:SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;delete from tb4001 where c1=4;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

View Code

==============================================================================

按照非唯一索引+條件更新

在可重復(fù)讀事務(wù)隔離級(jí)別下，按照對(duì)非唯一索引列進(jìn)行更新，會(huì)對(duì)掃描到的所有索引記錄進(jìn)行更新，無(wú)論該記錄是否滿足WHERE中的其他條件。

##=========================================##

測(cè)試數(shù)據(jù)：CREATE TABLE`tb4001` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`c1`int(11) DEFAULT NULL,

`c2`varchar(200) DEFAULT NULL,

`c3`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`idx_c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into tb4001(c1,c2,c3) values(2,2,2);insert into tb4001(c1,c2,c3) values(4,4,4);insert into tb4001(c1,c2,c3) values(4,4,44);insert into tb4001(c1,c2,c3) values(7,7,7);insert into tb4001(c1,c2,c3) values(8,8,8);

##=========================================##

##會(huì)話1：

##事務(wù)隔離級(jí)別：RR

會(huì)話1：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=444 where c1=4 and c3=4;

##=========================================##

##會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=444 where c1=4 and c3=44;

會(huì)話2未被阻塞成功執(zhí)行

##執(zhí)行結(jié)果：會(huì)話2被阻塞

mysql tablesin use 1, locked 1LOCK WAIT2 lock struct(s), heap size 1136, 1row lock(s)

MySQL thread id76, OS thread handle 140674621384448, query id 1636 127.0.0.1admin updatingupdate tb4001 set c2=444 where c1=4 and c3=44

------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 79 page no 4 n bits 72 index idx_c1 of table `test1`.`tb4001` trx id 10781lock_mode X waiting

Record lock, heap no3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000004; asc;;1: len 8; hex 8000000000000002; asc;;---------------------TRANSACTION 10780, ACTIVE 52 sec

4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1MySQL thread id75, OS thread handle 140674621916928, query id 1618 127.0.0.1admin

##=========================================##

View Code

==============================================================================

按照非唯一組合索引更新

假設(shè)表TB1上有列C1和C2，有索引IDC_C1_C2(C1,C2)

在可重復(fù)提交事務(wù)隔離級(jí)別下,會(huì)話1按照C1=3 AND C2=4進(jìn)行更新，則：1、會(huì)話2按照C1=3 AND C2=4進(jìn)行更新,更新操作被阻塞2、會(huì)話2按照C1=3 AND C2=M(M<>4)進(jìn)行更新,更新操作不會(huì)被阻塞3、會(huì)話2可以在C1=3 AND C2=4之后間隙插入記錄，但不能在C1=3 AND C2=4之前的間隙插入記錄

##=========================================##

##測(cè)試數(shù)據(jù)CREATE TABLE`tb4001` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`c1`int(11) DEFAULT NULL,

`c2`varchar(200) DEFAULT NULL,

`c3`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`idx_c1_c3` (`c1`,`c3`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into tb4001(c1,c2,c3) values(2,2,2);insert into tb4001(c1,c2,c3) values(4,4,4);insert into tb4001(c1,c2,c3) values(4,4,44);insert into tb4001(c1,c2,c3) values(7,7,7);insert into tb4001(c1,c2,c3) values(8,8,8);

##=========================================##

##會(huì)話1：

##事務(wù)隔離級(jí)別：RR

會(huì)話1：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=444 where c1=4 and c3=4;

##=========================================##

##會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=444 where c1=4 and c3=44;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

##會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=444 where c1=2 and c3=2;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

##會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(4,4,3);

會(huì)話2被阻塞

##=========================================##

View Code

==============================================================================

按照唯一索引進(jìn)行更新

在可重復(fù)讀的事務(wù)隔離級(jí)別下，在唯一索引列上進(jìn)行更新和刪除在唯一索引的索引行上加排他鎖。

如果更新或刪除條件為c1=7且c1列上存在唯一索引則：1、阻止其他會(huì)話刪除和修改c1=7的記錄2、阻止其他會(huì)話插入c1=7的記錄3、允許其他會(huì)話插入\刪除\修改c1<>7的記錄，但不允許將記錄修改為c1=7的記錄

以上限制通過(guò)在唯一索引的索引記錄上加排他鎖X來(lái)實(shí)現(xiàn)，不會(huì)生產(chǎn)GAP鎖

在根據(jù)唯一索引進(jìn)行更新時(shí)，讀提交事務(wù)隔離級(jí)別(RC)和可重復(fù)讀事務(wù)隔離級(jí)別(RR)都只需要依賴唯一索引便可以保證事務(wù)ACID特性，無(wú)須使用GAP鎖。

##=========================================##

測(cè)試數(shù)據(jù)：CREATE TABLE`tb4001` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`c1`int(11) DEFAULT NULL,

`c2`varchar(200) DEFAULT NULL,

`c3`int(11) DEFAULT NULL,PRIMARY KEY(`id`),UNIQUE KEY`idx_c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;insert into tb4001(c1,c2,c3) values(2,2,2);insert into tb4001(c1,c2,c3) values(4,4,4);insert into tb4001(c1,c2,c3) values(7,7,7);insert into tb4001(c1,c2,c3) values(8,8,8);

##=========================================##

##測(cè)試1：在沒有索引的列上更新

##事務(wù)隔離級(jí)別：RR

會(huì)話1：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=777 where c1=7;

##=========================================##

會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(6,6,6);

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(7,7,7);

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息---TRANSACTION 10727, ACTIVE 9 sec inserting

mysql tables in use 1, locked 1LOCK WAIT2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id53, OS thread handle 140674620851968, query id 1553 127.0.0.1 admin update

insert into tb4001(c1,c2,c3) values(7,7,7)------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 77 page no 4 n bits 72 index idx_c1 of table `test1`.`tb4001` trx id 10727lock mode S waiting

Record lock, heap no4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000007; asc;;1: len 8; hex 8000000000000003; asc;;---------------------TRANSACTION 10721, ACTIVE 32 sec

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1544 127.0.0.1admin

##=========================================##

會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c2=888 where c1=8;

會(huì)話2未被阻塞成功執(zhí)行

##=========================================##

會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;update tb4001 set c1=7 where c1=8;

會(huì)話2未被阻塞成功執(zhí)行

##執(zhí)行結(jié)果：會(huì)話2被阻塞

使用SHOW ENGINE INNODB STATUS \G查看阻塞發(fā)生時(shí)的鎖信息---TRANSACTION 10730, ACTIVE 4 sec updating or deleting

mysql tables in use 1, locked 1LOCK WAIT4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL thread id53, OS thread handle 140674620851968, query id 1567 127.0.0.1admin updatingupdate tb4001 set c1=7 where c1=8

------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 77 page no 4 n bits 72 index idx_c1 of table `test1`.`tb4001` trx id 10730lock mode S waiting

Record lock, heap no4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000007; asc;;1: len 8; hex 8000000000000003; asc;;---------------------TRANSACTION 10721, ACTIVE 100 sec

3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id52, OS thread handle 140674621650688, query id 1544 127.0.0.1admin

##=========================================##

會(huì)話2：SET SESSION tx_isolation='REPEATABLE-READ';

STARTTRANSACTION;SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;delete from tb4001 where c1=8;

會(huì)話2未被阻塞成功執(zhí)行

View Code

==============================================================================

總結(jié)

以上是生活随笔為你收集整理的mysql 事物 锁行 测试_MySQL Transaction--RR事务隔离级别下加锁测试的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。