SQL Server 事务隔离级别详解 SQL Server中锁与事务隔离级别

SQL 事务隔离级别

概述

  1.  隔离级别用于决定如果控制并发用户如何读写数据的操作，同时对性能也有一定的影响作用。

步骤

事务隔离级别通过影响读操作来间接地影响写操作；可以在回话级别上设置事务隔离级别也可以在查询（表级别）级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别：
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别：READ UNCOMMITTED隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

SQL Server 事务隔离级别的查看及更改

  1. --Sql Server 允许脏读查询sql
  2. select * from category with(nolock)
  4. --查看SQL事务隔离级别(isolation level)
  5. DBCC Useroptions
  7. --设置SQL事务隔离级别 SET TRANSACTION ISOLATION LEVEL <隔离级别>
  8. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  --允许脏读（读取未提交）
  9. SET TRANSACTION ISOLATION LEVEL READ COMMITTED        --不允许脏读（读取提交）
  11. --设置回话隔离
  12. SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
  13. --注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)
  15. --设置查询表隔离
  16. SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>)

造成脏读、不可复读 和幻读的根本原因是数据库锁的级别

隔离级别 脏读（Dirty read） 不可重复读（Non-repeatable read） 幻读（Phantom read）
READ_UNCOMMITED 允许 允许 允许
READ_COMMITED 不允许 允许 允许
REPEATABLE_READ 不允许 不允许 允许
SERIALIZBLE 不允许 不允许 不允许

脏读：脏读指的是一个事务允许读取其他正在运行的事务还没有提交的改变。这种情况的发生主要因为没有加锁。即事务A向表中插入了一条数据，此时事务A还没有提交，此时查询语句能把这条数据查询出来，这种现现象称为脏读；脏读比较好理解
不可重复读：不可重复读是指事务A读取了事务B已经提交的更改数据。不可重复读指的是一个事务内连续读却得到不同的结果，主要因为同时有其他事务更新了我们正在读取的数据。要达到允许可重复读的目的，我们必须让当前事务保持一个读共享锁。一个事务A第一次读取的结果之后， 另外一个事务B更新了A事务读取的数据，A事务在第二次读取的结果和第一次读取的结果不一样这种现象称为不可重复读。
SQL Server 事务隔离级别详解 - 图1
幻读：幻读指的是事务不是串行发生时发生的一种现象，是事务A读取了事务B已提交的新增数据。例如第一个事务对一个表的所有数据进行修改，同时第二个事务向表中插入一条新数据。那么操作第一个事务的用户就发现表中还有没有修改的数据行，就像发生了幻觉一样。解决幻读的方法是增加范围锁（range lock）或者表锁。四种事务隔离级别中只有SERIALIZABLE能够解决幻读。事务A更新表里面的所有数据，这时事务B向表中插入了一条数据，这时事务A第一次的查询结果和第二次的查询结果不一致，这种现象我称为幻读。
SQL Server 事务隔离级别详解 - 图2

MySQL的默认事务隔离级别是REPEATABLE_READ，ORACLE、SQL Server、DB2和PostgreSQL的默认事务隔离级别是READ_COMMITED。

事务ACID特性

事务的ACID特性分别指的是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。
原子性：将一组操作组合成原子操作，只有所有操作都成功才算成功，否则只要有一个操作失败就全部失败
一致性：事务必须保证系统处于一致性状态，不管事务如何交织并发执行。必须保证下面几条：

  • 如果一个操作触发了间接行为（CASCADE、TRIGGER等），那么间接行为也必须成功，否则事务失败
  • 如果一个系统包含多个数据节点，那么一致性强制要求修改必须传播到所有节点
  • 虽然事务可以并行执行，但系统就好像在串行执行事务一样，即与串行执行事务导致的最终系统状态是一样的

隔离性：隔离性保证一个事务中未提交的修改对外界不可见。隔离性通过锁机制达到。
持久性：一个成功的事务必须永久改变系统的状态，在事务执行结束之前状态改变被记录在事务日志中。这样万一系统崩溃或断电，未完成的事务也可以回放。