Nested transactions causing problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested transactions causing problems

Author

Topic

KlausJensen
Starting Member

14 Posts

Posted - 2004-01-20 : 07:40:13

Hi!

I have two transactions (simplified):

sp_Buy
sp_Pay

I use sp_Pay in sp_Buy to make a money-transaction.

Both sp's have transactions in them, to make sure either everything or nothing is committed.

Overview:

sp_Buy BEGIN TRANSACTION
--sp_Pay BEGIN TRANSACTION
---- (here some business-rule fails so I need to roll back and return an custom errorcode to sp_Buy)
--sp_Pay ROLLBACK TRANSACTION
sp_Buy ROLLBACK TRANSACTION

So what happens is...

1. sp_Buy starts a transaction (BEGIN TRANSACTION)

2. sp_Pay is called from sp_Buy (within the transaction)

3. sp_Pay starts a transaction (BEGIN TRANSACTION)

4. sp_Pay checks the user does not have enough money and rolls back (ROLLBACK TRANSACTION), returning an output param back to sp_Buy (no error!)

5. sp_Buy recieves the output param from sp_Bay and wants to do a rollback. (ROLLBACK TRANSACTION)

Pretty straight forward stuff, I thought... But no, I get this error...:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

Am I missing something? Is it not possible to nest two transactions like this?

I have spent nearly two days battling this problem, and I seem stuck... I would appreciate any help. :)

Thanks in advance!

- Klaus

---
Klaus Jensen
Developer

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-01-20 : 07:58:20

Try adding names to your transactions, nested do need them. Read about it in BOL under "transactions, nested".

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-20 : 08:06:19

begin tran tr1
insert into towns select 'xxx', 'xxxxxx'

save tran tr2
insert into towns select 'ooo', 'oooooo'
if {something wrong} rollback tran tr2

commit tran tr1

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-20 : 08:43:01

quote:
5. sp_Buy recieves the output param from sp_Bay and wants
to do a rollback. (ROLLBACK TRANSACTION)

Your case even simpler than I thought: you need NO rollback transaction in sp_buy!
It's already rolled back by the "rollback tran" from inside of sp_pay.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-21 : 00:13:36

>>Your case even simpler than I thought: you need NO rollback transaction in sp_buy!
It's already rolled back by the "rollback tran" from inside of sp_pay.

Stoad is correct. In nested transactions a ROLLBACK TRAN without a transaction name will rollback the current transaction and any transactions that started it. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27001

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources