is transaction safe within store procedure?

Hello,
Is it safe to do this in a store procedure? Please share your comments or
suggestions. Thanks!
create procedure PerformAtomicDataCheck
@.ObjId varchar(100),
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
// need to perform an atomic data operation here, might run into error
etc...
// if there is fatal error, would it leave the transaction around?
COMMIT TRANSACTIONFirst off if you are only doing a single operation (One insert, update or
delete ) regardless of the number of rows affected it will be an atomic
operation without adding BEGIN TRAN or changing the Isolation level. If you
do issue a Begin Tran it is up to you to either commit it or roll it back.
The only exception is if you use SET XACT_ABORT. If you get an error inside
a transaction and it is severe enough then you may not be able to address it
in the sp itself and must clean it up in the section that called the sp.
Errors above 15 severity usually abort the batch but do not commit or
rollback open transactions.
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Within SP, if you start a transation using Begin Transaction, you must eithe
r
execute Rollback, or COmmit, or you will leave an open transaction on your
server, along with all the locks it hasa created...
"Zeng" wrote:

> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>
>|||Transaction control can be used if it is necessary like you are going to do
more thatn one operation in the same Procedure. So, either all of its data
modifications are performed, or none of them is performed. Refer (ACID) BOL.
You can check for error at the end of the procedure
IF @.@.Error > 0
ROLLBACK TRANSACTION
Else
Commit Transaction
Thanks
Baiju
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Baiju wrote:
> Transaction control can be used if it is necessary like you are going
> to do more thatn one operation in the same Procedure. So, either all
> of its data modifications are performed, or none of them is
> performed. Refer (ACID) BOL.
> You can check for error at the end of the procedure
> IF @.@.Error > 0
> ROLLBACK TRANSACTION
> Else
> Commit Transaction
> Thanks
> Baiju
>
To be clear, you need to check @.@.ERROR after every SQL statement since
it's value is reset after each successful call.
David Gugick
Imceda Software
www.imceda.com