wns9778.com_威尼斯wns.9778官网

热门关键词: wns9778.com,威尼斯wns.9778官网
wns9778.com > 计算机教程 > SQL Server学习笔记之事务、wns9778.com锁定、阻塞、

原标题:SQL Server学习笔记之事务、wns9778.com锁定、阻塞、

浏览次数:96 时间:2020-03-18

本文实例讲述了SQL Server学习笔记之事务、锁定、阻塞、死锁用法。分享给大家供大家参考,具体如下:

 SQL Server 2008中SQL应用系列--目录索引

1、事务

隐式事务

一、锁的基本概念:

/*==================================================================当以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table语句首先执行的时候,SQL Server会话自动打开一个新的事务,如果在会话中激活了隐式事务模式,那么这个事务会一直保持打开状态,直到rollback或commit语句这个事务才结束,如果忘记提交事务,那么在相应的隔离级别下,事务占用的锁可能不会释放,因此尽量不要用隐式事务。====================================================================*/--会话1set implicit_transactions onupdate tset v = 'ext12'set implicit_transactions offselect @@TRANCOUNT --输出:1,说明事务没有释放 --占用的X独占锁不会释放,会阻塞其他会话

--会话2,被会话1阻塞住了,不会返回任何记录select *from t

锁定(Locking)是一个关系型数据库系统的常规和必要的一部分,它防止对相同数据作 并发更新 或在更新过程中查看数据, 从而保证被更新数据的完整性。它也能防止用户读取正在被修改的数据 。Sql Server动态地管理锁定,然而,还是很有必要 了解Transact- SQL查询如何影响SQL Server中的锁定。在此,简单介绍下锁的基本常识。

在会话1中执行commit来提交事务,那么会话2马上就会返回记录了。

锁定有助于防止并发问题的发生。当一个用户试图读取另一个用户正在修改的数据,或者修改另一个用户正在读取的数据时,或者尝试修改另一个事务正在尝试修改的数据时,就会出现并发问题。

现在把两个会话的执行顺序调换一下:

SQL Server资源会被锁定,资源的锁定方式称作它的锁定模式(lock mode),下表列出SQL Server处理的主要锁定模式:

--会话1set implicit_transactions on --打开了隐式事务select *from tset implicit_transactions offselect @@TRANCOUNT --输入:1,说明这个会话中的事务也没有提交

--会话2,会话2没有被会话1阻塞,--之所以这样是因为会话的默认隔离级别是read committed,--会话1中的事务虽然没有提交,但是select语句在这种隔离级别下,--运行完就会释放占用的S共享锁,所以不会阻塞写操作update tset v = 'ext'

名称

描述

共享 (S) 用于不更改或不更新数据的读取操作,如 SELECT 语句。
更新 (U) 用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排他 (X) 用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。
意向 用于建立锁的层次结构。 意向锁包含三种类型:意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
架构 在执行依赖于表架构的操作时使用。 架构锁包含两种类型:架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。
键范围 当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

显示数据库最早的活动事务

可以锁定SQL Server中的各种对象,既可以是一个行,也可以是一个表或数据库。可以锁定的资源在粒度(granularity)上差异很大。从细(行)到粗(数据库)。细粒度锁允许更大的数据库并发,因为用户能对某些未锁定的行执行查询。然而,每个由SQL Server产生的锁都需要内存,所以数以千计独立的行级别的锁也会影响SQL Server的性能。粗粒度的锁降低了并发性,但消耗的资源也较少。下表介绍SQL Server可以锁定的资源:

/*==============================================================如果事务在数据库中始终打开,有可能会阻塞其他进程的操作,为什么是有可能而不是一定呢,原因就是:在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。另外,日志备份也只会截断不活动事务的那部分日志,所以活动的事务会导致日志数据越来越多。为了找到没有提交的事务,可以用下面的命令显示某个数据库最早的活动事务.不过有个例外,就是下面的命令不会返回:不占用锁资源的未提交事务================================================================*/begin tran --开始显示事务select *from t --运行后立即释放共享锁select @@TRANCOUNT --输入:1,说明没有提交事务dbcc opentran('wc') --显示数据库最早的活动事务, --但是这儿显示"没有处于打开状态的活动事务"

资源

说明

KEY 索引中用于保护可序列化事务中的键范围的行锁。
PAGE 数据库中的 8 KB 页,例如数据页或索引页。
EXTENT 一组连续的八页,例如数据页或索引页。
HoBT 堆或 B 树。 用于保护没有聚集索引的表中的 B 树(索引)或堆数据页的锁。
TABLE 包括所有数据和索引的整个表。
FILE 数据库文件。
RID 用于锁定堆中的单个行的行标识符。
APPLICATION 应用程序专用的资源。
METADATA 元数据锁。
ALLOCATION_UNIT 分配单元。
DATABASE 整个数据库。

通过会话来查询事务信息

不是所有的锁都能彼此兼容。例如,一个被排他锁锁定的资源不能被再加其他锁。其他事务必须等待或超时,直到排他锁被释放。被更新锁锁定的资源只能接受其他事务的共享锁。被共享锁锁定的资源还能接受其他的共享锁或更新锁。

--由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁,--所以dbcc opentran命令并没有返回这个活动事务,--不过下面的视图解决了这个问题,可以找到所有活动事务。--找到活动事务select session_id, --session_id与transaction_id的对应关系 transaction_id, is_user_transaction, is_localfrom sys.dm_tran_session_transactions --会话中的事务,识别所有打开的事务where is_user_transaction =1--找到活动事务对应的执行语句select c.session_id, --session_id与connection_id的对应关系 c.connection_id, c.most_recent_sql_handle, s.textfrom sys.dm_exec_connections c --执行连接,最近执行的查询信息cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) swhere c.session_id = 361--活动事务的具体信息select t.transaction_id, t.name, --这里显示user_transaction t.transaction_begin_time, case t.transaction_type --事务类型 when 1 then '读/写事务' when 2 then '只读事务' when 3 then '系统事务' when 4 then '分布式事务' end 'transaction type', case t.transaction_state when 0 then '事务尚未完全初始化' when 1 then '事务已初始化但尚未启动' when 2 then '事务处于活动状态' when 3 then '事务已结束。该状态用于只读事务' when 4 then '已对分布式事务启动提交进程' when 5 then '事务处于准备就绪状态且等待解析' when 6 then '事务已提交' when 7 then '事务正在被回滚' when 8 then '事务已回滚' end 'transaction state'from sys.dm_tran_active_transactions t --活动的事务where transaction_id = 150764485

SQL Server自动分配和升级锁。升级意味着细粒度的锁(行或页锁)被转化为粗粒度的表锁。当单个T-SQL语句在单个表或索引上获取5000多个锁,或者SQL Server实例中的锁数量超过可用内存阈值时,SQL Server会尝试启动锁升级。锁占用系统内存,因此把很多锁转化为一个较大的锁能释放内存资源。然而,在释放内存资源的同时会降低并发性。

2、锁定

SQL Server 2008带来了新的表选项,可以禁用锁升级或在分区(而不是表)范围启用锁升级。

当一个用户要读取另一个用户正在修改的数据,或者一个用户正在修改另一个用户正在读取的数据,或者一个用户要修改另一个用户正在修改的数据,就会出现并发问题。锁定能防止并发问题。

资源的锁定方式称为锁定模式,SQL Server中的锁定模式:共享锁,意向锁,更新锁,排他锁,架构稳定锁,架构修改锁,大批量更新锁,键范围锁。不是所有锁模式都是兼容的,如:一个加了排他锁的资源不能再加其他锁,其他事务必须等待,直到释放排他锁。

二、查看锁的活动

可以锁定SQL Server中的各类对象,可以锁定的资源在粒度上差异很大,从细粒度(行、键)到粗粒度(数据库)。细粒度的锁允许用户能查询那些未被锁定的行,并发性更高,但是需要更多的锁资源(每个被锁定的行都需要一个锁资源);粗粒度的锁降低了并发性,但需要的锁资源很少。

下面演示一个实例,它使用sys.dm_tran_locks动态视图监视数据库中锁的活动。

在SQL Server中可锁定的资源:

打开一个查询窗口,执行如下语句:

DB(数据库) Metadata(系统元数据) Object(数据库对象:视图,函数,存储过程,触发器) Table(表) Hobt(堆或B树) Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面) Extent(8个8KB的页面) Page(8KB数据页面) Rid(行标示符对应一个堆表的行) Key(键范围上的锁、B树中的键)FileApplication
USE AdventureWorks
BEGIN TRAN
SELECT ProductID, ModifiedDate
FROM Production.ProductDocument
WITH (TABLOCKX)

查看锁的活动

打开另一个查询窗口,执行:

select resource_type, --资源类型 resource_database_id, --资源所在的数据库id resource_associated_entity_id, --数据库中与资源相关联的实体的 ID。 --该值可以是对象ID、Hobt ID 或分配单元 ID, --具体视资源类型而定 object_name(resource_associated_entity_id,resource_database_id), resource_lock_partition, --已分区锁资源的锁分区ID。对于未分区锁资源值为 0 resource_description, --资源的说明,其中只包含从其他资源列中无法获取的信息 request_session_id, --请求资源的会话 request_type, --请求类型,该值为 LOCK request_mode, --请求的模式,对于已授予的请求,为已授予模式, --对于等待请求,为正在请求的模式(锁定模式) request_status --请求的当前状态, --可能值为 GRANTED、CONVERT 或 WAITfrom sys.dm_tran_locksWHERE request_session_id = 361
SELECT request_session_id sessionid,
resource_type type,
resource_database_id dbid,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,
request_mode rmode,
request_status rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT')

控制表的锁升级

执行结果:

每个锁都会消耗内存资源,当锁的数量增加时,那么所需要的内存就会增加,而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值,SQL Server会将细粒度锁升级为粗粒度锁,这个过程就是锁升级。

/*
sessionid    type    dbid    objectname    rmode    rstatus
51    DATABASE    4    NULL    S    GRANT
52    DATABASE    4    NULL    S    GRANT
53    DATABASE    8    NULL    S    GRANT
56    DATABASE    8    NULL    S    GRANT
53    OBJECT    8    ProductDocument    X    GRANT
*/

锁升级的优点是可以减少锁的数量,相应的减少内存的使用量,而缺点是由于锁住了更大的资源,所以会导致阻塞,降低并发性。

解析:本示例中,我们首先启动了一个新事务,并使用TABLOCKX锁提示(这个提示对表放置了排他锁),对Production.ProductDocument表执行了一个查询。查询sys.dm_tran_locks动态管理视力可以监视当前SQL Server实例中打开了哪些锁。它返回了AdventureWorks数据库中活动锁的列表。可以在结果中的最后一行看到ProductDocument表上的排他锁。

--默认值,不管是不是分区表,会在表级别启用锁升级ALTER TABLE tSET (lock_escalation = TABLE)--当表升级时,如果表已经分区,会在分区级别启用锁升级ALTER TABLE tSET (lock_escalation = auto)--在表级别禁用锁升级,如果用了TabLock提示或在Serializable隔离级别下查询,还是会有表锁ALTER TABLE tSET (lock_escalation = disable)

前三列定义了会话锁、资源类型和数据库ID。第四列使用了Object_Name函数,注意它使用了两个参数(对象ID和数据库ID)来指定访问哪个名称(第二个参数是SQL Server 2005 SP2引入的,它用来指定为了转换对象名称而使用哪个数据库)。同时也查询锁定请求模式和状态,最后,From子句引用DMV,用Where子句指定了两个资源类型。Resource_Type指定了锁定的资源类型,如DatabaseObjectFilePageKeyRIDExtentMetadataApplicationAllocation_Unit或HOBT类型。依赖资源类型的resource_associated_entity_id,确定ID是object ID, allocation unit ID, 或Hobt ID。

本文由wns9778.com发布于计算机教程,转载请注明出处:SQL Server学习笔记之事务、wns9778.com锁定、阻塞、

关键词: wns9778.com

上一篇:sqlserver 数据库被注入解决方案

下一篇:没有了