代码语言
.
CSharp
.
JS
Java
Asp.Net
C
MSSQL
PHP
Css
PLSQL
Python
Shell
EBS
ASP
Perl
ObjC
VB.Net
VBS
MYSQL
GO
Delphi
AS
DB2
Domino
Rails
ActionScript
Scala
代码分类
文件
系统
字符串
数据库
网络相关
图形/GUI
多媒体
算法
游戏
Jquery
Extjs
Android
HTML5
菜单
网页交互
WinForm
控件
企业应用
安全与加密
脚本/批处理
开放平台
其它
【
MSSQL
】
一个常见的select ,update ,insert ,delete动作要申请
作者:
/ 发布于
2016/6/21
/
592
-一个常见的select ,update ,insert ,delete动作要申请的锁 --两个表都加了索引的字段 --employeeID --managerid --modifieddate --------------------一个常见的select动作要申请的锁----------------------------------------------- --在可重复读的级别下,共享锁要保留到事务提交的时候才释放,所以如果 --这个隔离级别下开启一个事务,再运行一个查询语句,就能看到这个查询所申请的主要 --共享锁。因此可以使用这种简单方法分析一下一个查询语句会申请哪些锁,并且不需要 --SQL Trace --(1)在连接A中,将事务隔离级别设置为可重复读(repeatable read) --(2)在运行查询语句之前先开启一个事务 --(3)运行查询语句,但是不提交这个事务 --(4)在第二个连接里,查询sys.dm_tran_locks动态管理视图来分析查询结束后连接 --A还持有的锁 --我们先在有聚集索引的那张表上运行一句最简单的查询 在SSMS里新建一个查询--------- --查询一: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO --以下查询使用了聚集索引查找 ctrl+l BEGIN TRAN SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200) ------------------------------------------------------------------------------------------------------- --查看DMV看一下有多少个锁被这个连接持有----------------------------------------------------------- USE [AdventureWorks] --要查询申请锁的数据库 GO SELECT [request_session_id], c.[program_name], DB_NAME(c.[dbid]) AS dbname, [resource_type], [request_status], [request_mode], [resource_description],OBJECT_NAME(p.[object_id]) AS objectname, p.[index_id] FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id]=p.[hobt_id] LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid] WHERE c.[dbid]=DB_ID('AdventureWorks') ----要查询申请锁的数据库 ORDER BY [request_session_id],[resource_type] --查询一所持有的锁 --(1)因为连接正在访问数据库[AdventureWorks],所以在数据库一级加了一个共享锁,以防止 --别人将数据库删除 --(2)因为正在访问表格[Employee_Demo_BTree],所以在表格上加了一个意向共享锁,以防止 --别人修改表的定义 resource_type:object --(3)查询有3条记录返回,所以在这3条记录所在的聚集索引键上,分别持有一个共享锁。 --在这3个键所在的页面上,持有一个意向共享锁 resource_type:page,key 锁住整页 --可以说,这个查询申请锁的数目是很少的。其他用户访问同一张表,只要不访问这3条记录 --,就不会影响到。这是因为查询使用了“聚集索引查找”的关系 --------------------------------------------------------------------------------------------- --运行查询二:在SSMS里新建一个查询,运行之前记得将前面查询一的事务提交或者回滚 多次运行最后那句COMMIT TRAN --BEGIN TRAN --SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200) --COMMIT TRAN --查询二 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO BEGIN TRAN SELECT [EmployeeID], [LoginID], [Title] FROM [dbo].[Employee_Demo_Heap] WHERE [EmployeeID]=3 --COMMIT TRAN --运行之后最后记得加上COMMIT TRAN ---------------分析------------------------------------------- --因为[Employee_Demo_Heap]的[EmployeeID]上是一个非聚集索引,所以SQL在用非聚集索引 --找到这条记录之后,必须再到数据页面上把其他的行上面的数据找出来(所谓的“书签查找” bookmark lookup) --从申请的锁上也能看出来,虽然只返回一条记录,可是他在[PK_Employee_EmployeeID_Demo_Heap] --(index_id是2 表明是非聚集索引)上申请了一个key锁,在RID(datapage数据页上的行row) --申请了一个row锁。在这两个资源所在的页面上各申请了一个page意向锁 --与上面的例子比较,虽然查询二返回的结果和查询一是一样的,但是由于他使用的是非聚集索引+书签查找 --bookmark lookup,所以申请的锁的数目要比查询一多。一个查询要使用的索引键(或者RID)数目越多 --,他申请的锁也就会越多。没有用到的索引上不会申请共享锁 --那么是不是所有的查询都只在返回的记录上加锁呢?现在再来做下面这个试验,运行他之前 --请记得将前面那个事务提交或回滚 -------------------------------------------------------------------------------------- --修改一 :update语句:在SSMS里新建一个查询 --首先开启一个事务,修改一条查询不会返回记录 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO BEGIN TRAN UPDATE [dbo].[Employee_Demo_Heap] SET [Title]='aaa' WHERE [EmployeeID]=70 --再在另一个连接里运行查询三 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO BEGIN TRAN SELECT [EmployeeID], [LoginID], [Title] FROM [dbo].[Employee_Demo_Heap] WHERE [EmployeeID] IN(3,30,200) ---------------------分析-------------------------------------------------- --由于要返回3条分布在不同数据页上的记录,SQL认为做非聚集索引+书签查找并不比 --做一个表扫描快,所以他直接选择了一个表扫描。 --DMV显示查询三已经得到了RID 1:3181:2和1:3181:29上的锁,他们应该是EmployeeID为3和30 --的,他在往下找EmployeeID为200的时候,读到了RID 1:3208:22。他是EmployeeID为70, --被上面的update语句修改了,update那个事务还没有提交,所以查询三被阻塞了 --现在把修改一回滚,阻塞解除,查询三能够执行完毕,可以看到他的执行计划与他持有的锁 --SQL一直往下找 --书签 --70 ->还没有提交 --80 --90 --100 --. --. --. --. --. --. --200 --所以不能继续往下找,如果跳过的话,他不知道跳过的是不是就是他要找的那条记录,所以阻塞了 --一个RID对应一个EmployeeID --与查询一不同的是,查询三不但在这三条记录所在页面(1:3211,1:3181)申请了IS锁, --还在表格的所有页面上都申请了IS锁。所以这就是全表扫描,扫描了所有的数据页面带来 --的后果。更严重的是,查询三在扫描每一张页面的时候,会对读到的每一个数据记录加上 --一个共享锁(读完了这条记录就会释放,不用等到整个语句结束)只要有任何一个记录 --上的锁没有申请到,查询就会被阻塞 -------------------------------------------------------------------------------------------- --运行修改二,再运行查询一 --修改二:update语句:在SSMS里新建一个查询 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO BEGIN TRAN UPDATE [dbo].[Employee_Demo_BTree] SET [Title]='aaa' WHERE [EmployeeID]=70 --再在另一个连接里运行查询一 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO SET STATISTICS PROFILE ON GO --以下查询使用了聚集索引查找 ctrl+l BEGIN TRAN SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200) ---------------------------分析------------------------------------------------------------- --这是因为查询一使用的是索引查找index seek,不需要每条记录都读一遍,所以就不用 --去读EmployeeID70,也就不会被阻塞住 因为select是select书签里面的内容根本不用到表里去读取 --数据 --------------------------总结select动作-------------------------------------------------------- --规律:在非“未提交读”的隔离级别上 --已提交读 --可重复读 --可序列化 --(1)查询在运行过程中,会对每一条读到的记录或键值加共享锁。如果记录不用返回。 --那锁就会被释放。如果记录需要被返回,则视隔离级别而定,如果是“已提交读”,则也释放 --否则,不释放 --(2)对每一个使用到的索引,SQL也会对上面的键值加共享锁 --(3)对每个读过的页面,SQL会加一个意向锁 --(4)查询需要扫描页面和记录越多,锁的数目也会越多。查询用到的索引越多,锁的数目 --也会越多 --避免阻塞采取的方法 --(1)尽量返回少的记录集,返回的结果越多,需要的锁也就越多 --(2)如果返回结果集只是表格所有记录的一小部分,要尽量使用index seek,避免全表扫描这种 --执行计划 --(3)可能的话,设计好合适的索引,避免SQL通过多个索引才找到数据 --当然,这些对于“已提交读”以上隔离级别而言。如果使用“未提交读”,SQL就不会申请这些共享锁 --阻塞也不会发生 --------------------一个常见的update动作要申请的锁----------------------------------------------- --对于update语句,可以简单理解为SQL先做查询,把需要修改的记录给找到,然后在这个记录 --上做修改。找记录的动作要加S锁,找到修改的记录后加U锁,再将U锁升级为X锁。 --这里用上面两张表做例子,选用repeatable read的隔离级别,运行一个update语句 USE [AdventureWorks] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN UPDATE [dbo].[Employee_Demo_Heap] SET [Title]='changeheap' WHERE [EmployeeID] IN(3,30,200) --这个update语句在非聚集索引上申请了3个U锁,在RID上申请了3个X锁。这是因为语句借助非聚集索引 --PK_Employee_EmployeeID_Demo_Heap(index_id是2)找到了这3条记录。非聚集索引PK_Employee_EmployeeID_Demo_Heap --本身没有用到Title这一列,所以他自己不需要做修改。但是数据RID上有了修改,所以RID上加的是X锁,其他 --索引上没有加锁 --从这个例子可以看出,如果update借助了哪个索引,这个索引的键值上就会有U锁,没有用到的 --索引上没有锁。真正修改发生的地方会有X锁。对于查询涉及的页面,SQL加了IU锁意向更新锁,修改 --发生的页面,SQL加了IX锁 意向排他锁 (先查询再修改)锁key 锁索引键值 因为修改的列没有被索引 --如果修改的列被一个索引使用到了,会是什么情况呢?为了完成这个测试,先在Employee_Demo_BTree --上建一个会被修改的索引 CREATE NONCLUSTERED INDEX [Employee_Demo_BTree_Title] ON [AdventureWorks].[dbo].[Employee_Demo_BTree] ([Title] ASC) --再运行下面语句 USE [AdventureWorks] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN UPDATE [dbo].[Employee_Demo_Heap] SET [Title]='changeheap' WHERE [EmployeeID] IN(3,30,200) --语句利用聚集索引找到要修改的3条记录.但是我们看到有9个键上有X锁。 --很有意思:PK_Employee_EmployeeID_Demo_BTree(index_id=1)聚集索引,也是数据存放的地方。 --刚才做的update语句没有改到他的索引列,他只需把Title这个列的值改掉。所以在index1上, --他只申请3个X锁,每条记录一个 --但是表格在Title上面有一个非聚集索引IX_Employee_ManagerID_Demo_BTree(index_id=5), --并且Title是第一列。他被修改后,原来的索引键值就要被删除掉,并且插入新的键值。 --所以在index_id=5 上要申请6个X锁,老的键值3个,新的键值3个 --因为其他索引没有使用到Title这一列,所以他们上面都没有申请锁 --这就是9个key锁的来源 --------------------update语句的规律--------------------------------------------------------- --(1)对每一个使用到的索引,SQL会对上面的键值加U锁 --(2)SQL只对要做修改的记录或键值加X锁 --(3)使用到要修改的列的索引越多,锁的数目也会越多 --(4)扫描过的页面越多,意向锁也会越多。在扫描的过程中,对所有扫描到的记录也会加锁,哪怕 --上面没有修改 -----------------------------结论------------------------------------------------------- --想降低一个update语句被别人阻塞住的几率,除了注意他的查询部分之外,数据库设计者 --还要做的事情有: --(1)尽量修改少的记录集。修改的记录越多,需要的锁也就越多 --(2)尽量减少无谓的索引。索引的数目越多,需要的锁也可能越多 --(3)但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分,要尽量使用index seek索引查找 --避免全表扫描这种执行计划 --------------------一个常见的delete动作要申请的锁----------------------------------------------- --这次使用read committed这个默认隔离级别 USE [AdventureWorks] BEGIN TRAN DELETE [dbo].[Employee_Demo_BTree] WHERE [LoginID]='adventure-works\kim1' --可以看到delete语句在聚集索引(index_id=1)和两个非聚集索引(index_id=2和3)上各申请了一个X锁 --在她们所在的页面上申请了一个IX锁 --如果使用repeatable read这个级别运行上面的delete命令,就能看出好像做select的时候一样,做 --delete的时候SQL也需要先找到要删除的记录。在找的过程中也会加锁 --现在运行一个新的delete语句,会使用全表扫描 USE [AdventureWorks] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN DELETE [dbo].[Employee_Demo_Heap] WHERE [LoginID]='adventure-works\tete0' --可以看到delete语句在3个非聚集索引(index_id=2、3、4)上各申请了一个X锁。在她们 --所在的页面上申请了一个IX锁。在修改发生的heap数据页面上,申请了一个IX锁,相应的 --RID上(真正的数据记录)申请了一个X锁。其他扫描过的页面申请了IU锁 ----------------------------规律----------------------------------------------------------------- --(1)delete的过程是先找到符号条件的记录,然后做删除。可以理解为先是一个select,然后 --是delete.所以,如果有合适的索引,第一步申请的锁就会比较少 不用表扫描 --(2)delete不但是把数据行本身删除,还要删除所有相关的索引键.所以一张表上索引数目越多 --锁的数目就会越多,也就越容易发生阻塞 --为了防止阻塞,我们既不能绝对地不建索引,也不能随随便便地建立很多索引, --而是要建立对查找有利的索引.对于没有使用到的索引,还是去掉比较好 --------------------一个常见的insert动作要申请的锁----------------------------------------------- --相对于select,update,delete,单条记录的insert操作对锁的申请比较简单。SQL会为新插入 --的数据本身申请一个X锁,在发生变化的页面上申请一个IX锁。由于这条记录是新插入的,被 --其他连接引用到的概率会相对小一些,所以出现阻塞的几率也要小 --还是用刚才的那两张表做例子。首先要插入的是heap结构的表 USE [AdventureWorks] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN INSERT INTO [dbo].[Employee_Demo_Heap] ( [EmployeeID] , [NationalIDNumber] , [ContactID] , [LoginID] , [ManagerID] , [Title] , [BirthDate] , [MaritalStatus] , [Gender] , [HireDate] , [ModifiedDate] ) SELECT 501, 480168528, 1009, 'adventure-works\thierry0', 263, 'Tool Desinger', '1949-08-29 00:00:00.000', 'M', 'M', '1998-01-11 00:00:00.000', '2004-07-31 00:00:00.000' --如果插入的是有B树结构的表格 USE [AdventureWorks] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN INSERT INTO [dbo].[Employee_Demo_BTree] ( [EmployeeID] , [NationalIDNumber] , [ContactID] , [LoginID] , [ManagerID] , [Title] , [BirthDate] , [MaritalStatus] , [Gender] , [HireDate] , [ModifiedDate] ) SELECT 501, 480168528, 1009, 'adventure-works\thierry0', 263, 'Tool Desinger', '1949-08-29 00:00:00.000', 'M', 'M', '1998-01-11 00:00:00.000', '2004-07-31 00:00:00.000' --两者都申请了以下锁资源: --(1)数据库上的S锁(resource_type=DATABASE) --(2)表上的IX锁(resource_type=OBJECT) --(3)每个索引上都要插入一条新数据,所以有一个key上的X锁 --(4)在每个索引上发生变化的那个页面,申请了一个IX锁(resource_type=PAGE) --唯一不同的是,是在heap结构上还得申请一个RID锁。因为真正的数据不是放在索引上,而是放在heap数据页面上 ----------------------------------------结论------------------------------------------- --如果SQLDBA要控制SQL锁的申请和释放行为,以缓解阻塞和死锁问题,需要考虑的因素有: --1、事务隔离级别的选定 --事务隔离级别越高,隔离度就越高,并发度也就越差。如果选择了比较高的隔离级别,SQL --不可避免地要申请更多的锁,持有的时间也会增加。所以在设计应用的时候,一定要 --和用户谈好,尽量选择默认的隔离级别(read committed) --2、事务的长短和事务的复杂度 --事务的长度和复杂度决定论这个事务在SQL内部会持续多长时间,也能决定SQL会同时在 --多少张表和索引上申请和持有锁。事务越简单,就越不容易发生阻塞和死锁。所以这 --也必须和用户商量好,尽量避免在一个事务里做很多事情 --3、从应用整体并发度考虑,单个事务一次处理的数据量不能过多 --应用的性能,不单要衡量单个连接的处理速度,也要衡量在并发处理的情况下,整体 --的平均速度怎么样。从连接个体来讲,可能在一个事务里把数据一次都处理掉比较快 --但是如果处理的数据量很大,就会影响到其他连接同时访问同一对象。所以,如果 --一个应用的并发要求比较高,就一定要严格控制单个事务处理的数据量。如果有什么 --事务操作需要访问或修改表格内的大量数据,最好调整到并发用户比较少的时候运行 --4、针对语句在表格上设计合适的索引 --合适的索引能使SQL在读取尽可能少的数据量的前提下,把需要处理的数据找到。如果 --没有合适的索引,SQL在做select,update,delete的时候,会申请比要处理的目标数据量 --多得多的锁,从而导致阻塞或死锁。这种情形可以通过加索引的方式提高并发度 --同时,SQL在做update,insert,delete的时候,会对有关联的所有索引都做修改,在她们 --上面申请锁。从这个角度讲,索引越多,产生的锁的数目也就越多,阻塞和死锁的几率 --也就会越高 --所以数据库设计员需要做的,是要确保有足够的索引,防止语句做全表扫描,但是也要 --去掉那些对语句运行贡献不大的索引。不能随便往表格上加索引
试试其它关键字
锁
同语言下
.
SQL查询 多列合并成一行用逗号隔开
.
查看存储过程修改时间,最近执行时间
.
设置手动批量删除数据库相关进程
.
获取某个表中特定字段的所有字符串形式
.
SQL 如何去除重复的字符串
.
怎么去掉一个字段中的重复数据
.
String 去除空格 回车 换行 水平制表符
.
SQL查询和替换含有回车,空格,TAB
.
SQL SERVER 查询每日新增用户数量、次留数量
.
判断两个字符串是否存在相同的内容
可能有用的
.
C#实现的html内容截取
.
List 切割成几份 工具类
.
SQL查询 多列合并成一行用逗号隔开
.
一行一行读取txt的内容
.
C#动态修改文件夹名称(FSO实现,不移动文件)
.
c# 移动文件或文件夹
.
c#图片添加水印
.
Java PDF转换成图片并输出给前台展示
.
网站后台修改图片尺寸代码
.
处理大图片在缩略图时的展示
贡献的其它代码
Label
Copyright © 2004 - 2024 dezai.cn. All Rights Reserved
站长博客
粤ICP备13059550号-3