锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
概述
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
![](https://i-blog.csdnimg.cn/blog_migrate/ff9aecddc1a7f2ec686b1af44669e5df.png)
MySQL表级锁的锁模式(MyISAM)
- 对MyISAM的读操作,不会阻塞其他用户对同一表请求,但会阻塞对同一表的写请求;
- 对MyISAM的写操作,则会阻塞其他用户对同一表的读和写操作;
- MyISAM表的读操作和写操作之间,以及写操作之间是串行的。
MySQL表级锁的锁模式
MySQL中的表锁兼容性
当前锁模式/是否兼容/请求锁模式 | 读锁 | 写锁 |
读锁 | 是 | 否 |
写锁 | 否 | 否 |
如何加表锁
1
2
|
SELECT
SUM
(total)
FROM
orders;
SELECT
SUM
(subtotal)
FROM
order_detail;
|
1
2
3
4
|
LOCK tables orders
read
local
,order_detail
read
local
;
SELECT
SUM
(total)
FROM
orders;
SELECT
SUM
(subtotal)
FROM
order_detail;
Unlock tables;
|
- 上面的例子在LOCK TABLES时加了‘local’选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾插入记录
- 在用LOCKTABLES给表显式加表锁是时,必须同时取得所有涉及表的锁,并且MySQL支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MySQL问题一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因
并发锁
concurrent_insert和local操作(此操作为MyISAM引擎专有,InnoDB无此功能)
上面我们说到只要给一个表加了读锁,其他session对该表的写操作将被阻塞。那么有没有办法让其他session也能往里面添加数据呢?
这里我们可以使用local关键字,语法如下:lock table 表名 read local。这样在当前表被加读锁的时候,可以让其他session往表里添加记录,但需要配合concurrent_insert全局变量使用。
- 当concurrent_insert设置为0时,不允许并发插入。
- 当concurrent_insert设置为1时,如果MyISAM允许在一个读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。如果MyISAM表中没有空洞洞(即表的中间没有被删除的⾏行行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插⼊入记录。这也是MySQL的默认设置。
- 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾插入记录,都允许在表尾并发插入记录。
MyISAM的锁调度
- 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
InnoDB锁问题
1.事务(Transaction)及其ACID属性
- 原性性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
- 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
- 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
- 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
2.并发事务带来的问题
- 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题
- 脏读(Dirty Reads):A事务读取B事务尚未提交的更改数据,并在这个数据的基础上进行操作,这时候如果事务B回滚,那么A事务读到的数据是不被承认的。。这种现象被形象地叫做“脏读”。
- 不可重复读(Non-Repeatable Reads):事务A首先读取了一条数据,然后执行逻辑的时候,事务B将这条数据改变了,然后事务A再次读取的时候,发现数据不匹配了,就是所谓的不可重复读了。
- 也就是说,当前事务先进行了一次数据读取,然后再次读取到的数据是别的事务修改成功的数据,导致两次读取到的数据不匹配,也就照应了不可重复读的语义。
- 幻读(Phantom Reads):事务A首先根据条件索引得到N条数据,然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据,导致事务A再次搜索发现有N+M条数据了,就产生了幻读。
也就是说,当前事务读第一次取到的数据比后来读取到数据条目少。
不可重复读和幻读比较:
两者有些相似,但是前者针对的是update或delete,后者针对的insert。
注意:不可重复读和幻读的区别是:前者是指读到了已经提交的事务的更改数据(修改或删除),后者是指读到了其他已经提交事务的新增数据。
3.事务隔离级别
事务4种隔离级别比较
隔离级别/读数据一致性及允许的并发副作用 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
未提交读(Read uncommitted)
| 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交度(Read committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
获取InonoD行锁争用情况
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show status
like
'innodb_row_lock%'
;
+
-------------------------------+-------+
| Variable_name | Value |
+
-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+
-------------------------------+-------+
5
rows
in
set
(0.00 sec)
|
InnoDB的行锁模式及加锁方法
- 共享锁(s):共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
- 排他锁(X):排他锁又称为写锁,简称X锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
![](https://i-blog.csdnimg.cn/blog_migrate/67006f1dea75f691f834150dffa7875e.png)
InnoDB行锁模式兼容性列表
我们有如下测试数据
现在我们对id=1的数据行排他查询,这里会使用begin开启事务,而不会看见我关闭事务,这样做是用来测试,因为提交事务或回滚事务就会释放锁。
打开一个查询窗口
会查询到一条数据,现在打开另一个查询窗口,对同一数据分别使用排他查和共享锁查询两种方式查询
排他查
共享查
我们看到开了排他锁查询和共享锁查询都会处于阻塞状态,因为id=1的数据已经被加上了排他锁,此处阻塞是等待排他锁释放。
如果我们直接使用以下查询呢
我们看到是可以查询到数据的。
我们再看一下一个事务获取了共享锁,在其他查询中也只能加共享锁或不加锁。
我们看到是可以查询数据的,但加排他锁就查不到,因为排他锁与共享锁不能存在同一数据上。
最后我们验证下上面说的mysql InnoDb引擎中update,delete,insert语句自动加排他锁的问题,
此时共享查询处于阻塞,等待排它锁的释放,但是用普通查询能查到数据,因为没用上锁机制不与排他锁互斥,但查到的数据是修改数据之前的老数据。
然后我们提交数据,释放排他锁看下修改后的数据,此时可用排他查,共享查和普通查询, 因为事务提交后该行数据释放排他锁,下面就只显示普通查询,其他的同学们自己去验证。
可以看到结果与预期的一样。
InnoDB行锁实现方式
![](https://i-blog.csdnimg.cn/blog_migrate/e026241fcf28d356adafa4c7c161d1d4.png)
间隙锁(Next-Key锁)
SELECT * FROM emp WHERE empid > 100 FOR UPDATE
什么时候使用表锁
- 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
1
2
3
4
5
|
SET
AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2
READ
, ...;
[do something
with
tables t1
and
here];
COMMIT
;
UNLOCK TABLES;
|
关于死锁
总结
- 尽量使用较低的隔离级别
- 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
- 选择合理的事务大小,小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
- 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
- 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。