mysql锁

mysql锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。加锁是消耗资源的,锁的各种操作,包括获得锁、检测锁是否是否已解除、释放锁等。

锁概述

下面关于mysql的锁机制做一些通用的介绍。

使用方式: 乐观锁和悲观锁

乐观锁假设不会发生并发冲突机制,只是在提交操作时检查是否违反数据完整性。一般来说,乐观锁是由开发人员自己实现的:

  1. 使用数据版本记录是乐观锁最常用的一种实现方式,一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。当我们提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数据。
  2. 使用时间戳(timestamp)。乐观锁定的第二种实现方式和第一种差不多,同样是在需要乐观锁控制的table中增加一个字段,名称无所谓,字段类型使用时间戳(timestamp), 和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则OK,否则就是版本冲突。

与乐观锁相对应的是悲观锁。悲观锁认为在操作数据时,此操作一定会出现数据冲突,所以在每次操作前,都需要通过获得锁才能进行。很显然,悲观锁会消耗更多的时间。好处是这是由数据库自己实现的,我们可以直接使用。

锁类型: X锁和S锁

对数据的操作其实只有两种,也就是读和写,而数据库在实现锁时,也会对这两种操作使用不同的锁;也就是共享锁(Shared Lock)和互斥锁(Exclusive Lock)。

  • 排他锁:(又称写锁,X锁),会阻塞其他事务读和写。若事务T对数据对象A加上X锁,则只允许T读取和修改A,其他任何事务都不能再对加任何类型的锁,知道T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。
  • 共享锁:(又称读取,S锁),会阻塞其他事务修改表数据。若事务T对数据对象A加上S锁,则其他事务只能再对A加S锁,而不能X锁,直到T释放A上的锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

锁之间的兼容矩阵如下:

当前锁模式\请求锁模式 读锁 写锁
读锁
写锁

锁粒度: 行级, 表级和页级

  • 行级锁
    • Mysql中锁定粒度最细的一种锁,只针对当前操作的行进行加锁。InnoDB支持行级锁。
    • 特点:
      • 开销大,加锁慢;会出现死锁;
      • 锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
  • 表级锁
    • MySQL中锁定粒度最大的一种锁。对当前操作的整张表加锁。
    • 特点:
      • 开销小,加锁快;不会出现死锁;
      • 锁定粒度大,发出锁冲突的概率最高,并发度最低。
  • 页级锁
    • MySQL中锁定粒度介于行级锁和表级锁中间的一种锁,一次锁定相邻的一组记录.
    • 特点:
      • 开销和加锁时间界于表锁和行锁之间;会出现死锁;
      • 锁定粒度界于表锁和行锁之间,并发度一般。
引擎\锁粒度 行锁 页锁 表锁
Innodb 支持 不支持 支持
MyISAM 不支持 不支持 不支持
BDB 不支持 支持 支持

MyISAM引擎锁分析

MyISAM只有表锁,其中又分为两种模式:表共享锁(Table Read Lock)和表独占写锁(Table Write Lock)。

  • 对MyISAM的读操作,不会阻塞其他用户对同一表读请求,但会阻塞对同一表的写请求;
  • 对MyISAM的写操作,则会阻塞其他用户对同一表的读和写操作;
  • MyISAM表的读操作和写操作之间,以及写操作之间是串行的。

当一个线程获得对一个表的写锁后,只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

表锁

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,一般来说不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

给MyISAM表显示加锁,一般是为了一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如,有一个订单表orders,其中记录有订单的总金额total,同时还有一个订单明细表order_detail,其中记录有订单每一产品的金额小计subtotal,假设我们需要检查这两个表的金额合计是否相等,可能就需要执行如下两条SQL:

1
2
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;

这时,如果不先给这两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,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)的原因

当使用LOCK TABLE时,不仅需要一次锁定用到的所有表,而且,同一个表在SQL语句中出现多少次,就要通过与SQL语句中相同的别名锁多少次,否则也会出错!

并发锁

在一定条件下,MyISAM也支持查询和操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。

  • 当concurrent_insert设置为0时,不允许并发插入。
  • 当concurrent_insert设置为1时,MyISAM允许在一个读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
  • 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入锁争用。例如,将concurrent_insert系统变量为2,总是允许并发插入;同时,通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片,收到因删除记录而产生的中间空洞。

锁调度

前面讲过,MyISAM存储引擎的读和写锁是互斥,读操作是串行的。那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?

答案是写进程先获得锁。不仅如此,即使读进程先请求先到锁等待队列,写请求后到,写锁也会插到读请求之前!这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节MyISAM的调度行为。

  • 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用中,读锁等待严重的问题。

另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL变暂时将写请求的优先级降低,给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题。因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

InnoDB引擎锁分析

与MyISAM不同,InnoDB的两大特点:支持事务和行级锁。

官方文档

行锁

innodb实现了标准的行级锁(也被称为记录锁),分为两种类型: 共享(s)锁和排它(x)锁。

  • 持有共享锁的事务可以读取一行。
  • 持有排它锁的事务可以更新或删除一行。

如果事务T1在一行上持有共享锁,事务T2对行的操作将遵循下面的规则:

  • 在T2 请求共享锁时,会被立刻授予,此时T1和T2 同时持有当前行的共享锁。
  • 在T2 请求排它锁时,将会等待T1让出。若等待时间超时,出现超时异常。

如果事务T1在一行上持有排它锁,此时其它事务T2将不能申请共享锁或排它锁,直到T1释放锁。

  • 共享锁的语法是:select * from user where age > 10 lock in share mode;在读取的任何行上设置共享模式锁定。其他会话可以读取行,但在事务提交之前无法修改它们。如果这些行中的任何行已被另一个尚未提交的事务更改,则查询将等待该事务结束,然后使用最新值。
  • 排它锁的语法是: select * from user where age > 10 for update;对于搜索遇到的索引记录,锁定行和任何关联的索引条目,阻止其他事务更新这些行,执行SELECT … LOCK IN SHARE MODE或从某些事务隔离级别读取数据。

提交或回滚事务时,将释放 由设置LOCK IN SHARE MODE和 FOR UPDATE查询设置的所有锁。

注意:只有在禁用自动提交时(通过使用START TRANSACTION或通过设置 autocommit为0 开始事务处理),才能锁定读取 。

表锁

对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中,也可以考虑使用表级锁。

  • 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。

当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表。在InnoDB下 ,使用表锁要注意以下两点。

  • 使用LOCK TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。
  • 在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下语句:
1
2
3
4
5
SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

意向锁

innodb 支持多粒度锁,允许表级锁和行级锁共存。意图锁是表级锁,它表示事务将会对表中的行所申请的锁类型:

  • 意图共享锁(IS) 说明事务意图在表中的行上设置共享锁。
  • 意图独占锁(IX) 说明事务意图在表中的行上设置排它锁。

例如,上面说到的行锁select ... lock in share mode会设置IS锁定,select ... for update会设置IX 锁定。

意图锁定协议如下:

  • 在事务可以获取表中某行的共享锁之前,它必须首先获取表上的IS锁或更强的锁。
  • 在事务可以获取表中某行的独占锁之前,它必须首先获取表上的IX锁

表级锁冲突矩阵:

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

假如表上面已经被加了意向排他锁(IX),证明此时有事务在修改表中的具体某行的数据,那么对应行的数据这时可能被加了x锁。

  1. 如果这时候有其他事务要再加意向锁,那么可以加成功(因为加了意向锁之后,后续查询或者修改的是某行的数据,这行和上面的x锁那行未必会冲突,所以意向锁和意向锁兼容)。
  2. 如果这时候其他事务加的是共享锁(S)(这里的S是要对整个表加共享锁),那么因为前面表中有某行数据正在被修改,那么这时候的S锁是加不成功的,所以意向排他锁和共享锁是冲突的。

而意向锁的作用,就是协调上面的x锁(行上面的锁)和表锁S、X之间的关系的。或者说意向锁就是协调行锁和表锁之间关系的。或者也可以说意向锁是协调行上面的读写锁和表上面的读写锁(也就是不同粒度)之间关系的。意向锁不会阻止除全表锁定请求(例如LOCK TABLES … WRITE)以外的任何内容。意向锁的主要目的是表明有人正在锁定表中的行,或者打算锁定表中的行。

如果没有意向锁,当已经有人使用行锁对表中的某一行进行修改时,如果另外一个请求要对全表进行修改,那么就需要对所有的行是否被锁定进行扫描,在这种情况下,效率是非常低的;不过,在引入意向锁之后,当有人使用行锁对表中的某一行进行修改之前,会先为表添加意向互斥锁(IX),再为行记录添加互斥锁(X),在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了,只需要通过等待意向互斥锁被释放就可以了。

事务提交或回滚后会自动释放锁.

行锁的实现-记录锁

行锁最简单的实现是记录锁,也就是行锁对单条记录的索引加锁。Record lock锁住的永远是索引,而非记录本身。Innodb一定存在聚簇索引,因此行锁最终都会落到聚簇索引上。即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。因此,当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个等同于表锁。

行锁的实现-gap(间隙)锁

间隙锁定是锁定索引记录之间的间隙,或锁定在第一个或最后一个索引记录之前的间隙。gap 锁的主要目的是解决RR隔离级别的幻读问题,因此间隙锁只针对事务隔离级别为可重复读或以上级别。。

所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。

考虑到B+树索引的有序性,满足条件的项一定是连续存放的。记录[6,c]之前,不会插入id=10的记录;[6,c][10,b]间可以插入[10, aa][10,b][10,d]间,可以插入新的[10,bb],[10,c]等;[10,d][11,f]间可以插入满足条件的[10,e],[10,z]等;而[11,f]之后也不会插入满足条件的记录。因此,为了保证[6,c][10,b]间,[10,b][10,d]间,[10,d][11,f]不会插入新的满足条件的记录,MySQL选择了用GAP锁,将这三个GAP给锁起来。

其实就是把可能导致幻读的地方都加上GAP,那什么地方是可能导致幻读的,结合例子分析,就是新记录符合where的条件。

  1. 使用唯一索引锁定行以搜索唯一行的语句不需要间隙锁定。例如SELECT * FROM child WHERE id = 100;(id 具有唯一索引),如果id未编入索引或具有非唯一索引,则该语句会锁定间隙。
  2. 如果是针对一个范围的当前读操作,不论索引是否唯一,也会使用间隙锁定。

此处值得注意的是,冲突锁可以通过不同的事务保持在间隙上。例如,事务A可以在间隙上保持共享间隙锁定(间隙S锁定),而事务B在同一间隙上保持独占间隙锁定(间隙X锁定)。允许冲突间隙锁定的原因是,如果从索引中清除记录,则必须合并由不同事务保留在记录上的间隙锁定。间隙锁定InnoDB是”纯粹抑制”:,这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务占用的间隙锁定不会阻止另一个事务在同一个间隙上进行间隙锁定。共享和独占间隙锁之间没有区别。它们彼此不冲突,它们执行相同的功能。

用户可以通过以下两种方式来显示的关闭Gap Lock:

  • 将事务的隔离级别设为 READ COMMITED。
  • 将参数innodb_locks_unsafe_for_binlog设置为1。

Next-Key 锁

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法。一个next-key lock =[对应的索引记录的record lock + 该索引前面的间隙的gap lock],因此很多资料会用左开右闭的区间来表示next-key lock,例如(1,3]。

假定索引包含值10、11、13和20。此索引的可能的Next-Key Lock涵盖以下间隔,其中,圆括号表示排除区间端点,方括号表示包括端点:

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

三者对比(有一个索引的行有:10,11,13,20)

  • 行锁包括:10,11,13,20
  • gap lock包括:(负无穷小,10),(10,11),(11,13),(13,20),(20,正无穷大)
  • next-key lock包括:(负无穷小,10],(10,11],(11,13],(13,20],(20,正无穷大]

但是不是所有索引都会加上Next-key Lock的,在查询的列是唯一索引(包含主键索引)的情况下,Next-key Lock会降级为Record Lock。innodb 除了通过范围条件加锁时使用 next-key 锁外,如果使用相等条件请求给一个不存在的记录加锁,innodb 也会使用 next-key 锁!

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE z (
a INT,
b INT,
PRIMARY KEY(a), // a是主键索引
KEY(b) // b是普通索引
);
INSERT INTO z select 1, 1;
INSERT INTO z select 3, 1;
INSERT INTO z select 5, 3;
INSERT INTO z select 7, 6;
INSERT INTO z select 10, 8;

在会话A中执行 SELECT * FROM z WHERE b = 3 FOR UPDATE ,索引锁定如下:

这时候会话B执行的语句落在锁定范围内的都会进行waiting.

1
2
3
SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;

插入意向锁

InnoDB在RR事务隔离级别下,使用插入意向锁来控制和解决并发插入的问题。插入意向锁是一种特殊的间隙锁,执行insert之前数据库会自动向插入的间隙加插入意向锁。该锁表示插入意向,当多个事务在同一区间(gap)插入位置不同的多条数据时,事务之间不需要互相等待。

  • 插入意向锁与已有的间隙锁冲突,因此间隙锁锁住的间隙是不能插入数据的
  • 插入意向锁之间互不冲突,因此允许同时向同一个间隙插入不同主键的数据

假设有索引记录,其值分别为4和7。单独的事务分别尝试插入值5和6,在获得插入行的排他锁之前,每个事务都使用插入意图锁来锁定4和7之间的间隙,但不要互相阻塞,因为是无冲突的。

客户端A创建一个包含两个索引记录(90和102)的表,然后启动一个事务,该事务将排他锁放置在ID大于100的索引记录上。排他锁在记录102之前包括一个间隙锁:

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+

客户B开始事务以将记录插入空白。事务在等待获得排他锁的同时获取插入意图锁。

1
2
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

执行SHOW ENGINE INNODB STATUS, InnoDB的监视器 输出:

1
2
3
4
5
6
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...

行锁兼容矩阵

要加的锁\已存在的锁 record lock gap lock insert intent lock next key lock
record lock - + + -
gap lock + + + +
insert intent lock + - + -
next key lock - + + -

+表示兼容,-表示冲突

丢失更新问题

InnoDB存储引擎在RR级别就已经解决了所有问题,但是它和Serializable的区别在哪里呢?区别就在于RR级别还存在一个丢失更新问题,而SERIALIZABLE无论对于查询还是更新都会进行锁定操作。

用户原始金额为100,如果程序中对于转账和存款的判断是先查询再更新的话就会出现丢失更新的问题,也就是后面的更新覆盖了前面的更新。如果想避免这种问题,只能每次更新的时候金额基于表里最新的值来做。如果必须要先查询再更新,可以在更新的条件里判断金额(乐观锁),也可以使用隔离级别最高的SERIALIZABLE。

自增锁

最后,我们再来介绍下自增锁。在MySQL的官方文档中有以下描述:

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

自增锁是一种特殊的表级锁,主要用于事务中插入自增字段,也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。innodb_autoinc_lock_mode和插入类型有关。此处不赘述,更多见官方文档

事务简介

事务是由一组SQL语句组成的逻辑处理单元,事务具有4属性,通常称为事务的ACID属性。

  • 原子性(Actomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以操持完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
  • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况。

  • 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题
  • 脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
  • 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了!这种现象叫做“不可重复读”。
  • 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。

在并发事务处理带来的问题中,“更新丢失”通常应该是完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式,基本可以分为以下两种。

  • 一种是在读取数据前,对其加锁,阻止其他事务对数据进行修改。
  • 另一种是不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户的角度,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion Concurrency Control,简称MVCC或MCC),也经常称为多版本数据库。

数据库的事务隔离级别越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的,同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。为了解决“隔离”与“并发”的矛盾,ISO/ANSI SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同,应用可以根据自己业务逻辑要求,通过选择不同的隔离级别来平衡"隔离"与"并发"的矛盾:

隔离级别/读数据一致性及允许的并发副作用 读数据一致性 脏读 不可重复读 幻读
未提交读(Read uncommitted) 最低级别,只能保证不读取物理上损坏的数据
已提交度(Read committed) 语句级
可重复读(Repeatable read) 事务级
可序列化(Serializable) 最高级别,事务级

Oracle只提供Read committed和Serializable两个标准级别,另外还自己定义的Read only隔离级别:SQL Server除支持上述ISO/ANSI SQL92定义的4个级别外,还支持一个叫做"快照"的隔离级别,但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL支持全部4个隔离级别,但在具体实现时,有一些特点,比如在一些隔离级下是采用MVCC一致性读,但某些情况又不是。

一致性读-mvcc

mysql innodb 引擎是基于多版本的并发控制协议(MVCC - Multi-Version Concurrency Control)实现的。和基于锁的并发控制,Lock-Based Concurrency Control,相比;MVCC 可以避免因为写锁的阻塞而造成读数据的阻塞问题,也就是读不加锁,读写不冲突。这样极大的增强了读多写少应用的并发性能。

InnoDB表数据的组织方式为主键聚簇索引,二级索引中采用的是(索引键值, 主键键值)的组合来唯一确定一条记录。InnoDB表数据为主键聚簇索引,mysql默认为每个索引行添加了4个隐藏的字段,分别是:

  • DB_ROW_ID:InnoDB引擎中一个表只能有一个主键,用于聚簇索引,如果表没有定义主键会选择第一个非Null的唯一索引作为主键,如果还没有,生成一个隐藏的DB_ROW_ID作为主键构造聚簇索引。
  • DB_TRX_ID:最近更改该行数据的事务ID。
  • DB_ROLL_PTR:undo log的指针,用于记录之前历史数据在undo log中的位置。
  • DELETE BIT:索引删除标志,如果DB删除了一条数据,是优先通知索引将该标志位设置为1,然后通过(purge)清除线程去异步删除真实的数据。

整个MVCC的机制都是通过DB_TRX_ID,DB_ROLL_PTR这2个隐藏字段来实现的。

当一个事务开始的时候,会将当前数据库中正在活跃的所有事务(执行begin,但是还没有commit的事务)保存到一个叫trx_sys的事务链表中,事务链表中保存的都是未提交的事务,当事务提交之后会从其中删除。

有了前面隐藏列和事务链表的基础,接下去就可以构造MySQL实现MVCC的关键——ReadView。ReadView说白了就是一个数据结构,在事务开始的时候会根据上面的事务链表构造一个ReadView。

1
2
3
4
5
6
7
8
9
10
ReadView::ReadView()
:
m_low_limit_id(),
m_up_limit_id(),
m_creator_trx_id(),
m_ids(),
m_low_limit_no()
{
ut_d(::memset(&m_view_list, 0x0, sizeof(m_view_list)));
}
  1. 当前活跃事务链表(trx_sys)中事务id最大的值被赋值给m_low_limit_id。
  2. 当前活跃事务链表中第一个值(也就是事务id最小)被赋值给m_up_limit_id。
  3. m_ids 为事务链表。
  4. m_creator_trx_id 是当前创建ReadView的事务ID。

通过该ReadView,新的事务可以根据查询到的所有活跃事务记录的事务ID来匹配能够看见该记录,从而实现数据库的事务隔离(事务的快照时间点是以第一个select来确认的。所以即便事务先开始。但是select在后面的事务的update之类的语句后进行,那么它是可以获取后面的事务的对应的数据。),主要逻辑如下:

  • 通过聚簇索引的行结构中DB_TRX_ID隐藏字段可以知道最近被哪个事务ID修改过。
  • 一个新的事务开始时会根据事务链表构造一个ReadView。
  • 当前事务根据ReadView中的数据去跟检索到的每一条数据去校验,看看当前事务是不是能看到这条数据。
    • 当检索到的数据的事务ID小于事务链表中的最小值(数据行的DB_TRX_ID < m_up_limit_id)表示这个数据在当前事务开启前就已经被其他事务修改过了,所以是可见的。
    • 当检索到的数据的事务ID表示的是当前事务自己修改的数据(数据行的DB_TRX_ID = m_creator_trx_id) 时,数据可见。
    • 当检索到的数据的事务ID大于事务链表中的最大值(数据行的DB_TRX_ID >= m_low_limit_id) 表示这个数据在当前事务开启后到下一次查询之间又被其他的事务修改过,那么就是不可见的。
    • 如果事务链表为空,那么也是可见的,也就是当前事务开始的时候,没有其他任意一个事务在执行。
    • 当检索到的数据的事务ID在事务链表中的最小值和最大值之间,那就需要判断一下trx_id属性值是不是在m_ids列表中,如果在,说明创建ReadView时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView时生成该版本的事务已经被提交,此时需要根据不同的事务隔离级别来确定。对于RC的事务隔离级别来说,对于事务执行过程中,已经提交的事务的数据,对当前事务是可见的;而对于RR隔离级别来说,事务启动时,已经开始的事务链表中的事务的所有修改都是不可见的;

RC隔离级别是能看到其他事务提交后的修改记录的,也就是不可重复读,但是RR隔离级别完美的避免了,但是它们都是使用的MVCC机制,那又为何有两种截然不同的结果呢?其实我们看一下他们创建ReadView的区别就知道了。

  • 在RC事务隔离级别下,每次语句执行都关闭ReadView,然后重新创建一份ReadView。
  • 在RR下,事务开始后第一个读操作创建ReadView,一直到事务结束关闭。

因为RC每次查询语句都创建一个新的ReadView,所以活跃的事务列表一直在变,也就导致如果事务B update提交了后事务A才进行查询,查询的结果就是最新的行,也就是不可重复读。而RR则一直用的事务开始时创建的ReadView。

第1步和第2步是非常容易理解的,而在第3步事务B插入一条新的数据后,在第4步事务A还是查不到,也就是利用了MVCC的特性来实现。当事务B提交后,第5步的查询在RC和RR隔离级别下的输出是不同的,这个的原因在另一篇博客中也说到了,是因为他们创建ReadView的时机不同。

但是很诡异的是在第6步的时候,事务A更新了一条它看不见的记录,然后查询就能够查询出来了。这里很多人容易迷惑,不可见不代表记录不存在,它只是利用了可见性判断忽略了而已。更新成功之后,事务A顺其自然的记录了这条记录的Undo log,在随后的查询中,因为它能够看见自己的改动这一个可见性的判断,自然就能够查询出来了。

这个例子说明了,mvcc 不能完全避免幻读(例如此时插入相同ID的数据,会有主键冲突异常)

undo log

Undo记录中存储的是老版本数据,当一个旧的事务需要读取数据时,为了能读取到老版本的数据,需要顺着undo链找到满足其可见性的记录。当版本链很长时,通常可以认为这是个比较耗时的操作。为了保证事务并发操作时,在写各自的undo log时不产生冲突,InnoDB采用回滚段(Rollback Segment,简称Rseg)的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式,每个回滚段又有多个undo log slot。具体的文件组织方式如下图所示:

上图展示了基本的Undo回滚段布局结构,其中:

  • rseg0预留在系统表空间ibdata中。
  • rseg 1~rseg 32 这32个回滚段存放于临时表的系统表空间中,用于临时表的undo。
  • rseg33~rseg 128 则根据配置(InnoDB >= 1.1默认128,可通过参数 innodb_undo_logs 设置)存放到独立undo表空间中(如果没有打开独立Undo表空间,则存放于ibdata中,独立表空间可以通过参数 innodb_undo_directory 设置),用于普通事务的undo。

如图所示,每个回滚段维护了一个段头页,在该page中又划分了1024个slot(TRX_RSEG_N_SLOTS),每个slot又对应到一个undo log对象,因此理论上InnoDB最多支持 96 * 1024个普通事务。

在InnoDB引擎中,undo log分为:

  • insert undo log
  • update undo log

insert undo log是指在insert操作中产生的undo log,因为insert操作的记录,只对事务本身可见,对其他事务不可见(这是事务隔离性的要求),故该undo log可以在事务提交后直接删除,不需要进行purge操作。而update undo log记录的是delete和update操作产生的undo log。该undo log可能需要提供MVCC机制,因此不能在事务提交时就进行删除,提交时放入undo log链表,等待purge线程进行最后的删除。下面是两种undo log的结构图。

对于一条delete语句 delete from t where a = 1,如果列a有聚集索引,则不会进行真正的删除,而只是在主键列等于1的记录delete flag设置为1,即记录还是在B+树中。而对于update操作,不是直接对记录进行更新,而是标识旧记录为删除状态,然后新产生一条记录。那这些旧版本标识位删除的记录何时真正的删除?怎么删除?

其实InnoDB是通过undo日志来进行旧版本的删除操作的,在InnoDB内部,这个操作被称之为purge操作,原来在srv_master_thread主线程中完成,后来进行优化,开辟了purge线程进行purge操作,并且可以设置purge线程的数量。purge操作每10s进行一次。

为了节省存储空间,InnoDB存储引擎的undo log设计是这样的:一个页上允许多个事务的undo log存在。虽然这不代表事务在全局过程中提交的顺序,但是后面的事务产生的undo log总在最后。此外,InnoDB存储引擎还有一个history列表,它根据事务提交的顺序,将undo log进行连接,如下面的一种情况:

在执行purge过程中,InnoDB存储引擎首先从history list中找到第一个需要被清理的记录,这里为trx1,清理之后InnoDB存储引擎会在trx1所在的Undo page中继续寻找是否存在可以被清理的记录,这里会找到事务trx3,接着找到trx5,但是发现trx5被其他事务所引用而不能清理,故再去history list中取查找,发现最尾端的记录时trx2,接着找到trx2所在的Undo page,依次把trx6、trx4清理,由于Undo page2中所有的记录都被清理了,因此该Undo page可以进行重用。

InnoDB存储引擎这种先从history list中找undo log,然后再从Undo page中找undo log的设计模式是为了避免大量随机读操作,从而提高purge的效率。

快照读和当前读

在Mysql中InnoDB,提供了两种事务隔离技术。第一个是mvcc(RR及以上隔离级别才可以用),第二个是next-key技术。读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

  • mvcc的优势是不加锁,并发性高。缺点是不是实时数据。不加lock in share mode之类的快照读就使用mvcc。
  • next-key的优势是获取实时数据,但是需要加锁。

innodb 定义了4种隔离级别:

  1. Read Uncommited: 可以读取未提交记录。此隔离级别,不会使用.
  2. Read Committed (RC): 快照读和当前读都有。针对当前读,RC隔离级别保证对读取到的记录加锁 (记录锁),存在幻读现象。
  3. Repeatable Read (RR): 快照读和当前读都有。针对当前读,RR隔离级别保证对读取到的记录加锁 (记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),不存在幻读现象。
  4. Serializable: 从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读,所有的读操作均为当前读,读加读锁 (S锁),写加写锁 (X锁)。Serializable隔离级别下,读写冲突,因此并发度急剧下降,在MySQL/InnoDB下不建议使用。

对于隔离级别 RC和RR 来说:

  • 简单的select操作,属于快照读,不加锁。
  • 特殊的读操作,如(select … lock in share mode, select … for update),插入/更新/删除操作,属于当前读,读取记录的最新版本,并且读取后还要加锁,保证隔离级别。
  • 在rr级别下,mvcc完全解决了重复读,但并不能真正的完全避免幻读,只是在部分场景下利用历史数据规避了幻读。对于快照读,mysql使用mvcc利用历史数据部分避免了幻读(在某些场景看上去规避了幻读)。要完全避免,需要手动加锁将快照读调整为当前读(mysql不会自动加锁),然后mysql使用next-key完全避免了幻读

补充说明

加锁分析例子

有表如下,pId为主键索引

pId(int) name(varchar) num(int)
1 aaa 100
2 bbb 200
3 bbb 300
7 ccc 200
  • RC/RU+条件列非索引
sql 分析
select * from table where num = 200 不加任何锁,是快照读
select * from table where num > 200 不加任何锁,是快照读
select * from table where num = 200 lock in share mode 当num = 200,有两条记录。这两条记录对应的pId=2,7,因此在pId=2,7的聚簇索引上加行级S锁,采用当前读。
select * from table where num > 200 lock in share mode 当num > 200,有一条记录。这条记录对应的pId=3,因此在pId=3的聚簇索引上加上行级S锁,采用当前读。
select * from table where num = 200 for update 当num = 200,有两条记录。这两条记录对应的pId=2,7,因此在pId=2,7的聚簇索引上加行级X锁,采用当前读。
select * from table where num > 200 for update 当num > 200,有一条记录。这条记录对应的pId=3,因此在pId=3的聚簇索引上加上行级X锁,采用当前读。
  • RC/RU+条件列是聚簇索引
sql 分析
select * from table where pId = 2 不加任何锁,是快照读
select * from table where pId > 2 不加任何锁,是快照读
select * from table where pId = 2 lock in share mode 在pId=2的聚簇索引上,加S锁,为当前读
select * from table where pId > 2 lock in share mode 在pId=3,7的聚簇索引上,加S锁,为当前读
select * from table where pId = 2 for update 在pId=2的聚簇索引上,加X锁,为当前读
select * from table where pId > 2 for update 在pId=3,7的聚簇索引上,加X锁,为当前读

为什么条件列加不加索引,加锁情况是一样的?

其实是不一样的。在RC/RU隔离级别中,MySQL Server做了优化。在条件列没有索引的情况下,尽管通过聚簇索引来扫描全表,进行全表加锁。但是,MySQL Server层会进行过滤并把不符合条件的锁当即释放掉,因此你看起来最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程!

  • RC/RU+条件列是非聚簇索引

我们在num列上建上非唯一索引。此时有一棵聚簇索引(主键索引,pId)形成的B+索引树,其叶子节点为硬盘上的真实数据。以及另一棵非聚簇索引(非唯一索引,num)形成的B+索引树,其叶子节点依然为索引节点,保存了num列的字段值,和对应的聚簇索引。

sql 分析
select * from table where num = 200 不加任何锁,是快照读
select * from table where num > 200 不加任何锁,是快照读
select * from table where num = 200 lock in share mode 当num = 200,由于num列上有索引,因此先在 num = 200的两条索引记录上加行级S锁。接着,去聚簇索引树上查询,这两条记录对应的pId=2,7,因此在pId=2,7的聚簇索引上加行级S锁,采用当前读。
select * from table where num > 200 lock in share mode 当num > 200,由于num列上有索引,因此先在符合条件的 num = 300的一条索引记录上加行级S锁。接着,去聚簇索引树上查询,这条记录对应的pId=3,因此在pId=3的聚簇索引上加行级S锁,采用当前读。
select * from table where num = 200 for update 当num = 200,由于num列上有索引,因此先在 num = 200的两条索引记录上加行级X锁。接着,去聚簇索引树上查询,这两条记录对应的pId=2,7,因此在pId=2,7的聚簇索引上加行级X锁,采用当前读。
select * from table where num > 200 for update 当num > 200,由于num列上有索引,因此先在符合条件的 num = 300的一条索引记录上加行级X锁。接着,去聚簇索引树上查询,这条记录对应的pId=3,因此在pId=3的聚簇索引上加行级X锁,采用当前读。
  • RR/Serializable+条件列非索引

RR级别需要多考虑的就是gap lock,他的加锁特征在于,无论你怎么查都是锁全表。

sql 分析
select * from table where num = 200 在RR级别下,不加任何锁,是快照读。在Serializable级别下,在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200 在RR级别下,不加任何锁,是快照读。在Serializable级别下,在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num = 200 lock in share mode 在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200 lock in share mode 在pId = 1,2,3,7(全表所有记录)的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num = 200 for update 在pId = 1,2,3,7(全表所有记录)的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200 for update 在pId = 1,2,3,7(全表所有记录)的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  • RR/Serializable+条件列是聚簇索引

pId用的就是聚簇索引。该情况的加锁特征在于,如果where后的条件为精确查询(=的情况),那么只存在record lock。如果where后的条件为范围查询(>或<的情况),那么存在的是record lock+gap lock。

sql 分析
select * from table where pId = 2 在RR级别下,不加任何锁,是快照读。在Serializable级别下,是当前读,在pId=2的聚簇索引上加S锁,不存在gap lock。
select * from table where pId > 2 在RR级别下,不加任何锁,是快照读。在Serializable级别下,是当前读,在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId = 2 lock in share mode 是当前读,在pId=2的聚簇索引上加S锁,不存在gap lock。
select * from table where pId > 2 lock in share mode 是当前读,在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId = 2 for update 是当前读,在pId=2的聚簇索引上加X锁。
select * from table where pId > 2 for update 在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId = 6 (lock in share mode) or (for update) 注意了,pId=6是不存在的列,这种情况会在(3,7)上加gap lock。
select * from table where pId > 18 (lock in share mode) or (for update) 注意了,pId>18,查询结果是空的。在这种情况下,是在(7,+∞)上加gap lock。
  • RR/Serializable+条件列是非聚簇索引

这里非聚簇索引,需要区分是否为唯一索引。因为如果是非唯一索引,间隙锁的加锁方式是有区别的。

先说一下唯一索引的情况。如果是唯一索引,情况和RR/Serializable+条件列是聚簇索引类似,唯一有区别的是:这个时候有两棵索引树,加锁是加在对应的非聚簇索引树和聚簇索引树上!

下面说一下非聚簇索引是非唯一索引的情况,他和唯一索引的区别就是通过索引进行精确查询以后,不仅存在record lock,还存在gap lock。而通过唯一索引进行精确查询后,只存在record lock,不存在gap lock。在num列建立非唯一索引。

sql 分析
select * from table where num = 200 在RR级别下,不加任何锁,是快照读。在Serializable级别下,是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加S锁,在(100,200)(200,300)加上gap lock。
select * from table where num > 200 在RR级别下,不加任何锁,是快照读。在Serializable级别下,是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock
select * from table where num = 200 lock in share mode 是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加S锁,在(100,200)(200,300)加上gap lock。
select * from table where num > 200 lock in share mode 是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。
select * from table where num = 200 for update 是当前读,在pId=2,7的聚簇索引上加S锁,在num=200的非聚集索引上加X锁,在(100,200)(200,300)加上gap lock。
select * from table where num > 200 for update 是当前读,在pId=3的聚簇索引上加S锁,在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock
select * from table where num = 250 (lock in share mode) or (for update) 注意了,num=250是不存在的列,这种情况会在(200,300)上加gap lock。
select * from table where num > 400 (lock in share mode) or (for update) 注意了,pId>400,查询结果是空的。在这种情况下,是在(400,+∞)上加gap lock。

死锁

myisam 表锁是 deadlock free 的,这是因为 myisam 总是一次获取所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在 innodb 中,除单个 sql 组成的事务外,锁是逐步获得的,这就决定了在 innodb 中发生死锁是可能的。

发生死锁后,innodb 一般都能自动检测到,并使一个事务释放锁回退,另一个事务获得锁,继续完成事务。但在涉及外部锁或表锁的情况下,innodb 并不能完全自动检测到死锁,只需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重的性能问题,甚至拖垮数据库。

通常来说,死锁都是应用设计的问题,通过调整业务流程,数据库对象设计、事务大小、以及访问数据库的 sql 语句,绝大部分死锁都可以避免。

  1. 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。
  2. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。
  3. 在事务中,如果要更新记录,应该申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。
  4. 在 repeatable-read 隔离级别下,如果两个线程同时对相同条件记录用 select … for update 加排他锁,在没有符合该条件记录情况下,两个线程过会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成 read committed ,就可避免问题。
  5. 当隔离级别为 read committed 时,如果两个线程都先执行 select … for update, 判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第 1 个线程提交后,第 2 个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁,如果有第 3 个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行 rollback 释放获得的排他锁

如果出现死锁,可以用 show innodb status 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 sql 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。可以据此分析产生死锁的原因。

显式表锁

下面介绍下显式加表锁:

mysql 提供了显式锁定表(lock tables)和解锁表(unlock tables)的语法。

1
2
3
4
5
6
7
8
9
10
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...

lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

lock table的锁类型有下面两种:

  • READ [LOCAL] 锁:
    • 持有锁的会话可以读取表(但是不能写入)
    • 多个会话可以同时获取READ表的锁定
    • 其它会话可以在不明确获取READ锁的情况下读取表
    • LOCAL修饰符表示可以允许在其他会话中对在当前会话中获取了READ锁的的表执行(不冲突的)并发插入。但是当持有锁时,若使用Server外的进程来操纵数据库则不能使用READ LOCAL。另外,对于InnoDB表,READ LOCAL与READ相同。
  • [LOW_PRIORITY] WRITE 锁:
    • 持有锁的会话可以读写表。
    • 只有持有锁的会话才能访问该表。在释放锁之前,没有其他会话可以访问它。
    • 在锁定时,阻止其他会话对表的WRITE请求。
    • MySQL 5.6.5以后,LOW_PRIORITY修饰符已弃用。
  1. WRITE锁通常具有比READ锁更高的优先级,以确保尽快处理更新。这意味着,如果一个会话获得READ锁定,然后另一个会话请求WRITE锁定,则后续 READ锁定请求将一直等到请求WRITE锁定的会话获得锁定并将其释放。(若max_write_lock_count 值较小,会出现相反情况)。
  2. lock table 会一直阻塞,直到获得锁。
  3. 会话必须在单个lock table 语句中获得所有需要的锁定,会话只能访问锁定的表,否则,会报错。(INFORMATION_SCHEMA 数据库中的表是个例外)
  4. 不能多次使用相同名字的表,需要使用别名:LOCK TABLE t WRITE, t AS t1 READ;
  5. 当使用别名锁定表时,需要使用相同的别名引用表。LOCK TABLE t AS myalias READ; SELECT * FROM t AS myalias;
  6. 通过别名引用表,则必须使用相同的别名锁定表。LOCK TABLE t READ; SELECT * FROM t AS myalias; 会产生异常:ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

当释放会话持有的表锁时,它们都会同时释放。会话可以显式释放其锁,或者可以在某些条件下隐式释放锁。

  • 会话可以通过UNLOCK TABLES 显式释放锁。
  • 如果会话LOCK TABLES在已经持有锁的情况下发出获取锁的语句,则在授予新锁之前将隐式释放其现有锁。
  • 如果会话开始事务(例如,with START TRANSACTION),则执行隐式UNLOCK TABLES操作,这会导致释放现有锁。

如果客户端重新连接,则锁将不再有效。此外,如果客户端具有活动事务,则服务器在断开连接时回滚事务,如果发生重新连接,则新会话将以启用自动提交开始。因此,客户可能希望禁用自动重新连接。使用自动重新连接时,如果发生重新连接,则不会通知客户端,但任何表锁或当前事务都将丢失。禁用自动重新连接后,如果连接断开,则发出的下一个语句将发生错误。客户端可以检测错误并采取适当的操作,例如重新获取锁或重做事务。

下面是表锁定对事务的影响:

  • lock tables 不是事务安全的,在尝试锁定表之前,隐式提交任何活动事务。
  • unlock tables 隐式提交任何活动事务,但仅限于在已用lock tables 获取表锁的情况下。
  • 导致事务隐式提交的其他语句不会释放现有的表锁。语句列表
  • 使用lock tables和事务的正确方式是:

    1
    2
    3
    4
    5
    SET autocommit=0; --通过autocommit 为0开启事务,而不是START TRANSACTION
    LOCK TABLES t1 WRITE, t2 READ, ...;
    ... do something with tables t1 and t2 here ...
    COMMIT; -- 显式提交事务后调用 unlock tables
    UNLOCK TABLES;
  • rollback 不释放表锁。

官方文档-显式lock

-------------本文结束感谢您的阅读-------------
坚持分享,您的支持将鼓励我继续创作!
0%