笔记48-徐 阻塞问题定位方法

笔记48-徐 阻塞问题定位方法

  1 --阻塞问题定位方法
  2 --阻塞是事务隔离带来的副作用,是一个数据库系统常见的现象。如果
  3 --阻塞持续的时间非常短,可能对性能的影响不会很大。所以在遇到性能问题
  4 --的时候,DBA或应用开发者要首先确认性能问题是不是由于阻塞直接导致
  5 --,以及阻塞在多大程度上影响了SQL的性能。不要一看到SQL里发生了阻塞,就
  6 --认为他是性能问题的主要原因
  7 
  8 --1、哪些应用出了问题,这些应用是在使用同一个数据库还是不同的数据库?
  9 --一般来讲,一个SQL里会有多个数据库。每个数据库可能在支持多个应用程序
 10 --假如其中一个数据库出现了阻塞,而我们关注的那个应用没有使用他,很有
 11 --可能这个阻塞和我们关注的性能问题没关系,无须做太多分析
 12 --但是如果有其他应用和关注的应用共享同一个数据库,而这个数据库上发生
 13 --了阻塞,那DBA就不能只关注一个应用上。因为阻塞很有可能因为不同应用之间
 14 --的相互作用导致的
 15 
 16 --2、应用端问题的现象
 17 --应用端问题表征对定位问题非常重要。不是所有问题都是阻塞导致的。但是
 18 --很多时候数据库有阻塞,应用也能运行得很好
 19 
 20 --如果应用遇到一些错误,那至少要得到应用从数据库端收到的错误原文是什么。
 21 --有时候应用会对错误封装,或者没有准确捕捉到错误原文。如果遇到这样情况
 22 --可能有必要对应用修改,要求他把错误的详细原文打印出来
 23 
 24 --如果应用端的现象是长时间没有响应(hang)或很慢,那首先要从应用端分析。
 25 --他是运行到哪一步以后才出问题,这一步是不是在做数据库操作,是不是等待
 26 --数据库返回。造成应用没有响应或响应很慢,数据库只是可能的因素之一。如果
 27 --直接跳进数据库性能分析,会有忽视其他因素的风险
 28 
 29 --3、什么因素让你认为阻塞是问题的主要原因?
 30 --得到错误信息后,要确认这个错误是否是SQL返回的。一般阻塞会导致命令不能及时
 31 --完成。所以得到的错误应该是运行时错误。如果错误不是SQL返回的,或者不是运行超时
 32 --那么问题的直接原因不是阻塞,要从其他角度去分析问题
 33 
 34 
 35 --4、阻塞发生的特征
 36 --如果怀疑阻塞是问题原因,要搞清楚阻塞本身的特征:阻塞每天在什么时间发生
 37 --每次持续多久,是不是应用负载越重,阻塞越严重,还是在运行某些特定任务的
 38 --的时候才容易发生阻塞,阻塞发生后,是自动消失,还是必须要重启SQL才能解决
 39 
 40 --如果确定是阻塞和当前问题相关,DBA要用以下问题来定位:
 41 --(1)SQL里有没有阻塞发生,是什么时候发生,在哪个数据库上,阻塞发生在那个表
 42 --哪些资源上
 43 --(2)和阻塞有关的连接是从哪些客户应用来的
 44 
 45 --(3)为什麽阻塞会发生
 46 --现在阻塞发生在哪个或哪些资源上
 47 --阻塞的源头是在做什么事情时候申请了这些锁,为什麽会申请这些锁
 48 --阻塞的源头当前的状态是什么,是一直在执行,还是已经进入空闲状态
 49 --如果他一直在执行,为什麽要执行这麽久
 50 --如果已经进入空闲状态,那为什么没有释放锁资源
 51 --其他阻塞的连接他们想要做什么?为什麽也要申请这些锁资源
 52 
 53 --SQL提供了很多信息帮助DBA回答上面问题,SQL2005以后又有新的改进
 54 
 55 --首先在范例数据库上造成一个阻塞,用SSMS建立两个连接
 56 --连接1:
 57 USE [AdventureWorks]
 58 BEGIN TRAN
 59 UPDATE [dbo].[Employee_Demo_Heap]
 60 SET [Title]='aaa'
 61 WHERE [EmployeeID]=70
 62 UPDATE [dbo].[Employee_Demo_BTree]
 63 SET [Title]='aaa'
 64 WHERE [EmployeeID]=70
 65 
 66 --连接2:
 67 USE [AdventureWorks]
 68 SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_Heap] WHERE [EmployeeID] IN (3,30,200)
 69 
 70 --之前说过,由于连接2会选取一个表扫描的执行计划,所以被连接1阻塞住
 71 
 72 -------------------正式开始-------------------------------------------------------------------------
 73 
 74 --1、运行
 75 SELECT * FROM master.[sys].[sysprocesses]
 76 --sysprocesses是一张非常重要的系统管理视图
 77 --重要字段
 78 --spid :SQL会话也叫SQL进程ID,这是SQL内部对一个连接的编号,一般来讲,小于50
 79 --的是SQL系统会话,大于50的是用户连接
 80 
 81 --kpid:Windows线程ID threadID
 82 
 83 --blocked:正在阻塞请求的会话的ID。如果此列为0,则表示请求未被阻塞,以下这些小于0
 84 --的值,有特殊含义
 85 -- -2:阻塞资源由孤立的分布式事务拥有
 86 -- -3:阻塞资源有延迟的恢复事务拥有
 87 -- -4:由于内部锁latch状态转换而无法确定阻塞锁所有者的会话ID
 88 
 89 
 90 --waittyep:当前连接的等待资源编号。如果进程没有等待,值是0x0000。等待资源的含义参考11章
 91 
 92 --waittime:当前等待时间(毫秒) 0的意思是进程没有等待
 93 
 94 --lastwaittype:指示上次或当前等待类型名称的字符串
 95 
 96 --waitsource:锁资源的文本化表示法
 97 
 98 --dbid:当前正由进程使用的数据库ID
 99 
100 --uid:执行命令的用户ID
101 
102 --login_time:客户端进程登录到服务器的时间。对于系统进程,将存储SQL的启动时间
103 
104 --last_batch:客户端进程上次执行远程存储过程调用或execute语句的时间。对于系统
105 --进程,将存储SQL的启动时间
106 
107 --open_tran:进程的打开事务个数。如果有嵌套事务,就会大于1
108 
109 --status:进程ID状态。可能的值有:
110 --dormant:SQL正在重置会话
111 --running:会话正在运行一个或多个批处理。多个活动的结果集MARS启用后,会话可以运行多个批处理
112 --background:会话正在运行一个后台任务,例如死锁检测
113 --rollback:会话正在处理事务回滚
114 --pending:会话正在等待工作线程变为可用
115 --runnable:会话中的任务在等待获取scheduler来运行的可执行队列中
116 --spinloop:会话中的任务正在等待自旋锁变为可用
117 --suspended:会话正在等待事件(如I/O)完成
118 
119 --hostname:建立连接的客户端工作站的名称
120 
121 --program_name:应用程序的名称
122 
123 --hostprocess:建立连接的应用在客户端工作站里的进程ID
124 
125 --cmd:当前正在执行的命令
126 
127 --nt_domain:客户端的Windows域(如果使用Windows身份验证)或可信连接的Windows域
128 
129 --nt_username:进程的Windows用户名(如果使用Windows身份验证)或可信连接的Windows用户名
130 
131 --net_address:为每个用户工作站的网络适配器的唯一标识符。当用户登录时,该标识符插入
132 --net_address列
133 
134 --net_library:用于存储客户端网络库的列。每个客户端进程都在网络连接上进入。常用
135 --常用的网络库有LPC(shared memory),tcp/ip,name pipe
136 
137 --loginname:登录名
138 
139 
140 
141 
142 
143 --2、数据库上面有没有阻塞发生,哪些连接发生了阻塞,是谁阻塞住了谁
144 --查找[sysprocesses]的blocked字段不为0,如果不为0,而且也不是-2,-3,-4
145 --那他就是被这个字段值的那个连接给阻塞住了。一般来讲,阻塞源头的blocked
146 --字段会是NULL。如果他也不等于0,说明他也被别人阻塞,要继续查找阻塞住他的连接
147 
148 
149 --如果你发现一个连接的blocked字段的值等于他自己,那倒不说明什么问题。常常是因为
150 --这个连接正在做磁盘读写,他要等自己的I/O做完。
151 
152 --什么时候开始的?
153 --这里只要看waittime,就可以知道此次阻塞发生的时间。如果你运行多次查询,每次
154 --被阻塞住的SPID waittime都很短,那说明单个阻塞持续的时间都不是很长。阻塞
155 --可能还不是很严重(当然这也会影响响应速度)
156 
157 --范例结果SPID53的waittye是557421,也就是说已经阻塞了557.421秒,这是一个发生
158 --时间很长的阻塞。如果真的发生在生产环境里,这种阻塞的影响会很大
159 
160 
161 --在哪个数据库上?
162 --一般检查dbid即可。得到dbid以后,可以运行以下查询得到数据库的名字
163 SELECT name,dbid  FROM sys.[sysdatabases]
164 
165 
166 --阻塞在哪个表上,哪些资源上?
167 --可以运行sp_lock,在结果集中寻找状态是wait的锁资源。或者直接运行下面的查询
168 --得到一样的结果
169 SELECT 
170 CONVERT(SMALLINT,[req_spid]) AS spid,
171 [rsc_dbid] AS dbid,
172 [rsc_objid] AS objid,
173 [rsc_indid] AS indid,
174 SUBSTRING(v.[name],1,4) AS TYPE,
175 SUBSTRING([rsc_text],1,32) AS RESOURCE,
176 SUBSTRING(u.[name],1,8) AS mode,
177 SUBSTRING(x.[name],1,5) AS status
178 FROM master.[dbo].[syslockinfo],
179 master.[dbo].[spt_values] v,
180 master.[dbo].[spt_values] x,
181 master.[dbo].[spt_values] u
182 WHERE master.[dbo].[syslockinfo].[rsc_type]=v.[number]
183 AND v.[type]='LR'
184 AND master.[dbo].[syslockinfo].[req_status]=x.[number]
185 AND x.[type]='LS'
186 AND master.[dbo].[syslockinfo].[req_mode]+1=u.[number]
187 AND u.[type]='L'
188 AND SUBSTRING(x.[name],1,5)='WAIT'
189 ORDER BY [SPID]
190 ------------------------------------------------------------------------------
191 EXEC [sys].[sp_lock]
192 SELECT DB_NAME(1)
193 SELECT OBJECT_NAME(1115151018)
194 
195 --数据库名字和表名可以使用DB_NAME和OBJECT_NAME函数
196 --查询索引名字
197 SELECT * FROM sys.[indexes] WHERE [object_id]=1115151018 --
198 
199 --以下是对不同种类的资源含义的解释,只有了解他们的意思,才能真正看懂阻塞
200 --RID:格式为fileid:pagenumber:rid的标识符,其中fileid标识包含页的文件,
201 --pagenumber标识包含行的页,rid标识页上的特定行。fileid与sys.databases_files
202 --目录视图中的file_id列相匹配
203 --例如:例子中有个正处于wait状态的RID Resource是1:6681:26
204 --他的意思是在第一个数据文件上的第6681页上的第26个行上的锁资源。如果要查看
205 --这个页面上到底有哪些数据,可以考虑使用DBCC PAGE() 第一章
206 
207 
208 --KEY:数据库引擎内部使用的十六进制数。这个值和sys.partions.hobt_id相对应
209 --出现这种资源说明锁是在一个索引上面。通过查询sys.partitions视图里相应的
210 --object_id和index_id就能找到这个索引
211 --例子里有个KEY(46000227c460), 他的dbid是9,object_id是834674039,index_id是1
212 --从上面sys.indexes的结果,就可以知道他是PK_Employee_EmployeeID_Demo_BTree这个
213 --索引
214 
215 
216 --PAG:格式为fileid:pagenumber的数字,其中fileid标识包含页的文件,pagenumbe标识页
217 --EXT:标识区中的第一页的数字。该数字的格式为fileid:pagenumber
218 --TAB:没有提供信息,因为已在Objid列中标识了表
219 --DB:没有提供信息,因为已经在dbid列中标识了数据库
220 --FIL:文件的标识符,与sys.database_files目录视图中的file_id列相匹配
221 
222 EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname
223 --通过这些资源的名字,读者可以找到阻塞发生在哪个对象的哪种资源上
224 
225 
226 --和阻塞有关的连接是从哪些应用来的?
227 --[检查]:sysprocesses的结果提供了很多信息告诉用户一个连接是从什么客户端连接
228 --过来的。
229 
230 --hostname:建立连接的那台机器叫什么名字
231 --program_name:建立的程序叫什么名字
232 --hostprocess:运行程序的进程在他的机器上ID是多少。program_name是应用告诉SQL的
233 --名字。有些应用如果自己没有特别的名字,那很可能会是.NET SQLCLIENT DATA PROVIDER
234 --之类的通用名字。需要到任务管理器通过进程ID找到应用程序到底是哪一个
235 
236 --loginname,nt_domain,nt_username:从这些字段可以知道连接是用什么帐号连入SQL的
237 
238 --net_address:客户端机器的MAC地址。有时候应用程序没有告诉SQL自己的信息,
239 --hostname,program_name之类的字段会是空的。这时候只能通过MAC地址来找是哪台
240 --客户机连上来的
241 
242 --net_library:连接是使用哪个网络协议连上来的。一般为tcp/ip,named pipe,LPC(local procedure call)
243 
244 --1、阻塞的源头是在做什么事情的时候申请了这些锁?为什麽会申请这些锁
245 --这个问题比较复杂。锁可能是会话正在运行中的语句申请的,但也可能是这个会话
246 --在先前开启了一个事务,一直都没有提交或回滚,锁资源是事务开启后的任何一个
247 --语句申请的,当时阻塞可能还没有发生
248 
249 --如果是前者只要捉住连接发过来的最后一句话即可。如果是后者,则要在阻塞发生之前
250 --预先开启SQL Trace,一直跟踪到问题发生。
251 --如果阻塞问题已经发生而跟踪没有开启,那就没有办法知道事务是怎麽开启的,以及
252 --锁是什么语句申请的。
253 
254 --所以有时候要捉住阻塞问题的根源,必须下决心在出问题之前就开启服务器端跟踪,等到问题
255 --重现为止
256 
257 --如果没有跟踪,可以运行一些脚本得到某个连接当前正在运行的语句,和空闲连接上次
258 --运行的最后一条语句
259 
260 
261 
262 --正在运行中的语句
263 --下面这个查询返回所有正在运行中的连接和他正在运行的语句,如果一个连接处于空闲状态,就
264 --不会被返回
265 SELECT  p.[session_id] ,
266         p.[request_id] ,
267         p.[start_time] ,
268         p.[status] ,
269         p.[command] ,
270         p.[blocking_session_id] ,
271         p.[wait_type] ,
272         p.[wait_time] ,
273         p.[wait_resource] ,
274         p.[total_elapsed_time] ,
275         p.[open_transaction_count] ,
276         p.[transaction_isolation_level] ,
277         SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
278                   ( CASE WHEN p.[statement_end_offset] = -1
279                          THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
280                          ELSE p.[statement_end_offset]
281                     END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
282         p.[statement_end_offset] ,
283         batch = qt.[text]
284 FROM    master.sys.[dm_exec_requests] p
285         CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
286 WHERE   p.[session_id] > 50
287 
288 
289 --空闲连接上次运行的最后一条语句
290 --运行DBCC INPUTBUFFER(SPID)可以获得从客户端发送到SQL实例的最后一个批处理语句
291 --这句话的优点是不管连接是否正在运行,都会返回结果。缺点是他返回的是整个批处理
292 --语句,而不是当前正在执行的子句。所以对于正在运行的连接,第一种方法比较好
293 DBCC INPUTBUFFER(58)
294 
295 --2、阻塞的源头当时是什么状态,是一直在执行还是已经进入空闲状态
296 --[检查]一个最简单的方法,是看sysprocesses里面的kpid和waittype两个字段
297 --如果两个都是0,就是处于空闲状态,如果不都是0,或者连接正在运行
298 --或者因为资源等待而暂时挂起
299 
300 --3、如果一直运行,为什麽执行这麽久?
301 --[检查]如果一个连接的kpid值不是0(连接拿到了一个线程资源),waittype值是0
302 --(他不需要等待任务资源),他的状态就会是runnable或running。如果一个连接
303 --的kpid值不是0,waittype值也不是0,则说明他要等待某个资源才能继续执行。这时候
304 --连接的状态一般会是suspended
305 
306 --4、如果已经进入空闲状态,那为什么没有释放资源
307 --[检查]如果一个连接的kpid值是0(连接没有占用线程资源),waittype值也是0
308 --他不需要等待任务资源,那么这个连接已经完成了客户端发过来的所有请求,现在
309 --进入空闲状态。正在等待客户端发送新的请求
310 
311 --如果这是他还是阻塞源头,一般是他有先前开启的事务没有及时提交。这可以通过
312 --检查sysprocesses里的open_tran字段是否大于0确认。通过
313 DBCC INPUTBUFFER(58)
314 --也可以知道最后发过来的那句话是什么
315 
316 
317 --5、其他阻塞的连接他们想要做什么?为什麽也要申请这些锁资源
318 --使用以下脚本
319 SELECT  p.[session_id] ,
320         p.[request_id] ,
321         p.[start_time] ,
322         p.[status] ,
323         p.[command] ,
324         p.[blocking_session_id] ,
325         p.[wait_type] ,
326         p.[wait_time] ,
327         p.[wait_resource] ,
328         p.[total_elapsed_time] ,
329         p.[open_transaction_count] ,
330         p.[transaction_isolation_level] ,
331         SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
332                   ( CASE WHEN p.[statement_end_offset] = -1
333                          THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
334                          ELSE p.[statement_end_offset]
335                     END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
336         p.[statement_end_offset] ,
337         batch = qt.[text]
338 FROM    master.sys.[dm_exec_requests] p
339         CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
340 WHERE   p.[session_id] > 50
341 --去比较sp_lock的结果,就能大致判断他申请的锁数量是否合理
342 --如果不合理,可以通过优化语句,加合适索引解决
343 
344 
345 
346 
347 --5种常见的阻塞类型
348 --类型          waittype          open_tran     status      自我修复          
349 --1              不为0             >=0           runnable    是的,当语句运行结束之后
350 --类型1:由于语句运行时间太长而导致阻塞,语句本身正常运行,只需等待某些系统资源
351 --解决方法:
352 --DBA要与数据库应用设计人员合作,共同解决以下问题:
353 --(1)语句本身有没有可优化的空间?
354 --这里包括修改语句本身复杂度,修改表设计,调整索引
355 --(2)SQL整体性能如何,是不是有资源瓶颈影响了语句执行速度?
356 --当SQL遇到诸如内存,硬盘读写,CPU等资源瓶颈时,原来能很快完成的语句有可能会花很长时间
357 
358 --(3)如果语句天生就很复杂,无法调优(很多处理报表的语句就是这样)就需考虑怎样
359 --把这一类应用(一般就是数据库仓库应用)从OLTP系统中隔离出来
360 
361 
362 --类型       waittype        open_tran     status          自我修复          
363 --2           ox0000            >0         spleeping      不能,要运行KILL语句   在SQL Trace里能够看到一个ATTENTION事件
364 
365 --类型2:由于一个未按预期提交的事务导致的阻塞
366 --这一类阻塞的特征,就是问题连接早就进入了空闲状态(sysprocesses.status='sleeping'和sysprocesses.cmd=
367 --awaiting command)但是如果检查open_tran,就会发现他不为0,事务没有提交。这类问题很多是因为
368 --应用端遇到了一个执行超时,或者其他原因,当时执行的语句被提前终止了,但是连接还保留着。应用
369 --没有跟随发来的事务提交或回滚,导致一个事务被遗留在SQL里
370 
371 --一般ADO或ADO.NET的连接默认超时时限是30秒。如果30秒以内SQLSERVER没有完成语句返回任何结果,客户端
372 --会发送一个ATTENTION的消息给SQL,告诉SQL他不想继续等下去了。SQL收到这个消息之后,会终止当前正在
373 --运行的语句(或批处理),但是为了维护客户端的逻辑,SQL默认不会自动回滚或提交这个连接已经打开的
374 --事务,而是等待客户端的后续决定。如果客户端不发来回滚或提交指令,SQL会永远把这个事务保持下去
375 --直到客户端断开连接为止
376 
377 --实验:
378 USE [AdventureWorks]
379 GO
380 BEGIN TRAN
381 SELECT * FROM [Person].[Address] WITH(HOLDLOCK)
382 SELECT * FROM sys.[objects] s1,sys.[objects]s2
383 COMMIT TRAN
384 
385 --由于使用了holdlock参数,第一句select会在运行结束之后,在表格上维持一个TAB的S锁。
386 --如果批处理全部完成,这个锁会在提交事务的时候释放。但是第二句select会执行很久。
387 --请在等待3~4秒以后取消执行。然后运行下面的语句,检查open_tran和锁的情况
388 SELECT @@TRANCOUNT
389 GO
390 [sys].[sp_lock]
391 GO
392 
393 --(1)批处理被取消之后,commit tran这条语句没有被执行到,SQL没有对begin tran开启的那个事务做
394 --任何处理,只是保持其活动状态
395 --(2)第一句select带来的锁由于事务没有结束,所以锁还保持着objectid=55671246,type=TAB,mode=S
396 --现在如果有其他连接要修改这张表,就会被阻塞住
397 
398 --解决办法:
399 --应用程序必须意识到任何语句都有可能意外终止的情况,做好错误处理工作,这些工作包括
400 --(1)在做SQL调用的时候,需加上错误捕捉和处理语句
401 --SQL客户端驱动程序(包括OLEDB和ODBC)当语句执行遇到意外终止时(包括超时)的时候,都会
402 --向应用程序返回错误信息.客户端在捕捉错误信息时,除了做记录以外(这对问题定位很重要)
403 --运行下面这句话把没有提交的事务回滚掉
404 IF @@TRANCOUNT>0 ROLLBACK TRAN
405 
406 --有些程序员会问,我在TSQL批处理里已经写了TSQL层面的错误捕捉和处理语句
407 IF @@ERROR<>0 ROLLBACK TRAN
408 --还有必要让应用程序再做一遍吗?需要意识到,有些异常(比如超时)终止的是整个TSQL批处理语句
409 --的执行。而不仅仅是当前语句。所以,当这些异常发生的时候,TSQL层面的错误捕捉和处理语句很可能
410 --也一起被取消了。他们不能发挥想象中的作用。在应用程序里的错误捕捉和处理语句是必不可少的
411 
412 --(2)设置连接属性
413 SET XACT_ABORT ON
414 --当为ON时,如果执行TSQL语句产生运行错误,则整个事务将终止并回滚
415 --当为OFF时,处理方法是不唯一的。有时只回滚TSQL语句,而事务继续进行
416 --如果错误很严重,即使SET XACT_ABORT 为OFF,也会回滚整个事务
417 --OFF是默认值
418 --如果没有办法很快规范应用程序的错误捕捉和处理语句,一个最快的方法就是在每个连接建立以后
419 --或者是最容易出问题的存储过程的开头,运行SET XACT_ABORT ON,让SQL帮助应用程序回滚事务

https://social.msdn.microsoft.com/Forums/zh-CN/b1370f2d-41f8-4907-bace-2bb5a184d9db/xactabort?forum=sqlserverzhchs
基本上你只有在SSMS里面才能达到这个默认效果。
大部分程序里面自动就设置为ON了。


420 421 422 --(3)考虑是否需要关闭连接池 423 --一般SQL应用都会使用连接池来得到良好的性能。如果有一个连接忘记把事务关闭就退出连接 424 --那么这个连接会被交还给连接池,但是这时候,事务不会被清理。客户端驱动程序会在这个 425 --连接下一次被重用的时候(又有新的用户要建立连接),发一句sp_reset_connection命令(这是一个存储过程) 426 --清理当前连接上次遗留下来的所有对象,包括回滚未提交的事务。如果连接交还给连接池以后 427 --很久都没有被重用,那他的事务就会持续很长时间,引起阻塞。有些JAVA程序使用的驱动程序 428 --提供连接池功能,但是不提供连接重用时的事务清理功能。这样的连接池对应用开发质量 429 --要求很高,比较容易发生阻塞 430 --如果不能很快实施建议(1)和(2),把连接池关闭能缩短事务持续时间,也能从一定程度上 431 --缓解阻塞问题 432 433 --2、分析为什麽会遇到异常终止 434 --这里又谈到错误信息记录了。有了错误信息,就可以判定是超时问题,还是其他SQL错误。 435 --如果是超时问题,可按照第一种阻塞进程处理 436 437 --还有一种孤儿事务的来源,是连接开启了隐式事务(implicit transaction)而没有加入及时 438 --提交事务的机制。如果连接处于隐式事务 439 SET IMPLICIT_TRANSACTIONS ON 440 --并且连接当前不在事务中,则执行下列任何一条语句都会开启一个新的事务 441 --会开启新事物的语句 442 --alter table fetch revoke 443 --create grant select 444 --delete insert truncate table 445 --drop open update 446 447 448 --对于因为此设置为ON而自动打开的事务,SQL会自动帮你打开事务,但是不会自动帮你提交 449 --用户必须在该事务结束时将其显式提交或回滚。否则,当用户断开连接时,事务及其 450 --包含的所有数据更改将被回滚。事务提交后,执行上述一条语句又会开启一个新事物 451 --隐式事务将始终生效,直到连接执行 452 SET IMPLICIT_TRANSACTIONS OFF 453 --语句使连接恢复为自动提交模式,在自动提交模式下,所有单个语句都会自动提交 454 --不会有事务遗留 455 456 --为什麽会有连接要开启隐式事务呢?除了程序员有意之外,很多是客户端的数据库连接驱动 457 --或者控件为了实现他的事务功能(注意不是SQL通过TSQL语句直接提供的)而选用这个机制 458 --如果应用程序出现意外,或者脚本没有处理好,会有应用层事务未提交的现象。在SQL里 459 --也体现为一个孤儿事务。严格约束应用层对事务的使用,直接使用SQL里面的事务,是避免 460 --这种问题出现的好方法 461 462 463 --类型3: wait type open_tran status 自动修复 464 --类型3:oxooo,oxo800,oxoo63 >=0 runnable 不能,直到客户把所有结果都主动取走或断开连接或KILL但是要花 465 --长达30秒 客户端没有及时把所有结果取走,这时可能open_tran=0,事务隔离级别也为默认 466 --read committed,但这个连接还会持有锁资源 467 468 --类型3:由于客户端没有及时把结果集取出而导致的语句长时间运行 469 --语句在SQL内执行总时间不仅包含SQL的执行时间,还包含把结果集发给客户端的时间 470 --如果结果集比较大,SQL会分几次打包发出,每发一次,都要等待客户端确认。只有 471 --确认之后,SQL才会发送下一个结果集包。所有结果都发完以后,SQL才认为语句执行 472 --完毕,释放执行申请的资源(包括锁资源) 473 474 --如果出于某种原因,客户端应用处理结果非常慢甚至没有响应,或者干脆不理睬SQL 475 --发送结果集的请求,则SQL会耐心地等待,因此会导致语句长时间执行而产生阻塞 476 477 --解决办法: 478 --(1)设计程序时,一定要慎重返回大结果集。这种行为不仅会对SQL和网络带宽带来 479 --很大负担,对应用程序来说,也要花很多资源去处理结果集。如果最终用户只需要 480 --部分结果集就可以,则在发送SQL指令时就要指定好。避免语句不管三七二十一 481 --所有数据都要,而结果集只取走开头一部分去展示这样的行为发生 482 483 --(2)如果应用程序的确需要返回大结果集,例如一些报表系统,则要考虑报表数据库 484 --和生产数据库分开 485 486 --(3)如果1和2在短期内不能实现,可以和最终用户协商,返回大结果集的连接使用 487 --read uncommitted事务隔离级别,这样查询语句就不会申请S锁了 488 489 490 --类型4: wait type open_tran status 自动修复 491 --类型4: oxoooo >0 rollback 是的 在SQL Trace里能够看到这个SPID已经发来了一个 492 --ATTENTION事件,说明客户端已经遇到了超时,或者主动要求回滚 493 494 --类型4:阻塞的源头连接一直处于rollback状态 495 --这种情况是由第一类情况衍生出来的。有时候DBA发现一个连接阻塞住了别人,为了 496 --解决问题,会让连接主动退出(强制退出应用,或者直接在SQL端KILL掉连接)。 497 --对于大部分情况,这些措施会消除阻塞,但是要记住,不管是在客户端退出还是 498 --服务点KILL,为了维护数据库事务一致性。SQL都会对连接还没有来得及完成提交 499 --的事务做回滚操作。SQL要找到所有当前事务修改过的记录,把他们改回原来 500 --的状态。所以,如果一个delete,insert,update已经运行了一个小时,可能回滚也 501 --需要一个小时。在这个过程中,阻塞还会延续,我们只能等待 502 503 --有些用户可能等不及,直接重启SQL。当SQL关闭的时候,回滚操作会被中断 504 --SQL会被很快关掉。但是这个回滚动作在下次SQL重启的时候会重新开始 505 --(恢复数据库的时候),重启的时候如果回滚不能很快结束,整个数据库都不可用 506 --可能会带来更严重的后果 507 508 --解决办法; 509 --最好的方法就是在工作时间尽量不要做这种大的修改操作,这些操作尽量安排 510 --在半夜或周未的时间完成。如果操作已经做了很久,最好耐心等他做完。 511 --如果一定要在工作负荷的时候做,最好把一个大操作分成若干个小操作分步完成 512 513 514 --类型5: wait type open_tran status 自动修复 515 -- 各种值都有可能 >=0 runnable 不能,直到客户把所有结果都主动取走或断开连接或KILL但是--要花长达30秒 应用程序运行中产生死锁,在SQL中,以阻塞形式体现,sysprocesses里阻塞和被阻塞的连接 516 --hostname值是一样的 517 518 --类型5:应用程序运行中产生死锁,在SQL中以阻塞形式体现 519 --一个客户端的应用在运行过程中会使用到许多资源,包括线程资源,信号量资源,内存资源,I/O资源等 520 --SQL也是资源之一。如果发生死锁的两端不全是SQL,SQL的死锁判断机制可能不起作用。这时 521 --如果没有处理好,可能会永远等待下去。而SQL内部的表现可能仅仅是一个阻塞,但是 522 --这个阻塞不会自动消除。这样的阻塞对SQL的性能会产生很大影响 523 524 --下面举两个应用端死锁的例子 525 --(1)在应用的一个线程中开启不止一个数据库连接而产生死锁 526 --假设应用有一个线程有这样的逻辑: 527 --开始运行 528 --建立数据库连接A,调用存储过程ProcA,打开结果A 529 --建立数据库连接B,调用存储过程ProcB,打开结果B 530 --轮流读取结果集A,B,整合输出最终结果 531 --关闭结果集A,B,关闭连接A,B 532 --结束运行 533 534 535 536 --应用端死锁 537 --连接A 运行ProcA 调用ProcA获取结果集A 538 --连接B 运行ProcB 调用ProcB获取结果集B 539 540 --连接A 获取结果集A取到一行之后,去取结果集B 所有结果都取完之后关闭两个连接 541 542 --在正常情况下,这种设计没有问题,但是实际上很脆弱。因为在线程内部,这个逻辑 543 --是线性执行。假设存储过程ProcA是一个事务,在返回结果集之前因为一些操作申请了 544 --一些排他锁,而ProcB为了返回结果又要用到这些锁,那会发生什么情况呢? 545 --发生的情况会是连接A在等线程把连接B上的结果读出来,再来处理结果集A,而连接B 546 --等待连接A完成事务后再释放锁。双方相互等待,产生死锁 547 548 549 550 551 --(2)两个线程间的死锁 552 --如果应用有两个线程,每个线程各开一个数据库连接,那上面的逻辑不会出问题。 553 --因为运行ProcA的那个线程会先做完,释放阻塞住连接B的锁,让B也能够接着跑完。 554 --但是假设有下列逻辑: 555 556 --线程A:建立数据库连接A,不断读取表A,按条取出记录,做一定处理后发给线程B 557 --的输入缓存 558 559 --线程B:建立数据库连接B,从输入缓存读取数据,依据收到的记录对表A进行修改 560 561 --这个逻辑会产生什么问题呢?我们知道表格修改会在表上申请一些排他锁。如果 562 --线程A正在读取这条记录,修改动作会被阻塞住。这时候线程B就会进入等待状态。 563 --但是线程A需要线程B把输入缓存清空后才能写入。如果线程B还没得及清空,他 564 --也不得不等待,这时候也会产生死锁(在SQL里是一个阻塞) 565 566 567 --线程B不能清空缓冲,线程A读到的数据放不进来 568 --线程B把缓冲区的结果做处理,修改表A数据 569 --线程A将表A里的结果取出来放在缓冲区里 570 --线程A的查询阻塞住了线程B的修改动作 571 572 573 --解决方法: 574 --为了避免死锁,应用在调用SQL的时候设置执行超时,并写好错误处理机制,一旦 575 --死锁发生,SQL的操作在等待一段时间后会因为超时而放弃,并且释放出SQL内部的 576 --资源,解决死锁 577 578 579 --小结: 580 --解决阻塞需要在应用程序层面做很多工作,不能光从SQL端努力是不能解决阻塞问题的 581 --例如应用在做连接的时候选择什么隔离级别,事务开始和结束的时间点选择,连接的 582 --建立和回收机制,指令复杂度的控制等。应用程序还应该考虑到控制结果集的大小, 583 --并及时从SQL端取走数据。还要考虑SQL指令执行时间长短控制,以及发生超时或其他 584 --意外后的错误处理机制等。尤其是对高并发,高响应要求的关键业务系统,在设计应用 585 --选择的是能够满足业务需求的最低隔离级别,事务大小已经控制到了最小的粒度。 586 --而运行的语句也要有良好的数据库设计,保证他不会随着数据库的增大和用户量的增多 587 --占用更多资源和运行时间。 588 589 --如果做不到这几点,那么当用户量和数据量增大的时候,性能也越来越慢 590 591 --有时候一些应用会利用现成的数据库控件,以提高开发效率。某些控件会把数据库访问 592 --功能打包,例如什么时候建立连接,什么时候开启事务,数据结果集用什么方式取回, 593 --都由控件底层完成,对开发者透明。如果是要求开发性能比较高的数据库应用,必须 594 --仔细评估这些控件的处理机制。例如,有些控件会默认开启隐式事务(implicit transaction) 595 --使得事务的长度大大加长。有些控件会大量使用数据库端游标,让SQL必须为这些 596 --游标维护相应的资源。有些控件默认使用Unicode传递参数,妨碍了非Unicode字段 597 --上的索引的使用。有些控件甚至默认使用最高的事务隔离级别,使得并发度很低。 598 --这些都需要开发者做到心知肚明,达到扬长避短的效果 599 600 601 602 --对于一个设计良好的应用程序,SQL是完全有能力支持高并发的繁忙的业务系统的。 603 --本人曾多次看到每秒能够处理完3000甚至4000多个批处理(BATCH)的SQL应用系统 604 --前端并发的SQL连接数目(不考虑连接池等客户端机制帮助SQL减少实际并发用户数目) 605 --可以超过2000多个并发连接。这种系统一般来讲设计得比较精细,事务控制紧凑, 606 --程序错误控制严密,并且严格分离了OLTP系统和数据仓库系统这两类需求,从而 607 --严格控制指令的复杂度,有些还会通过定期归档的方式控制OLTP数据库的大小 608 --这些都能够帮助一个数据库应用有效地避免阻塞问题

 

posted @ 2013-07-27 16:24 桦仔 阅读(...) 评论(...)  编辑 收藏