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=4的数据但是不提交（没有手动提交），在客户端1和客户端2中分别查询。

UPDATE test_innodb_lock SET b='4567' WHERE a ='4';
SELECT * FROM test_innodb_lock;

• 结论：客户端1中可以查询到更新但没提交的数据，客户端2中查询不到客户端1中更新但没有提交的数据。

3、在客户端1中更新a=4的数据并手动提交，然后在客户端1和客户端2中分别查询。

UPDATE test_innodb_lock SET b='4567' WHERE a ='4';
COMMIT;
SELECT * FROM test_innodb_lock;

• 结论：客户端1中可以查询到更新并且手动提交的数据，客户端2中同样可以查询到客户端1中更新并且手动提交的数据。因为客户端2也关闭了自动提交，所以查询之前要commit。

4、在客户端1中更新a=4的数据但没有手动提交，然后在客户端2中也更新a=4的数据也没有手动提交，查看客户端2中是否能更新数据。

#在客户端1中执行
UPDATE test_innodb_lock SET b='4001' WHERE a ='4';
#然后在客户端2中执行
UPDATE test_innodb_lock SET b='4002' WHERE a ='4';

• 结论：在客户端1中更新a=4的数据但没有手动提交，在客户端2中也更新a=4的数据但没有手动提交，客户端2产生阻塞。

5、在客户端1中更新a=4的数据并且手动提交，然后在客户端2中查看是否可以更新a=4的数据。

#在客户端1中执行
UPDATE test_innodb_lock SET b='4001' WHERE a ='4';
COMMIT;

#然后在客户端2中执行
UPDATE test_innodb_lock SET b='4002' WHERE a ='4';
COMMIT;

• 结论：在客户端1中更新a=4的数据并且手动提交，在客户端2中才可以更新a=4的数据。

6、在客户端1中更新a=4的数据但不手动提交，然后在客户端2中更新a=8的数据也不手动提交。查看客户端1和客户端2是否能更新成功。

#在客户端1中执行
UPDATE test_innodb_lock SET b='4005' WHERE a ='4';
#然后在客户端2中执行
UPDATE test_innodb_lock SET b='8001' WHERE a ='8';

• 结论：在客户端1中更新a=4的数据但不手动提交，然后在客户端2中更新a=8的数据也不手动提交。客户端1和客户端2都能更新数据成功。