数据库系列：数据库事务、锁、死锁

本文主要介绍数据库的事务和锁，内容包括：

• 数据库事务和级别
  • 事务的四大特性（ACID）
  • 数据库事务级别
  • 事务级别的使用
  • 脏读、不可重复读、幻读
• 保障数据库事务的锁
  • 读锁、写锁、共享锁（S锁）、独占锁（X锁）、意向共享锁（IS）、意向排他锁
  • 行锁、表锁、页锁
  • 悲观锁、乐观锁
  • 锁的本质
  • 释放锁
  • 共享或排他锁的三种形式
• 非锁
  • MVCC
• 死锁
  • 死锁示例
  • 死锁产生原因

数据库事务和级别

---

• 数据库所有增删改查操作其实都是事务，通过指令：SHOW VARIABLES LIKE ‘autocommit’;查看自动事务开启状态，可以通过set autocommit on/off ;来开启或者关闭。
• 事务的四大特性（ACID）
  • 1、原子性（Atomicity）
  • 2、一致性（Consistency）
  • 3、隔离性（Isolation）
  • 4、持久性（Durability）
• 数据库事务级别
  • Read Uncommitted（读取未提交内容）
  • Read Committed（读取提交内容）
  • Repeatable Read（可重读）
  • Serializable（可串行化）
• 事务级别的使用
  • 查看当前事务的隔离级别：select @@tx_isolation;
  • 设置事务的隔离 级别：
    • set [glogal | session] transaction isolation level 隔离级别名称;
    • set tx_isolation=’隔离级别名称;’
  • 注意事项
    • 设置数据库的隔离级别一定要是在开启事务之前
    • 隔离级别的设置只对当前链接有效
    • JDBC
      • setAutoCommit(false)
      • setTransactionIsolation(level)
• 脏读、不可重复读、幻读
  • 脏读：
    • 两个事务同时运行，A事务执行完查询语句后，B事务进行了更改，但是未Commit，A事务再次查询，结果不一致。
  • 不可重复读：
    • 两个事务同时运行，A事务执行完查询语句后，B事务进行了更改（删除和修改），并且Commit，A事务再次查询，结果不一致。
  • 幻读：
    • 两个事务同时运行，A事务执行完查询语句后，B事务进行了更改（新增），并且Commit，A事务再次查询，结果不一致。
• 事务的实现原理

保障数据库事务的锁

---

• 读锁、写锁、共享锁（S锁）、独占锁（X锁）、意向共享锁（IS）、意向排他锁（IX）

  • 读锁：
    • 允许多个事务对同一个数据进行读操作。
    • 示例：lock table xxx read
  • 写锁：
    • 排他锁，只能允许一个事务进行操作。
    • 示例：lock table xxx read
  • 读写锁的升级关系（也是产生死锁的原因）
    • 读锁无法升级为写锁
    • 写锁可以降级为读锁
  • 共享锁：
    • 类似读锁。
    • 示例：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
  • 独占锁：
    • 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
    • 示例：SELECT * FROM table_name WHERE … FOR UPDATE
  • 意向共享锁：事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁：事务在给一个数据行加排他锁前必须先取得该表的IX锁

• 行锁、表锁、页锁（锁的粒度）

  • 锁定数据库的行、表、页等模块，不同的数据库，不同的引擎时下形式不同。

• 悲观锁、乐观锁

  • 利用时间戳等，通过不停的对比来更新数据的，是乐观锁。
  • 加独占锁，就是悲观锁，比如for update。

• 锁的本质

  • 锁的本质是锁索引，锁的是聚簇索引，
    • InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
    • InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
    • 存在主键的话是主键索引
    • 不存在主键的话，是锁_row索引，因此会造成行锁升级为表锁

• 释放锁

  • 显示加锁后，使用unlock
  • 隐式加锁后，使用回滚(rollback)或者提交(commit)

• 共享或排他锁的三种形式

  • Record lock
    • 锁的时候，只锁特别的记录
  • Gap lock
    • 锁住记录的间隙
  • Next-Key Locks
    • 除了锁住记录本身，还要再锁住索引之间的间隙

• 参考博客

  • MySQL中锁详解（行锁、表锁、页锁、悲观锁、乐观锁等）
  • 理解innodb的锁(record,gap,Next-Key lock)

非锁 MVCC

---

• MVCC

  • 多版本并发控制的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果

  • 快照读和当前读

快照读
SELECT * FROM t WHERE id=1
当前读
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM t WHERE id=1 FOR UPDATE;

• 参考博客：MVCC多版本并发控制

死锁

---

• 死锁示例
  delete from dept
  where dname IN (
  select dname from dept
  group by dname
  having count(1) > 1
  )
• 会出现如下错误：
  • [Err] 1093 - You can’t specify target table ‘dept’ for update in FROM clause
• 死锁产生原因
  • 更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作
• 解决办法：
  • 把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。
  • delete from dept
    where dname IN (
    select t.dname from (
    select dname from dept
    group by dname
    having count(1) > 1) t
    )