MySQL ----- 事务,锁机制

13. 事务

Transaction, 事务就是为了保证数据的一致性(多用户对同一数据的更改会相互影响)

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求

事务的ACID特性:

原子性(Atomicity), 一致性(Consistency), 隔离性(Isolation), 持久性(Durability)

默认情况下, 每条SQL语句都是一个事务(执行完成后自动提交)

开启:

标记一个事务的起始点

• BEGIN; 或

• START TRANSACTION;

提交:

• COMMIT; 更新都写到磁盘上的物理数据库中(不能再回滚), 并结束事务

事务控制语句

回滚:

• ROLLBACK;撤销事务的所有已完成的更新操作, 回滚到事务开始时的状态, 并结束事务

# 案例: 银行转账500元,此时其他会话也在访问同一数据
# A会话开启事务
mysql> BEGIN;
mysql> update bank set curMoney=curMoney-500
where custName='张三'; # 事务没有COMMIT, 更新不刷入磁盘

# B会话
mysql> select * from bank; # 数据没更新

隔离级别:

• 若事务无隔离性, 可能产生现象:

  • 脏读: 更新的数据还没提交, 导致读取的数据无用;
  • 不可重复读: 同一事务多次读取同一数据可能不同,因为其他事务的更新操作;
  • 幻读: 事务按相同条件重新读取发现其他事务插入了满足查询条件的新记录;

• 四种隔离级别:

  低级别的隔离级别可以支持更高的并发处理, 同时占用资源更少

  隔离级别(低->高)	脏读	不可重复读	幻读
  read uncommited(可读取未提交的更新数据)	Y	Y	Y
  read commited(只能读取已提交的更新数据)	N	Y	Y
  reapeatable read(事务读取数据时其他事务不能更新同一数据, 默认)	N	N	Y
  serializable(串行化, 未提交的更新数据事务可以被其他事务读取, 在每个读取的数据行上加上共享锁实现)	N	N	N

  • 修改隔离级别: set GLOBAL TRANSACTION isolation level 隔离级别;
  • 查询隔离级别: show variables like '%tx_isolation%;'

自动提交事务:

每条sql语句都默认为一个事务, 执行完成默认自动提交(可更改)

• 查看自动提交模式默认为ON: show variables like 'autocommit';
• 设置自动提交模式的值
  • 关闭事务自动提交: set autocommit = 0; 或 set autocommit = OFF;
  • 开启事务自动提交: set autocommit = 1; 或 set autocommit = ON;

注意:

mysql的事务是一个非常消耗资源的功能

• 事务尽可能简短
• 事务中访问的数据量尽可能少
• 查询数据时尽量不使用事务
• 事务处理过程中尽量不要有等待用户输入的操作
• 事务隔离方法有两种:
  • 加锁
  • 不加锁, 通过一定机制生成一个数据请求时间点的一致性数据快照(Snapshot), 并用其提供一定级别的一致性读取, 这样数据库可以提供同一数据的多个版本,这种技术叫多版本并发控制(MultiVersion Concurrency Control), 简称**‘MVCC’** 或 MCC, 即多版本数据库

14. 锁机制

为了保证数据库并发访问时的数据一致性, 也实现了mysql各级隔离级别, 直接影响了数据库的并发处理能力和系统性能

锁级别分类

• 共享锁S, Share, 也称读锁, 获取该锁可读取但无法写入对应数据集

  • 锁粒度是行/元组
  • 一个事务获取共享锁后, 可以锁定该数据集执行读操作, 会阻止其他事务获取相同数据集的排他锁.

• 排他锁X, eXclusive, 也称写锁, 获取该锁可读取但无法写入对应数据集

  • 锁粒度是行/元组
  • 一个事务获取排他锁后, 可以锁定该数据集执行写操作, 会阻止其他事务获取相同数据集的 共享锁 和 排他锁.

• 意向锁I, Intention, 分为 意向共享锁IS 和 意向排他锁IX, InnoDB自动加的, 不需用户干预

  • 锁粒度是表锁, 锁定整张表
  • 意向: 指的是事务希望对数据上共享锁 和 排他锁, 但并没有真正执行

• 锁的兼容情况

  兼容表示作用于同一数据集的两把锁可以同时存在不同事务中

  不兼容表示作用于同一数据的两把锁不可同时存在, 必须等待释放

  锁类型	X	S	IX	IS
  X	N	N	N	N
  S	N	Y	N	Y
  IX	N	N	Y	Y
  IS	N	Y	Y	Y

锁粒度分类

• 行级锁, row lock

  • 主要应用于 InnoDB 存储引擎
  • 粒度最小的锁, 也最容易发生死锁
  • 最大程度的支持高并发, 因为遇到锁等待的可能性最小
  • 但每次获取释放锁需要做的事情更多, 带来了最大的性能开销

• 表级锁, table lock

  • 粒度最大的锁, 很好的避免死锁
  • 一个事务对表进行写操作时, 要先获得写锁, 再阻塞其他事务获取锁
  • 系统开销最小, 但是遇到锁等待的可能性最大, 并发度大打折扣
  • 查看表级锁争用情况: show status like 'table%'; Table_locks_waited的值比较高,说明有较严重的表级锁争用情况

• 页级锁

  • mysql比较独特的锁定级别
  • 主要应用于BDB存储引擎

  	表级锁	行级锁	页级锁
  开销	小	大	中
  加锁	快	慢	中
  死锁	不会	会	会
  锁粒度	大	小	中
  并发度	低	高	一般

  表级锁: 适合以查询为主, 只有少量按索引条件更新数据的应用,如Web应用

  行级锁: 适合有大量按索引条件, 同时有并发查询的应用, 如在线事务处理系统(OLTP)

使用锁

• 自动加锁

  • 查询语句(SELECT)会自动给涉及的表加读锁
  • 更新操作(UPDATE, DELETE, INSERT等)会自动给涉及的表加写锁

• 手动加锁

  mysql> lock table student write; # 给student加表级写锁,其他事务要等待写锁释放
  mysql> select * from student lock in share mode; # 读锁
  
  mysql> lock table student read; # 其他会话需要等待释放才能获取写锁
  mysql> select * from student for update; # 写锁
  mysql> unlock tables; # 释放锁并自动提交事务
  

• 并发插入

  MyISAM引擎有一个concurrent_insert系统变量, 用于控制并发插入的行为

  • 查看: show variables like 'concurrent_insert';
  • 值
    • 0, 不允许并发插入
    • 1, 默认值, 若表没有空洞(没有中间记录被删除), 允许其他会话在表尾并发插入, 但不可更新
    • 2, 无论表是否有空洞, 都允许其他会话在表尾并发插入, 但不可更新

死锁

• MyISAM只支持表锁, 要么全部获取, 要么等待, 是不会发生死锁的
• InnoDB中, 锁是逐步获取的(默认行锁)
  • 若两个事务都需要获得对方持有的排他锁才能继续完成事务, 这种循环等待就是经典的死锁
  • InnoDB能自动处理行锁导致的死锁, 先释放一个事务的锁且回滚, 另一个事务获得锁继续完成事务
  • 对于外部锁和表锁, InnoDB不能自动检测到死锁, 可通过设置 innodb_lock_wait_timeout 合适的锁等待超时阈值(默认50)
• 其他方法避免死锁(应用程序中)
  • 应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
  • 应用程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能