Posted on

SQL with(nolock)详解

大家在写查询时,为了性能,往往会在表后面加一个nolock,或者是with(nolock),其目的就是查询是不锁定表,从而达到提高查询速度的目的。

什么是并发访问：同一时间有多个用户访问同一资源,并发用户中如果有用户对资源做了修改，此时就会对其它用户产生某些不利的影响，例如：

1：脏读

一个用户对一个资源做了修改，此时另外一个用户正好读取了这条被修改的记录，然后，第一个用户放弃修改，数据回到修改之前，这两个不同的结果就是脏读。

2：不可重复读

一个用户的一个操作是一个事务，这个事务分两次读取同一条记录，如果第一次读取后，有另外用户修改了这个数据，然后第二次读取的数据正好是其它用户修改的数据，这样造成两次读取的记录不同，如果事务中锁定这条记录就可以避免。

3：幻读

指用户读取一批记录的情况，用户两次查询同一条件的一批记录，第一次查询后，有其它用户对这批数据做了修改，方法可能是修改，删除，新增，第二次查询时，会发现第一次查询的记录条目有的不在第二次查询结果中，或者是第二次查询的条目不在第一次查询的内容中。

为什么会在查询的表后面加nolock标识？为了避免并发访问产生的不利影响，SQL Server有两种并发访问的控制机制：锁、行版本控制,表后面加nolock是解决并发访问的方案之一。

1.锁

每个事务对所依赖的资源会请求不同类型的锁，它可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。当事务不再依赖锁定的资源时，锁将被释放。

锁的类型：1:表类型：锁定整个表；2:行类型：锁定某个行；3:文件类型：锁定某个数据库文件；4:数据库类型：锁定整个数据库；5:页类型：锁定8K为单位的数据库页。

锁的分类还有一种分法，就是按用户和数据库对象来分：

1). 从数据库系统的角度来看：分为独占锁（即排它锁），共享锁和更新锁

1:共享 (S) ：用于不更改或不更新数据的操作（只读操作），一般常见的例如select语句。

2:更新 (U) ：用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

3:排它 (X) ：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

2). 从程序员的角度看：分为乐观锁和悲观锁。

1:乐观锁：完全依靠数据库来管理锁的工作。

2:悲观锁：程序员自己管理数据或对象上的锁处理。

一般程序员一看到什么锁之类，觉的特别复杂，对专业的DBA当然是入门级知识了。可喜的是程序员不用去设置,控制这些锁，SQLServer通过设 置事务的隔离级别自动管理锁的设置和控制。锁管理器通过查询分析器分析待执行的sql语句，来判断语句将会访问哪些资源，进行什么操作，然后结合设定的隔 离级别自动分配管理需要用到的锁。

2.行版本控制

当启用了基于行版本控制的隔离级别时，数据库引擎 将维护修改的每一行的版本。应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据，而不是使用锁保护所有读取。通过使用行版本控制，读取操作阻 止其他事务的可能性将大大降低。也就是相当于针对所有的表在查询时都会加上nolock，同样会产生脏读的现象，但差别在于在一个统一管理的地方。说到了基于行版本控制的隔离级别，这里有必要说下隔离级别的概念。

隔离级别的用处：控制锁的应用，即什么场景应用什么样的锁机制。

最终目的：解决并发处理带来的种种问题。

隔离级别的分类：

1：未提交读,隔离事务的最低级别，只能保证不读取物理上损坏的数据;

2：已提交读,数据库引擎的默认级；

3：可重复读；

4：可序列化；隔离事务的最高级别，事务之间完全隔离。

小结：NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别 。nolock确实在查询时能提高速度，但它并不是没有缺点的，起码它会引起脏读。

nolock的使用场景(个人观点)：

1:数据量特别大的表，牺牲数据安全性来提升性能是可以考虑的；

2:允许出现脏读现象的业务逻辑，反之一些数据完整性要求比较严格的场景就不合适了，像金融方面等。

3:数据不经常修改的表，这样会省于锁定表的时间来大大加快查询速度。

最后说下nolock和with(nolock)的几个小区别：

1:SQL05中的同义词，只支持with(nolock);

2:with(nolock)的写法非常容易再指定索引。

跨服务器查询语句时 不能用with (nolock) 只能用nolock,同一个服务器查询时 则with (nolock)和nolock都可以用

Posted on

SQL Server中的Transaction、error check、Lock、Isolation level、save point

Transaction及错误检查

SQL Server 中最重要的知识点莫过于事务，比如很多OLTP（联机事务处理）应用程序。什么是事务?事务就是一系列SQL语句的集合。事务包括隐性事务（例如Insert，Update等语句）和显性事务（用Begin Tran语句显式指明的事务）。事务中通常需要进行错误检查，用@@error来进行检查，比如：

Begin Tran
Update A set id =5 where id=1
If @@error<>0
rollback Tran
Update A set id =5 where id=2
If @@error<>0
rollback Tran
Commit Tran

Lock (锁)

按锁的粒度分，锁可以分成如下几类：

Key Lock(键锁)—>Row Lock(行级锁)—>Page Lock(页级锁)—>Extent Lock(扩展盘曲锁)—>Table Lock(表锁)—>Database Lock(数据库锁)

按锁的模式分，锁可以分为如下几类：

holdlock(共享锁)，xlock(排它锁)，Updlock(更新锁)，Schlock(架构锁)，Intent lock(意向锁)等等。如果要查看锁的类型，使用系统存储过程sys_lock来查看。

Isolation level (隔离级别)

事务有4中隔离级别，分别为：
read uncommitted(未提交读) — 读未提交，可以读取到内存中已经修改但是没有保存到硬盘上的信息，即允许数据脏读。
read committed(提交读) — 读提交，只能读取到已经提交到硬盘的信息，如果信息在内存中修改了，但是还没有提交到硬盘，即没有commit tran，则另一个事务什么也读取不到，被另一事物阻塞在此。当修改数据的事务一旦commit tran，则读取数据的事务立即运行，修改后的数据被读取到。
repeatable read(重复读) — 当事务A设置隔离级别为repeatable read在对数据进行读取，此时，事务B来修改数据，由于repeatable read隔离级别对操作的实体(行或者表)设置了更新锁，所以此时事务B不能对数据进行更新，但是事务B可以insert新数据，因为repeatable read隔离级别对操作的实体(行或者表)没有设置排它锁，所以事务A可以读取到幻象。
serializable(串行读) — 串行化，即事务一个接一个地进行操作，包括对操作实体的update，insert，select等等。

实际上隔离级别和锁的关系是密不可分的，隔离级别的实现本质上是对锁来进行操作，由于我们在操作一个实体对象的时候不能准确地判断到底应该上什么具体的锁 ，所以鉴于此，SQL server数据库为我们开辟了一个简单的途径，即使用隔离级别。实体的隔离级别越高，说明实体上锁的数量越多，种类越复杂；实体的隔离级别越高，并行化的几率越低，串行化的几率越高。

Save Point(保存点)

保存点的出现，是为了在事务恢复时更加地迅速和容易，因为不用把所有的操作都恢复，而是只用恢复到保存点即可，关于如何恢复以及更具体的知识，会在事务的恢复博客中详述。举个简单的例子：

Begin Tran
Update A set id =4 where id=1
Save tran t1
Update A set id =3 where id=2
If @@error<>0
rollback t1
Update A set id=5 where id =3
Commit Tran