一、表锁和行锁
概述
- 表级锁:开销小,加锁快;不会出现死锁;锁粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁粒度最小,发生锁冲突的概率最低,并发度最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁粒度界于表锁和行锁之间,并发度一般。查看更多
MySQL表级锁的锁模式(MyISAM)
- 对MyISAM的读操作,不会阻塞其他用户对同一表请求,但会阻塞对同一表的写请求;
- 对MyISAM的写操作,则会阻塞其他用户对同一表的读和写操作;
- MyISAM表的读操作和写操作之间,以及写操作之间是串行的。
MySQL表级锁的锁模式
MySQL中的表锁兼容性
当前锁模式/是否兼容/请求锁模式 | None | 读锁 | 写锁 |
读锁 | 是 | 是 | 否 |
写锁 | 是 | 否 | 否 |
如何加表锁
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表并发插入条件的情况下,允许其他用户在表尾插入记录
- 在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及的表锁,并且MySQL支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MySQL一次性获得事务所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因
并发锁
- 当concurrent_insert设置为0时,不允许并发插入。
- 当concurrent_insert设置为1时,如果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):一个事务读取另一个事务修改后还未提交的纪录,这种现象被形象地叫做“脏读”。
- 不可重复读(Non-Repeatable Reads):一个事务在两次读取同一纪录期间,由于另一个事务的更新操作,两次读取的数据不一样,这种现象叫做“不可重复读”。
- 幻读(Phantom Reads):同一事务下,连续执行两次同样的SQL语句,由于其他事务插入了满足其查询条件的新数据,导致产生不同的结果,这种现象就称为“幻读”。
3.事务隔离级别
事务4种隔离级别比较
隔离级别/读数据一致性及允许的并发副作用 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
未提交读(Read uncommitted)
| 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交度(Read committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
获取InonoDB行锁争用情况
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):允许事务读取行纪录,阻止其他事务获得相同纪录的排他锁。
- 排他锁(X):允许获取排他锁的事务更新纪录,阻止其他事务取得相同纪录的共享读锁和排他写锁。
InnoDB行锁模式兼容性列表
当前锁模式/是否兼容/请求锁模式 | X | IX | S | IS |
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
InnoDB行锁实现方式
间隙锁(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;
|
关于死锁
总结
- 尽量使用较低的隔离级别
- 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
- 选择合理的事务大小,小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
- 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
- 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。
二、悲观锁和乐观锁
1、悲观锁(Pressimistic Locking)
对数据被外界(包括本系统当前的其他事务,以及来自 外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。例如:
select * from table_name where id = ‘xxx’ for update;
这样查询出来的这一行数据就被锁定,这个事务提交之前其他外界是不能修改这条数据的,但是这种处理方式效率比较低,一般不推荐使用。
2、乐观锁(Optimistic Locking)
相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。如一个金融系统,当某个操作员读取用户的数据,并在读出的用户数据的基础上进行修改时(如更改用户帐户余额),如果采用悲观锁机制,也就意味着整个操作过程中(从操作员读出数据、开始修改直至提交修改结果的全过程,甚至还包括操作员中途去煮咖啡的时间),数据库记录始终处于加锁状态,可以想见,如果面对几百上千个并发,这样的情况将导致怎样的后果。
乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交纪录的版本信息与数据库表对应记录的版本信息进行比对,如果提交的版本号大于数据库表版本号,则予以更新,否则认为是过期数据。
举个乐观锁的例子(数据库version默认为0):
现在一件衣服就剩一个库存了,但是有两个用户同时下单,如果这时候不加以控制很容易出现库存卖超的情况,这时候我们可以这样操作:
第一个用户将这件衣服读出(version=0),并将库存-1,
第二个用户也将这件衣服读出(version=0),并将库存-1,
第一个个用户完成操作,将数据库版本version+1,执行更新库存时由于提交的数据版本大于数据库记录的版本,数据被更新,数据库中的version被更新为2。
update goods set store=store-1,version=version+1 where id=xx and version=orginal_version
第二个用户也完成了操作,也将版本version+1,执行更新库存时发现执行版本和数据库记录的版本相同,不符合提交版本必须大于数据库记录版本的乐观锁策略,所以第二个用户的下单请求被驳回,我们可以通过人性化处理异常给用户提示该商品已售罄等。
乐观锁机制避免了长事务中的数据库加锁开销(两个用户操作过程中,都没有对数据库数据加锁),大大提升了大并发量下的系统整体性能表现。
悲观锁:交给数据库来处理的,由事务(分隐私和显式事务,平时单条SQL语句就是一个隐式事务)+锁控制,其中事务相当于锁的作用域,根据事务的提交失败或回滚来释放掉显式事务中开启的锁。(事前处理)
乐观锁:使用版本号来控制的,这种机制并发性和性能更好。(事后处理)