事务的隔离性

事务具有隔离性，不同事务中所使用的时间必须要和其他事务进行隔离，在同一时间可以有很多事务正在处理数据，但是每个数据在同一时刻只能有一个事务进行操作。如果将数据锁定，使用数据的事务就必须要排队等待，可以防止多个事务相互影响。但是如果有几个事务因为锁定了自己的数据，同时又在等待其他事务释放数据，造成死锁。

注：

事务具有原子性、一致性、隔离性、持续性四个属性，缩写字母为ACID。

事务的隔离级别

1. read uncommitted (未提交读,读脏),相当于(nolock)
2. read committed (已提交读,默认级别)
3. repeatable read (可以重复读),相当于(holdlock)
4. serializable(可序列化)
5. snapshot(快照)
6. read committed snapshot(已经提交读隔离)

对于前四个隔离级别：read uncommitted<read committed<repeatable read<serializable

隔离级别越高,读操作的请求锁定就越严格,锁的持有时间就越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

锁

多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制。它主要用于多用户环境下保证数据库完整性和一致性。

死锁

在两个或者多个任务中，如果每个任务锁定了其他任务试图锁定的资源，会造成这些任务永久阻塞，从而出现死锁。此时系统处于死锁状态。

形成死锁的4个必要条件：

• 请求与保持条件：获取资源的进程可以同时申请新的资源
• 非剥夺条件：已经分配的资源不能从该进程中剥夺。
• 循环等待条件：多个进程构成环路，并且其中每个进程都在等待相邻进程正占用的资源。
• 互斥条件：资源只能被一个进程使用。

可能造成死锁的资源

每个用户会话可能有一个或多个代表他运行的任务，其中每个任务可能获取或者等待获取各种资源。

1.锁

等待获取资源（如对象、页、行、元数据和应用程序）的锁可能导致死锁。

2.工作线程

排队等待可用于工作线程的任务可能导致死锁。如果排队等待的任务拥有阻塞所有工作线程的资源，则将导致死锁。

3.内存

当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。

4.并行查询执行的相关资源。

通常与交换端口关联的处理协调器、发生器或使用者线程至少包含一个不属于并行查询的进程时，可能会相互阻塞，从而导致死锁。此外，当并行查询启动执行时，SQL Server将根据当前的工作负荷确定并行度或工作线程数。

减少死锁的策略

1. 按同一顺序访问对象。所有事务中以相同的次序使用资源。
2. 保持事务简短并且在一个批处理中。
3. 为死锁超时参数设置一个合理范围。如3-10分钟；超时，则自动放弃本次操作。
4. 避免在事务中用户交互。减少资源的锁定时间。
5. 使用较低的隔离级别。相比较高的隔离级别能够有效减少持有共享锁的时间，减少锁之间的竞争。
6. 使用绑定连接(Bound Connections)。 Bound Connections 允许两个或者多个事务连接共享事务和锁，而且任何一个事务连接都要申请锁如同另一个事务要申请锁一样，因此可以运行这些事务共享数据而不会有加锁冲突。
7. 使用基于行版本控制的隔离级别。持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生死锁的几率降至最低。

锁管理器

解决不同用户进程间锁冲突的职责落到了SQL Server Lock Manager身上。SQL Server 自动地给进程分配锁，以保证资源的当前用户拥有该资源的一致视图，从某个特定操作的开始至结束。

1. Lock Manager 负责决定适当的锁类型（如shared, exclusive, update）和锁粒度（如row, page,table），根据正在执行的操作类型和所影响的数据量。
2. Lock Manager还管理试图访问同一资源的锁类型之间的兼容性，解决死锁，必要时升级锁到一个更高的级别。
3. Lock Manager 为共享数据和内部系统资源管理锁。对于共享数据，Lock Manager 管理表以及数据页、文本页、叶级索引页上的行级锁、页级锁和表级锁。内部地，Lock Manager使用门闩（latch）来管理索引行和页上的锁控制对内部数据结构的访问，以及在某些情况下，用于取回单个的数据行。门闩提供了更好的系统性能，因为它不像锁那般资源密集。门闩也提供了比锁更好的并发性。门闩典型地用于像页拆分、索引行的删除、索引中行的移动等操作。锁与门闩之间最主要的区别在于，锁在整个事务存续期间都被持有，而门闩仅在需要它的操作存续期间被持有。锁用于保证数据的逻辑一致性，而门闩用于保证数据和数据结构的物理一致性。

可锁定资源

1.锁粒度

锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小。

2.12种可锁定的资源（可对应粒度）

1． holdlock: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

2． nolock：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

3． paglock：指定添加页锁（否则通常可能添加表锁）。

4． readcommitted用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。

5． readpast: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，readpast仅仅应用readcommitted隔离性级别下事务操作中的select语句操作。

6．readuncommitted：等同于nolock。

7． repeatableread：设置事务为可重复读隔离性级别。

8． rowlock：使用行级锁，而不使用粒度更粗的页级锁和表级锁。

9． serializable：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 holdlock。

10． tablock：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了holdlock，该锁一直保持到这个事务结束。

11． tablockx：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。

12． uplock ：指定在读表中数据时设置更新 锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用uplock的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。

3.锁定时间的长短

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。用于保护读取操作的共享锁的保持时间取决于事务隔离级别。

SQL Server 提供的锁级别

• Database-- 无论何时当一个SQL Server 进程正在使用除master以外的数据库时，Lock Manager为该进程授予数据库级的锁。数据库级的锁总是共享锁，用于跟踪何时数据库在使用中，以防其他进程删除该数据库，将数据库置为脱机，或者恢复数据库。注意，由于master和tempdb数据库不能被删除或置为脱机，所以不需要在它们之上加锁。
• File-- 文件级的锁用于锁定数据库文件。
• Extent -- Extent锁用于锁定extents,通常仅在空间分配和重新分配的时候使用。一个extent由8个连续的数据页或索引页组成。Extent锁可以是共享锁也可以是独占锁。
• Allocation_unit-- 使用在数据库分配单元上。
• Table -- 这种级别的锁将锁定整个表，包括数据和索引。何时将获得表级锁的例子包括在Serializable隔离级别下从包含大量数据的表中选取所有的行，以及在表上执行不带过滤条件的update或delete。
• Heap or B-Tree (HOBT) -- 用于堆数据页，或者索引的二叉树结构。
• Page -- 使用页级锁，由8KB数据或者索引信息组成的整个页被锁定。当需要读取一页的所有行或者需要执行页级别的维护如页拆分后更新页指针时，将会获取页级锁。
• Row ID (RID) -- 使用RID锁，页内的单一行被锁定。无论何时当提供最大化的资源并发性访问是有效并且可能时，将获得RID锁。
• Key -- SQL Server使用两种类型的Key锁。其中一个的使用取决于当前会话的锁隔离级别。对于运行于Read Committed 或者 Repeatable Read 隔离模式下的事务，SQL Server 锁定与被访问的行相关联的的实际索引key。（如果是表的聚集索引，数据行位于索引的叶级。行上在这些你看到的是Key锁而不是行级锁）若在Serializable隔离模式下，通过锁定一定范围的key值从而不允许新的行插入到该范围内，SQL Server防止了“幻读”。这些锁因而被称作“key-range lock”。
• Metadata -- 用于锁定系统目录信息（元数据）。
• Application -- 允许用户定义他们自己的锁，指定资源名称、锁模式、所有者、timeout间隔。

锁的分类

锁模式决定了并发事务访问资源的方式。锁在SQL Server中是自动处理的。Lock Manager 基于事务类型（如select,insert,update或 delete）选择锁的类型.

1. 更新锁：更新锁用于锁定用户进程想要修改的行或页。当一个事务试图修改某行时，它必须先读取该行以确保它正在修改合适的记录。假如事务先在资源上加了共享锁，要修改该记录，最终它将需要获取该资源上的独占锁，以防止任何其他事务修改同一记录。问题是，当多个事务试图同时修改同一资源的时候这可能导致死锁
2. 排他锁：用于数据修改操作，例如insert、update、或者delete。确保不会同时对同一资源进行多重更新。
3. 共享锁：用于读取数据操作，允许多个事务读取相同的数据，单不允许其他事务修改当前数据，如select语句。
4. 键范围锁：可防止幻读。通过保护行之间键的范围，还可以防止对事务访问的记录集进行幻想插入或者删除。
5. 架构锁：执行表的数据定义操作时使用架构修改锁 ，在架构修改锁起作用的期间，会防止对表的并发访问。
6. 大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。
7. 意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。

程序员的角度：乐观锁和悲观锁。

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

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

MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制。

处理死锁和设置死锁优先级

死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待。

可以使用set deadlock_priority控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

处理超时和设置锁超时持续时间。

@@lock_timeout 返回当前会话的当前锁超时设置，单位为毫秒

set lock_timeout 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于lock_timeout设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息。

实例：

将锁超时期限设置为 1,800 毫秒。

set lock_timeout 1800

设置事务隔离级别。

对 select,insert,update或 delete 语句使用表级锁定提示。

配置索引的锁定粒度

可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度.

查看锁的信息

1 执行 exec sp_lock 报告有关锁的信息

2 查询分析器中按Ctrl+2可以看到锁的信息

本文部分内容参考至网络，如有错误，敬请指正，如有侵权，请联系修改。