Transaction Handling

Question

Transaction Handling

Hemantjemmy

Old Hand

Points: 316
More actions
August 22, 2005 at 3:59 am

#363474

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_Docalculation
As
Exec p_sp1
Exec p_sp2 ….
Exec p_sp10
GO
In each sp’s 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’t display the status of each sp execution to front application. Please provide your inputs to fix the issue.
Note: To understand the issue I have used example of sp execution status, there may be counter number updates using batch sp’s

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

xblondel Right there with Babe Points: 768 More actions · Answer 1

Hi!

Actually, things are written in your SP_Status table, but this latter is locked and you cannot read it from another transaction until the writing one is committed. This is the baseline, nevertheless, if you use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the start of the transaction reading SP_Status, you should get the contents of it.

Please also note that, if you rolloback, SP_Status will be empty, since it will also have be rollbacked.

BTW, two points on your code:

1. You should check errors after calling each of your stored procedures.

2. Better pout your "commit tran" in an else clause, lest you will rollback, then try to commit (and fail!)

Bye,

Xavier

Max Mulawa SSC Enthusiast Points: 174 More actions · Answer 2

U can try to start transaction on client,than run each SP from client,and finally commit transaction on client or rollback if sth will go wrong.

If u r using .NET this might be helpful: SqlConnection.BeginTransaction Method

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

having the @@error at the end will only check if the last stored procedure executed resulted in any error or not...it will commit even if the previous procedures resulted in errors...is this what you want ?!

at any rate, it is always better to store the @@error result in a variable and check after the execution of each of the stored procedures!

**ASCII stupid question, get a stupid ANSI !!!**

Hemantjemmy Old Hand Points: 316 More actions · Answer 4

Hello Everbody,

Thanks for your reply. I got another way to fix above issue. To read status of sp execution i will be writing SELECT queries WITH (NOLOCKS) option. which will provide me uncommited data read from data pages.