兰州网络推广做啥的,搜索引擎优化的方式有哪些,移动知识库管理系统,武威做网站按照非索引列更新在可重复读的事务隔离级别下#xff0c;在非索引列上进行更新和删除会对所有数据行进行加锁#xff0c;阻止其他会话对边进行任何数据的增删改操作。如果更新或删除条件为c34且c3列上没有索引则#xff1a;1、不允许其他会话插入任意记录#xff0c;因为所…按照非索引列更新在可重复读的事务隔离级别下在非索引列上进行更新和删除会对所有数据行进行加锁阻止其他会话对边进行任何数据的增删改操作。如果更新或删除条件为c34且c3列上没有索引则1、不允许其他会话插入任意记录因为所有记录的主键索引上存在X排他锁无法申请插入意向X锁(lock_mode X insertintention waiting Record lock)2、不允许其他会话删除任意记录因为所有记录的主键索引上存在X排他锁3、不允许其他会话更新任意记录。因为所有记录的主键索引上存在X排他锁####测试数据CREATE TABLEtb4001 (idbigint(20) NOT NULLAUTO_INCREMENT,c1int(11) DEFAULT NULL,c2varchar(200) DEFAULT NULL,c3int(11) DEFAULT NULL,PRIMARY KEY(id),KEYidx_c1 (c1)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;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);######测试1在没有索引的列上更新##事务隔离级别RR会话1SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2777 where c37;####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(9,9,9);##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息------- 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 00: len 8; hex 73757072656d756d; ascsupremum;;---------------------TRANSACTION 10571, ACTIVE 404 sec2 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####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2888 where c38;##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息------- 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 waitingRecord lock, heap no2 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: 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####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;delete from tb4001 where c38;##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息------- 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 waitingRecord lock, heap no2 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: 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 adminView Code按照非唯一索引更新在可重复读的事务隔离级别下按照非主键非唯一索引进行更新和删除会对满足条件的行加行锁满足条件的区域加gap锁如果更新或删除条件为c14且c1列上有非唯一索引则1、允许其他会话插入c14的记录但不允许插入c14的记录2、允许其他会话更新c14的记录但不允许将记录更新为c14的记录3、允许其他会话删除c14的记录。4、允许插入\删除\修改在c1列索引上与c14相邻的记录虽然操作会影响gap锁的边界值。####测试数据CREATE TABLEtb4001 (idbigint(20) NOT NULLAUTO_INCREMENT,c1int(11) DEFAULT NULL,c2varchar(200) DEFAULT NULL,c3int(11) DEFAULT NULL,PRIMARY KEY(id),KEYidx_c1 (c1)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;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);######测试1在没有索引的列上更新##事务隔离级别RR会话1SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2777 where c17;####会话2:begin;SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(9,9,9);会话2未被阻塞成功执行####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(7,7,7);##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息---TRANSACTION 10600, ACTIVE 7 sec insertingmysql 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 updateinsert 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 waitingRecord lock, heap no5 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000008; asc;;1: len 8; hex 8000000000000004; asc;;---------------------TRANSACTION 10598, ACTIVE 270 sec4 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####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;delete from tb4001 where c18;会话2未被阻塞成功执行####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2888 where c18;会话2未被阻塞成功执行####会话2:SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;delete from tb4001 where c14;会话2未被阻塞成功执行####View Code按照非唯一索引条件更新在可重复读事务隔离级别下按照对非唯一索引列进行更新会对扫描到的所有索引记录进行更新无论该记录是否满足WHERE中的其他条件。####测试数据CREATE TABLEtb4001 (idbigint(20) NOT NULLAUTO_INCREMENT,c1int(11) DEFAULT NULL,c2varchar(200) DEFAULT NULL,c3int(11) DEFAULT NULL,PRIMARY KEY(id),KEYidx_c1 (c1)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;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);######会话1##事务隔离级别RR会话1SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2444 where c14 and c34;######会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2444 where c14 and c344;会话2未被阻塞成功执行##执行结果会话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 c2444 where c14 and c344------- 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 waitingRecord lock, heap no3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000004; asc;;1: len 8; hex 8000000000000002; asc;;---------------------TRANSACTION 10780, ACTIVE 52 sec4 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按照非唯一组合索引更新假设表TB1上有列C1和C2有索引IDC_C1_C2(C1,C2)在可重复提交事务隔离级别下,会话1按照C13 AND C24进行更新则1、会话2按照C13 AND C24进行更新,更新操作被阻塞2、会话2按照C13 AND C2M(M4)进行更新,更新操作不会被阻塞3、会话2可以在C13 AND C24之后间隙插入记录但不能在C13 AND C24之前的间隙插入记录######测试数据CREATE TABLEtb4001 (idbigint(20) NOT NULLAUTO_INCREMENT,c1int(11) DEFAULT NULL,c2varchar(200) DEFAULT NULL,c3int(11) DEFAULT NULL,PRIMARY KEY(id),KEYidx_c1_c3 (c1,c3)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;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);######会话1##事务隔离级别RR会话1SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2444 where c14 and c34;######会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2444 where c14 and c344;会话2未被阻塞成功执行######会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2444 where c12 and c32;会话2未被阻塞成功执行######会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(4,4,3);会话2被阻塞####View Code按照唯一索引进行更新在可重复读的事务隔离级别下在唯一索引列上进行更新和删除在唯一索引的索引行上加排他锁。如果更新或删除条件为c17且c1列上存在唯一索引则1、阻止其他会话删除和修改c17的记录2、阻止其他会话插入c17的记录3、允许其他会话插入\删除\修改c17的记录但不允许将记录修改为c17的记录以上限制通过在唯一索引的索引记录上加排他锁X来实现不会生产GAP锁在根据唯一索引进行更新时读提交事务隔离级别(RC)和可重复读事务隔离级别(RR)都只需要依赖唯一索引便可以保证事务ACID特性无须使用GAP锁。####测试数据CREATE TABLEtb4001 (idbigint(20) NOT NULLAUTO_INCREMENT,c1int(11) DEFAULT NULL,c2varchar(200) DEFAULT NULL,c3int(11) DEFAULT NULL,PRIMARY KEY(id),UNIQUE KEYidx_c1 (c1)) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8;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);######测试1在没有索引的列上更新##事务隔离级别RR会话1SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2777 where c17;####会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(6,6,6);会话2未被阻塞成功执行####会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;insert into tb4001(c1,c2,c3) values(7,7,7);##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息---TRANSACTION 10727, ACTIVE 9 sec insertingmysql 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 updateinsert 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 waitingRecord lock, heap no4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000007; asc;;1: len 8; hex 8000000000000003; asc;;---------------------TRANSACTION 10721, ACTIVE 32 sec3 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####会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c2888 where c18;会话2未被阻塞成功执行####会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;update tb4001 set c17 where c18;会话2未被阻塞成功执行##执行结果会话2被阻塞使用SHOW ENGINE INNODB STATUS \G查看阻塞发生时的锁信息---TRANSACTION 10730, ACTIVE 4 sec updating or deletingmysql 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 c17 where c18------- 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 waitingRecord lock, heap no4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000007; asc;;1: len 8; hex 8000000000000003; asc;;---------------------TRANSACTION 10721, ACTIVE 100 sec3 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####会话2SET SESSION tx_isolationREPEATABLE-READ;STARTTRANSACTION;SELECT GLOBAL.tx_isolation, SESSION.tx_isolation;delete from tb4001 where c18;会话2未被阻塞成功执行View Code
