mysql-行锁案例讲解

建表

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');

create index test_innodb_a_ind on test_innodb_lock(a);

create index test_innodb_lock_b_ind on test_innodb_lock(b);

行锁定基本演示

• 首先要在两个会话中都关闭自动提交

set autocommit=0;

image.png
image.png
image.png image.png

无索引行锁升级为表锁

image.png

间隙锁危害

image.png image.png

常考如何锁定一行

image.png

案例结论

image.png image.png image.png image.png

优化建议

image.png