mysql高级(锁机制)
1、锁分类
2、表锁
2.1、表锁--加读锁
2.2、表锁--加写锁
2.3、总结
2.4、表锁定的统计信息
3、行锁
3.1、不能读取未提交的数据
3.2、不同线程更新同一条记录,后更新的被阻塞
4、索引失效导致行锁变表锁
5、间隙锁的危害
6、如何锁定一行
7、如何分析行锁的统计信息
1、锁分类 <--返回目录
从对数据操作的类型分类:
1)读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响。
2)写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。
从对数据操作的粒度分类:表锁(偏读)、行锁(偏写)、页锁
2、表锁 <--返回目录
表锁特点:偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
演示建表SQL:
create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam; insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e');
手动增加表锁:lock table 表名1 read[write], 表名2 read[write];
查看表上的锁:show open tables; in_use 为1表示有锁
删除所有锁:unlock tables;
2.1、表锁--加读锁 <--返回目录
会话1给mylock表添加读锁,会话1可以读mylock表,不可以更新mylock表;会话2可以读mylock表,更新mylock表时被阻塞,等待会话1解锁。
会话2给mylock表加读锁后,在没有解锁前,不可以读其他表;会话2可以读其他表。
2.2、表锁--加写锁 <--返回目录
会话2给mylock表添加写锁,会话1可以读和写mylock表,不可以读写其他表;并且阻塞其他会话对mylock表的读和写。
2.3、总结 <--返回目录
MyISAM是表锁:执行select给表加表读锁,执行增删改给表加表写锁。
读锁会阻塞 其他会话 对加了读锁的表的写操作,但不会阻塞读操作;写锁会阻塞 其他会话 读和写。
2.4、表锁定的统计信息 <--返回目录
查看哪些表被加锁了:show open tables;
分析表锁定的次数:show status like ''table%;
此外,MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写操作为主的引擎。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。
3、行锁 <--返回目录
行锁特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的最大不同有两点:一是支持事务;二是采用了行级锁。
演示建表SQL:
create table innodb_lock( a int not null primary key auto_increment, b varchar(20) )engine innodb; insert into innodb_lock(b) values('b1'); insert into innodb_lock(b) values('b2'); insert into innodb_lock(b) values('3'); insert into innodb_lock(b) values('4000'); insert into innodb_lock(b) values('5000'); insert into innodb_lock(b) values('6000'); insert into innodb_lock(b) values('7000'); create index innodb_lock_a on innodb_lock(a); create index innodb_lock_b on innodb_lock(b);
关闭自动提交:set autocommit=0;
查看:show variables like 'autocommit';
3.1、不能读取未提交的数据 <--返回目录
会话1可以查看到自己更新的记录,其他会话无法查看到(会话1未提交的记录)
会话1commit后,会话2才能查看到更新的数据
3.2、不同线程更新同一条记录,后更新的被阻塞 <--返回目录
会话1未commit前,会话2更新相同的记录(被会话1行锁锁定了)会被阻塞
4、索引失效导致行锁变表锁 <--返回目录
正常情况下,会话1更新记录b=‘5000’(原来a=5),会话2更新记录a=1,互不干扰。
如果会话1SQL使用不当,索引失效从而导致行锁变表锁,则会阻塞会话2的更新操作
5、间隙锁的危害 <--返回目录
会话1更新 a>1 and a<200的记录,导致会话2插入阻塞
会话1 更新条件 a>1也会导致其他会话更新阻塞
6、如何锁定一行 <--返回目录
begin;
select * from 表名 where id=xxx for update;
commit;
7、如何分析行锁的统计信息 <--返回目录
show status like 'innodb_row_lock%';
---
posted on 2020-07-20 00:58 wenbin_ouyang 阅读(210) 评论(0) 编辑 收藏 举报