lock（1）——创建及更新表过程中SQL SERVER锁资源分配情况

2022-08-08 12:25:51

锁应该说是由关系型数据库ACID（Atomicity，Consistency，Isolation，Durability）特性而引出的。

以下将测试在创建及更新表过程中SQL Server锁资源分配情况

获取当前会话的事务隔离级别：DBCC USEROPTIONS

测试环境：SQL SERVER 2008 R2 read committed隔离级别下

创建表

当我们只是打开一个SSMS查询窗口，选择数据库为master和tempdb时，没有任何锁产生，当我选择其他数据库，sql server就会在相应的数据库加上共享锁，这个就避免了其他人在数据库层

面做一些改变，比如删除该数据库或者是修改该库的排序规则，有了这个锁，这些操作是不会成功的。

我开启一个事务BEGIN TRAN，锁没有变

新建一张具有聚集索引的表：

BEGIN TRAN
CREATE TABLE [dbo].[Employee_demo_Btree](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Age] [tinyint] not null,
    [LoginID] [nvarchar](256) NOT NULL,
    [JobTitle] [nvarchar](50) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
    [HireDate] [date] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Employee_demo_Btree_ID] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )ON [PRIMARY]
) ON [PRIMARY]

GO
SELECT 
DB_NAME(resource_database_id) AS [DataBase]
,resource_type --DATABASE、FILE、OBJECT、PAGE、KEY、EXTENT、RID、APPLICATION、METADATA、HOBT 或ALLOCATION_UNIT
--,resource_subtype
,resource_associated_entity_id
,CASE WHEN resource_type=‘OBJECT‘
      THEN  (SELECT OBJECT_NAME(object_id) FROM sys.objects O WITH(NOLOCK) WHERE O.object_id=L.resource_associated_entity_id)
      WHEN resource_type IN(‘KEY‘,‘PAGE‘,‘RID‘,‘HOBT‘,‘METADATA‘)
      THEN  ( SELECT OBJECT_NAME(object_id) FROM sys.partitions P WITH(NOLOCK) WHERE P.hobt_id=L.resource_associated_entity_id)
 END AS Resource_type_name
,request_mode
,request_type
,request_status
,request_reference_count
,request_session_id
,request_request_id
,request_owner_type --TRANSACTION = 请求由事务所有。CURSOR = 请求由游标所有。SESSION = 请求由用户会话所有。
                    --SHARED_TRANSACTION_WORKSPACE = 请求由事务工作区的共享部分所有。EXCLUSIVE_TRANSACTION_WORKSPACE = 请求由事务工作区的排他部分所有。
                    -- NOTIFICATION_OBJECT = 请求由内部SQL Server 组件所有。此组件已经请求锁管理器在有其他组件等待获取锁时进行通知。FileTable 功能是使用此值的一个组件。 
,request_owner_id
 FROM sys.dm_tran_locks  as L with(nolock)
WHERE  request_session_id=62
--COMMIT TRAN

View Code

以下结构本来有66行，由于版面问题，我只截了对我比较重要的一部分。从以下图中看出，我们在创建表过程中数据库层面是加了共享锁，该表的树结构和对象PK_Employee_demo_Btree_ID，Employee_demo_Btree都会加上架构修改锁，其他还会在相关系统表添加意向排他和排他锁。

提交事务，创建一个堆表，

再这里在说明一下，以下查询锁资源的脚本像第一个脚本一样，后面就不再将查询锁资源这部分脚本加进去，这样我们的篇幅也不至于那么长

BEGIN TRAN
CREATE TABLE [dbo].[Employee_demo_Heap](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Age] [tinyint] not null,
	[LoginID] [nvarchar](256) NOT NULL,
	[JobTitle] [nvarchar](50) NOT NULL,
	[BirthDate] [date] NOT NULL,
	[MaritalStatus] [nchar](1) NOT NULL,
	[Gender] [nchar](1) NOT NULL,
	[HireDate] [date] NOT NULL,
	[VacationHours] [smallint] NOT NULL,
	[SickLeaveHours] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
) ON [PRIMARY]
--COMMIT TRAN

这次SQL SERVER 总共申请了56个锁资源，这里我只截了一部分，从下图可以看出创建堆表，首先数据库层面肯定会有个共享锁，下来表对象Employee_demo_Heap和堆结构Employee_demo_Heap上加有架构修改锁，元数据上加了架构稳定锁。其他对应的系统表及key上加有意向排他和排他锁。

提交事务。

创建索引

下来开启一个事务，在聚集索引表上创建索引

BEGIN TRAN
CREATE NONCLUSTERED INDEX IX_DBA_Name
ON [dbo].[Employee_demo_Btree](Name)
GO
--COMMIT TRAN

锁的情况如下，可以看到我们在往一个表上加索引时，在该表上有加共享锁和架构稳定锁，然后还在该表的树结构（HOBT）上加了架构修改锁。还有两个元数据（METADATA）也加了共享和架构修改锁。其他还有一些系统表有加意向排它锁，相应的key上加了排它锁。

提交事务。重新开启一个事务，在堆表上创建索引

BEGIN TRAN 
CREATE NONCLUSTERED INDEX IX_DBA_Name
ON [dbo].[Employee_demo_Heap](Name)
GO
--COMMIT TRAN

可以发现，其实在堆表上和聚集索引表上加非聚集索引，申请的锁资源基本一致。

向表中插入数据

向聚集表中插入数据。

BEGIN TRAN
INSERT INTO [dbo].[Employee_demo_Btree]
SELECT EmployeeID
		,Name = left(replace(newid(),‘-‘,‘‘),10)
		,Age = datediff(YEAR,BirthDate,getdate())
		,LoginID
		,Title
		,BirthDate
		,MaritalStatus
		,Gender
		,HireDate
		,VacationHours
		,SickLeaveHours
		,ModifiedDate
FROM [HumanResources].[Employee]
GO
--COMMIT TRAN

图中只截了一部分，本身总共是591行数据，除了图中的，其他的都是Employee_demo_Btree key上的排它锁。从图中可以看出，我们在修改聚集索引表时sql server申请了数据库的共享锁，元数据的架构稳定锁，表对象的意向排它锁，页上意向排它锁，key上的排它锁。

向堆表插入数据

BEGIN TRAN
INSERT INTO [dbo].[Employee_demo_Heap]
SELECT EmployeeID
        ,Name = left(replace(newid(),‘-‘,‘‘),10)
        ,Age = datediff(YEAR,BirthDate,getdate())
        ,LoginID
        ,Title
        ,BirthDate
        ,MaritalStatus
        ,Gender
        ,HireDate
        ,VacationHours
        ,SickLeaveHours
        ,ModifiedDate
FROM [HumanResources].[Employee]
GO
--COMMIT TRAN

图中只截了一部分，本身总共是591行数据，除了图中的，其他的都是Employee_demo_Heap key上的排它锁。经对比，向堆表中插入数据过程中，申请的锁资源跟向聚集索引表中插入的数据申请的锁资源基本是一样的。

查询数据

BEGIN TRAN
SELECT * FROM  [dbo].[Employee_demo_Btree]
--COMMIT TRAN

这个查询结果有点让我意外，因为我以为在未提交事务前，该表都会持有共享锁，谁知道结果竟然如下，只是在数据库上加了共享锁，request owner type是SHARED_TRANSACTION_WORKSPACE

官方解释：对于默认隔离级别“已提交读”，SQL Server Compact 3.5 中的 SELECT 语句不需要使用 S 锁来读取数据。虽然这对于 MicrosoftSQL Server 是必需的，但 SQL Server Compact 3.5 不需要使用 S 锁来强制执行“提交读”。SELECT 语句需要的唯一锁是 Sch-S，它在操作执行时保护架构。因此，SELECT 语句是高度并发的。有关详细信息，请参阅事务隔离级别。

BEGIN TRAN
SELECT * FROM  [dbo].[Employee_demo_heap]
--COMMIT TRAN

结构类似聚集表。

删除数据

删除聚集表中的数据

BEGIN TRAN
DELETE FROM  [dbo].[Employee_demo_Btree]
where ID=16
--COMMIT TRAN

锁资源申请情况如下：

有人不禁提问了，这里我只删了一条数据，为什么产生两个也的意向排他和两个key的排他，好像是删了两条数据一样。其实事实就是这样的，确实是删了两条，一个结构是我们本身表的树结构，它需要删除掉这条数据，那另外一个是我们前面创建的索引结构，索引的树结构中也需要删掉这条数据。

删除堆表中的数据

BEGIN TRAN
DELETE FROM  [dbo].[Employee_demo_heap]
where ID=16
--COMMIT TRAN

删除堆表中的一条数据申请的锁资源基本与聚集表类似，只有点点的差别就是，其中一个KEY换成了RID，这个因为我们堆表在定位某一行的数据时根据的是RID。

更新数据

更新聚集表

BEGIN TRAN
UPDATE  [dbo].[Employee_demo_Btree]
SET JOBTitle=‘Production Supervisor - WC10‘
where ID=18
--COMMIT TRAN

结果如下，这里因为我更新的是非索引列，所以不会像上面个一样，会产生两个page上的IX锁，和两个key上的X锁。这里只需要修改聚集索引树结构中的该条数据

更新堆表

BEGIN TRAN
UPDATE  [dbo].[Employee_demo_Heap]
SET JOBTitle=‘Production Supervisor - WC10‘
where ID=18
--COMMIT TRAN

结果类似聚集索引表，只是这里定位一行索引使用的是RID，所以是在RID加上X锁。

添加列

BEGIN TRAN
ALTER  TABLE  DBO.Employee_demo_Btree
add  address1 varchar(100) null
--COMMIT TRAN

给聚集索引表添加一个列address1，申请的锁如下

BEGIN TRAN

ALTER  TABLE  DBO.Employee_demo_Heap
add  address1 varchar(100) null
--COMMIT TRAN

在堆表上添加一个列address1，申请的锁与聚集索引表类似。

删除列

删除聚集索引表上的一列

BEGIN TRAN
ALTER  TABLE  DBO.[Employee_demo_Btree]
drop column  ModifiedDate
--COMMIT TRAN

申请的锁如下：

BEGIN TRAN
ALTER  TABLE  DBO.[Employee_demo_Heap]
drop column  ModifiedDate
--COMMIT TRAN

删除堆表上的一列，跟删除聚集索引表一列申请的锁资源类似。

由以上操作，我们可以发现，其实在操作堆表和聚集索引表时，产生的锁资源基本都类似，除了牵扯到锁定行时，堆表用的RID，聚集索引表用的是KEY.

除此之外，我们总结一下在操作过程中添加锁的一些规律。

最后我们应该还有一个锁升级的问题，以上更新，删除，添加数据，锁资源的申请有可能会随着我们的数据量的变化有所变化，这就是锁升级带来的影响，关于锁升级我将在另外一个篇文章中陈述。

lock（1）——创建及更新表过程中SQL SERVER锁资源分配情况,布布扣,bubuko.com

lock（1）——创建及更新表过程中SQL SERVER锁资源分配情况

码农公寓

相关文章