Multiple Transactions - SQL Server Forums

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)

Multiple Transactions

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2003-12-15 : 11:22:04

I have a stored procedure that consists of multiple transactions. They are not nested. I have twelve transactions within this stored procedure and each consecutive transaction is dependent upon the preceding one completing without fail.


create procedure test_trans

declare @sqlerror integer
set @sqlerror = 0

Begin Transaction No1
  update tblA
    set col1 = 'something'
    where col1 = 'nothing'

  set @sqlerror = @@error
  if @sqlerror <> 0
    begin
      rollback transaction No1
      return
    end
Commit Transaction No1

Begin Transaction No2
  update tblb
    set col1 = 'nothing'
    where col1 = 'something'

  set @sqlerror = @@error
  if @sqlerror <> 0
    begin
      rollback transaction No2
      return
    end
Commit Transaction No2

If transaction No1 fails does it rollback and move onto Transaction No2 or does it rollback and the stored procedure stop?

I'm using SQL 7.0

Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-15 : 11:27:02

the "return" command in the error checking block should cause the stored procedure to terminate and not move to the second transaction.

What if the first one succeeds, but then the second one fails? Is that acceptable?

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-15 : 11:33:06

As you have it there is no point in having the transactions as each statement is atomic. If @@error <> 0 then the statement will not have had an effect.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TJ
Posting Yak Master

201 Posts

Posted - 2003-12-15 : 11:37:28

quote:
What if the first one succeeds, but then the second one fails? Is that acceptable? - Jeff

Jeff,

That would be acceptable as long no concurrent transactions are processed. Is there a better way to write this?

It was reported to me that a transaction did not rollback and records were left in an incomplete state. I haven't been able to find anything in the code that would allow this type of behavior. We're in a testing mode right now and everything that was there to analyze is gone. I'm beginning my own testing this morning to hopefully re-create the errors and find the issue. Any other advice would be great!

Thanks for responding!
Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-15 : 11:40:20

Note that some errors will terminate the batch and there is nothing you can do about it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TJ
Posting Yak Master

201 Posts

Posted - 2003-12-15 : 11:43:34

quote:
Originally posted by nr

As you have it there is no point in having the transactions as each statement is atomic. If @@error <> 0 then the statement will not have had an effect.

Did I understand this correctly? What do you mean by atomic?


  update tblA
    set col1 = 'something'
    where col1 = 'nothing'

  set @sqlerror = @@error
  if @sqlerror <> 0
    return

  update tblb
    set col1 = 'nothing'
    where col1 = 'something'

  set @sqlerror = @@error
  if @sqlerror <> 0
    return

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

TJ
Posting Yak Master

201 Posts

Posted - 2003-12-15 : 11:49:56

quote:
Originally posted by nr

Note that some errors will terminate the batch and there is nothing you can do about it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NOTHING? That's a very bad thing!

Some changes were made to a table and I wasn't informed of them. Subsequently, the stored procedure was not updated to allow for the change. The error was '512'.. subquery returned more than one value when I tried to do a select. I was expecting one value and received two.

How can I tell which errors will cause this problem?

Thanks for helping!

Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-16 : 04:21:50

Atomic means that the statement will complete or have no affect i.e. it will never partially complete.

>> How can I tell which errors will cause this problem?
Don't think there is any way - you just have to allow for it.
For changes to a table the SP may well fail to compile and not run at all.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources