MySQL高级——MySql间隙锁的案例

一、建表SQL

1、创建test_innodb_lock表的SQL

CREATE TABLE test_innodb_lock (
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4,'4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6,'6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8,'8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

2、创建test_innodb_lock表a字段索引

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);

3、创建test_innodb_lock表b字段索引

CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

4、执行查询语句

SELECT * FROM test_innodb_lock;

二、MySql间隙锁的基本演示

1、 在客户端1 和客户端2中都手动关闭自动提交功能

#关闭mysql自动提交
SET autocommit=0;
#开启mysql自动提交
SET autocommit=1;

2、在客户端1中更新a>1 AND a1 AND a1 AND a1 AND a