事务与锁

 

什么是锁

SQL Server 2000使用锁来实现多用户同时修改数据库同一数据时的同步控制。

死锁

多个会话同时访问数据库一些资源时，当每个会话都需要别的会话正在使用的资源时，死锁就有可能发生。死锁在多线程系统中都有可能出现，并不仅仅局限于于关系数据库管理系统。

锁的类型

一个数据库系统在许多情况下都有可能锁数据项。其可能性包括：

·   Rows—数据库表中的一整行

·   Pages—行的集合（通常为几kb）

·   Extents—通常是几个页的集合

·   Table—整个数据库表

·   Database—被锁的整个数据库表

除非有其它的说明，数据库根据情况自己选择最好的锁方式。不过值得感谢的是，SQL Server提供了一种避免默认行为的方法。这是由锁提示来完成的。

锁提示

Tansact－SQL提供了一系列不同级别的锁提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它们来告诉SQL Server你需要如何通过重设锁。可以实现的提示包括：

·  FASTFIRSTROW—选取结果集中的第一行，并将其优化

·  HOLDLOCK—持有一个共享锁直至事务完成

·  NOLOCK—不允许使用共享锁或独享锁。这可能会造成数据重写或者没有被确认就返回的情况；因此，就有可能使用到脏数据。这个提示只能在SELECT中使用。

·  PAGLOCK—锁表格

·  READCOMMITTED—只读取被事务确认的数据。这就是SQL Server的默认行为。

·  READPAST—跳过被其它进程锁住的行，所以返回的数据可能会忽略行的内容。这也只能在SELECT中使用。

·  READUNCOMMITTED—等价于NOLOCK.

·  REPEATABLEREAD—在查询语句中，对所有数据使用锁。这可以防止其它的用户更新数据， 但是新的行可能被其它的用户插入到数据中，并且被最新访问该数据的用户读取。

·  ROWLOCK—按照行的级别来对数据上锁。SQL Server通常锁到页或者表级别来修改行， 所以当开发者使用单行的时候，通常要重设这个设置。

·  SERIALIZABLE—等价于HOLDLOCK.

·  TABLOCK—按照表级别上锁。在运行多个有关表级别数据操作的时候，你可能需要使用到这个提示。

·  UPDLOCK—当读取一个表的时候，使用更新锁来代替共享锁，并且保持一直拥有这个锁直至事务结束。它的好处是，可以允许你在阅读数据的时候可以不需要锁，并且以最快的速度更新数据。

·  XLOCK—给所有的资源都上独享锁，直至事务结束。

 

 微软将提示分为两类：granularity和isolation-level。

Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。

而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。

 

·       可以在Transact-SQL声明中使用这些提示。它们被放在声明的FROM部分中，位于WITH之后。WITH声明在SQL Server 2000中是可选部分，但是微软强烈要求将它包含在内。这就使得许多人都认为在未来的SQL Server发行版中，就可能会包含这个声明。下面是提示应用于FROM从句中的例子：

[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
  < table_hint > ::=
  { INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW
  | HOLDLOCK
  | NOLOCK
  | PAGLOCK
  | READCOMMITTED
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK }


锁模式：
         共享锁(S)：用于只读操作(SELECT)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。
         排他(独占)锁(X): 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。
         更新锁(U): 当更新事务数据需要获取排它锁时，必须先获取更新琐。引擎为防止死锁的发生，一次允许允许一个事务可以获得资源的更新锁（U 锁），只有获取更新锁的查询才可创建排它锁。
         意向锁: 用于建立锁的层次结构。意向锁的类型有：意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。引擎在创建共享锁和独占锁之前，先使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上
         架构锁(Sch-M)：执行表的数据定义语言 (DDL) 操作时使用架构锁。在架构修改锁起作用的期间，该锁之外的所有操作都将被阻止.
        大容量更新锁(BU 锁):当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新锁。大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。

监视与管理锁

         使用 SQL Server Profiler监视Locks 事件，来捕获有关跟踪中锁事件的信息的锁事件类别。
         使用系统监视器监视SQL Server Locks 对象，监视数据库引擎实例中的锁级别。
         查询 sys.dm_tran_locks 动态管理视图获得有关数据库引擎实例中锁当前状态的信息。
         使用系统存储过程sp_lock (Transact-SQL) 返回有关数据库引擎实例中的活动锁的信息。对于 SQL Server 2005，请改用 sys.dm_tran_locks 动态管理视图。
         使用系统视图sys.syslockinfo (Transact-SQL)返回有关数据库引擎实例中的活动锁的信息。对于 SQL Server 2005，请改用 sys.dm_tran_locks 动态管理视图。

将死锁减至最少

         按同一顺序访问对象。
         避免事务中的用户交互。
         保持事务简短并处于一个批处理中。
         使用较低的隔离级别。
         使用基于行版本控制的隔离级别。
          将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。
          使用快照隔离。
          使用绑定连接。

 

 

事务隔离

SQL 标准用三个必须在并行的事务之间避免的现象定义了四个级别的事务隔离。 这些不希望发生的现象是：

脏读（dirty reads）

一个事务读取了另一个未提交的并行事务写的数据。

不可重复读（non-repeatable reads）

一个事务重新读取前面读取过的数据， 发现该数据已经被另一个已提交的事务修改过。

幻读（phantom read）

一个事务重新执行一个查询，返回一套符合查询条件的行， 发现这些行因为其他最近提交的事务而发生了改变。

这四种隔离级别和对应的行为在Table 12-1里描述。

Table 12-1. SQL 事务隔离级别

隔离级别

脏读（Dirty Read）

不可重复读（NonRepeatable Read）

幻读（Phantom Read）

读未提交（Read uncommitted）

可能

可能

可能

读已提交（Read committed）

不可能

可能

可能

可重复读（Repeatable read）

不可能

不可能

可能

可串行化（Serializable ）

不可能

不可能

不可能

在 PostgreSQL 里，你可以请求四种可能的事务隔离级别中的任意一种。 但是在内部，实际上只有两种独立的隔离级别，分别对应读已提交和可串行化。如果你选择了读未提交的级别，实际上你用的是读已提交， 在你选择可重复的读级别的时候，实际上你用的是可串行化，所以实际的隔离级别可能比你选择的更严格。 这是 SQL 标准允许的：四种隔离级别只定义了哪种现象不能发生，但是没有定义那种现象一定发生。 PostgreSQL 只提供两种隔离级别的原因是， 这是把标准的隔离级别与多版本并发控制架构映射相关的唯一的合理方法。 可用的隔离级别的行为在下面小节里描述。

12.2.1. 读已提交隔离级别

读已提交（Read Committed） 是 PostgreSQL 里的缺省隔离级别。 当一个事务运行在这个隔离级别时， 一个 SELECT 查询只能看到查询开始之前提交的数据而永远无法看到未提交的数据或者是在查询执行时其他并行的事务提交做的改变。 （不过 SELECT 的确看得见同一次事务中前面更新的结果。即使它们还没提交也看得到。） 实际上，一个 SELECT 查询看到一个在该查询开始运行的瞬间该数据库的一个快照。 请注意两个相邻的 SELECT 命令可能看到不同的数据，哪怕它们是在同一个事务里， 因为其它事务会在第一个SELECT执行的时候提交．

UPDATE， DELETE， 或者 SELECT FOR UPDATE 在搜索目标行的时候的行为和SELECT 一样： 它们只能找到在命令开始的时候已经提交的行。不过，这样的目标行在被找到的时候可能已经被其它并发的事务更新（或者删除，或者标记为更新的）。 在这种情况下，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在处理）。如果第一个更新回滚，那么它的作用将被忽略，而第二个更新者将继续更新最初发现的行。 如果第一个更新者提交，那么如果第一个更新者删除了该行，则第二个更新者将忽略该行，否则它将试图在该行的已更新的版本上施加它的操作。系统将重新计算命令搜索条件（WHERE 子句）， 看看该行已更新的办不那是否仍然符合搜索条件。如果是，则第二个更新继续其操作，从该行的已更新版本开始。

因为上面的规则，正在更新的命令可能会看到不一致的快照 — 它们可以看到影响它们试图更新的并发更新命令的效果， 但是它们看不到那些命令对数据库里其它行的作用。 这样的行为令读已提交模式不适合用于哪种涉及复杂搜索条件的命令。不过，它对于简单的情况而言是正确的。比如，假设我们用类似下面这样的命令更新银行余额：

BEGIN;

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;

COMMIT;

如果两个并发事务试图修改帐号 12345 的余额，那我们很明显希望第二个事务是从帐户行的已经更新过的版本上进行更新。 因为每个命令只是影响一个已经决定了的行，因此让它看到更新后的版本不会导致任何不一致的问题。

因为在读已提交模式里，每个新的命令都是从一个新的快照开始的，而这个快照包含所有到该时刻为止已经提交的事务，因此同一个事务里的后面的命令将看到任何已提交的并发事务的效果。 这里要考虑的问题是我们在一个命令里是否看到数据库里绝对一致的视图。

读已提交模式提供的部分事务隔离对于许多应用而言是足够的，并且这个模式速度快，使用简单。不过，对于做复杂查询和更新的应用，可能需要保证数据库有比读已提交模式提供的更加严格的一致性视图。

12.2.2. 可串行化隔离级别

可串行化（Serializable） 级别提供最严格的事务隔离。 这个级别模拟串行的事务执行，就好象事务将被一个接着一个那样串行的，而不是并行的执行。 不过，使用这个级别的应用必须准备在串行化失败的时候重新发动事务．

当一个事务处于可串行化级别， 一个 SELECT 查询只能看到在事务开始之前提交的数据而永远看不到未提交的数据或事务执行中其他并行事务提交的修改。 （不过，SELECT 的确看得到同一次事务中前面的更新的效果。即使事务还没有提交也一样。） 这个行为和读已提交级别是不太一样，它的 SELECT 看到的是该事务开始时的快照，而不是该事务内部当前查询开始时的快照。 这样，一个事务内部后面的 SELECT 命令总是看到同样的数据。

UPDATE， DELETE，和 SELECT FOR UPDATE 在搜索目标行上的行为和 SELECT 一样： 它们将只寻找在事务开始的时候已经提交的目标行。但是，这样的目标行在被发现的时候可能已经被另外一个并发的事务更新了（或者是删除或者是标记为更新）。 在这种情况下，可串行化的事务将等待第一个正在更新的事务提交或者回滚（如果它仍然在处理中）。如果第一个更新者回滚，那么它的影响将被忽略， 而这个可串行化的就可以继续更新它最初发现的行。 但是如果第一个更新者提交了（并且实际上更新或者删除了该行，而不只是为更新选中它）那么可串行化事务将回滚，并返回下面信息

ERROR: Can't serialize access due to concurrent update

因为一个可串行化的事务在可串行化事务开始之后不能更改被其他事务更改过的行。

当应用收到这样的错误信息时，它应该退出当前的事务然后从头开始重新进行整个事务。第二次运行时，该事务看到的前一次提交的修改是该数据库初始的样子中的一部分， 所以把新版本的行作为新事务更新的起点不会有逻辑冲突。

请注意只有更新事务才需要重试，只读事务从来没有串行化冲突．

可串行化事务级别提供了严格的保证：每个事务都看到一个完全一致的数据库的视图。不过，如果并行更新令数据库不能维持串行执行的样子，那么应用必须准备重试事务。 因为重做复杂的事务的开销可能是非常可观的，所以我们只建议在更新命令中包含足够复杂的逻辑，在读已提交级别中可能导致错误的结果的情况下才使用。 最常见的是，可串行化模式只是在这样的情况下是必要的：一个事务连续做若干个命令， 而这几个命令必须看到数据库完全一样的视图。

12.2.2.1. 可串行化隔离与真正的可串行化之比较

执行的"可串行化"的直观含义（以及数学定义）是两个成功提交的并发事务将显得好像严格地串行执行一样，一个跟着一个 — 尽管我们可能无法预期哪个首先执行。我们必须明白，禁止那些在 Table 12-1里面列出的行为并不能保证真正的可串行化， 并且，实际上 PostgreSQL 的可串行化模式并不保证在这种含义下的可串行化。 举例来说，假设一个表 mytab，最初包含

 class | value

-------+-------

     1 |    10

     1 |    20

     2 |   100

     2 |   200

假设可串行化事务 A 计算

SELECT SUM(value) FROM mytab WHERE class = 1;

然后把结果（30）作为 value 到表中，class = 2。 同时，一个并发的可串行化的事务 B 进行下面计算

SELECT SUM(value) FROM mytab WHERE class = 2;

并且获取结果 300，然后它插入一行新行，class = 1。 然后两个事务都提交。所有列出的禁止行为都不会发生，但是我们拿到的结果是不可能在任何一种串行执行下看到的。如果 A 在 B 之前执行，B 应该计算出总和 330，而不是 300，如果是另外一种顺序，那么 A 计算出的综合也会不同。

为了保证真正数学上的可串行化，一个数据库系统必须强制谓词锁定，这就意味着一个事务不能插入或者更改这样的数据行：这个数据行的数据匹配另外一个并发事务的 WHERE 条件。 比如，一旦事务 A 执行了查询 SELECT ... WHERE class = 1，那么一个谓词锁定系统将禁止事务 B 插入任何 class 为 1 的新行，直到 A 提交。 [1]这样的锁系统实现起来非常复杂，并且执行起来代价高昂， 因为每个会话都必须要知道每个并发事务的每个查询的执行细节。并且这样大量的开销在大部分情况下都是浪费掉的， 因为在实际情况下大部分应用都不做会导致问题的这种事情。 （当然，上面的例子是静心设计的，不能代表真实的软件。）因此，PostgreSQL 并未实现谓词锁定， 而就我们所知，没有其它的生产中的 DBMS 实现了这个。

在那些非串行化执行真的可能有危险的场合，可以通过使用明确的锁定来避免问题的法上。更多的讨论在下面的小节进行。

 

 

UPDATE   表中可用的锁为:  
   
  FASTFIRSTROW  
  等同于   OPTION   (FAST   1)。  
   
  HOLDLOCK  
  等同于   SERIALIZABLE。  
   
  PAGLOCK  
  在通常采取单个共享表锁的地方采取共享页锁。  
   
  READCOMMITTED  
  指定用与运行在   READ   COMMITTED   隔离级别的事务相同的锁定语义执行扫描。  
   
  REPEATABLEREAD  
  指定用与运行在   REPEATABLE   READ   隔离级别的事务相同的锁定语义执行扫描。  
   
  ROWLOCK  
  指定在通常采取单个共享页或表锁的情形下采取共享行锁。  
   
  SERIALIZABLE  
  等同于   HOLDLOCK。通过将共享锁保持到事务完成，使之更具有约束性（而不要不管事务是否完成，都在不再需要所需的表或数据页时就立即释放共享锁）。用与运行在   SERIALIZABLE   隔离级别的事务相同的语义执行扫描。  
   
  TABLOCK  
  指定在表上采取的共享锁一直保持到语句结束。如果同时指定   HOLDLOCK，那么在事务结束之前该共享表锁将一直保持。  
   
  TABLOCKX  
  指定在表上采取的排它锁一直保持到语句结束或事务结束。  
   
  UPDLOCK  
  指定读取表时采取更新锁而不是共享锁，且将这些更新锁保持到语句结束或事务结束。  
   
  XLOCK  
  指定应采取排它锁，且保持到由语句所处理的全部数据上的事务结束。如果与   PAGLOCK   或   TABLOCK   一同指定，则排它锁适用于适当的粒度级别。