锁的概述
锁的定义
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中,除传统的计算资源(如cpu, ram, i/o等)的争用外,数据也是一种提供许多用户共享的源。如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤为重要,也是更加复杂。
锁的分类
- 从对数据操作的类型(读、写)分
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响。
- 写锁(排他锁):当前写操作没有完成前,它会阻断其他写锁和读锁。
- 从对数据操作的粒度分:表锁和行锁。
表锁(偏读)
-
特点:偏向MyISAM存储引擎,加锁开销小,加锁快。不产生死锁,锁定粒度大,发生冲突的概率高,并发度最低。
-
案例分析
- 建表SQL
【表级锁分析-建表SQL】 drop table mylock; 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'); select * from mylock; 【手动增加表锁】 lock table 表名字 read(write), 表名字2 read(write), 其他; 【查看表上加过的锁】 show open tables; 【释放锁】 unlock tables;
- 加读锁
- 加写锁
- 建表SQL
-
案例结论
MyISAM在执行查询语句(select)前,会自动给设计的所有表加读锁;在执行增删改操作前,会自动给涉及的表加写锁。MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和 表独占写锁(Table Write Lock)锁类型 可否兼容 读锁 写锁 读锁 是 是 否 写锁 是 否 否 结论:结合上表,对 MyISAM 表进行操作,会有以下情况:
- 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的写操作。
- 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞。
-
表锁分析
【看看哪些表被加锁了】 show open tables; 【如何分析表锁定】 可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定: show status like 'table%';
这里有两个状态变量记录MySQL内部表级锁定情况,两个变量说明如下:
Table_locks_immediate:产生表级锁定的次数,表示可以立即获得锁的查询次数,每次立即获取锁定值加1;
Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在这较严重的表级锁定争用情况;
此外,MyISAM的读写锁调度是写优先,这也是MyISAM不适合做以写为主的表的引擎。因为写锁存在,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。
行锁(偏读)
-
特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度小,发生冲突概率低,并发度高。InnoDB与MyISAM最大的不同点:支持事务和采用了行锁机制。
-
案例分析
- 见表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'); create index test_innodb_a_ind on test_innodb_lock(a); create index test_innodb_lock_b_ind on test_innodb_lock(b); select * from test_innodb_lock;
- 行锁的基本演示
- 无索引更新,导致行锁升级为表锁
test_innodb_lock 这个表上 a,b两个字段都创建了索引。innodb引擎下的表,在更新的时候,默认是行锁。锁更新的行。但是,更新语句操作不当,会使得行锁变成表锁。例如:
但是如果语句写成了update test_innodb_lock set a = 10 where b = '4000';
update test_innodb_lock set a = 10 where b = 4000;
- 见表SQL
-
案例结论:
Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面锁带来的性能损耗可能比表锁要高一些,但是在整体并发处理能力方面要远远由于MyISAM的表级锁定。当系统并发较高的时候,Innodb的整体性能和MyISAM相比会有比较明显的优势了。但是Innodb的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让innodb的整体性能表现比MyISAM差。 -
行锁分析
【如何分析锁定行】通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况 show status like '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: 系统启动后到现在总共等待的次数对于这5个状态变量,比较重要的是
Innodb_row_lock_time_avg (等待平均时长)
Innodb_row_lock_waits (等待总次数)
Innodb_row_lock_time (等待总时长)这三项尤其是当前等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。
-
间隙锁
当我们使用范围条件而不是相等条件去检索数据,并请求共享锁或排它锁时,InnoDB会给复合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做 间隙(GAP) InnoDB也会对这个 间隙 加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。 -
优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引或索引失效导致行锁升级为表锁。
- 合理设计索引,尽量缩小锁的范围。
- 范围检索的范围尽量小点,避免间隙锁。
- 控制事务的大小,减少锁定资源量和时间长度。