SQLServer之锁定数据库表

用户锁定表注意事项

通过指定锁定方法、一个或多个索引、查询处理操作（如表扫描或索引查找）或其他选项，表提示在数据操作语言 (dml) 语句执行期间覆盖查询优化器的默认行为。表提示在 dml 语句的 from 子句中指定，仅影响在该子句中引用的表或视图。

如果查询计划不访问表，则将忽略表提示。 这可能是由于优化器选择了完全不访问该表，也可能是因为改成了访问索引视图。 在后一种情况中，使用 option (expand views) 查询提示可阻止访问索引视图。

所有锁提示将传播到查询计划访问的所有表和视图，其中包括在视图中引用的表和视图。 另外， sql server 还将执行对应的锁一致性检查。

获取行级别锁的锁提示 rowlock、updlock 和 xlock 可能对索引键而不是实际的数据行采用锁。 例如，如果表具有非聚集索引，而且由涵盖索引处理使用锁提示的 select 语句，则获得的锁针对的是涵盖索引中的索引键，而不是基表中的数据行。

如果表包含计算列，而该计算列是由访问其他表中的列的表达式或函数计算的，则不在这些表中使用表提示，并且不会传播这些提示。 例如，在查询的表中指定 nolock 表提示。 此表包含的计算列是由访问另一表中的列的表达式和函数组合计算的。 表达式和函数引用的表在被访问时将不使用 nolock 表提示。

对于 from 子句中的每个表， sql server 不允许存在多个来自以下各个组的表提示：

• 粒度提示：paglock、nolock、readcommittedlock、rowlock、tablock 或 tablockx。
• 隔离级别提示：holdlock、nolock、readcommitted、repeatableread 和 serializable。

使用t-sql脚本添加、查询、删除锁

添加表锁：

语法：

select * from 表名 as 别名 with（<table_hint> [ [, ]...n ]）;

<table_hint> ::= [ noexpand] { index( index_value [ ,...n ] ) | index= ( index_value ) | forceseek[( index_value ( index_column_name [ ,... ] ) ) ] | forcescan | forceseek | holdlock | nolock | nowait | paglock | readcommited | readcommitedlock | readpast | readuncommitted | repeatableread | rowlock | serializable | snapshot | spatial_window_max_cells= integer | tablock | tablockx | updlock | xlock }；

语法解析：

--noexpand
--指定查询优化器处理查询时，不扩展任何索引视图来访问基础表。查询优化器将视图当成包含聚集索引的表处理。 noexpand 仅适用于索引视图。

--index(index_value [,... n ] ) | index = ( index_value)
--index()语法指定供查询优化器在处理该语句时使用的一个或多个索引的名称或 id。 另一供选择的 index = 语法指定单个索引值。 只能为每个表指定一个索引提示。
--如果存在聚集索引，则 index(0) 强制执行聚集索引扫描，index(1) 强制执行聚集索引扫描或查找。 如果不存在聚集索引，则 index(0) 强制执行表扫描，index(1) 被解释为错误。
--如果在单个提示列表中使用了多个索引，则会忽略重复项，其余列出的索引将用于检索表中的行。 索引提示中的索引顺序很重要。 多索引提示还强制执行索引 and 运算，查询优化器将对所访问的每个索引应用尽可能多的条件。
--如果提示索引的集合并未包含查询引用的所有列，则会在 sql server 数据库引擎检索所有索引列后执行提取操作以检索其余列。

--forceseek[(index_value(index_column_name [ ,... n ] )) ]
--指定查询优化器仅使用索引查找操作作为表或视图中的数据的访问途径。
--index_value
--是索引名称或索引 id 值。 不能指定索引 id 0（堆）。 若要返回索引名称或 id，请查询 sys.indexes 目录视图。
--index_column_name
--是要包含在查找操作中的索引列的名称。指定带索引参数的forceseek类似于将forceseek与index提示一起使用。但是，您可以通过指定要查找的索引和查找操作中要考虑的索引列，更好地控制查询优化器使用的访问路径。 该优化器可以根据需要考虑其他列。
--使用forceseek提示（具有或不带索引参数）时，考虑以下准则：
--该提示可以指定为表提示或查询提示。 有关查询提示的详细信息，请参阅查询提示 (transact-sql)。
--若要将forceseek应用到索引视图，还必须指定noexpand提示。
--对每个表或视图最多应用该提示一次。
--不能为远程数据源指定该提示。 带索引提示指定forceseek时，将返回错误7377；不带索引提示使用forceseek时，将返回错误8180。
--如果forceseek导致找不到计划，将返回错误8622。
--使用索引参数指定forceseek时，遵循以下准则和限制：
--不能为作为insert、update或delete语句的目标的表指定该提示。
--该提示不能与index提示或另一个forceseek提示一起指定。
--至少必须指定一个列且该列为第一个键列。
--可以指定其他索引列，但是不能跳过键列。 例如，如果指定的索引包含键列 a、b 和 c，则有效的语法应包含forceseek(myindex (a))和forceseek(myindex (a, b)。无效的语法应包含 forceseek (myindex (c)) 和 forceseek (myindex (a, c)。
--在提示中指定的列名顺序必须与引用的索引中列的顺序匹配。
--不能指定不在索引键定义中的列。 例如，在非聚集索引中，只能指定定义的索引键列。 不能指定自动包含在索引中的聚集键列，但是优化器可以使用这些列。
--xvelocity 内存优化的列存储索引不能作为索引参数指定。 返回错误 366。
--修改索引定义（例如通过添加或删除列）可能需要修改引用该索引的查询。
--该提示阻止优化器考虑表的任何空间或 xml 索引。
--该提示不能与forcescan提示一起指定。
--对于分区的索引，不能在 forceseek 提示中指定 sql server 隐式添加的分区列。

--forcescan
--适用于：通过 sql server 2017 的 sql server 2008 r2 sp1。
--指定查询优化器仅使用索引扫描操作作为引用的表或视图的访问途经。对于优化器低估受影响的行数并选择一个查找操作而非扫描操作的查询，forcescan提示很有用。 出现这样的情况时，授予该操作的内存量太小，查询性能将受影响。
--指定forcescan时有无index提示均可。与索引提示组合使用 (index = index_name, forcescan) 时，查询优化器在访问引用的表时仅考虑通过指定的索引扫描访问路径。可以带索引提示 index(0) 指定 forcescan，以强制对基表执行表扫描操作。
--对于分区的表和索引，在通过查询谓词评估消除分区后应用 forcescan。 这意味着扫描仅适用于剩余分区而非整个表。
--forcescan 提示存在以下限制：
--不能为作为 insert、update 或 delete 语句的目标的表指定该提示。
--该提示不能与一个以上的索引提示一起使用。
--该提示阻止优化器考虑表的任何空间或 xml 索引。
--不能为远程数据源指定该提示。
--该提示不能与 forceseek 提示一起指定。

--holdlock
--等同于serializable。
--holdlock 仅应用于那些为其指定了 holdlock 的表或视图，并且仅在使用了 holdlock 的语句定义的事务的持续时间内应用。 holdlock 不能被用于包含 for browse 选项的 select 语句。

--nolock
--等同于readuncommitted。

--nowait
--指示数据库引擎在遇到表的锁时，立即返回一条消息。
--nowait等同于将特定表的 set lock_timeout 值指定为 0。 当tablock提示也包含在内时，nowait提示不起作用。 若要在使用tablock提示时终止查询而不等待，请改为在查询前加上setlock_timeout 0;。

--paglock
--在通常行或键采用单个锁的地方，或者通常采用单个表锁的地方，请采用页锁。
--默认情况下，请使用与操作相对应的锁模式。 在从snapshot 隔离级别操作的事务中指定时，除非将paglock与需要锁的其他表提示（例如，updlock 和 holdlock）组合，否则不会取得页锁。

--readcommited
--指定读操作使用锁定或行版本控制来遵循有关 read committed隔离级别的规则。
--如果 read_committed_snapshot 数据库选项为off，数据库引擎会在读取数据时获取共享锁,在读操作完成后释放这些锁。
--如果数据库选项 read_committed_snapshot 为on，则数据库引擎不获取锁，并使用行版本控制。

--readcommitedlock
--指定读操作使用锁定来遵循有关read committed隔离级别的规则。 无论read_committed_snapshot数据库选项的设置如何， 数据库引擎都将在读取数据时获取共享锁，在读操作完成后释放这些锁。
--不能对 insert 语句的目标表指定此提示；将返回错误 4140。

--readpast
--指定数据库引擎不读取由其他事务锁定的行。
--如果指定readpast，则跳过行级锁，但不跳过页级锁。 也就是说， 数据库引擎将跳过这些行，而不是阻塞当前事务直到锁被释放。
--可为 update 或 delete 语句中以及 from 子句中引用的任何表指定 readpast。 如果 readpast 是在 update 语句中指定的，则仅当读取数据以标识要更新的记录时才应用 readpast，而不考虑语句中指定 readpast 的位置。
--不能为insert语句的 into 子句中的表指定 readpast。 读取外键或索引视图或者修改辅助索引时，使用 readpast 的更新或删除操作可能发生阻塞。
--仅可在运行于 read committed 或 repeatable read 隔离级别的事务中指定 readpast。 在从 snapshot 隔离级别操作的事务中指定时，readpast 必须与需要锁的其他表提示（例如，updlock 和 holdlock）组合。
--当 read_committed_snapshot 数据库选项设置为 on 并且满足以下条件之一时，无法指定 readpast 表提示：
--会话的事务隔离级别为 read committed。
--查询中也指定了 readcommitted 表提示。
--若要在上述情况下指定 readpast 提示，请删除 readcommitted 表提示（如果存在），然后在查询中包括 readcommittedlock 表提示。

--readuncommitted
--指定允许脏读。 不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。
--允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。 这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。
--readuncommitted 和 nolock 提示仅适用于数据锁。 所有查询（包括那些带有 readuncommitted 和 nolock 提示的查询）都会在编译和执行过程中获取 sch-s（架构稳定性）锁。 因此，当并发事务持有表的 sch-m（架构修改）锁时，将阻塞查询。
--例如，数据定义语言 (ddl) 操作在修改表的架构信息之前获取 sch-m 锁。 所有并发查询（包括那些使用 readuncommitted 或 nolock 提示运行的查询）都会在尝试获取 sch-s 锁时被阻塞。 相反，持有 sch-s 锁的查询将阻塞尝试获取 sch-m 锁的并发事务。
--不能为通过插入、更新或删除操作修改过的表指定 readuncommitted 和 nolock。 sql server 查询优化器忽略 from 子句中应用于 update 或 delete 语句的目标表的 readuncommitted 和 nolock 提示。
--可以通过使用以下任意一种方法，在保护事务避免对未提交的数据修改进行脏读的同时最大程度地减少锁争用：
--read committed 隔离级别，其中 read_committed_snapshot 数据库选项设置为 on。
--snapshot 隔离级别。

--repeatableread
--指定事务在repeatable read 隔离级别运行时，使用相同的锁定语义执行一次扫描。

--rowlock
--指定通常采用页锁或表锁时，采用行锁。 在从 snapshot 隔离级别操作的事务中指定时，除非将 rowlock 与需要锁的其他表提示（例如，updlock 和 holdlock）组合，否则不会取得行锁。

--serializable
--等同于holdlock。保持共享锁直到事务完成，使共享锁更具有限制性；而不是无论事务是否完成，都在不再需要所需表或数据页时立即释放共享锁。 执行扫描时所用的语义与在 serializable 隔离级别运行的事务的语义相同。

--snapshot
--适用范围： sql server 2014 (12.x) 到 sql server 2017。
--内存优化表在snapshot隔离下访问。 snapshot只能用于内存优化表 (不能用于基于磁盘的表)。

--spatial_window_max_cells=integer
--适用范围： sql server 2012 (11.x) 到 sql server 2017。
--指定在分割 geometry 或 geography 对象时使用的最大单元格数。 number 是介于 1 和 8192 之间的值。
--通过使用此选项，可以在主要和辅助筛选器执行时间之间权衡性能以微调查询执行时间。 较大的数字将减少辅助筛选器执行时间，但会增加主要筛选器执行时间，而较小的数字恰相反。
--对于较密的空间数据，较大的数字通过为主要筛选器提供更好的近似值并减少辅助筛选器执行时间，从而缩短了执行时间。 对于较稀疏的数据，较小的数字将减少主要筛选器执行时间。
--此选项适用于手动和自动网格分割。

--tablock
--指定在表级别应用获取的锁。 获取的锁类型取决于正在执行的语句。 例如，select 语句可能获取一个共享锁。 通过指定 tablock，将该共享锁应用到整个表而非在行或页级别应用。 如果同时指定了 holdlock，则会一直持有表锁，直至事务结束。
--在使用 insert into <target_table> select <columns> from <source_table> 语句将数据导入某个堆时，可通过为目标表指定 tablock 提示，实现语句的优化日志记录和锁定。 此外，数据库的恢复模式必须设置为简单或大容量日志模式。
--在与 openrowset bulk 行集提供程序一起使用以将数据导入表时，tablock 允许多个客户端使用优化日志记录和锁定，以并发方式将数据加载到目标表中。

--tablockx
--指定对表采用排他锁。

--updlock
--指定采用更新锁并保持到事务完成。updlock 仅对行级别或页级别的读操作采用更新锁。 如果将 updlock 与 tablock 组合使用或出于一些其他原因采用表级锁，将采用排他 (x) 锁。
--指定 updlock 时，忽略 readcommitted 和 readcommittedlock 隔离级别提示。 例如，如果将会话的隔离级别设置为 serializable 且查询指定 (updlock, readcommitted)，则忽略 readcommitted 提示且使用 serializable 隔离级别运行事务。

--xlock
--指定采用排他锁并保持到事务完成。 如果同时指定了 rowlock, paglock 或 tablock，则排他锁将应用于相应的粒度级别。

示例：

select * from test1 with(tablockx)；

查看数据库所有锁：

exec sp_lock;

删除表锁：

declare @spid int;
set @spid = 1; --锁表进程
declare @sql varchar(1000) ;
set @sql='kill '+cast(@spid as varchar) ;
exec(@sql) ;