MySQL锁

https://blog.csdn.net/qq_21579045/article/details/99702766

 

#锁
CREATE TABLE mylock(
id int PRIMARY key auto_increment,
name VARCHAR(20)
)engine myisam;
INSERT mylock(name) values('e');
SELECT * from mylock;
#查看是否有锁
show OPEN tables;
#手动加锁
lock TABLE mylock read ,book WRITE;
show OPEN tables;
#解锁
UNLOCK tables;
show OPEN tables;

#加读锁影响，可共享，不能写入，别人也可以
lock TABLE mylock read;
SELECT * from mylock;
update mylock set name='a2' where id=1;#session1不能访问 ，session2堵塞了
SELECT * from book;#不能访问
UNLOCK tables;#出现阻塞了，赶快解锁

#加写锁影响，独占，自己一个人更新，查询都可以，别人就不行
lock TABLE mylock WRITE;
SELECT * from mylock;#自己可以，session2阻塞了
update mylock set name='a2' where id=1;#可以放问
SELECT * from book;#无法访问
UNLOCK tables;

#查看表锁分销
show open tables;#哪些表被锁了
#如何分析表锁定
show status like 'table%';

#行锁
#查看当前数据库事物隔离级别：默认应该是可重复读，不应该出现脏读和幻读
show variables like 'tx_isolation';

CREATE TABLE test_innodb_lock(a int(11),b VARCHAR(16)) engine=innodb;
INSERT into test_innodb_lock VALUES(1,'b1');
INSERT into test_innodb_lock VALUES(2,'3');
INSERT into test_innodb_lock VALUES(3,'100');
SELECT * from test_innodb_lock;
CREATE INDEX idx_a on test_innodb_lock(a);#索引默认是行锁
CREATE INDEX idx_b on test_innodb_lock(b);
#关闭默认提交
set autocommit=0;

UPDATE test_innodb_lock set b='001' where a=1;
SELECT * from test_innodb_lock;#session2出现了脏读
commit;

UPDATE test_innodb_lock set b='003' where a=1;#session2 也关掉commit,两边同时执行了update session2阻塞了
commit;

UPDATE test_innodb_lock set b='001' where a=1;#session1
UPDATE test_innodb_lock set b='002' where a=2;#session2
COMMIT;

SELECT * from test_innodb_lock;

#无索引会导致行锁升级为表锁
show INDEX from test_innodb_lock;
UPDATE test_innodb_lock set a='11' where b=001;#session1
UPDATE test_innodb_lock set a=11 where b='001';#session1
COMMIT;
UPDATE test_innodb_lock set b='00002' where a=2;#session2
SELECT * from test_innodb_lock;

#间隙锁危害
UPDATE test_innodb_lock set b='0000222' where a>2 and a<10;;;#session1 范围条件检索
INSERT into test_innodb_lock VALUES(9,'xxxx');#session2 此时阻塞了
COMMIT;

#如何锁定一行
BEGIN;
select * from test_innodb_lock where a=2 for update;#session1该行上锁
UPDATE test_innodb_lock set b='111' where a=2;#session2
COMMIT;

#行锁分析
show status like 'innodb_row_lock%';#主要查看：Innodb_row_lock_waits，Innodb_row_lock_current_waits