SQL查询优化，nolock问题

在做过的很多项目中，发现大家不管对什么表，逢select必定加上nolock（或者with（nolock）），好像已经是制度化的一种东西。有领导高人解释说加上nolock可以提高查询速度，不影响对数据表的其他并发操作。  
但是真有必要每个查询都加nolock吗？个人认为加不加nolock还是值得我们根据实际情况斟酌一番的（至少需要知其然然后知其所以然吧）。下面就来简单分析一下加不加nolock以及加了nolock对实际查询的一些影响。
一、重要概念
(此处沉思5秒，安静回想经典数据库教科书里的一些重用概念。嗯......什么，你也想不全了？那好吧，别闲烦，道理是要讲的，书是不得不参考的（bs直接抄书的）)
并发访问：同一时间有多个用户访问同一资源。如果并发用户中有其他用户同时对资源进行了修改，这样对同一数据的访问就会出现“所见不是所得”的情况，从而对其它用户产生某些不利的影响，包括：
1：脏读：有一个用户对某一个资源做了修改，此时另外一个用户正好读取了这条被修改的记录，然后第一个用户又放弃了修改，数据还原到修改之前，这两个不同的结果就是脏读。

2：幻读：特指用户读取一批记录的情况。用户两次查询同一条件的一批记录，第一次查询后，有其它用户对这批数据做了修改，方法可能是insert,update或delete，第二次查询时，用户会发现第一次查询的记录条目有的不在第二次查询结果中，或者是第二次查询的条目不在第一次查询的内容中，造成前后查询结果的不一致。
3：不可重复读：系统中某一个用户的一个操作是一个事务，这个事务分两次读取同一条记录。如果第一次读取后，正好有另外一个用户修改了这条记录，然后第二次读取的正好是之前进行修改记录的那位用户的数据，这样就有可能造成两次读取的数据不同。当然如果我们在事务中锁定这条记录就可以避免。

二、如何消除并发访问的不利影响
如前所述，既然并发访问会造成这么多不利影响，我们又该如何解决呢？估计一般程序员的下意识反应就是像我们在控制多线程并发编程的时候一样，加锁，lock一下，over。没错，还真不能说你说的不对！真是聪明又幸福的程序员啊！
其实在ms的Sql Server中，有两种并发访问的控制机制：锁和行版本控制,关于并发控制，ms的阐述和解决方案真是详细而周到。你不得不pf我们的ms是多么的亲妈啊，真的什么都帮我们想好并且做好了。
先分析一下数据库的锁。
小抄一段参考书上的：

1、锁：“每个事务对所依赖的资源会请求不同类型的锁，它可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。当事务不再依赖锁定的资源时，锁将被释放”。 从数据库系统的角度来看：我们可以把锁分为共享锁、独占锁（排它锁）和更新锁：
（1）、共享 (S) ：用于不更改或不更新数据的操作（只读操作），比如我们常见的select语句等。
（2）、更新 (U) ：用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
（3）、排它 (X) ：用于数据修改操作，例如insert、update或delete。确保不会同时对同一资源进行多重更新。

对于如此看似简单其实重要繁琐的东西，当然不能让庞大的程序员群体去设置或控制它们。Sql Server通过设置事务的隔离级别自动管理锁的设置和控制。锁管理器通过查询分析器分析待执行的sql语句，进而来判断这些sql语句将会访问哪些资源，进行什么操作，然后结合设定的隔离级别自动分配管理需要用到的锁。

下面接着来了解一下行版本控制。
2、行版本控制:
还用想吗？小抄一下：
（1）、简介
“ 行版本控制的隔离是Sql Server 2005一个新的隔离框架。使用行版本控制的隔离可以在大量并发的情况下，显著减少所得产生，并且与nolock相比，它又可以显著降低肮脏读，幻影，丢失更新等现象的发生(READ_COMMITTED_SNAPSHOT)。当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁（S 锁），因此不会阻塞正在修改数据的事务。另外，锁定资源的开销随着所获取的锁的数量的减少降至最低。使用行版本控制的已提交读隔离和快照隔离可以提供副本数据的语句级或事务级读取一致性”。
（2）、原理
“Sql Server 2005的行版本控制原理上很简单（不说不知道，笔者注），就是在库表中每一行的记录上都悄悄的增加了一个类时间戳列（行版本列）。当使用行版本控制的隔离时，Sql Server 2005 Database Engine 向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。事务在执行 BEGIN TRANSACTION 语句时启动。但是，事务序列号在执行 BEGIN TRANSACTION 语句后的第一次读/写操作时开始增加。事务序列号在每次分配时都增加1。当事务执行时，Sql Server根据行版本列，来提供的行的相应版本。而Sql Server将维护所有在数据库中执行的数据修改的逻辑副本（版本）。特定的事务每次修改行时，数据库引擎 实例都存储以前提交的 tempdb 中行的图像版本。每个版本都标记有进行此更改的事务的事务序列号。已修改行的版本使用链接列表链接在一起。最新的行值始终存储在当前的数据库中并链接至版本存储区 tempdb 中存储的版本。（修改大型对象 (LOB) 时，只有已更改的片段才会复制到 tempdb 中的版本存储区,  对于短期运行的事务，已修改行的版本将可能保存在缓冲池中，而不会写入 tempdb 数据库的磁盘文件中。如果只是临时需要副本行，它将只是简单地从缓冲池中删除而不会引发 I/O 开销。）”
（3）、优势
使用行版本控制的隔离级别具有以下优点：
  a、读取操作检索一致的数据库快照；
  b、select语句在读取操作过程中不锁定数据（读取器不阻塞编写器，编写器也不阻塞读取器）；
  c、select语句可以在其他事务更新行时访问最后提交的行值，而不阻塞应用程序；
  d、死锁的数量减少；
  e、事务所需的锁的数量减少，这减少了管理锁所需的系统开销；
       f、锁升级的次数减少。
（4）、行版本控制小结：
当启用了基于行版本控制的隔离级别时，数据库引擎将维护修改的每一行的版本。应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据，而不是使用锁保护所有读取。通过使用行版本控制，读取操作阻止其他事务的可能性将大大降低，也就是相当于针对所有的表在查询时都会加上nolock。虽然同样会产生脏读的现象，但差别在于我们不用每次查询都加上nolock，行版本控制策略默认的一个设置就帮我们搞定了。
BTW，既然说到了基于行版本控制的隔离级别，不得不说下隔离级别。隔离级别，怎么说呢？您别不怀好意地笑，抄书ing：
<1>、用处：控制锁的应用，即什么场景应用什么样的锁机制，解决并发处理带来的种种问题；；
<2>、分类：

  a、未提交读（UnCommitted Read）：悲观，相当于（nolock；隔离事务的最低级别，只能保证不读取物理上损坏的数据。
b、已提交读（Read Committed）：悲观，数据库引擎的缺省模式，读操作共享锁时间一直到读取结束。
c、可重复读（Repeatable Read）：悲观，读操作共享锁时间比已提交读模式更长，一直到事务结束。
d、可序列化（Serializable）：悲观，相当于（HoldLock），最严谨。
e、已提交读快照（Read Committed Snapshot）：乐观，2005新增，基于行版本控制，所有读操作不受其他锁的影响，历史数据保存更短，Temp空间更少，支持分布式。
Alter Database 数据库名称 Set Read_Committed_Snapshot On
f、快照（Snapshot）：乐观，2005新增，基于行版本控制，所有读操作不受其他锁的影响，历史数据保存更长，Temp空间更多，不支持分布式。
Alter Database 数据库名称 Set Allow_Snapshot_Isolation On

 <3>、查看当前隔离模式和行版本控制状态 （2005）

DBCC UserOptions
Select name, snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on From sys.databases

 3、小结
根据前面的分析，我们知道，Sql Server 2005控制并发访问已经有了两种有效的途径；nolock语句执行时不发出共享锁，允许脏读 ，等于READ UNCOMMITTED事务隔离级别，从这种意义上来讲，nolock确实在查询的时候能提高速度。但现在我们再来问一下自己，nolock需要加吗，不需要加吗？真的需要加吗，真的不需要加吗？？您能再肯定点回答吗？

三、nolock的适用场景（下面的几点完全是个人意见，可以54。）
1、“持久化”的表：也就是数据不会经常变动的表，比如我们熟知的省、市、县和航空公司、机场等等。它们的共同特征就是至少从目前来看，这些数据长时间不会有任何改变。其实从长远来看，甚至一个非常成熟的公司的部门表也可以作为这类数据来处理，但是和部门有关系的员工表就不可以；
2、允许脏读的一些业务逻辑：这个没什么好说的，客户需求决定了你不在这上面“较真”。比如我们要查询某个业务部门某一个季度或某一年的业绩统计，需要了解大概情况就可以了。这种情形下，查询nolock多少次都无所谓。
3、存储了海量数据的表：这个毫无疑问，数据量大，重要性越强，访问也就越多，并发操作影响到的记录也就可能越大，所谓“树大招风”，不过如此。我们给查询加上nolock可以大大提升性能和用户体验，当然，它是以牺牲数据一致性和安全性来提升性能的。
最后，通过以上分析，我们得出结论，查询（尤其是海量数据）不加锁，毫无疑问，速度确确实实是提高了，但是我们更应该有选择性的挑选最适合的表来使用nolock。因为我们已经都知道，“对数据表的并发操作”很可能造成一些查询结果的困扰，比如我们所熟知的“脏读“。设想一下吧，对于没有预期的一些查询（所谓”预期查询“，就是使用者认为前后查询结果不一致也是合理的，比如订单查询中一个订单的订单状态的变化导致前后结果不一致等等），因为”脏读“造成的”脏数据“前后查询结果不一致，一次两次也就罢了，可能使用的人以为自己眼花了还是怎么的。但是如果多次或者大数据量地出现数据不匹配，肯定会让不明所以的使用者困惑，心理素质好的会习惯性地把问题推给系统，心里素质不好的的还以为自己误操作还是怎么的，直接造成恐慌甚至怀疑自己rp。

主要示例：  SELECT  * FROM  t_BOS220000001 with(nolock)（有关键字with和没有关键字with都没有关系）nolock关键字紧跟着表名后面，  SELECT  * FROM  t_BOS220000001 (nolock)这样也可以或是  update table（nolock）set a=‘‘‘‘ where id=1

本文转载自：http://youzhangcai.blog.163.com/blog/static/1668481842010111782534757/