图片 22

InnoDB:Lock & Transaction,innodblock

 

   
如果转载,请注明博文来源: www.cnblogs.com/xinysu/ 
 ,版权归 博客园 苏家小萝卜 所有。望各位支持!

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

 

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

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

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

 

 

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

4.1 脏读

    读取未提交事务中修改的数据,称为脏读。

    举例,表格 A (name,age),记录1为name=’xinysu’,age=188

    图片 1

    这里,事务2 中读出来的数据是
(name,age)=(’xinysu’,299),这一条是
事务1中未提交的记录,属于脏数据。

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也同样如此:

 

 

 

 

4.2 丢失更新

      多个更新操作并发执行,导致某些更新操作数据丢失。

      举例,表格 A
(name,age),记录1为name=’xinysu’,age=188。并发2个更新操作如下:

      图片 2

     
正常情况下,如果是事务1操作后,age为288,事务2再进行288+100=388,但是实际上,事务2的操作覆盖事务1的操作,造成了事务1的更新丢失。

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的索引锁的。

 

 

 

5.2.4 Read Serializable

所有事务隔离级别设置: set session transaction isolation level
Serializable   ;

 

该隔离级别不支持快照读,所有SELECT查询都是当前读,并且持有S锁.

 

测试1:update数据不提交,另起查询;INSERT数据不提交,另起事务多次查询

测试结果:该隔离级别下所有select语句持有S锁,导致update申请X锁处于等待情况,INSERT申请X也被堵塞,同个事务内的多次查询结果一致,不存在脏读、不可重复读及幻读情况。

 图片 3

 

总结:无快照读,所有SELECT查询都是当前读,不存在脏读、不可重复读问题、幻读问题。

 




 

以为没了,not,还有一个概念这里没有提交,这里补充介绍下:semi-consistent
read

 




1、Lock

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

 

PS: semi-consistent read

 

在read committed或者read uncommitted 隔离级别下,有这样的测试现象:

 

测试表格及数据

 

CREATE TABLE `tblock` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

 

insert into tblock(name) select ‘su’;

insert into tblock(name) select ‘xin’;

 

 

测试1:两个update事务并发,分别update不同行,update条件列无索引

测试结果:两条update互不干扰,正常执行。

图片 4

 

测试2:update语句不提交,另起事务当前读操作

测试结果:当前读被堵塞,无法正常加X锁

图片 5.png)

图片 6

 

    问题点:为啥两个测试中的sql序号2,都是申请X锁,测试1可以正常申请情况,而测试2不行呢?

 

   
正常情况下,where条件中的name列没有索引,故这个update操作是对全表做scan扫描加X锁,正常情况下,在第一个事务中,update语句没有提交的情况下,这个表格有一个表锁X,对每一行数据都无法申请S锁或者X锁,那么为什么
测试1 可以正常申请呢?

 

    在这里,需要引入semi-constent-read,半一致性读。官网解释如下:

 

semi consistent
read:

A type of read operation used
for UPDATE statements, that is a combination of read committed and
consistent read. When an UPDATE statement examines a row that is already
locked, InnoDB returns the latest committed version to MySQL so that
MySQL can determine whether the row matches the WHERE condition of the
UPDATE. If the row matches (must be updated), MySQL reads the row again,
and this time InnoDB either locks it or waits for a lock on it. This
type of read operation can only happen when the transaction has the read
committed isolation level, or when the
innodb_locks_unsafe_for_binlog option is enabled.

 

   semi-consistent read是update语句在读数据的一种操作, 是read
committed与consistent
read两者的结合。update语句A在没有提交时,另外一个update语句B读到一行已经被A加锁的记录,但是这行记录不在A的where条件内,此时InnoDB返回记录最近提交的版本给B,由MySQL上层判断此版本是否满足B的update的where条件。若满足(需要更新),则MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。semi-consistent
read只会发生在read committed及read
uncommitted
隔离级别,或者是参数innodb_locks_unsafe_for_binlog被设置为true。
对update起作用,对select insert delete 不起作用。这就导致了update
不堵塞,但是当前读的select则被堵塞的现象。

 

   发生 semi consitent read的条件:

  1. update语句
  2. 执行计划时scan,range scan or table scan,不能时unique scan
  3. 表格为聚集索引

总结如下:

 图片 7

 

2.2 ACID

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

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

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

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

 

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

5.2.3 Read Repeatable

所有事务隔离级别设置: set session transaction isolation level
repeatable read ;

 

由于该隔离级别支持快照读,不添加for update跟lock in share mode的select
查询语句,使用的是快照读,不添加锁。所以测试使用当前读的模式测试,添加lock
in share mode,添加S锁。

 

测试1:update数据不提交,另起查询

测试结果:由于当前读持有S锁,导致update申请X锁处于等待情况,无法更新,同个事务内的多次查询结果一致,无脏读及不可重复读情况。

图片 8.png)

图片 9

 

测试2:INSERT数据不提交,另起事务多次查询

测试结果:同个事务多次读取相同范围的数据,会有GAP锁锁定,故同个事务多次当前读结果记录数都是一致的,不存在幻读情况。

 图片 10

 

测试3:快照读测试

测试结果:同个事务多次读取相同记录,不存在脏读及丢失更新、不可重复读及幻读等情况。

图片 11

 

总结:支持快照读,快照读跟当前读不存在脏读、不可重复读问题、幻读问题。

 

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、能够有效的避免死锁的发生。

 

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

 

 

 

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

 

 

 

 

 

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。

 

 

结论:虽然事务A看不到事务B做的修改,但是修改也会影响事务B已经提交的数据,且修改发生后,被修改的记录(尽管是其他事务提交的),也会变为对该事务可见

2  Innodb的MVCC

      在Innodb db中,无论是聚簇索引,还是二级索引,每一行记录都包含一个
DELETE bit,用于表示该记录是否被删除,
同时,聚簇索引还有两个隐藏值:DATA_TRX_ID,DATA_ROLL_PTR。DATA
_TRX_ID表示产生当前记录项的事务ID,这个ID随着事务的创建不断增长;DATA
_ROLL_PTR指向当前记录项的undo信息。

  1. 无论是聚簇索引,还是二级索引,只要其键值更新,就会产生新版本。将老版本数据deleted
    bti设置为1;同时插入新版本。
  2. 对于聚簇索引,如果更新操作没有更新primary
    key,那么更新不会产生新版本,而是在原有版本上进行更新,老版本进入undo表空间,通过记录上的undo指针进行回滚。
  3. 对于二级索引,如果更新操作没有更新其键值,那么二级索引记录保持不变。
  4. 对于二级索引,更新操作无论更新primary
    key,或者是二级索引键值,都会导致二级索引产生新版本数据。
  5. 聚簇索引设置记录deleted
    bit时,会同时更新DATA_TRX_ID列。老版本DATA_TRX_ID进入undo表空间;二级索引设置deleted
    bit时,不写入undo。

       MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ
UNCOMMITED不是MVCC兼容的,因为查询不能找到适合他们事务版本的行版本;它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容,因为读操作会锁定他们返回的每一行数据

 

     
在MVCC中,读操作分为两类:当前读跟快照读,当前读返回最新记录,会加锁,保证该记录不会被其他事务修改;快照读,读取的是记录的某个版本(有可能是最新版本也有可能是旧版本),不加锁。

 

      快照读:RU,RC,RR隔离级别下,select * from tbname where ….

      当前读:

  1. select * from tbname where ….  for update (加X锁)
  2. select * from tbname where ….  lock in share mode(加S锁)
  3. insert into tbname …. (加X锁,注意如果有unique key的情况)
  4. delete from tbname … (加X锁)
  5. update tbname set … where .. (加X锁)

       本部分参考:

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。

 

 

 

 

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)

最高级别,事务级

 

 

 

 

 

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。

 

 

3 Two Phase Locking

     
2-PL,也就是两阶段锁,锁的操作分为两个阶段:加锁、解锁。先加锁,后解锁,不相交。加锁时,读操作会申请并占用S锁,写操作会申请并占用X锁,如果对所在记录加锁有冲突,那么会处于等待状态,知道加锁成功才惊醒下一步操作。解锁时,也就是事务提交或者回滚的时候,这个阶段会释放该事务中所有的加锁情况,进行一一释放锁。

 

   
 假设事务对记录A和记录B都有操作,那么,其加锁解锁按照逐行加锁解锁顺序,如下:

     

BEGIN
LOCK A
READ A
A:A+100
WRITE A
UNLOCK A
LOCK B
READ B
UNLOCK B
COMMIT

 

   
 两阶段锁还有几种特殊情况:conservative(保守)、strict(严格)、strong
strict(强严格),这三种类型在加锁和释放锁的处理有些不一样。

  1. conservative
    • 在事务开始的时候,获取需要的记录的锁,避免在操作期间逐个申请锁可能造成的锁等待,conservative
      2PL 可以避免死锁
  2. strict 
    • 仅在事务结束的时候(commit or rollback),才释放所有 write
      lock,read lock 则正常释放
  3. strong strict

    • 仅在事务结束的时候(commit or
      rollback),才释放所有锁,包括write lock 跟 read lock
      都是结束后才释放。

      图片 12

       

     
这部分可以查看维基百科:,

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编程一下,要保证数据的线程安全性…

 

 

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的约束。

 

 

 

1 什么是MVCC 

      MVCC全称是: Multiversion concurrency
control
,多版本并发控制,提供并发访问数据库时,对事务内读取的到的内存做处理,用来避免写操作堵塞读操作的并发问题。

 

     
举个例子,程序员A正在读数据库中某些内容,而程序员B正在给这些内容做修改(假设是在一个事务内修改,大概持续10s左右),A在这10s内
则可能看到一个不一致的数据,在B没有提交前,如何让A能够一直读到的数据都是一致的呢?

 

      有几种处理方法,第一种:
基于锁的并发控制,程序员B开始修改数据时,给这些数据加上锁,程序员A这时再读,就发现读取不了,处于等待情况,只能等B操作完才能读数据,这保证A不会读到一个不一致的数据,但是这个会影响程序的运行效率。还有一种就是:MVCC,每个用户连接数据库时,看到的都是某一特定时刻的数据库快照,在B的事务没有提交之前,A始终读到的是某一特定时刻的数据库快照,不会读到B事务中的数据修改情况,直到B事务提交,才会读取B的修改内容。

      

     
一个支持MVCC的数据库,在更新某些数据时,并非使用新数据覆盖旧数据,而是标记旧数据是过时的,同时在其他地方新增一个数据版本。因此,同一份数据有多个版本存储,但只有一个是最新的。

 

      MVCC提供了 时间一致性的
处理思路,在MVCC下读事务时,通常使用一个时间戳或者事务ID来确定访问哪个状态的数据库及哪些版本的数据。读事务跟写事务彼此是隔离开来的,彼此之间不会影响。假设同一份数据,既有读事务访问,又有写事务操作,实际上,写事务会新建一个新的数据版本,而读事务访问的是旧的数据版本,直到写事务提交,读事务才会访问到这个新的数据版本。

 

     
MVCC有两种实现方式,第一种实现方式是将数据记录的多个版本保存在数据库中,当这些不同版本数据不再需要时,垃圾收集器回收这些记录。这个方式被PostgreSQL和Firebird/Interbase采用,SQL
Server使用的类似机制,所不同的是旧版本数据不是保存在数据库中,而保存在不同于主数据库的另外一个数据库tempdb中。第二种实现方式只在数据库保存最新版本的数据,但是会在使用undo时动态重构旧版本数据,这种方式被Oracle和MySQL/InnoDB使用。

      

     
这部分可以查阅维基百科:

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

 

 

4.4 幻读

     
同个事务多次读取某段段范围内的数据,但是读取到底行数不一致的情况,称之为幻读。

      举例,表格 A
(name,age),记录1为name=’xinysu’,age=188。操作如下:

      图片 13

     
事务1中,第一次读取的结果行数有1行,如果事务2执行的是delete,则事务1第二次读取的为0行;如果事务2执行的是INSERT,则事务2第二次读取的行数是2行,前后记录数不一致,称之为幻读。

 

2、Transaction

 

5.1 隔离级别介绍

  1. Read Uncommited
    • 简称 RU,读未提交记录,始终是读最新记录
    • 不支持快照读,都是当前读
    • 可能存在脏读、不可重复读、幻读等问题;
  2. Read Commited

    • 简称 RC ,读已提交记录
    • 支持快照读,读取版本有可能不是最新版本
    • 支持当前读,读取到的记录添加锁
      • 不存在脏读、不可重复读
      • 存在幻读问题;
  3. Read Repeatable

    • 简称 RR ,可重复读记录
    • 支持快照读,读取版本有可能不是最新版本
    • 支持当前读,读取到的记录添加锁,并且对读取的范围枷锁,保证满足查询条件的记录不能够被insert进来
    • 不存在脏读、不可重复读及幻读情况;
  4. Read Serializable
    • 简称 RS,序列化读记录
    • 不支持快照读,都是当前读
    • select数据添加S锁,updateinsertdelete数据添加X锁
    • 并发度最差,除非明确业务需求及性能影响,才使用,一般不建议在innodb中应用

 

 5.2.2 Read Committed

所有事务隔离级别设置: set session transaction isolation level read
committed ;

 

由于该隔离级别支持快照读,不添加for update跟lock in share mode的select
查询语句,使用的是快照读,读取已提交记录,不添加锁。所以测试使用当前读的模式测试,添加lock
in share mode,添加S锁。

 

测试1:update数据不提交,另起查询

测试结果:由于当前读持有S锁,导致update申请X锁处于等待情况,无法更新,同个事务内的多次查询结果一致,无脏读及不可重复读情况。

图片 14

 

测试2:INSERT数据不提交,另起事务多次查询

测试结果:同个事务多次读取相同范围的数据,但是行数不一样,属于幻读(这里注意,如果insert
分为beigin;commit,一直不commit的话,3的查询会处于等待情况,因为它需要申请的S锁被
insert的X锁所堵塞) 图片 15

 

测试3:快照读测试

测试结果:同个事务多次读取相同记录,读取的都是已提交记录,不存在脏读及丢失更新情况,但是存在不可重复读及幻读。
图片 16

 

总结:支持快照读,快照读
不存在脏读及丢失更新情况,但是存在不可重复读及幻读;而当前读不存在脏读、不可重复读问题,存在幻读问题。
 

 

4 数据不一致情况

 

4.3 不可重复读

     
同个事务多次读取同一条存在的记录,但是读取的结构不一致,称之为不可重复读。

      举例,表格 A
(name,age),记录1为name=’xinysu’,age=188。操作如下:

      图片 17

     
事务1第一次读出来的结构是name=’xinysu’,age=188,第二次读出来的结果是name=’xinysu’,age=288,同个事务中,多次读取同一行存在的记录,但结果不一致的情况,则为不可重复读。

 

 

 

 

 

5 innodb的隔离级别

 


 

    回来写博客,少年前端时间被django迷了心魄
图片 18


 


 

5.2 隔离级别测试   

测试各个隔离级别下的数据不一致情况。

1.查看当前会话隔离级别
select @@tx_isolation;
 
2.查看系统当前隔离级别
select @@global.tx_isolation;
 
3.设置当前会话隔离级别
set session transaction isolation level repeatable read;
 
4.设置系统当前隔离级别
set global transaction isolation level repeatable read;

5.2.1 Read Uncommitted

所有事务隔离级别设置: set session transaction isolation level read
Uncommited ;

 

该隔离级别没有的快照读,所有读操作都是读最新版本,可以读未提交事务的数据。

 

测试1:update数据不提交,另起查询

测试结果:正常select可以查询到不提交的事务内容,属于脏读

 图片 19

 

测试2:修改数据不提交,另起事务多次查询

测试结果:同个事务多次读取同一行记录结果不一致,属于重复读

 图片 20

 

测试3:INSERT数据不提交,另起事务多次查询

测试结果:同个事务多次读取相同范围的数据,但是行数不一样,属于幻读

图片 21

 

测试4:不同事务对同一行数据进行update

测试结果:由于INNODB有锁机制,所有所有update都会持有X锁互斥,并不会出现事务都提交成功情况下的丢失更新,所以四个隔离级别都可以避免丢失更新问题。

图片 22

 

总结:没有快照读,都是当前读,所有读都是读可以读未提交记录,存在脏读、不可重复读、幻读等问题。

      

 

 

 


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;
  insert into tab1 values (1,"1");
  commit;

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

 

3.

 

 

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

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

参考网址:

另外:

结论:尽管事务A比事务B先开始,但是第一次查询在B事务提交后,所以可以查询到结果

 

2. 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.