MySQL之锁机制(表锁和行锁)
MySQL之锁机制(表锁和行锁)
锁的定义
锁是计算机协调多个进程或线程并发访问某一资源的机制。
- 在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种共享资源,如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。
- 锁冲突是影响数据库并发访问性能的一个重要因素,从这个角度来说,锁对数据库而言显得尤为重要,而且也更加复杂
锁的分类
从对数据的操作类型分为:读锁(共享锁)和 写锁(排他锁)
读锁:针对同一份数据,对该数据的读操作可以同时进行且不受影响。
写锁:写操作未完成前,会阻断其他的读操作和写操作。
从对数据的操作粒度分为:表锁 和 行锁
表锁
表锁特点
MylSAM引擎
使用表锁,开销小,加锁快,无死锁,锁定力度大,发生锁冲突的概率最高。- 并发度最低
- 不支持事务
模拟数据
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) 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');
select * from mylock;
查看数据库锁
SHOW OPEN TABLES in hanyxx; --查看数据库hanyxx中的表是否加锁
LOCK TABLE book read , phone write -- book表加读锁,phone表加写锁
--全部解锁
UNLOCK TABLES;
表锁(读锁)
主机A给表加上表锁(读锁)
- 主机A和其他主机都可以读取该表的信息
- 主机A不能读取其他表的信息,但其他主机可以读取库中其他表的信息
- 主机A不能对锁住的表进行修改
- 其他主机对表进行修改,会被阻塞,直到表锁(读锁)被释放
表锁(写锁)
主机A给表加上表锁(写锁)
- 主机A可以读取该表信息,但其他主机读取时,会进入阻塞状态,直到表锁(写锁)被释放
- 主机A不能读取其他表的信息,但其他主机不能读取该表信息,但可以读取其他表的信息
- 主机A可以对该表进行修改
- 其他主机不能对该表进行修改,会被阻塞,直到表锁(写锁)被释放
总结
读锁不会阻塞读,只会阻塞写。但是写锁会阻塞读和写。
表锁定分析
行锁(重点)
行锁特点
- 开销大,枷锁慢,会出现死锁
- 锁定粒度最小,发生锁冲突概率最低,并发度最高
InnoDB和MyISAM最大的不同点
- 支持事务
- 采用行锁
并发事务产生的问题
- 脏读
- 不可重复读
- 幻读
事务隔离级别
- 查看数据库隔离级别
show variables like 'tx_isolation'; -- MySQL 5.7之前的版本
show variables like 'transaction_isolation'; -- MySQL 5.7之后的版本
MySQL数据库默认隔离级别:REPEATABLE-READ
模拟数据
-- 创建表
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);
-- InnnDB事务自动提交,如果需要演示行锁,需要关闭自动提交
SET autocommit=0;
行锁基本演示
行锁演示结论
如果两个客户端对同一条记录进行修改
- 客户端A修改后,未提交(未commit),此时客户端B修改,则会阻塞
- 客户端A修改后,提交后,客户端B再修改,则不会阻塞
- 如果两个客户端分别对不同的记录行进行修改,则不会被阻塞
索引失效
索引失效,行锁变表锁(通过varchar类型不加单引号让索引失效)
当索引失效后,即使多个客户端操作的不是同一条记录,如果未提交,其他客户端也会进入阻塞状态
所以要避免索引失效
为甚么索引失效行锁会变表锁
- InnoDB 行级锁是通过给索引上的索引项加锁来实现的,InnoDB行级锁只有通过索引条件检索数据,才使用行级锁
- 否则,InnoDB使用表锁 在不通过索引(主 键)条件查询的时候,InnoDB是表锁而不是行锁。
间隙锁
什么是间隙锁
间隙锁演示
间隙锁的危害
面试题:如何锁定一行
行锁分析
InnoDB_row_lock
- 通过检查InnoDB_row_lock状态变量分析行锁竞争情况
show STATUS like 'innodb_row_lock%'; -- 查看InnoDB_row_lock状态变量
字段说明
字段 | 说明 |
---|---|
Innodb_row_lock_current_waits | 当前正在等待锁定的数量 |
Innodb_row_lock_time (重要) | 从系统启动至今,总锁定了多长时间 |
Innodb_row_lock_time_avg (重要) | 每次锁定的平均时间 |
Innodb_row_lock_time_max | 最长的一次锁定时间 |
Innodb_row_lock_waits (重要) | 从系统启动至今,一共锁定了多少次 |
优化建议
- 尽可能让数据检索通过索引完成,避免无索引,让行锁升级为表锁
- 合理设计索引,缩小锁的范围
- 尽可能减少检索条件,避免间隙锁
- 尽可能控制事务的大小,减少锁定资源量和时间长度
- 尽可能采用低级别的事务隔离级别
总结
页锁(补充):
- 开销和加锁时间介于表锁和行锁之间,会出现死锁
- 锁定粒度介于表锁和行锁之间,并发度一般