SQL SECRUISERVE汉兰达 GO命令循环利用实例

发布时间:2019-10-29  栏目:MyBatis  评论:0 Comments

因此GO  命令 来达到语句循环功效  也叫批循环

—实例DB:AdventureWorks2014

print '输出10次'
GO 10

— 创建view DBLocks

图片 1

图片 2图片 3

 也可用来新增加 语句 

USE [AdventureWorks2014]
GO

/****** Object:  View [dbo].[DBlocks]    Script Date: 8/17/2016 6:38:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[DBlocks] AS
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN
object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, index_id,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = db_id();
GO
NOCOUNT ON 用来屏蔽 新增语句输出的影响行数 我们只看循环次数

SET NOCOUNT ON;
GO


INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100

View Code

图片 4

实例1: Read Commit(SQL SEEnclaveVE悍马H2暗中同意隔离品级) 隔开品级下的SELECT

 

图片 5图片 6

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT * FROM [Production].[Product] WHERE Name ='Reflector';
SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID
COMMIT TRAN

View Code

DBLocks 的输出结果

spid dbname entity_name index_id
58 AdventureWorks2014 sysrowsets NULL
58 AdventureWorks2014 n/a NULL
58 AdventureWorks2014 DBlocks NULL

 Product 表的数码还未有LOCK,所以批管理实施select 操作得到了共享锁。SQL
SEVELacrosse风流倜傥旦读完数据之后立时释放分享锁。由此进行DBLoks试图时,上边提到的分享锁不设有了。

实例2: 可另行读隔开等级下的SELECT

SQL 批处理

 

图片 7图片 8

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
SELECT * FROM [Production].[Product] WHERE Name ='Reflector';
SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID
COMMIT TRAN

View Code

DBLocks 的出口结果

spid dbname entity_name index_id resource description
58 AdventureWorks2014 sysrowsets NULL OBJECT  
58 AdventureWorks2014 n/a NULL DATABASE  
58 AdventureWorks2014 Product 1 PAGE 0.590972222
58 AdventureWorks2014 DBlocks NULL OBJECT  
58 AdventureWorks2014 Product 1 KEY (b031e2166063)
58 AdventureWorks2014 Product 3 KEY (489415cc61a4)
58 AdventureWorks2014 Product NULL OBJECT  
58 AdventureWorks2014 Product 3 PAGE 3.626388889

鉴于Product表上有集中索引,数据行正是页级其他万事索引行,重临四个数据行上的锁是键锁,用来找寻有关数据。在Product表上存在Name上的非聚焦索引,能够通过Index_ID字段的值来分别聚焦所以和非集中索引,Index_ID=1表示数据行和集中索引,Index_ID=3表示非聚焦索引。由于作业的割裂等第是可另行读,分享锁要等到专门的学业结束结束。NOTE:索引行具有分享锁(S)而数据分页,索引分页以致表本人具备意向分享锁(IS)。

实例3:可串行隔开分离等级下的SELECT

图片 9图片 10

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM [Production].[Product] WHERE Name LIKE 'Reflect%';
SELECT * FROM [dbo].[DBlocks] WHERE spid= @@SPID
COMMIT TRAN

View Code

DBLocks的出口结果

 

spid dbname entity_name index_id resource description
58 AdventureWorks2014 sysrowsets NULL OBJECT  
58 AdventureWorks2014 n/a NULL DATABASE  
58 AdventureWorks2014 Product 1 PAGE 0.590972222
58 AdventureWorks2014 n/a NULL METADATA schema_id = 7
58 AdventureWorks2014 DBlocks NULL OBJECT  
58 AdventureWorks2014 Product 1 KEY (b031e2166063)
58 AdventureWorks2014 Product 3 KEY (489415cc61a4)
58 AdventureWorks2014 Product NULL OBJECT  
58 AdventureWorks2014 Product 3 PAGE 3.626388889
58 AdventureWorks2014 Product 3 KEY (53bdd09497c4)
58 AdventureWorks2014 NULL NULL OBJECT  

留下评论

网站地图xml地图