SQL Server Performance Forum – Threads Archive
Transaction Handling
I have one typical issue related to Transaction handling. I am running sequence of SP(Almost 10 sp) in one transaction. My sample code is as below Create proc P_DocalculationAs Exec p_sp1 Exec p_sp2 ….
Exec p_sp10
GO In each sp#%92s I have progress statement written which will update status of each sp execution successfully to table SP_Status (spname, status) which contain data like spname status
p_sp1 successfully executed
…………………………………………….. And from front end my application will read the data from this table and display it to user regarding the progress of the execution. Now I planned to include the transaction handling which is as below Create proc P_Docalculation
As Begin Tran Exec p_sp1 Exec p_sp2 ….
Exec p_sp10 If @@Error<>0 Begin Rollback Tran End Commit Tran
Go Now my issue is that till the time all is getting committed I will not get the data in SP_Status table and I can#%92t display the status of each sp execution to front application. Please provide your inputs to fix the issue. Thanks and Regards Ravi K Note: To understand the issue I have used example of sp execution status, there may be counter number updates using batch sp#%92s
What’s more important: reporting the status on-the-fly, or the ability to rollback the whole transaction? THink of it that way, then it’s no longer a problem but a priorities issue.
Is there is no way to get both functionality even by setting up ISOLATION LEVEL
I think the problem is that you can’t ask SQL Server to maintain a transaction and ask for status information in between – that would break the transaction. The connection is ‘hung’ until the transaction completes or fails. You could try including some PRINT commands, which may come out in QA, but I’m not sure they would come out immediately. Anyway, it is always recommended not to include PRINT commands in stored procedures.
Here is a trick:
http://sqlteam.com/item.asp?ItemID=2290 Using sp_OA and SQL-DMO to make a new connection to SQL server and use that for loging during the transaction.
Maybe this will also help:http://www.umachandar.com/technical/SQL6x70Scripts/Main70.htm —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
How about using noLock hint for reading progress info?
The issue here is that the status info would be rollbacked as well if the transaction is rollbacked. So you execute another connection to SQL server to get around it.
I don’t think the problem is rollback. When transaction is rolled back, transaction info should be rolled back as well. The problem is that without noLock hint or executing by different connection, progress updates will not be available untill transaction completes. I prefer noLock hint, because in case of rollback, progress data will be rolled back too, which is more correct IMO.
Depends on what info you are logging. If it’s a status log I wouldn’t want it to be rollbacked. I use this procedure in a few transfer jobs that involve multiple steps. That way I can see which parts that fail if problem occurs. If the status log was rollbacked I couldn’t identify which part that failed.
The more I think of it, the more I wonder why there is no ‘OUTSIDE TRANSACTION’ exception thingy for queries to mimick the sp_OA* stuff mentioned above (great info, by the way).
Argyle: I agree with your disccussion. However I thought the only purpose of logging steps here was allowing client to see the progress.
Adriaan: In ORACLE you can simply configure inside transaction to be autonomous. This is one of a few features where mssql server 2005 is still behind oracle at least as far as i know.
]]>