锁粒度：

锁粒度查看测试:

CREATE TABLE mytest(
	id	int,
	name varchar(20),
	info varchar(20)
)

insert into mytest
select 1,'kk',null
union all
select 2,'mm',null

create nonclustered index ix_mytest_name on mytest(name,id)
--create clustered index ix_mytest on mytest(id) 

--	事务的锁资源类型
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin tran
	update t set info='kk' from mytest t where ID=1	--OBJECT,PAGE,RID(堆),KEY(聚集索引)
--	update t set info='kk' from mytest t with(rowlock) where ID=1	--OBJECT,PAGE,RID(堆),KEY(聚集索引)
--	update t set info='kk' from mytest t with(paglock) where ID=1	--OBJECT,PAGE
--	update t set info='kk' from mytest t with(tablock) where ID=1	--OBJECT
--	alter table mytest add col int		--DATABASE,METADATA(Sch-S),OBJECT(Sch-M),OBJECT,RID(堆),KEY(聚集索引)

	select resource_type,resource_description,request_mode,request_status,request_type,request_lifetime
	from sys.dm_tran_locks where resource_database_id=DB_ID() and request_session_id=@@SPID
rollback tran

--	事务的锁资源类型
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin tran
	alter index ix_mytest_name on mytest rebuild	--几乎包括所有基本类型

	select resource_type,resource_description,request_mode,request_status,request_type,request_lifetime
	from sys.dm_tran_locks where resource_database_id=DB_ID() and request_session_id=@@SPID
rollback tran

--	所有正在请求的资源信息
select request_session_id,resource_type,resource_description,request_mode,request_status,request_type
,request_lifetime,request_owner_type,resource_associated_entity_id,lock_owner_address
from sys.dm_tran_locks where resource_database_id=DB_ID() order by request_session_id

--	根据sys.dm_tran_locks(resource_associated_entity_id)可确定正在锁定的是哪个对象或索引
--（对象正在访问则需等待结束）
select p.partition_id,p.partition_number,OBJECT_NAME(p.object_id) as table_name
,i.name as index_name,i.type_desc,p.rows
from sys.partitions p 
inner join sys.indexes i on p.object_id=i.object_id and p.index_id=i.index_id
where  281474979397632 IN(p.object_id,p.hobt_id)
--即where resource_associated_entity_id IN(p.object_id,p.hobt_id)

摘：

Microsoft SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。但其开销较低，因为需要维护的锁较少。

锁模式：

--当前表记录(只有非聚集索引ix_mytest_name)
select * from mytest
id	name	info
1	kk		NULL
2	mm		NULL

create nonclustered index ix_mytest_name on mytest(name) with(drop_existing=on)

--事务1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
	select * from mytest 
	
	select resource_type,resource_description,request_mode
	,request_status,request_type,request_lifetime
	from sys.dm_tran_locks 
	where resource_database_id=DB_ID() and request_session_id=@@SPID
	
	update t set info='kk' from mytest t where name='kk'
	
	select resource_type,resource_description,request_mode
	,request_status,request_type,request_lifetime
	from sys.dm_tran_locks 
	where resource_database_id=DB_ID() and request_session_id=@@SPID
--	waitfor delay '00:30:00'
rollback tran

说明:

先执行查询时，整个表为共享锁（S）。接着进行更新操作，在隔离级别serializable中，查询会将对象升级为排他锁。而如果其他事务也有共享锁，就不能转为排他锁，所以数据库引用了更新锁。当一个事务有了更新锁，其他事务就不能再申请更新锁。这个事务就正常升级为排他锁进行操作，避免了死锁可能（如几个事务都拥有共享锁，都打算进行排他操作，这样就相互等待成死锁）。在共享粒度较大的表转变为更新粒度较小的对象时，就会产生共享意向排他锁（SIX）。只有表中有索引并且需要查询键列（如name=‘kk‘），就会产生键范围锁（RangeS-U），在表共享锁转到键锁过程，对表个层次的粒度都加了相应的锁。

如下表格：锁粒度递增，锁模式更低。

要检验上面的粒度是否存在锁，先运行上面的【事务1】，等待30分钟再结束。接着再打开另一个查询窗口，逐条执行下面的【事务2】。

--事务2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
	select name from mytest where name='kk'		--KEY(RangeS-U)
--	select id from mytest where name='kk'		--RID(X)
--	select name from mytest where name='mm'		--PAGE(IU)
--	select id,name from mytest where name='mm'	--PAGE(IX)
--	select * from mytest 						--OBJECT(SIX)
--	create table t(id int)						--DATABASE(S)
	select resource_type,resource_description,request_mode,request_status,request_type,request_lifetime
	from sys.dm_tran_locks where resource_database_id=DB_ID() and request_session_id=@@SPID
rollback tran

上面可以验证【事务1】在各个级别粒度是否加锁。

意向锁：

摘：

数据库引擎使用意向锁来保护共享锁（S锁）或排他锁（X锁）放置在锁层次结构的底层资源上。意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

意向锁有两种用途：

防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

提高数据库引擎在较高的粒度级别检测锁冲突的效率。

例如，在该表的页或行上请求共享锁（S锁）之前，在表级请求共享意向锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X锁）。意向锁可以提高性能，因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁不多说明了，上面是操作也出现过，具体参考文档吧

大容量更新锁查看测试：

大容量更新锁是在大容量操作时才出现，一下测试查看

--先创建表
select c.name tablename,c.name columnname, o.object_id,c.column_id,o.type,o.type_desc 
into bulkTest
from sys.objects o,sys.columns c 
where 1<>1

select * from bulkTest

--导出测试数据.
exec sp_configure 'show advanced options',1	--启用高级配置选项设置
reconfigure;
exec sp_configure 'xp_cmdshell',1	--启用xp_cmdshell
reconfigure;

--必须放在同一行执行
EXEC master..xp_cmdshell 'bcp "select c.name tablename,c.name columnname, o.object_id,c.column_id,o.type,o.type_desc from sys.objects o,sys.columns c" queryout C:\Users\Administrator\Desktop\bulkTest.txt -c -t"|" -r "\n" -Slocalhost -Usa -Psa'  

--测试用,无需记录大量日志
ALTER DATABASE [mytest] SET RECOVERY SIMPLE WITH NO_WAIT
--ALTER DATABASE [mytest] SET RECOVERY BULK_LOGGED WITH NO_WAIT

--好了,开始操作!!打开一个查询窗口,执行以脚本
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
	BULK INSERT mytest.dbo.bulkTest
	FROM 'C:\Users\Administrator\Desktop\bulkTest.txt'
	WITH (
		TABLOCK,
		FIELDTERMINATOR ='|',
		ROWTERMINATOR ='\n'
	);

	select resource_type,resource_description,request_mode
	,request_status,request_type,request_lifetime
	from sys.dm_tran_locks 
	where resource_database_id=DB_ID() and request_session_id=@@SPID
ROLLBACK TRAN


--	truncate table mytest.dbo.bulkTest
--	SELECT * from mytest.dbo.bulkTest

此文章只是观察数据库中锁粒度和锁模式出现的相关操作和部分影响。

参考：

锁粒度和层次结构
键范围锁定
锁模式

sys.dm_tran_locks (Transact-SQL)
BULK INSERT (Transact-SQL)

SqlServer 并发事务（二）：锁粒度和锁模式