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

GO

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

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

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

GO

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

GO

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

GO

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

GO

CREATE CLUSTERED INDEX cidx ON t1 (c1)

CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)

GO

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

GO

CREATE PROC p2 @p1 int AS

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

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

GO

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

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

-- Batch #2

USE deadlocktest

SET NOCOUNT ON

WHILE (1=1) EXEC p2 4

GO

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

-- Batch #3

USE deadlocktest

SET NOCOUNT ON

CREATE TABLE #t1 (c2 int, c3 int)

GO

WHILE (1=1) BEGIN

INSERT INTO #t1 EXEC p1 4

TRUNCATE TABLE #t1

END

GO

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

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

GO

CREATE PROC p2 @p1 int AS

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

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

GO

很奇怪吧！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，使得已提交读事务使用行版本控制。

使用快照隔离。

使用绑定连接。

SqlServer2000对锁的疑问

sqlserver中

更新记录实际是隐藏事务

更新的时候是锁的

更新锁（U）：当数据修改时，首先实施更新锁，遍历整张表，

找到要修改的记录，再将更新锁提升为排它锁，

进行修改。更新锁不能与更新锁或者排它锁共存，但可与共享锁共存。

更新锁是一种意图锁，用来防止死锁。

排它锁（X）：在事务对记录修改时，SQL Server实施排它锁，其它事务看不到该数据，直到提交。

在微软技术丛书 上有解释的很清楚

这个是2000的

sqlserver 怎么清理 dm

SQL Server常见的问题主要是SQL问题造成，常见的主要是CPU过高和阻塞。

一、CPU过高的问题

1、查询系统动态视图查询执行时间长的sql语句

WITH ProcessCTE(blocked) AS ( SELECT spid FROM sys.sysprocesses WHERE cpu500 ) SELECT distinct a.* FROM ( SELECT TEXT,AA.* FROM sys.sysprocesses AA CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle) ) a JOIN ProcessCTE bucte WITH(NOLOCK) ON bucte.blocked=a.spid --where loginame = 'TCScenery' ORDER BY a.CPU

二、阻塞问题

1、查询系统动态视图查询阻塞的sql语句

WITH ProcessCTE(blocked) AS ( SELECT blocked FROM sys.sysprocesses WHERE blocked0 union SELECT blocked FROM sys.sysprocesses WHERE blocked0 ) SELECT distinct a.* FROM ( SELECT TEXT,AA.* FROM sys.sysprocesses AA CROSS APPLY sys.dm_exec_sql_text(AA.sql_handle) ) a JOIN ProcessCTE bucte WITH(NOLOCK) ON bucte.blocked=a.spid ORDER BY a.blocked

2、使用系统自带的存储过程

Sp_who2和sp_lock以及使用dbcc inputbuffer(spid) 也可以用来分析阻塞

sp_who可以返回如下信息： (可选参数LoginName, 或active代表活动会话数)

Spid (系统进程ID)

status (进程状态)

loginame (用户登录名)

hostname(用户主机名)

blk (阻塞进程的SPID)

dbname (进程正在使用的数据库名)

Cmd (当前正在执行的命令类型)

sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息： (可选参数LoginName, 或active代表活动会话数)

CPUTime (进程占用的总CPU时间)

DiskIO (进程对磁盘读的总次数)

LastBatch (客户最后一次调用存储过程或者执行查询的时间)

ProgramName (用来初始化连接的应用程序名称，或者主机名)

下面是sp_who的用法，sp_who2与此类似

A.列出全部当前进程

以下示例使用没有参数的 sp_who 来报告所有当前用户。

USE master; GO EXEC sp_who; GO

B.列出特定用户的进程

以下示例显示如何通过登录名查看有关单个当前用户的信息。

USE master; GO EXEC sp_who 'janetl'; GO

C.显示所有活动进程

USE master; GO EXEC sp_who 'active'; GO

D.显示会话 ID 标识的特定进程

USE master; GO EXEC sp_who '10' --specifies the process_id; GO

sp_lock用法说明

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ][ ; ]

[ @spid1 = ] 'session ID1'

来自用户想要锁定其信息的 sys.dm_exec_sessions 的数据库引擎会话 ID 号。 session ID1 的数据类型为 int，默认值为 NULL。 执行 sp_who 可获取有关该会话的进程信息。 如果未指定会话 ID1，则显示有关所有锁的信息。

[ @spid2 = ] 'session ID2'

来自 sys.dm_exec_sessions 的另一个数据库引擎会话 ID 号，该会话 ID 号可能与 session ID1 同时具有锁，并且用户也需要其有关信息。 session ID2 的数据类型为 int，默认值为 NULL。

在 sp_lock 结果集中，由 @spid1 和 @spid2 参数指定的会话所持有的每个锁都对应一行。 如果既未指定 @spid1 又未指定 @spid2，则结果集将报告当前在数据库引擎实例中处于活动状态的所有会话的锁。

列名

数据类型

说明

spid

smallint

请求锁的进程的数据库引擎会话 ID 号。

dbid

smallint

保留锁的数据库的标识号。 可以使用 DB_NAME() 函数来标识数据库。

ObjId

int

持有锁的对象的标识号。 可以在相关数据库中使用 OBJECT_NAME() 函数来标识对象。 值为 99 时是一种特殊情况，表示用于记录数据库中页分配的其中一个系统页的锁。

IndId

smallint

持有锁的索引的标识号。

类型

nchar(4)

锁的类型：

RID = 表中单个行的锁，由行标识符 (RID) 标识。

KEY = 索引内保护可串行事务中一系列键的锁。

PAG = 数据页或索引页的锁。

EXT = 对某区的锁。

TAB = 整个表（包括所有数据和索引）的锁。

DB = 数据库的锁。

FIL = 数据库文件的锁。

APP = 指定的应用程序资源的锁。

MD = 元数据或目录信息的锁。

HBT = 堆或 B 树索引的锁。 在 SQL Server 中此信息不完整。

AU = 分配单元的锁。 在 SQL Server 中此信息不完整。

Resource

nchar(32)

标识被锁定资源的值。 值的格式取决于 Type 列标识的资源类型：

Type 值：Resource 值

RID：格式为 fileid:pagenumber:rid 的标识符，其中 fileid 标识包含页的文件，pagenumber 标识包含行的页，rid 标识页上的特定行。 fileid 与sys.database_files 目录视图中的 file_id 列相匹配。

KEY：数据库引擎内部使用的十六进制数。

PAG：格式为 fileid:pagenumber 的数字，其中 fileid 标识包含页的文件，pagenumber 标识页。

EXT：标识区中的第一页的数字。 该数字的格式为 fileid:pagenumber。

TAB：没有提供信息，因为已在 ObjId 列中标识了表。

DB：没有提供信息，因为已在 dbid 列中标识了数据库。

FIL：文件的标识符，与 sys.database_files 目录视图中的 file_id 列相匹配。

APP：被锁定的应用程序资源的唯一标识符。 格式为 DbPrincipleId:资源字符串的前 2 个到 16 个字符哈希运算值。

MD：随资源类型而变化。 有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL) 中 resource_description 列的说明。

HBT：没有提供任何信息。 请改用 sys.dm_tran_locks 动态管理视图。

AU：没有提供任何信息。 请改用 sys.dm_tran_locks 动态管理视图。

模式

nvarchar(8)

所请求的锁模式。 可以是：

NULL = 不授予对资源的访问权限。 用作占位符。

Sch-S = 架构稳定性。 确保在任何会话持有对架构元素（例如表或索引）的架构稳定性锁时，不删除该架构元素。

Sch-M = 架构修改。 必须由要更改指定资源架构的任何会话持有。 确保没有其他会话正在引用所指示的对象。

S = 共享。 授予持有锁的会话对资源的共享访问权限。

U = 更新。 指示对最终可能更新的资源获取的更新锁。 用于防止一种常见的死锁，这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。

X = 排他。 授予持有锁的会话对资源的独占访问权限。

IS = 意向共享。 指示有意将 S 锁放置在锁层次结构中的某个从属资源上。

IU = 意向更新。 指示有意将 U 锁放置在锁层次结构中的某个从属资源上。

IX = 意向排他。 指示有意将 X 锁放置在锁层次结构中的某个从属资源上。

SIU = 共享意向更新。 指示对有意在锁层次结构中的从属资源上获取更新锁的资源进行共享访问。

SIX = 共享意向排他。 指示对有意在锁层次结构中的从属资源上获取排他锁的资源进行共享访问。

UIX = 更新意向排他。 指示对有意在锁层次结构中的从属资源上获取排他锁的资源持有的更新锁。

BU = 大容量更新。 用于大容量操作。

RangeS_S = 共享键范围和共享资源锁。 指示可串行范围扫描。

RangeS_U = 共享键范围和更新资源锁。 指示可串行更新扫描。

RangeI_N = 插入键范围和 Null 资源锁。 用于在将新键插入索引前测试范围。

RangeI_S = 键范围转换锁。 由 RangeI_N 和 S 锁的重叠创建。

RangeI_U = 由 RangeI_N 和 U 锁的重叠创建的键范围转换锁。

RangeI_X = 由 RangeI_N 和 X 锁的重叠创建的键范围转换锁。

RangeX_S = 由 RangeI_N 和 RangeS_S 锁的重叠创建的键范围转换锁 。

RangeX_U = 由 RangeI_N 和 RangeS_U 锁的重叠创建的键范围转换锁。

RangeX_X = 排他键范围和排他资源锁。 这是在更新范围中的键时使用的转换锁。

状态

nvarchar(5)

锁的请求状态：

CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。

GRANT：已获取锁。

WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。

DBCC INPUTBUFFER

显示从客户端发送到 Microsoft® SQL Server™ 的最后一个语句。

语法

DBCC INPUTBUFFER (spid)

参数

spid

是 sp_who 系统存储过程的输出中所显示的用户连接系统进程 ID (SPID)。

结果集

DBCC INPUTBUFFER 返回包含如下列的行集。

列名

数据类型

描述

EventType

nvarchar(30)

事件类型，例如：RPC、语言或无事件。

Parameters

Int

0 = 文本

1- n = 参数

EventInfo

nvarchar(255)

对于 RPC 的 EventType，EventInfo 仅包含过程名。对于语言或无事件的 EventType，仅显示事件的头 255 个字符。

例如，当缓冲区中的最后事件是 DBCC INPUTBUFFER(11) 时，DBCC INPUTBUFFER 将返回以下结果集。

EventType Parameters EventInfo

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

Language Event 0 DBCC INPUTBUFFER (11)

(1 row(s) affected)

SQL 各种锁？

SQL Server数据库发生死锁时不会象ORACLE那样自动生成一个跟踪文件.

有时可以在[管理]-[当前活动] 里看到阻塞信息(有时SQL Server企业管理器会因为锁太多而没有响应).

设定跟踪1204:

USE MASTER

DBCC TRACEON (1204,-1)

显示当前启用的所有跟踪标记的状态:

DBCC TRACESTATUS(-1)

取消跟踪1204:

DBCC TRACEOFF (1204,-1)

在设定跟踪1204后,会在数据库的日志文件里显示SQL Server数据库死锁时一些信息,

但那些信息很难看懂,需要对照SQL Server联机丛书仔细来看.

根据PAG锁要找到相关数据库表的方法:

DBCC TRACEON (3604)

DBCC PAGE (db_id,file_id,page_no)

DBCC TRACEOFF (3604)

请参考sqlservercentral.com上更详细的讲解.

从CSDN学到了一个找到死锁原因的方法.

我稍加修改, 去掉了游标操作并增加了一些提示信息,写了一个系统存储过程sp_who_lock.sql.

需要的时候直接调用:

sp_who_lock

就可以查出引起死锁的进程和SQL语句.

SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用.

如果想知道其它tracenum参数的含义,请看文章

我们还可以设置锁的超时时间(单位是毫秒), 来缩短死锁可能影响的时间范围:

例如:

use master

seelct @@lock_timeout

set lock_timeout 900000

-- 15分钟

seelct @@lock_timeout

sqlserver事务中更新某张表是不是就开启了排它锁

有排他锁，但是排他锁生存时间非常的短，

1. 当开始更新时首先在表上放一个架构锁，防止其他事务修改架构；

2. 在非序列化隔离级别下，整个表上会放一个意向共享锁，允许其他事务进行读取；

3. 然后事务开始更新这个表，更新是会逐行更新的，你可以把更新理解为一个游标；

4. 每一行上首先放上一个更新锁，成功放置更新锁以后，更新锁会变为排他锁；

5. 然后更新这一行数据，更新完毕后就会释放这一行的排它锁；

6. 整个表遍历完毕后释放架构锁，释放意向共享锁。

求助，sqlserver什么情况下会锁表

锁的类别有两种分法：

从数据库系统的角度来看锁分为独占锁（即排它锁），共享锁和更新锁

MS-SQL Server 使用以下资源锁模式。

锁模式 描述

共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。