如何防止与解决SQL发生死锁

Question

公司开发的ERP系统，客户刚使用的时候，数据量还小，感觉不到SQL处理延时的问题，现在（两年后）客户使用系统时，在查询与提交数据时SQL经常会返回以下错误提示：
    System.Data.SqlClient.SqlException: Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim。
    请教各位该如何解决。

Answer 1

需要的明确的是楼主的部分SQL语句产生了deadlock，也就是死锁。在这种情况下无法你硬件怎么强大，都无济于事。所以需要分析你写的SQL语句或存储过程，产生死锁的原因。以及如何减少死锁的发生。
一般可以通过以下几点处理可能会收到不错的效果。1.对关键的字段建立索引，不过不能建立太多，否则维护索引的成本会增大。对于某些查询可以走索引。
2.在业务逻辑上允许的情况下尽量降低SQL执行时的事务隔离级别。可以通过 “SET TRANSACTION ISOLATION LEVEL 语句”实现。
3.使用Table With 关键字语句介绍锁的开销。
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 }如果掌握以上几点的应用，相信对SQL的大数据量访问性能会明显提高。我深有体会。