谷歌零碎笔记之数据库事务

概述

数据库避免不了并发多事务问题，并发多事务会造成脏写、脏读、不可重复读、幻读等问题。为了解决多事务并发问题，数据库设计了事务隔离机制、锁机 制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

事务属性（ACID）

• 原子性(Atomicity)：

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

• 一致性(Consistent)：

  在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规 则都必须应 用于事务的修改,以保持数据的完整性。

• 隔离性(Isolation)：

  保证事务在不受外部并发操作影响的“独 立”环境执行。

• 持久性(Durable)：

  事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

并发事务带来的问题

1. 脏写：

   当两个或多个事务选择同一行，由于隔离性不知道其他事务对数据进行修改，做个事务最后事务修改覆盖掉其他的

2. 脏读：读到未提交数据

   事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求。

3. 不可重复读：同一事务多次读取数据结果不同

   事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

4. 幻读：事务A读取到了事务B提交的新增数据，不符合隔离性

   原表有三条数据，事务A读取，事务B加了一条。事务A虽然看不到，但是可以修改

事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(repeatable read)	不可能	不可能	可能
可串行化(Serializable)	不可能	不可能	不可能

数据库事务隔离相关：

• 默认级别：

  可重复读

• 事务级别：

  -- 查询事务级别
  show variables like 'tx_isdlation';
  -- 设置事务级别：
  set tx_isolation = 'REPEATABLE-READ; -- 读未提交 隔离级别的英文大写	
  

锁分类

• 性能上：

  乐观锁：无锁等待，可以操作失败，代码去处理

  悲观锁：有锁等待，一个事务对数据处理其他要等待（读锁，写锁均属于悲观锁）

• 数据库操作：

  读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

  写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

• 数据操作上：

  表锁：

  • 每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。
  • 加读锁:当前session和其他session都可以读该表 当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
  • 加写锁：当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

  -- 手动增加表锁
  lock table 表名称 read（write）,表名字read（write）
  -- 查看上锁的表
  show open tables;
  -- 删除表锁
  unlock tables;
  

  行锁：

  • 每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

  • 一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

  • 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

  • 行锁分析：

    -- 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
    show status like 'innodb_row_lock%';
    Innodb_row_lock_current_waits: -- 当前正在等待锁定的数量
    Innodb_row_lock_time: -- 从系统启动到现在锁定总时间长度
    Innodb_row_lock_time_avg: -- 每次等待所花平均时间
    Innodb_row_lock_time_max：-- 从系统启动到现在等待最长的一次所花时间
    -- 比较重要的主要是：
    Innodb_row_lock_time_avg （等待平均时长）
    Innodb_row_lock_waits （等待总次数）
    Innodb_row_lock_time（等待总时长）
    
    

  • 查看INFORMATION_SCHEMA系统库锁相关数据表

    ‐‐ 查看事务
    select * from INFORMATION_SCHEMA.INNODB_TRX;
    ‐‐ 查看锁
    select * from INFORMATION_SCHEMA.INNODB_LOCKS;
    ‐‐ 查看锁等待
    select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    ‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
    kill trx_mysql_thread_id
    ‐‐ 查看锁等待详细信息
    show engine innodb status\G;
    
    

  间隙锁：

  • 锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁 在某些情况下可以解决幻读问题。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-olSPTcZC-1655461785940)(C:\Users\gulu\AppData\Roaming\Typora\typora-user-images\image-20220617181149460.png)]

    如果update account set name =‘’ where id <20 and id <7;

    会默认上间隙锁，在3-正无穷都不允许修改

  临时锁：

  • 同上图
  • 如果update account set name =‘’ where id <18 and id <7;
  • 会默认上间隙锁，在3-19都不允许修改（包含19）；

锁优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

  -- 如果条件是索引，会把对应的数据上锁。如果条件不是索引，可能对整个表进行上锁
    update account set balance = 800 where name = 'lilei';
  -- 锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)
  -- 例如：
  select * from test_innodb_lock where a = 2 for update; 
  -- 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
  

• 合理设计索引，尽量缩小锁的范围

• 尽可能减少检索条件范围，避免间隙锁

• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

• 尽可能低级别事务隔离

这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

- 合理设计索引，尽量缩小锁的范围

- 尽可能减少检索条件范围，避免间隙锁

- 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

- 尽可能低级别事务隔离