Microsoft SQL Server 2008技术内幕：T-SQL语言基础（事务和并发）

九、事务和并发

9.1 事务

• 事务
  • 作为单个工作单元而执行的一系列操作
• 定义事务边界的方式
  • 显式
    • begin tran; … commit tran;
    • begin tran; … rollback tran;
  • 隐式
    • 默认
    • 每个单独的语句作为一个事务
      • 执行完每个语句之后自动提交事务
  • 修改默认行为
    • set implicit_transactions on
• 判断当前是否位于一个打开的事务中
  • select @@TRANCOUNT
• 事务的属性
  • 原子性
    • 全都执行或全都不执行
  • 一致性
    • 同时发生的事务在修改和查询数据时不发生冲突
    • 使用隔离级别进行控制
  • 隔离性
    • 控制数据访问的机制
    • 使用锁进行隔离
  • 持久性
    • 在将数据修改写入到磁盘上数据库的数据分区前，先把修改写入磁盘上数据库的事务日志中
      • 在写入事务日志后，数据修改实际得到更新前，即使发生故障，也可重新执行

9.2 锁定和阻塞

9.2.1 锁

9.2.1.1 锁模式及其兼容性

• 排他锁（X）
  • 试图修改数据时，事务会为所依赖的数据资源请求排他锁，直到事务完成才会释放锁
  • 特点
    • 对于相同的数据资源，已经获取排他锁后，其他事务无法再获取任何类型的锁
    • 不能改变为修改数据资源而请求的锁模式
    • 不能改变持有锁的时间长度
• 共享锁（S）
  • 试图读取数据时，事务会请求共享锁，读操作一完成，就立刻释放共享锁
  • 特点
    • 同一数据资源，不同的事务可以同时获取共享锁
    • 可以控制如何处理锁定
• 兼容性
  • 如果数据正在由一个事务进行修改，其他事务就既不能修改，也不能读取（至少默认不能），直至事务完成
  • 如果数据正在被读取，则其他事务不能修改该数据（至少默认不能）

9.2.1.2 可锁定资源的类型及意向锁

• 资源类型
  • 行
  • 页
  • 对象
  • 数据库
• 意向锁
  • 为了获得特定资源类型上的锁，事务必须先在更高粒度级别上获得相同模式的意向锁
  • 作用
    • 可以很容易在更高粒度级别上检测不兼容的锁定请求
    • 例如，事务持有一个行锁，而其他事务想在包含那一行的整个页或表上请求不兼容的锁模式
  • 意向排他锁（IX）
  • 意向共享锁（IS）

9.2.1.3 并发性与系统资源的平衡

• 并发性
  • 只锁定需要的资源可以获得理想的并发性
• 系统资源
  • 锁定需要占用内存资源和内部管理开销
  • 理想的并发性可能会消耗大量的系统资源
• 锁升级
  • 当细粒度的锁的数量达到一定级别时，会自动升级为更高粒度级别的锁
  • 例如，当单个语句获得至少5000个锁时，会触发锁升级
• 可以控制锁升级的处理方式
  • 例如，禁用或者指定资源类型（默认为表）

9.2.2 检测阻塞

• 阻塞产生的条件
  • 事务对同一资源请求不兼容的锁时，发出请求的事务会进入等待状态
  • 默认情况下，阻塞会一直持续

--会话1请求并获取行排他锁
begin tran;
update Production.Products
set unitprice+=1.00
where productid=2;

--会话2请求行共享锁进入阻塞状态
select *
from Production.Products
where productid = 2;

9.2.2.1 获取锁信息

• sys.dm_tran_lock
  • 视图
  • 锁信息
    • 会话进程ID——spid
    • 被锁定资源的类型——restype
    • 数据库ID——dbname
    • 资源说明和与资源相关的实体ID——res、resid
    • 锁模式——mode
    • 已经授予了锁，还是正在请求锁——status

9.2.2.2 获取联接信息

• sys.dm_exec_connections
  • 联接建立的时间
  • 联接中最后一次发生读操作和写操作的时间
  • 此联接上执行的最后一个SQL批处理
    • sys.dm_exec_sql_text()
      • 表函数，返回结果为表
      • 可以将参数转化为SQL代码

select session_id,connect_time,last_read,last_write,text
from sys.dm_exec_connections
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) as st
where session_id in (54,53);

9.2.2.3 获取会话信息

• sys.dm_exec_sessions
  • 建立会话时间——login_time
  • 特定于会话的客户端工作站名称——host_name
  • 初始化会话的客户端程序名称——program_name
  • 会话所使用的SQL Server登录名——login_name
  • 客户端的windows用户名——nt_user_name
  • 最近一次会话请求的开始时间——last_request_start_time
  • 最近一次会话请求的完成时间——last_request_end_time

9.2.2.4 获取请求信息

• sys.dm_exec_requests

9.2.2.5 设置超时期限及终止阻塞进程

• 设置超时期限
  • 超过期限后，试图获取锁的事务会被取消执行
  • set lock_timeout 毫秒值
    • 0 立刻超时
    • -1 无限期（默认）
• 终止阻塞进程
  • kill session_id
    • 终止长时间运行的事务
    • 该事务会发生回滚

9.3 隔离级别

• 隔离级别
  • 用于决定如何控制并发用户读写数据的操作
• 隔离级别的设置
  • 会话层面
    • set transaction isolation level <isolation name>
      • isolation name
        • 需要时可以带空格
  • 单个查询
    • select … from <table> with (<isolationName>)
      • isolationname
        • 不需要带空格

9.3.1 read uncommitted 未提交读

• read uncommitted级别下读操作不请求共享锁
  • 在读的同时可以写入数据
  • 在写入数据同时可以都，即使写入的数据未提交（脏读）

9.3.2 read committed 已提交读

• read committed 级别下读操作请求共享锁
  • 数据库默认隔离级别
  • 共享锁的释放是在读操作结束，而不是在事务提交后
    • 同一个事务内部的两次读操作可能得到不同的值
      • 不可重复读或不一致的分析

9.3.3 repeatable read 可重复读

• repeatable read 级别下读操作请求共享锁，且锁的持有时间延续到事务结束
  • 事务内的两次读操作可以得到一致的结果
  • 其它事务请求写操作的排他锁会发生阻塞
  • 两个事务读取同一个值，并基于最初读取的值进行计算，再更新值
    • 造成死锁
      • 原因
        • 事务A的读操作持有共享锁直到事务结束，事务B的写操作请求排他锁被事务A的读操作阻塞
        • 事务B的读操作持有共享锁直到事务结束，事务A的写操作请求排他锁被事务B的读操作阻塞
        • 两个事务互相阻塞
      • 避免丢失更新
        • 丢失更新
          • 最后进行更新的值被保留
          • 之前的更新发生丢失
• repeatable read的缺点
  • 只能锁定查询范围内的数据行，无法阻止insert
  • 同一事务内的两次读操作之间，若发生insert，且insert的值满足查询范围，则第二次读操作将读取到insert的数据，即幻读

9.3.4 serializable 可序列化

• serializable 级别下读操作请求共享锁且直到事务结束，同时锁定范围包括未来可能满足查询搜索条件的行

9.3.5 snapshot 隔离级别

• 基于快照的隔离级别
  • 以行版本控制为基础
  • 把事务已经提交的行的上一个版本保存在tempdb数据库中
    • 执行update和delete之前将当前版本复制到tempdb中
    • 对update和delete操作的性能会产生负面影响
    • 读取的是事务开始时可用的上一次提交版本
      • 事务开始后提交的数据不会获取
• 类似serializable 的一致性
• 与serializable 的差异
  • 读操作不需要请求共享锁
  • 不需要等待，速度更快
• 启用方式
  • 需要先对数据库进行设置
    • alter database tsqlfundamentals2008 set allow_snapshot_isolation on;
  • 再设置会话级别
    • set transaction isolation level name
• 检测更新冲突
  • 发现更新冲突后，直接失败终止，不会发生死锁

9.3.6 read committed snapshot 隔离级别

• 启用方式
  • 对数据库进行设置
    • alter database tsqlfundamentals2008 set read_committed_snapshot on;
  • 设置后会话的隔离级别默认为read committed snapshot
• 特点
  • 读操作读取的数据行是语句启动前最后提交的版本
    • 不是事务启动前最后提交的版本
  • 一致性和read committed 类似

9.3.7 隔离级别总结

9.4 死锁

• SQL Server会检测死锁，并选择终止其中你个一个事务以干预死锁状态
  • 默认选择终止做过的操作最少的事务
    • 可以让回滚开销降到最少
  • 可以设置优先级，数据库引擎会终止优先级最低的事务
    • 设置优先级的方式
      • set deadlock_priority -10~10
• 发生死锁的情况
  • 逻辑冲突
    • 事务A更新表1
    • 事务B更新表2
    • 事务A查询表2，发生阻塞，除非事务B提交，否则阻塞一直持续
    • 事务B查询表1，除非事务A提交，否则阻塞一直持续
    • 事务A和事务B都无法解除阻塞造成死锁
  • 缺少良好索引
    • 没有索引，需要扫描并锁定表中所有行造成死锁