none
如何监控孤立的事务 RRS feed

  • 问题

  • 前段应用程序执行某个操作之后,由于报错或者超时,导致会话状态变成了sleeping,但是开启的事务并没有提交或者回滚。目前能抓取到这个会话的SPID,和最后一次执行的语句。有没有什么好的办法能抓取到这个会话执行的完整的sql,以便迅速定位问题。因为只抓到最后一次执行的语句有时候定位问题太难了。

    方法1 开启profiler  筛选事务ID>0的,缺点:抓取的数据太多,对性能产生影响

    方法2,等问题出现的时候,通过抓取阻塞信息,获取SPID,然后根据SPID再去开profiler。缺点:需要人为干预

    这个问题一直没有很好的办法来解决。

    2017年12月28日 2:13

答案

  • EXEC sp_configure 'user options', 16384; 
    GO 
    RECONFIGURE ;

    如果你原来设置过user options,把值或上16384设置进去。


    想不想时已是想,不如不想都不想。

    2018年1月25日 3:18
    版主

全部回复

  • 先监控长事务,发现苗头后再做跟踪或扩展事件,没好办法

    也可以跟MS建议,加个开关,保存事务的完整SQL,当然,事务提交或回滚后就清理掉


    SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴

    2017年12月28日 3:27
  • 一直是我头疼的问题,,,看看其他人有没有好办法

    2017年12月28日 3:42
  • select * 
    from sysprocesses p
    	outer apply sys.dm_exec_sql_text(P.sql_handle)
    where spid > 50 and open_tran > 0 and status = 'sleeping'

    2017年12月28日 3:42
  • 感谢回复,这个语句也只能抓出最后一次提交的语句,不能抓出完整的SQL
    2017年12月28日 4:01
  • 感谢回复,这个语句也只能抓出最后一次提交的语句,不能抓出完整的SQL

    完整的 SQL  只能开 profile 了,再根据时间点和 SPID 去对应,通常没什么必要

    DBCC INPUTBUFFER(spid) 结合我的那个查询的信息,通常足以定位程序了,毕竟你程序某个功能会用到什么 SQL,通常还是比较能够对应的,不至于弄一个可以提交任意 SQL 的应用程序

    2017年12月28日 6:08
  • 有时候只能抓到一些无关紧要的语句,或者这个语句在程序里面出现的频率非常高,,,这样排查问题就很不方便了

    2017年12月28日 6:40
  • 针对你这个情况,我建议你对频繁出现的相似sql做一个标记

    update xx set f='a' where zz='x' --我是标记1
    
    update xx set f='a' where zz='x' --我是标记2
    
    update xx set f='a' where zz='x' --我是标记....
    这样就很容易定位执行位置了。

    family as water

    2017年12月28日 7:12
  • 嗯,非常不错的思路。,但是如果这个语句有几十个以上就很麻烦了,另外有时候抓取到的语句是那种毫无特征性的

    2017年12月28日 7:19
  • 感谢回复,这个语句也只能抓出最后一次提交的语句,不能抓出完整的SQL

    完整的就算 profile 也头疼的,比如就随便在连接中开一个事务,然后一句一句慢慢提交处理需求,等提交完几千个请求之后,我就什么也不做了,也不提交事务,这时候你拿到抓的数据结果也又能怎么样

    所以完如果考虑极端情况,那确实是很难的,只能说尽量通过诸如加注释,区分帐号,app, host, client PID 这些手段去缩小范围,再通过小范围的 profile + 业务特征去定位吧

    2017年12月28日 8:35
  • 因为我们公司是做数据库监控产品的。在监控客户的系统的时候,经常出现这个问题。现实是:账号(他们一般都不区别经常用一个).APP ,host 这些有时间抓不到。其实就算这些抓到了也很恼火。因为代码不是我们的。客户当然希望一下就能够准确定位到问题.如果是自己维护的系统,当时很好说。人为的跟几次,肯定能定位清楚。

    2017年12月28日 9:35
  • 那确实没什么好办法,像我说的那种极端情况很抓瞎

    如果仅从实现的角度,可以考虑程序做 profiler,这个 smo 有提供相关的类

    你可以程序开一个 profiler, 接收 trace 到的数据,然后判断语句是否开事务了,如果开了,把相同 spid + host + client PID 的后续数据记录下来,如果后续数据有 commit,那么释放

    如果后续没有读到 commit, 在达到预设的空闲时间后查 sysprocess 看是否孤立事务,如果是,那么这个数据存下来并 kill  进程

    这样应该能够抓到完整的数据供分析,但 profile 显然对服务器是有压力的,所以其实并不建议长时

    2017年12月28日 10:11
  • 我比较倾向于设置成出错自动回滚

    想不想时已是想,不如不想都不想。

    2018年1月16日 9:55
    版主
  • 是想要出错自动回滚,但是某些程序代码忘记加入try catch 这样的异常捕捉,导致无法自动回滚。现在是想找到这样的代码
    2018年1月19日 5:42
  • 你不是有transaction吗?可以设置成事务中出错自动回滚的。

    想不想时已是想,不如不想都不想。

    2018年1月19日 9:40
    版主
  • 你不是有transaction吗?可以设置成事务中出错自动回滚的。

    想不想时已是想,不如不想都不想。

    你说的设置是在什么地方设置,存储过程还是代码里面
    2018年1月23日 7:00
  • sp_configure 里面设

    存储过程/代码里面也可以,但是就得每段代码都去加。直接系统全局设置比较方便。


    想不想时已是想,不如不想都不想。

    2018年1月23日 10:17
    版主
  • 具体是哪个值能说下吗,谢谢
    2018年1月25日 0:47
  • EXEC sp_configure 'user options', 16384; 
    GO 
    RECONFIGURE ;

    如果你原来设置过user options,把值或上16384设置进去。


    想不想时已是想,不如不想都不想。

    2018年1月25日 3:18
    版主
  • 非常不错的想法,之前没这么想过,,这个全局选项,对于程序中SQL的语句出现错误(不是存储过程的)也同样有效吗
    2018年1月25日 8:30
  • 如果程序没有去set option,就有效。当然,前提是有事务,否则也没法回滚前面的。

    话说你没去看过 sp_configure 有些啥内容吗?


    想不想时已是想,不如不想都不想。

    2018年1月26日 3:26
    版主
  • 很多之前看过的,也忘记了,,熟悉的是常用的那几个。这个选项我之前真没用过。之前都是在会话级别使用。SET XACT_ABORT
    2018年1月26日 3:45
  • 有空可以翻翻文档,不用记住,大概知道要用的时候可以去哪儿找。

    想不想时已是想,不如不想都不想。

    2018年1月26日 5:26
    版主
  • 再请教一个问题,这个选项在生产上面开过了吗
    2018年3月29日 12:12