SQLSERVER Negative SQL Server SPIDs(Unit of Work ID)
来源:互联网 发布:java hadoop 读取文件 编辑:程序博客网 时间:2024/05/22 06:15
一、孤立事务产生的场景和机制
当MSSQL数据库是所在的windows server 启用了MSDTC 服务,从应用或者客户端请求数据库访问并获取数据集的过程中,应异常中断导致SESSION未正常释放或异常断开,会产生这类孤立的SESSION(即如果应用程序调用了一个开启了事务操作的存储过程,当发生异常的时候,有可能会出现数据库连接关闭,而存储过程中的事务既没有提交,也没有回滚的情况)。
二、概念
官方给的说法是从2008 至今版本,定义UOW 是一个分布式事务引起的
UOW:
标识分布式事务的工作单元 ID (UOW - Unit of Work ID) Dentifies the Unit of Work ID (UOW) of distributed transactions. UOW is a GUID that may be obtained from the request_owner_guid column of the sys.dm_tran_locks dynamic management view. UOW also can be obtained from the error log or through the MS DTC monitor. For more information about monitoring distributed transactions,Unit of Work ID which is a 24-character GUID that is assigned to the transaction by MSDTC.
A session with SPID equal to -2 is an orphaned distributed transaction. A distributed transaction is a database transaction that calls databases hosted on different servers. Orphaned, also called in-doubt MSDTC transactions, are a Microsoft Distributed Transaction with unknown transactional state, as the MSDTC service is not able to identify it correctly due to a MSDTC service crash or unplanned restart.
UOW:
标识分布式事务的工作单元 ID (UOW - Unit of Work ID) Dentifies the Unit of Work ID (UOW) of distributed transactions. UOW is a GUID that may be obtained from the request_owner_guid column of the sys.dm_tran_locks dynamic management view. UOW also can be obtained from the error log or through the MS DTC monitor. For more information about monitoring distributed transactions,Unit of Work ID which is a 24-character GUID that is assigned to the transaction by MSDTC.
A session with SPID equal to -2 is an orphaned distributed transaction. A distributed transaction is a database transaction that calls databases hosted on different servers. Orphaned, also called in-doubt MSDTC transactions, are a Microsoft Distributed Transaction with unknown transactional state, as the MSDTC service is not able to identify it correctly due to a MSDTC service crash or unplanned restart.
三、Troubshooting
3.1使用 sys.dm_tran_active_transactions, sys.dm_tran_locks 等MV查询到UOW ID并kill,在使用SP_WHO2 查找block session会发现SPID为 -2,
然后使用KILL { session ID | UOW } [ WITH STATUSONLY ],此时直接 kill -2 肯定会报错。通过下述方法找到UOW 的实际GUID后:
USE Master;
GO
SELECT
DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks
WHERE request_session_id =-2
GO
--kill ‘GUID’,如:
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF';
GO
SELECT
DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks
WHERE request_session_id =-2
GO
--kill ‘GUID’,如:
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF';
3.2 使用MSDTC Monitor Trace跟踪
3.3 Action Plan
1)客户端层面和SQL Server层面的.NET版本都请升级到最新。
2)SQL Server端根据需要,在出现孤立事务的时候,根据微软官方选择如下设置:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/in-doubt-xact-resolution-server-configuration-option
阅读全文
0 0
- SQLSERVER Negative SQL Server SPIDs(Unit of Work ID)
- LUW (Logical Unit of Work)
- Unit of Work in ORM
- Unit Of Work--工作单元
- Unit of work, Transactions and Grails
- 企业模式之Unit Of Work模式
- 企业模式之Unit Of Work模式
- Entity Framework Repository & Unit Of Work T4 Template on CodePlex
- 对工作单元——Unit of Work的理解
- Unit Of Work Pattern(ORM Transaction Controller) Implementation Sample
- Axon Framework工作单元,The Unit of Work
- 【翻译】在Entity Framework 4.0中使用 Repository 和 Unit of Work 模式
- Using Repository and Unit of Work patterns with Entity Framework 4.0
- ASP.NET MVC3.0+ JqGrid+Unit Of Work+ Repository/ EF 4.1 CRUD应用 (多层结构)
- Revisiting the Repository and Unit of Work Patterns with Entity Framework
- Null or zero primary key encountered in unit of work clone exception not working 解决方法
- SQL Server Backup Planning Work Sheet...
- Index of sql server
- 数值归一化
- java web框架 spring+spring mvc+mybatis/hibernate项目总结(1)
- 【第8章】数据的归宿
- UVA10214TreesInAWood
- 关键字结构体的介绍以及typede与define的区别
- SQLSERVER Negative SQL Server SPIDs(Unit of Work ID)
- Vivado中几种仿真模式比较
- Java并发编程:Lock
- java从hdfs中下载文件到本地
- 传值,传指针,传引用的一些事
- Big Event in HDU(多重背包问题)
- WPF PasswordBox样式(占位符,清除按钮 )
- 【leetcode】【套路题】476. Number Complement
- iOS与Unity3D、Arkit框架混编