189 8069 5689
SQL SERVER里的锁机制：
创新互联坚持“要么做到，要么别承诺”的工作理念，服务领域包括：网站设计、网站制作、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的驿城网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！
NOLOCK（不加锁）
此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。
HOLDLOCK（保持锁）
此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。 例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。
UPDLOCK（修改锁）
此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。
TABLOCK（表锁）
此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。
PAGLOCK（页锁）
此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。
TABLOCKX（排它表锁）
此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。
nolock是不加锁查询。能够读取被事务锁定的数据，也称为脏读。
一般用于此类语句中：select * from t with(NOLOCK)
不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。
使用场景
综合考虑性能情况与业务要求来决定是否使用WITH(NOLOCK),例如涉及到金融或会计成本之类的系统，出现脏读那是要产生严重问题的。大体来说一般有下面一些场景可以使用WITH(NOLOCK)
1、基础数据表，这些表的数据很少变更。
2、历史数据表，这些表的数据很少变更。
3、业务允许脏读情况出现涉及的表。
4、数据量超大的表，出于性能考虑，而允许脏读。
另外一点就是不要滥用WITH(NOLOCK)。
锁一个表的某一行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table ROWLOCK WHERE id = 1
实例：
--排它锁
--新建两个连接
--在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
--在第二个连接中执行以下语句
begin tran
select * from table1
where B='b2'
commit tran
--若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒
锁的类别有两种分法：
从数据库系统的角度来看锁分为独占锁（即排它锁），共享锁和更新锁
MS-SQL Server 使用以下资源锁模式。
锁模式 描述
共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
这是一个典型的“丢失更新”问题。通常的解决方式是提高隔离级别，或者为select加排他锁。但我推荐下面这种方式，可以在sql server默认的隔离级别和锁机制下解决问题。
begin tran
--注意：事务中的第一句必须是update
update 表名 set id=id+1 where ...
--获取原来的id值
declare @id int;
select @id=id-1 from 表名 where ...
利用@id，进行相应操作
视情况commit tran或rollback tran
核心思路是调整语句顺序，将update放到事务最开始，利用其排他锁，阻塞其他并发事务，保证同一时间只有一个事务执行。
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)