一、MySQL事务

image.png

1.1 非预期行为

脏读

当一个事务读取到另外一个事务修改但未提交的数据时,就可能发生脏读。
image.png

不可重复读

当执行SELECT 操作时没有获得读锁或者SELECT操作执行完后马上释放了读锁;另外一个事务对数据进行了更新,读到了不同的结果。
image.png

幻读

“幻读”又叫"幻象读",是''不可重复读''的一种特殊场景,当事务1两次执行''SELECT ... WHERE''检索一定范围内数据的操作中间,事务2在这个表中创建了(如[[INSERT]])了一行新数据,这条新数据正好满足事务1的“WHERE”子句。
image.png
不可重复读强调的是update,幻读强调的是insert/delete

1.2 隔离级别

未提交读

是最低的隔离级别,在这种隔离级别下,如果一个事务已经开始写数据,则另外一个事务则不允许同时进行写操作,但允许其他事务读此行数据。
会产生脏读。

已提交读

读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行,会对该写锁一直保持直到到事务提交。
避免了产生脏读,防不了不可重复读。

可重复读

Innodb默认隔离级别,并不能防范所有幻读情况。

串行化

在选定对象上的读锁和写锁保持直到事务结束后才能释放,所以能防住上诉所有问题,但因为是串行化的,所以效率较低。

切换隔离级别
SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';(或者是MIXED)

1.3 MVCC(多版本并发控制)

image.png

  1. 在对象上加锁,是一种悲观锁机制。有很多文章说可重复读的隔离级别防不了幻读,是认为可重复读会对读的行加锁,导致他事务修改不了这条数据,直到事务结束。但是这种方案只能锁住数据行,如果有新的数据进来,是阻止不了的,所以会产生幻读。
  2. InnoDB使用了以乐观锁为理论基础的MVCC(多版本并发控制)来避免不可重复读和幻读,在每行数据后添加两个额外的隐藏的值来实现MVCC,这两个值一个记录这行数据何时被创建,另外一个记录这行数据何时过期(或者被删除)。
  3. SELECT时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。
  4. INSERT时,保存当前事务版本号为行的创建版本号
  5. DELETE时,保存当前事务版本号为行的删除版本号
  6. UPDATE时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行

通过MVCC可以减少锁的使用,大多读操作都不用加锁,读取数据操作简单,性能好。
image.png

1.4 快照读/当前读

  1. 通过MVCC读取出来的数据其实是历史数据,而不是最新数据,即快照读(snapshot read)。这样可以减少加锁所带来的开销。
  2. 读取数据库当前版本数据的方式,叫当前读(current read)。对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。

1.5 Next-Key锁

假设用到的User表需要对Name建立非聚簇索引
image.png

  1. B+树的特点是所有数据都存储在叶子节点上,以非聚簇索引的秦寿生为例,在秦寿生的右叶子节点存储着所有秦寿生对应的Id
  2. 在我们对这条数据做了当前读后,就会对这条数据加行锁,对于行锁很好理解,能够防止其他事务对其进行update或delete
  3. 为什么要加GAP锁呢? B+树的所有数据存储在叶子节点上,当有一个新的叫秦寿生的数据进来,一定是排在在这条id=34的数据前面或者后面的,我们如果对前后这个范围进行加锁了,那当然新的秦寿生就插不进来了。
  4. 如果有一个新的范统要插进行呢?因为范统的前后并没有被锁住,是能成功插入的,这样就极大地提高了数据库的并发能力。

MVCC+Next-Key并不能完全防范幻读的情况。
image.png
a事务先select,b事务insert确实会加一个gap锁,但是如果b事务commit,这个gap锁就会释放(释放后a事务可以随意操作)
只要在一个事务中,第二次select多出了row就算幻读,所以这个场景下,算出现幻读了。

二、MySQL表锁和行锁

2.1 前言

  1. MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql5.5以上的默认引擎。MyISAM不支持行锁,而InnoDB支持行锁和表锁。
  2. MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,因此一般不需要显式加锁。
  3. 显式加锁
-- 上共享锁(读锁)
select  math from zje where math>60 lock in share mode;
-- 上排它锁(写锁)
select math from zje where math >60 for update;

2.2 表锁

不会出现死锁,发生锁冲突几率高,并发低。

  1. MySQL的表级锁有两种模式:表共享读锁和表独占写锁
  2. 读锁会阻塞写,写锁会阻塞读和写
  3. MyISAM不适合做写为主表的引擎,因为写锁后其它线程不能做任何操作,大量更新会使查询很难得到锁,从而造成永远阻塞

2.3 行锁

会出现死锁,发生锁冲突几率低,并发高。

  1. 在MySQL的InnoDB引擎支持行锁,与Oracle不同,MySQL的行锁是通过索引加载的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。
  2. 在一条select语句后加上for update,询到的数据会被加上一条排它锁,其它事务只能读取不能进行更新和插入操作
  3. 两个事务不能锁同一个索引。
  4. insert,delete,update在事务中都会自动默认加上排它锁。
行锁场景

A用户消费,service层先查询该用户的账户余额,若余额足够,则进行后续的扣款操作;这种情况查询的时候应该对该记录进行加锁。

否则,B用户在A用户查询后消费前先一步将A用户账号上的钱转走,而此时A用户已经进行了用户余额是否足够的判断,则可能会出现余额已经不足但却扣款成功的情况。

为了避免此情况,需要在A用户操作该记录的时候进行for update加锁

三、MySQL连接

image.png

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议