知方可补不足~Sqlserver中的几把锁和.net中的事务级别

回到目录

当数据表被事务锁定后，我们再进行select查询时，需要为with(锁选项)来查询信息，如果不加，select将会被阻塞，直到锁被释放，下面介绍几种ＳＱＬ的锁选项

SQL的几把锁

NOLOCK（不加锁）

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。 

HOLDLOCK（保持锁）

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。 

UPDLOCK（修改锁）

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。 

TABLOCK（表锁）

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。 

PAGLOCK（页锁）

此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。 

TABLOCKX（排它表锁）

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

下面看一下.net frameworks平台关于事务级别的枚举，它对应于sql的事件级别

namespace System.Transactions
{
    // 摘要:
    //     Specifies the isolation level of a transaction.
    public enum IsolationLevel
    {
        // 摘要:序列化隔离级别，约束力最高，在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。
　　　　 // 这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。
        //     Volatile data can be read but not modified, and no new data can be added
        //     during the transaction.
        Serializable = 0,
        //
        // 摘要:可重复读的隔离级别，可能出现幻读，锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，
        // 且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。
        //     Volatile data can be read but not modified during the transaction.New data
        //     can be added during the transaction.
        RepeatableRead = 1,
        //
        // 摘要:不能读但可修改，可能出现不可重复读,指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，
        // 从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。
        //     Volatile data cannot be read during the transaction, but can be modified.
        ReadCommitted = 2,
        //
        // 摘要:可以读也可以修改，可能出现脏数据,执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。
        // 当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。这是四个隔离级别中限制最小的级别。
        //     Volatile data can be read and modified during the transaction.
        ReadUncommitted = 3,
        //
        // 摘要:忽略数据的修改,得到修改前的数据
        //     Volatile data can be read.Before a transaction modifies data, it verifies
        //     if another transaction has changed the data after it was initially read.If
        //     the data has been updated, an error is raised.This allows a transaction to
        //     get to the previously committed value of the data.
        Snapshot = 4,
        //
        // 摘要:
        //     The pending changes from more highly isolated transactions cannot be overwritten.
        Chaos = 5,
        //
        // 摘要:
        //     A different isolation level than the one specified is being used, but the
        //     level cannot be determined.An exception is thrown if this value is set.
        Unspecified = 6,
    }
}

对于事务级别的总结

下面是在嵌套事务中经常遇到的情况

　　１　 脏读：一个事务会读进还没有被另一个事务提交的数据，所以你会看到一些最后被另一个事务回滚掉的数据。

　　２　 读值不可复现：一个事务读进一条记录，另一个事务更改了这条记录并提交完毕，这时候第一个事务再次读这条记录时，它已经改变了。

　　３ 　幻影读：一个事务用select子句来检索一个表的数据，另一个事务insert一条新的记录，并且符合select条件，这样，第一个事务用同一个select条件来检索数据后，就会多出一条记录。

下面是IsolationLevel级别在使用过程中的一些说明（来自博文：http://www.cnblogs.com/CN5135/archive/2011/10/24/2222350.html）

ReadCommitted：
假设A事务对正在读取数据Data放置了共享锁，那么Data不能被其它事务改写，所以当B事务对Data进行读取时总和A读取的Data数据是一致的，所以避免了脏读。由于在A没有提交之前可以对Data进行改写，那么B读取到的某个值可能会在其读取后被A更改从而导致了该值不能被重复取得；或者当B再次用相同的where字句时得到了和前一次不一样数据的结果集，也就是幻像数据。

ReadUncommitted：
假设A事务即不发布共享锁，也不接受独占锁，那么并发的B或者其它事务可以改写A事务读取的数据，那么并发的C事务读取到的数据的状态和A的或者B的数据都可能不一致，那么。脏读、不可重复读、幻象数据都可能存在。

RepeatableRead：
（注意MSDN原文中的第一句话：在查询中使用的所有数据上放置锁，所以不存在脏读的情况）。
假设A事务对读取的所有数据Data放置了锁，以阻止其它事务对Data的更改，在A没有提交之前，新的并发事务读取到的数据如果存在于Data中，那么该数据的状态和A事务中的数据是一致的，从而避免了不可重复的读取。但在A事务没有结束之前，B事务可以插入新记录到Data所在的表中，那么其它事务再次用相同的where字句查询时，得到的结果数可能上一次的不一致，也就是幻像数据。

Serializable：
 在数据表上放置了排他锁，以防止在事务完成之前由其他用户更新行或向数据集中插入行，这是最严格的锁。它防止了脏读、不可重复读取和幻象数据。

它的对应表如下：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读已提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

回到目录