sqlserver原子锁,sqlserver 锁

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

如何处理SQL Server死锁问题

死锁，简而言之，两个或者多个trans，同时请求对方正在请求的某个对象，导致双方互相等待。简单的例子如下：

trans1 trans2

------------------------------------------------------------------------

1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction

2.update table A 2.update table B

3.update table B 3.update table A

4.IDBConnection.Commit 4.IDBConnection.Commit

那么，很容易看到，如果trans1和trans2，分别到达了step3，那么trans1会请求对于B的X锁，trans2会请求对于A的X锁，而二者的锁在step2上已经被对方分别持有了。由于得不到锁，后面的Commit无法执行，这样双方开始死锁。

好，我们看一个简单的例子，来解释一下，应该如何解决死锁问题。

-- Batch #1

CREATE DATABASE deadlocktest

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

-- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc

--traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。

IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1

IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))

DECLARE @x int

SET @x = 1

WHILE (@x = 1000) BEGIN

INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)

SET @x = @x + 1

END

CREATE CLUSTERED INDEX cidx ON t1 (c1)

CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)

CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

CREATE PROC p2 @p1 int AS

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

上述sql创建一个deadlock的示范数据库，插入了1000条数据，并在表t1上建立了c1列的聚集索引，和c2列的非聚集索引。另外创建了两个sp，分别是从t1中select数据和update数据。

好，打开一个新的查询窗口，我们开始执行下面的query：

-- Batch #2

USE deadlocktest

SET NOCOUNT ON

WHILE (1=1) EXEC p2 4

开始执行后，然后我们打开第三个查询窗口，执行下面的query：

-- Batch #3

USE deadlocktest

SET NOCOUNT ON

CREATE TABLE #t1 (c2 int, c3 int)

WHILE (1=1) BEGIN

INSERT INTO #t1 EXEC p1 4

TRUNCATE TABLE #t1

END

开始执行，哈哈，很快，我们看到了这样的错误信息：

Msg 1205, Level 13, State 51, Procedure p1, Line 4

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

spid54发现了死锁。

那么，我们该如何解决它？

在SqlServer 2005中，我们可以这么做：

1.在trans3的窗口中，选择EXEC p1 4，然后right click，看到了菜单了吗？选择Analyse Query in Database Engine Tuning Advisor。

2.注意右面的窗口中，wordload有三个选择：负载文件、表、查询语句，因为我们选择了查询语句的方式，所以就不需要修改这个radio option了。

3.点左上角的Start Analysis按钮

4.抽根烟，回来后看结果吧！出现了一个分析结果窗口，其中，在Index Recommendations中，我们发现了一条信息：大意是，在表t1上增加一个非聚集索引索引：t2+t1。

5.在当前窗口的上方菜单上，选择Action菜单，选择Apply Recommendations，系统会自动创建这个索引。

重新运行batch #3，呵呵，死锁没有了。

这种方式，我们可以解决大部分的Sql Server死锁问题。那么，发生这个死锁的根本原因是什么呢？为什么增加一个non clustered index，问题就解决了呢？这次，我们分析一下，为什么会死锁呢？再回顾一下两个sp的写法：

CREATE PROC p1 @p1 int AS

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

CREATE PROC p2 @p1 int AS

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

很奇怪吧！p1没有insert，没有delete，没有update，只是一个select，p2才是update。这个和我们前面说过的，trans1里面updata A，update B；trans2里面upate B，update A，根本不贴边啊！

那么，什么导致了死锁？

需要从事件日志中，看sql的死锁信息：

Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.

The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.

首先，我们看看p1的执行计划。怎么看呢？可以执行set statistics profile on，这句就可以了。下面是p1的执行计划

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))

|--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] = [@p1] AND [t1].[c2] = [@p1]+(1)) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

我们看到了一个nested loops，第一行，利用索引t1.c2来进行seek，seek出来的那个rowid，在第二行中，用来通过聚集索引来查找整行的数据。这是什么？就是bookmark lookup啊！为什么？因为我们需要的c2、c3不能完全的被索引t1.c1带出来，所以需要书签查找。

好，我们接着看p2的执行计划。

UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

|--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))

|--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))

|--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...

|--Top(ROWCOUNT est 0)

|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

通过聚集索引的seek找到了一行，然后开始更新。这里注意的是，update的时候，它会申请一个针对clustered index的X锁的。

实际上到这里，我们就明白了为什么update会对select产生死锁。update的时候，会申请一个针对clustered index的X锁，这样就阻塞住了（注意，不是死锁！）select里面最后的那个clustered index seek。死锁的另一半在哪里呢？注意我们的select语句，c2存在于索引idx1中，c1是一个聚集索引cidx。问题就在这里！我们在p2中更新了c2这个值，所以sqlserver会自动更新包含c2列的非聚集索引：idx1。而idx1在哪里？就在我们刚才的select语句中。而对这个索引列的更改，意味着索引集合的某个行或者某些行，需要重新排列，而重新排列，需要一个X锁。

SO………，问题就这样被发现了。

总结一下，就是说，某个query使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁，就这样发生鸟。

那么，为什么我们增加了一个非聚集索引，死锁就消失鸟？我们看一下，按照上文中自动增加的索引之后的执行计划：

SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

|--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] = [@p1] AND [deadlocktest].[dbo].[t1].[c2] = [@p1]+(1)) ORDERED FORWARD)

哦，对于clustered index的需求没有了，因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。

实际上，在sqlserver 2005中，如果用profiler来抓eventid：1222，那么会出现一个死锁的图，很直观的说。

下面的方法，有助于将死锁减至最少（详细情况，请看SQLServer联机帮助，搜索：将死锁减至最少即可。

按同一顺序访问对象。

避免事务中的用户交互。

保持事务简短并处于一个批处理中。

使用较低的隔离级别。

使用基于行版本控制的隔离级别。

将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON，使得已提交读事务使用行版本控制。

使用快照隔离。

使用绑定连接。

mongod可以代替sqlserver吗

可以，并且有这些优势

1、使用JSON风格语法，易于掌握和理解：MongoDB使用JSON的变种BSON作为内部存储的格式和语法。针对MongoDB的操作都使用JSON风格语法，客户端提交或接收的数据都使用JSON形式来展现。相对于SQL来说，更加直观，容易理解和掌握。这也是根据我自己项目的情况出发，最后选择了mongodb的一个原因。

2、Schema-less，支持嵌入子文档：MongoDB是一个Schema-free的文档数据库。一个数据库可以有多个Collection，每个Collection是Documents的集合。Collection和Document和传统数据库的Table和Row并不对等。无需事先定义Collection，随时可以创建。Collection中可以包含具有不同schema的文档记录。这意味着，你上一条记录中的文档有3个属性，而下一条记录的文档可以有10个属性，属性的类型既可以是基本的数据类型（如数字、字符串、日期等），也可以是数组或者散列，甚至还可以是一个子文档（embed document）。这样，可以实现逆规范化（denormalizing）的数据模型，提高查询的速度。

3、简单易用的查询方式：直接使用JSON，支持范围查询、正则表达式查询。

4、CRUD更加简单，支持in-place update：只要定义一个数组，然后传递给MongoDB的insert/update方法就可自动插入或更新；对于更新模式，MongoDB支持一个upsert选项，即：“如果记录存在那么更新，否则插入”。MongoDB的update方法还支持Modifier，通过Modifier可实现在服务端即时更新，省去客户端和服务端的通讯。这些modifer可以让MongoDB具有和Redis、Memcached等KV类似的功能：较之MySQL，MonoDB更加简单快速。Modifier也是MongoDB可以作为对用户行为跟踪的容器。在实际中使用Modifier来将用户的交互行为快速保存到MongoDB中以便后期进行统计分析和个性化定制

5、所有的属性类型都支持索引，甚至数组：这可以让某些任务实现起来非常的轻松。在MongoDB中，“_id”属性是主键，默认MongoDB会对_id创建一个唯一索引。

6、性能高效，速度快： MongoDB使用c++/boost编写，在多数场合，其查询速度对比MySQL要快的多，对于CPU占用非常小。部署也很简单，对大多数系统，只需下载后二进制包解压就可以直接运行，几乎是零配置。

7、服务端脚本和Map/Reduce：MongoDB允许在服务端执行脚本，可以用Javascript编写某个函数，直接在服务端执行，也可以把函数的定义存储在服务端，下次直接调用即可。MongoDB不支持事务级别的锁定，对于某些需要自定义的“原子性”操作，可以使用Server side脚本来实现，此时整个MongoDB处于锁定状态。Map/Reduce也是MongoDB中比较吸引人的特性。Map/Reduce可以对大数据量的表进行统计、分类、合并的工作，完成原先SQL的GroupBy等聚合函数的功能。并且Mapper和Reducer的定义都是用Javascript来定义服务端脚本。

sql server2000高手看下

sql server 的锁是自动管理的，事务是最小的原子单位，即是在begin train

commit train/rollback train之间只做一次提交，结果是要么全部执行成功要么全部执行失败

在另一个用户里执行insert 语句的时候很快不用等这20秒是insert 语句的时候没有调用这个事务，你将这个事务放到查询分析器执行是要20秒的

楼主，我建议是你看下sql server锁机制，因为你insert的记录是新的跟color='白' 没有关系，如果你在这个时候再去update Mobbile set num=num+5 where color='白' 肯定是需要等待的

在用户有sql请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，就需要了解sql server的锁机制，掌握数据库锁定方法