1.select … for update和select … lock in share mode(8.0是select …
for share)会重新生成read view

1.3 Records Lock (Index Record Lock)

    记录锁,其实是index record Lock,也就是index row lock,不是数据row
lock。Index Record Lock分为两种:SX锁,也就是对index row加上S、X锁。

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; ``如果``c1``是索引,将通过对c1=10的index
加上X Lock,这样就可以阻止任何其他的事务去持有t.c1=10 的索引锁。
也就是说其他的事务中, inserting、 update、
delete操作是拿不到t.c1=10的索引锁的。

 

 

 

2.6 SQL 加锁分析

·select … from … Lock IN SHARE MODE (也称为加锁read)

默认情况下(REPEATABLE_READ),这个select
SQL中如果使用了index,会在所有匹配行的index record上,加上shared
next-key
lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index
record上,加上shared index record Lock。

如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加shared index
record。

 

·Select … from (不加锁读)

如果执行该SQL的事务采用的是SERIALIZABLE级别,则会转为select … from
… Lock IN SHARE MODE,也就是会变成加锁读。

在其它的隔离级别下,则不会加锁,是从snapshot中读取数据。

 

·select … from … FOR UPDATE

在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select
SQL中如果使用了index, 会在匹配的行上加上exclusive next-key
lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index
record上,加上exclusive index record Lock。

如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加exclusive
index record。

 

·UPDATE … WHERE …

在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select
SQL中如果使用了index, 会在匹配的行上加上exclusive next-key
lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index
record上,加上exclusive index record Lock。

 

如果是更低的隔离级别READ_COMMITED、READ_COMMITED,则直接加exclusive
index record。

 

 

当执行update操作时,如果是在clustered index
record(聚簇index)上,会隐式对所有的受影响的二级索引都加上锁。例如
table test 有聚簇index (a,b,c),那么index record
就是由a,b,c组成的。如果更新时使用:update test set d=’1’ where a=’1’ and
b=’2’;这个 SQL在执行时,会对与a, b匹配的所有的index record
都加上锁exclusive。

 

·DELETE… WHERE …

在REPEATABLE_READ、SERIALIZABLE隔离级别下,这个select
SQL中如果使用了index, 会在匹配的行上加上exclusive next-key
lock。如果select中使用的index是一个唯一索引的话,则只是在匹配行的index
record上,加上exclusive index record Lock。

 

·INSERT

Insert 时会先查找有没有匹配的index,如果有:会在匹配的index上加上shared
index Lock。

如果没有,会在某个要插入的row上加上exclusive index lock (没有对gap
加锁,防止对并发插入产生影响)。

 

 

 

如果在执行上述几种SQL时,没有使用到index,会引发全表扫描。在全表扫描时,并不会锁住整个表的。

有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL
Server层进行过滤。因此也就把所有的记录,都锁上了。

注:在实际的实现中,MySQL有一些改进,在MySQL
Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁
(违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。

 

结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL
Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。

 

 

2. 

2.5 隔离级别

READ UNCOMMITED:顾名思义,未提交的数据也可以读。
其实,这种隔离级别下,Select语句在执行时,能够读取到相关行的当前版本(也就是最新版本),所以一些没有提交的事务对数据的变更,也能读取到。故而可能发生
脏读了。

在此种隔离级别下,采用的是current读,所以也不会创建Snapshot了。

 

READ
COMMITED
:读取已提交的数据行。每一次都会读取已提交的数据行,所以每一次Select都要刷新到最新的Snapshot。所以他会发生不可重复读的问题,必然的,幻读也会发生。

    REPEATABLE
READ
:可重复读。为了保证能够在同一个事务内可重复读,在一个事务开启后,由第一条要采用Snapshot方式的SQL(该select
SQL未必是当前事务中的)来触发Snapshot的建立。这个也是InnoDB默认的隔离级别。

 

             Session A                     Session B

 

           SET autocommit=0;        SET autocommit=0;

time

|          SELECT * FROM t;

|          empty set

|                                                 INSERT INTO t VALUES (1, 2);

|

v          SELECT * FROM t;

           empty set

                                                      COMMIT;

 

           SELECT * FROM t;

           empty set

 

           COMMIT;

 

           SELECT * FROM t;

           ———————

           |    1    |    2    |

           ———————

 

 

SERIALIZABLE:序列化。对于该级别的事务,如果客户端采用了autocommit的事务,则直接提交,那么连接下的每一个SQL都是一个单独的事务。如果没有采用autocommit方式,则采用REPEATABLE
READ
隔离级别,但是会将所有的简单的Select转换为Select … LOCK IN SHARE
MODE,即转为current 读。

 

 

读数据一致性及允许的并发副作用

隔离级别

读数据一致性

脏读

不重复读

幻读

未提交读(Read uncommitted)

最低级别,只能保证

不读取物理上损坏的数据

可能

可能

可能

已提交度(Read committed)

语句级

可能

可能

可重复读(Repeatable read)

事务级

可能

可序列化(Serializable)

最高级别,事务级

 

 

 

参考网址:

2.3 MVCC

在说隔离级别前,先了解一下MVCC(Multi-Version-Concurrency-Control)。

大致意思就是:当数据库采用MVCC方案来设计事务时,通常是这样的:

当修改一行时,在提交之前,在内存中,不会使用新数据直接覆盖老数据,而是对老版本数据做一个标记,并创建一个新版本的数据。并且老版本的数据与新版本的数据是一个链式结构。如此一来,每一个修改的数据都有一个history
chain。

   
当删除一行数据时,在提交之前,不会真的将数据从内存中删除,只是做一个删除标记罢了。

    这里可以了解到变更的数据都有一个history
chain。也就是说在内存中保留了相关Row的多个版本。保留多个版本,那么在进行并发读取时,就会大大提供并发量。

这也是MVCC最大的好处:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不重读非常重要。会极大的增加系统的并发性能。这也是为什么现阶段所有的RDBMS,都支持MVCC。

 

在运行时,不同的SQL语句采用不同的数据读取方式。根据读取方式的不同,分为snapshot读current
。上面说的读不加锁,读写不冲突是针对snapshot读而言的。而对于当前读(读取最新数据),还是要加锁的。

快照读:通常情况下,像这样简单的Select,是从snapshot读取的:

select * from table where ?;

当然也有例外,如果一个事务是READ_UNCOMMITED,即便是简单的Select,也会采用current读。

 

对于从临时表(包括嵌套查询生成的表)读取时,会采用current读。

 

Snapshot如何建立?

对于不同的隔离级别,Snapshot建立方式也是不同的,这里不做详细说明,在隔离级别小节中说明。

 

下面例子采用current读:

select * from table where ? lock in share mode;  // S Lock

select * from table where ? for update; // X Lock and Index Lock

insert into table values (…); // X Lock

update table set ? where ?; // X Lock

delete from table where ?; // X Lock

 

 

 

 

InnoDB:Lock & Transaction,innodblock

InnoDB
是一个支持事务的Engine,要保证事务ACID,必然会用到Lock。就像在Java编程一下,要保证数据的线程安全性,必然会用到Lock。了解Lock,Transaction可以帮助sql优化、deadlock分析等。

  • 1、Lock
    • 1.1 Shared Lock & Exclusive Lock
    • 1.2 Intention Lock
    • 1.3 Index Record Lock
    • 1.4 Gap Lock
    • 1.5 Next-Key Lock
  • 2、Transaction
    • 2.1 事务过程中可能出现的问题
    • 2.2 ACID
    • 2.3 MVCC
    • 2.4 现阶段锁(2PL)管理
    • 2.5 隔离级别
    • 2.6 SQL 加锁分析
  • 3、DeadLock

 

 

2.4 两阶段锁(2PL)管理

在MySQL中,锁采用两阶段处理方式,即分为加锁阶段、释放锁阶段。

在2.3中,将Insert、update、delete都划归到 current读 方式中。为什么呢?

下面看看Update执行过程:

 

Update分为多轮进行,每一轮都有信号交流过程:current read、lock &
return、 update row、success。Delete也是如此的。

 

对于Insert则略有不同,因为他要先查找是否存在同样的Key。

从这个过程中,可以看出每一轮进行一个current
read,并加锁,直到读完为止。Update完毕,并不会立即释放锁,而是接着执行,直到事务提交时才释放锁,insert,
delete也同样如此:

 

 

 

 

1.1 Shared Lock & Exclusive Lock

共享锁(S)与排他锁(X),这两个锁是row-level的锁,也就是说,可以理解为,每一行记录都有一把S,一把X锁。共享锁是读锁(Read
Lock),事务执行时,如果要读取一行数据,就要先持有该行数据的读锁(S)。排他锁是写锁(Write
Lock),事务执行时,如果要写数据(即更新数据, 例如update,
delete),则要先持有相应的行的写锁(X)。

    此外,Read
Lock可以同时被多个事务(实际上是执行这多个事务的线程)持有,Write
Lock则不能。这一点,从设计上来讲,和java中的ReadLock WriteLock是类似的。

也就是说ReadLock可以同时被多个线程持有,WriteLock只能被一个线程持有。

当一个线程A持有着ReadLock(S)时,线程B也可以持有ReadLock(S),但线程B不能去持有WriteLock(X)。同时线程A如果持有着ReadLock时,如果还想再去持有WriteLock,那么必须等待其他的线程释放ReadLock,并且没有持有WriteLock。

   
当一个线程A持有着WriteLock时,其他的线程不能去持有WriteLock或者ReadLock,但他自己(线程A)还是可以去读取的,而不需要去持有ReadLock。

 

 

1、Lock

InnoDB中,有多种类别的锁,下面将一一说明。

 

1.2 Intention Lock

意向锁,想要做某事时的锁,这是个表锁。分为两种:意向读锁(IS)、意向写锁(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

 

使用意向锁,有两个好处:1、能够很快的进行上锁、或者不上锁操作,因为开启意向锁之后,有一个线程持有一把读锁或者意向读锁后,
另外一个线程想要持有写锁,
就要先去持有意向写锁,而意向写锁很容易就知道了暂时拿不到。如果不使用意向锁,那么就得先找到这条记录,
找到记录后,发现该行记录的读锁因为已经被其他线程持有,而不能完成写锁的持有。这样白白的浪费了查找的时间。

2、能够有效的避免死锁的发生。

 

但是也因为是表锁,粒度太大,导致并发很低差。在多个事务同时操作一张表时,就变成了串行操作的了。

 

 

1.5 Next-Key Lock

一个next-key lock是结合了一个index lock和它之前的gap lock。

InnoDB的默认隔离级别是:REPEATABLE_READ,这种隔离级别下,InnoDB使用在index
scan 时,采用的是next-key。Next-key 本身不存在,只代表了index
lock和它之前的gap lock。

 

 

 

3、DeadLock

事务A
持有row1的lock的同时,事务B持有row2的lock。然后事务A也想要去持有row2的Lock,但同时事务B又不会立刻马上释放row2的lock。这种情况下,就会在事务A中抛出DeadLock的错误。

 

 

相关配置项:

–innodb_deadlock_detect:
用于控制deadlock的检测。默认值ON,代表开启。如果 要关闭,设置为OFF。

–innodb_lock_wait_timeout:
一个事务等待一行lock的时间。超过这时间,就抛出Error,并执行rollback:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个配置项在innodb_deadlock_detect
开启时是不会使用的,当innodb_deadlock_detect关闭时,都会使用的。这是因为:开启时,一旦发现死锁,就会立即触发Rollback。

 

    当发生死锁时,处理方案:

1) 找到相关的SQL

2) 通过Explain 分析SQL执行方式(主键索引、
唯一键扫描、范围扫描、全表扫描)

3)结合事务级别、SQL执行顺序等对死锁原因进行分析。

Transaction,innodblock InnoDB
是一个支持事务的Engine,要保证事务ACID,必然会用到Lock。就像在Java编程一下,要保证数据的线程安全性…

session A session B
truncate table tab1;  

start transaction;

 
  start transaction;
  insert into tab1 values (1,"1");
  commit;

select * from tab1;
+——+——+
| col1 | col2 |
+——+——+
| 1 | 1 |
+——+——+

 

2.1事务数据库可能发生的问题有哪些?

 

Dirty reads: 
(脏读)一个事务A读到了另一个事务B还没有提交的数据(未提交的增删改的数据)。此时事务A就发生了脏读。因为有可能事务B不再提交这个数据,那么A就是读到的垃圾数据。

Fuzzy or non-repeatable
reads:
 当一个事务内,重新读取之前已经读过的数据时,发现读取到了其他的事务修改了某些数据或者删除了某些数据。

Phantom
reads: 
 (幻读)在一个事务内,重新执行一个查询时,发现有其他的已提交的事务插入了新的数据符合查询条件的数据。

 

 

 

2.2 ACID

Atomicity:事务是一个原子操作,对其数据的修改,要么全部执行,要么都不执行。

Consistent:连续性,一致性。必须保证一个事务内,连续两次执行同样的查询,执行结果是一样的。

Isolation:隔离性。事务提供了几种不同的隔离级别。隔离是值事务与事务之间的隔离,隔离强度越大,出现的问题就越少。

Durable:持久性。事务完成后,数据的变更是持久化的。MySQL
InnoDB通过undo来保证持久性。

 

 

2、Transaction

 

1.4 Gap Lock

缝隙锁,所谓gap是指两个索引之间的gap。每一个gap也有一把锁,称为gap
lock。在第一条数据之前,最后一条数据之后,也各有一个gap,所以也有gap
lock。

Gap Lock可以有效的避免幻读发生。例如一个事务A在执行SELECT c1 FROM t
WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 同时另一个事务B要insert 一个
c1=15的行。此时事务B是拿不到gap lock的,因为10到20直接的gaps
locks都被事务A持有。此时并不会管有没有一条c1=15的记录存在,事务B都拿不到Gap。

 

Gap
Lock可以显式的禁用,具体方式是设置隔离级别为READ_COMMMITED或者设置系统变量:Innodb_locks_unsafe_for_binlog。这种情况下,在scan
index时是无效的,只会在foreign-key检查时才会有效。也可以理解成:一个事务如果是REPEATABE_READ隔离级别,则(可能)会Gap
Lock 。这是说的可能,是因为有一个特殊情况
:如果一个select语句只是从一个唯一索引的表,查询一条记录时,是不会使用gap
lock的,因为没有必要的。

SELECT * FROM child WHERE id = 100;

Id是索引,并且唯一的。此时执行上述SQL时,最多只会找到一行记录,就不需要持有gap
lock,而是直接持有index record lock。

 

 

 

session A session B
start transaction;  
  start transaction;

select * from tab1;
Empty set

 
  insert into tab1 values (1,"1");

select * from tab1;
Empty set

 
  commit;

select * from tab1;
Empty set

 

commit;

 

select * from tab1;
+——+——+
| col1 | col2 |
+——+——+
| 1 | 1 |
+——+——+

 

 

 

 

 

session A session B
truncate table tab1;  

start transaction;

 
  start transaction;

select * from tab1;
Empty set

 
  insert into tab1 values (1,"1");
  insert into tab1 values (2,"2");
  insert into tab1 values (3,"3");
  commit;

select * from tab1;
Empty set

 

update tab1 set col2 ="22" where col1>=2;

2 rows affected

 

select * from tab1;
+——+——+
| col1 | col2 |
+——+——+
| 2 | 22 |
| 3 | 22 |
+——+——+

 

 

 

 

 

 

 

 

2.select … with consistent
snapshot不会读到在本事务开始后提交的数据,即使第一次select是在其他事务提交后

innodb为实现MVCC所使用的内部快照,RR(REPEATABLE
READ)隔离级别下在第一次查询时创建read view,RC(READ
COMMITTED)隔离级别下会在每次查询时创建read view
以下测试在RR隔离级别下,数据库版本为5.7.20
1.

 

 

 

 

结论:在已经查询后,其他事务做的修改,在本事务不可见

发表评论

电子邮件地址不会被公开。 必填项已用*标注