How to query all sql executed inside and outside a nested transaction

博雄 胡 475 Reputation points
2024-04-15T07:50:56.9633333+00:00

Follow the comments and execute the following SQL to complete the construction of the problem environment

/** Please ensure that the sessions are in READ COMMITTED **/
/** create session 1
create test object **/
create table testtable(id int)
insert into testtable values(1)
go

create proc P_test
as
begin
begin tran
if 1=1 return
commit
end

/** Manual execution "exec P_test" **/
exec P_test

/** Manual execution "update testtable set id = id+1" **/
update testtable set id = id+1

/** create session 2
Manual execution  "select * from testtable" **/
select * from testtable

How to find "exec P_test" or "exec P_test update table set id=id+1" instead of "update table set id=id+1" in a new session.

Because the issue of non commit transactions occurs on "exec P_test", when troubleshooting the problem, we want to find it as soon as possible instead of "update testable set id=id+1".

Sorry for my poor English. Thank you for reading and I look forward to your help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,817 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.