sqlserver 锁

【对锁机制的研究要具备两个条件：】----------------------------------------
1．数据量大
2．多个用户同时并发
如果缺少这两个条件，数据库不容易产生死锁问题。
如果具备这两个条件，则数据库使用中可能存在以下问题：
1）丢失更新
  两个用户同时对数据进行修改,其中一个用户修改提交后，另一个用户再提交，结果破坏了另一个修改结果。
2）脏读
  A用户修改了数据时,B用户也在读该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致
3）不可重复读
  A用户读取数据，同时B用户读取数据并进行修改，A用户再次读取数据。此时A用户读取的数据前后不一致。


【锁的功能说明：】-------------------------------------------------------

NOLOCK（不加锁）
  不加任何锁。在这种情况下，用户有可能读取“脏数据”。【等于 READ UNCOMMITTED事务隔离级别】

HOLDLOCK（保持锁）
  此选项被选中时，可查询,SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。【等于SERIALIZABLE事务隔离级别】
  
UPDLOCK（修改锁）
  强制在读表时使用更新而不用共享锁。此选项被选中时，SQL Server 在读取数据时使用修改锁来代替[共享锁]，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

TABLOCK（表锁） 
  此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。 
  
PAGLOCK（页锁）
  此选项为默认选项，当被选中时，SQL Server 使用[共享页锁]。在使用一个表锁的地方用多个页锁 
  
TABLOCKX（排它表锁）
  此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。强制使用独占表级锁,这将防止其他进程读取或修改表中的数据。 

ROWLOCK （行锁）
  强制使用行锁



【锁模式描述：】-------------------------------------------------------

共享(S)用于不更改或不更新数据的操作（只读操作），如SELECT语句。
更新(U)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它(X)用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时同一资源进行多重更新。

意向锁用于建立锁的层次结构。意向锁的类型为：
  意向共享(IS)
  意向排它(IX)
  意向排它共享(SIX)

架构锁在执行依赖于表架构的操作时使用。架构锁的类型为：
  架构修改(Sch-M)
  架构稳定性(Sch-S)

大容量更新(BU)向表中大容量复制数据并指定了TABLOCK提示时使用。



◆共享锁
　　共享(S)锁允许并发事务读取(SELECT)一个资源。资源上存在共享(S)锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享(S)锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享(S)锁。

◆更新锁
　　更新(U)锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享(S)锁，然后修改行，此操作要求锁转换为排它(X)锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它(X)锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它(X)锁以进行更新。由于两个事务都要转换为排它(X)锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
  简单地讲：就是两个事务都可以同时获得某对象上的共享锁，如果两个事务都试图修改该对象，更新或删除操作等必定要使用排它锁，因此两个事务都试图升级为排它锁，但是对方都占有共享锁，所以互相等待，不能升级，成为死锁。
  若要避免这种潜在的死锁问题，请使用更新(U)锁。一次只有一个事务可以获得资源的更新(U)锁。如果事务修改资源，则更新(U)锁转换为排它(X)锁。否则，锁转换为共享锁。

◆排它锁
　　排它(X)锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它(X)锁锁定的数据。

◆意向锁（意向共享(IS)、意向排它(IX)，意向排它共享(SIX)）：
  意向锁表示SQLServer需要在层次结构中的某些底层资源上获取共享(S)锁或排它(X)锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享(S)锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它(X)锁。意向锁可以提高性能，因为SQLServer仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享(IS)、意向排它(IX)以及与意向排它共享(SIX)：
  意向共享(IS)通过在各资源上放置S锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。

　　意向排它(IX)通过在各资源上放置X锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX是IS的超集。

　　与意向排它共享(SIX)通过在各资源上放置IX锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发IS锁。例如，表的SIX锁在表上放置一个SIX锁（允许并发IS锁），在当前所修改页上放置IX锁（在已修改行上放置X锁）。虽然每个资源在一段时间内只能有一个SIX锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的IS锁来读取层次结构中的底层资源。



【锁相关设置：】-------------------------------------------------------
1 处理死锁和设置死锁优先级：
  使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。
  SET DEADLOCK_PRIORITY { LOW | NORMAL}
  其中LOW说明该进程会话的优先级较低，在出现死锁时，可以首先中断该进程的事务。

2 处理超时和设置锁超时持续时间：
  @@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒
  SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息
  示例：
  1）将锁超时期限设置为 1,800 毫秒。
    SET LOCK_TIMEOUT 1800
  2) 配置索引的锁定粒度
    可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度
    参考：http://msdn.microsoft.com/zh-cn/library/aa238800.aspx
  3）设置事务隔离级别
    SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
    参考：http://msdn.microsoft.com/zh-cn/library/ms173763.aspx



【锁相关测试：】-------------------------------------------------------

1 查看锁相关信息：
SELECT request_session_id sessionid,  
resource_type type,  
resource_database_id dbid,  
--OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,  
request_mode rmode,  
request_type rtype,
request_status rstatus
FROM sys.dm_tran_locks  

exec sp_lock

--按Ctrl+2



--解决办法，杀掉会话
--kill request_session_id
--kill 56



2 加锁测试（AdventureWorks）：

a.新建一个查询会话，执行以下语句：
 BEGIN TRAN  
  SELECT * FROM Person.Contact WITH (UPDLOCK) 
  --SELECT * FROM Person.Contact ROWLOCK WHERE ContactID=1
  WAITFOR DELAY '00:00:30'
 ROLLBACK
--COMMIT

b.再新建一个查询会话，执行以下语句：
SELECT * FROM Person.Contact
update Person.Contact set FirstName='Gustavo' where ContactID=1

c.可再建一个查询会话,在30秒内查看运行dm_tran_locks,exec sp_lock查看相关锁信息,每次可换锁的模式[UPDLOCK]测试查看




【怎样避免死锁：】-------------------------------------------------------
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 所有的SP都要有错误处理（通过@error）
4 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
5 优化程序，检查并避免死锁现象出现；
 1）合理安排表访问顺序
 2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。
 3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。
 4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务
 5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。
 6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。
 7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
 8）考虑使用乐观锁定或使事务首先获得一个独占锁定。

为应用程序资源加锁,释放锁
sp_getapplock http://msdn.microsoft.com/zh-cn/library/ms189823.aspx
sp_releaseapplock  http://msdn.microsoft.com/zh-cn/library/ms178602.aspx

小总结：