ASE Database forum - Transaction

Author

Transaction

Hallerence

2005-12-16, 3:24 am

Hi,

I feel very confuse about the transaction in ase.
If I write a stored procedure in ase :
Create procedure sp
as
BEGIN TRAN
Update something for Table A
Insert something for Table B
Select something for Table A
Update something for Table A
COMMIT TRAN
end

1) If there are some errors in the third line of the store procedure, will
it keep lock of the Table A and Table B and cause deadlock to other
transaction?
2) How should I rewrite the stored procedure so that the whole transaction
will be rollback if any of the above statement is failed?

Thank you so much!!

Mano Bill

2005-12-16, 9:23 am

Hi,

To check the errors you can do the following:

BEGIN TRAN
Update something for Table A
if @@error <> 0
begin
ROLLBACK TRAN
return
end

Insert something for Table B
if @@error <> 0
begin
ROLLBACK TRAN
return
end

Select something for Table A
if @@error <> 0
begin
ROLLBACK TRAN
return
end

Update something for Table A
if @@error <> 0
begin
ROLLBACK TRAN
return
end

COMMIT TRAN

By checking @@error variable, you can see if there is something wrong on
your statement and rollback if needed (if different than zero, it got an
error).

Thanks,
Bill

Hallerence wrote:
> Hi,
>
> I feel very confuse about the transaction in ase.
> If I write a stored procedure in ase :
> Create procedure sp
> as
> BEGIN TRAN
> Update something for Table A
> Insert something for Table B
> Select something for Table A
> Update something for Table A
> COMMIT TRAN
> end
>
> 1) If there are some errors in the third line of the store procedure, will
> it keep lock of the Table A and Table B and cause deadlock to other
> transaction?
> 2) How should I rewrite the stored procedure so that the whole transaction
> will be rollback if any of the above statement is failed?
>
> Thank you so much!!
>
>

Hallerence

2005-12-19, 3:24 am

Thanks for your reply.

Checking errors in this way seems quite complex....
As I know, SQL server has something called SET XACT_ABORT ON which specifies
that the SQL server will automatically roll back the transaction in case any
of the statements fail to complete.
http://msdn.microsoft.com/library/d...ettransact1.asp

So, only one rollback statement is needed for the whole stored procedure. I
think it is helpful. Does ASE have this feature as well?

Also, it seems that some tables are being locked when I write the
transaction inside the stored procedure instead of using SQLCA.Autocommit.
Are they the same?

Thanks everyone.

"Mano Bill" < NO_SPAM_TO_ME_manobi
ll_at_gmail_dot_com> wrote in message
news:43a2c86a$1@foru
ms-2-dub...[color=darkred]
> Hi,
>
> To check the errors you can do the following:
>
> BEGIN TRAN
> Update something for Table A
> if @@error <> 0
> begin
> ROLLBACK TRAN
> return
> end
>
> Insert something for Table B
> if @@error <> 0
> begin
> ROLLBACK TRAN
> return
> end
>
> Select something for Table A
> if @@error <> 0
> begin
> ROLLBACK TRAN
> return
> end
>
> Update something for Table A
> if @@error <> 0
> begin
> ROLLBACK TRAN
> return
> end
>
> COMMIT TRAN
>
> By checking @@error variable, you can see if there is something wrong on
> your statement and rollback if needed (if different than zero, it got an
> error).
>
> Thanks,
> Bill
>
> Hallerence wrote:
will[color=darkred]
transaction[color=da
rkred]

Mark A. Parsons

2005-12-19, 3:24 am

Sybase doesn't have the MS feature that you mention (or anything like
Oracle's exceptions handling code). It is possible to kinda/sorta
modularize the error checking/handling in Sybase ... and I'll leave that
for a separate post.

As for the locks, as long as you're inside a transaction your locks will
be maintained; thus it's always a good idea to design your unit of work
so that you hold the lock(s) for the shortest period of time (ie, you
complete your work and close out your transaction as fast as possible.)

If you need smaller lock granularity you could consider converting the
table(s) in question from allpages locking to either datapages or
datarows locking. But this will *NOT* eliminate locking ... Sybase (and
Oracle and MS and IBM) will maintain exclusive locks on any modified
data until you close out your transaction.

Whether you have autocommit set or cleared won't matter.

If autocommit is disabled this means you'll be running in chained
transaction mode, ie, you always have an open transaction; after a unit
of work has been processed you'll still need to close out your
transaction in order to release your locks.

If autocommit is enabled then you'll have to issue an explicit 'begin
tran' in order to insure a group of DML statements are considered as
part of a single unit of work. Again, locks will be held until you
close out the transaction.

Hallerence wrote:
> Thanks for your reply.
>
> Checking errors in this way seems quite complex....
> As I know, SQL server has something called SET XACT_ABORT ON which specifies
> that the SQL server will automatically roll back the transaction in case any
> of the statements fail to complete.
> http://msdn.microsoft.com/library/d...ettransact1.asp
>
> So, only one rollback statement is needed for the whole stored procedure. I
> think it is helpful. Does ASE have this feature as well?
>
> Also, it seems that some tables are being locked when I write the
> transaction inside the stored procedure instead of using SQLCA.Autocommit.
> Are they the same?
>
> Thanks everyone.
>
> "Mano Bill" < NO_SPAM_TO_ME_manobi
ll_at_gmail_dot_com> wrote in message
> news:43a2c86a$1@foru
ms-2-dub...
>
>
> will
>
>
> transaction
>
>
>
>

Mark A. Parsons

2005-12-19, 3:24 am

Handling errors (ie, exceptions) in Sybase/TSQL can be a little
cumbersome ... though another way to look at it is to say it's very
flexible! ;-)

The problem I see quite a bit is a usually a combination of a)
developers really not understanding transactional management techniques
and methods and/or b) developers having different (and often mutually
exclusive) methods for handling transactions.

I've posted/cancelled a couple messages so far trying to come up with a
condensed, somewhat simplified example of how to take care of most
tranactional issues in Sybase. This is what I've got so far ...

--------------

Assumptions:

- Inside a proc I am only responsible for the transaction within my
stored proc

- The parent process is handling any higher-level transactional management

- If the subordinate trigger or stored proc messes up my @@trancount I
will attempt to rollback my work to the same trancount level as when I
started, plus I will kick out an error message about the mismatched
trancounts

- This proc will return a 0 if all is successful; a number > 0 will be
returned if there is a problem (it is up to the developer to work out a
standard of return codes within his/her organization)

- For the sake of this example we are *NOT* running in chained
transaction mode (ie, autocommit = true); transactional management under
chained transation mode (ie, autocommit = false) is another post for
another day

--------------

So a sample stored proc would look like the following.

[I've split out some code to later sections so that this stored proc is
somewhat 'easy' to read.]

----------------- begin stored proc definition
create proc ...

declare @begin_tranc
ount int, -- copy of @@trancount
@error int, -- copy of @@error
@error_no int, -- our error #
@error_msg varchar(
255), -- our error msg
@return_cd int, -- our return cd
@return_status int
-- subordinate proc's return cd

select @being_tranco
unt = @@trancount
@return_cd = 0

begin tran -- start our own unit of work

save tran rollback_to_here -- give ourselves a place
-- to rollback to if needed

-- 1+ insert/update/delete/execs that
-- make up my unit of work; order of
-- individual insert/update/delete/execs
-- is up to the developer as long
-- as s/he meets their coding specs

insert/update/delete
<test_block1>
...

exec @return_status = some_other_proc
<test_block2>
...

goto COMMIT_IT

ROLL_IT_BACK:
<rollback_block>

COMMIT_IT:
<commit_block>

-- and now the only way out of this proc, ie, one
-- entrance and one exit ...

return @return_cd
go
----------------- end stored proc definition

After each insert/update/delete we need to test @@error.

We also need to check @@trancount to make sure a subordinate trigger (or
stored proc subordinate to the trigger) didn't leave a transaction open
.... or rollback too far.

So after each insert/update/delete we'll want some code that looks like
the following:

----------------- begin test_block1
select @error = @@error

if @@trancount != (@begin_trancount + 1)
begin
select @error_no = 23400, -- unique within this proc
@error_msg =
'ERROR: Invalid trancount after <insert/update/delete> of
<table_name>, pk#1='+convert(varch
ar,@pk1)+
',pk#2='+convert(var
char,@pk2)+ ',
@begin_trancount='+
convert(varchar,@beg
in_trancount)+
',@@trancount='+conv
ert(varchar,@@tranco
unt),
@return_cd = 500

goto ROLL_IT_BACK
end
else if @error != 0
begin
select @error_no = 23420, -- unique within this proc
@error_msg = 'ERROR: some informational message about this particular
insert/update/delete; possibly include some PK values as further debug
info.',
@return_cd = 250

goto ROLL_IT_BACK
end
----------------- end test_block1

You'll notice that I mention the unique use of error numbers. This is
just for debug purposes. If this code generates an error message, said
message will include an error number, some text, and the name of the
stored proc. The use of a unique error number allows me to quickly jump
to the point in the proc just after where the error was generated.

Consider ... 2 inserts to the same table ... one of them generates and
error ... which one? If you issue each insert it's own error number
this will help in tracking down which insert was at fault.

Also consider putting some helpful info in the error message ... for
delete/update this may be the PK(s) for the record(s) in question. Same
goes for an insert. The objective being to give yourself some
additional info with which to debug the stored proc if you do get an error.

[Although I didn't include it here, but some folks may want to ... if an
error occurs you could just issue a 'select' of all your parameters
involved in the failed operation. This is up to you and also depends on
whether the receiving application can handle the additional result sets.
Again, the sole purpose is to provide debug info. -- Though you could
dump the information to a table, realize that this info will get rolled
back when we issue our rollback statement.]

The text block for a stored proc is pretty much the same except that
you'll also need to test the return code (@return_status) sent back by
the subordinate proc ...

----------------- begin test_block2
select @error = @@error

if @@trancount != (@begin_trancount + 1)
begin
select @error_no = 23500, -- unique within this proc
@error_msg =
'ERROR: Invalid trancount after exec of <proc_name>'+
', param#1='+convert(va
rchar,@param1)+
', param#2='+convert(va
rchar,@param2)+
',@begin_trancount='
+
convert(varchar,@beg
in_trancount)+
', @@trancount='+conver
t(varchar,@@trancoun
t),
@return_cd = 500

goto ROLL_IT_BACK
end
else if @error != 0
or @return_status != 0
begin
select @error_no = 23520, -- unique within this proc
@error_msg = 'ERROR: some informational message about this particular
exec; possibly include some parameter values as further debug info.',
@return_cd = case
when @return_status != 0
then @return_status
else 250
end

goto ROLL_IT_BACK
end
----------------- end test_block2

When putting together @error_msg you'll want to make sure you don't
exceed the 255 character limit else the message will be truncated at
255. Obviously if you're running 12.5+ you can redefine @error_msg to
be larger thus allowing for more info to be displayed with the error
message.

With the rollback code we will only issue a rollback if @@trancount is
equal to @begin_trancount (@@trancount from when we entered the proc) +
1 (our 'begin tran').

We'll also issue a rollback if @@trancount is larger than
(@begin_trancount + 1), just in case a subordinate trigger or proc began
a new transaction and failed to close it out.

The objective is to get us back to the same @@trancount as when the proc
was entered.

If we issue 'rollback tran' we will rollback all the way to the topmost
transaction. Instead, we'll rollback to our savepoint ...

----------------- begin rollback_block
raiserrror @error_no, @error_msg

if @@trancount >= (@begin_trancount + 1)
begin
rollback tran rollback_to_here
end
----------------- end rollback_block

At this point we're looking to close our own 'begin tran'. In this
situation @@trancount should equal (@begin_trancount + 1).

If we just issued a 'rollback tran rollback_to_here', then our
transaction is empty but it is also still open, hence our need to close
it out with a 'commit tran'.

If @@trancount <= @begin_trancount that means a subordinate trigger or
stored proc rolled back beyond your own 'begin tran' in which case we
are not going to further confuse the situation by issuing a 'commit
tran' for a higher level transaction. Prior tests for @@trancount
should have already generated an apppropriate error message and set
@return_cd accordingly.

----------------- begin commit_block
if @@trancount = (@begin_trancount + 1)
begin
commit tran
end
----------------- end commit_block

Hallerence wrote:

> Thanks for your reply.
>
> Checking errors in this way seems quite complex....
> As I know, SQL server has something called SET XACT_ABORT ON which specifies
> that the SQL server will automatically roll back the transaction in case any
> of the statements fail to complete.
> http://msdn.microsoft.com/library/d...ettransact1.asp
>
> So, only one rollback statement is needed for the whole stored procedure. I
> think it is helpful. Does ASE have this feature as well?
>
> Also, it seems that some tables are being locked when I write the
> transaction inside the stored procedure instead of using SQLCA.Autocommit.
> Are they the same?
>
> Thanks everyone.
>
> "Mano Bill" < NO_SPAM_TO_ME_manobi
ll_at_gmail_dot_com> wrote in message
> news:43a2c86a$1@foru
ms-2-dub...
>
>
> will
>
>
> transaction
>
>
>
>

Hallerence

2005-12-20, 3:24 am

Hi Mark,

Thank you very much for your helpful assistance!
Now I know how should I change my stored procedure.
But I have one more question.
How could I check what is being executing when I found some process is
blocking others?
I'm not a DBA so I just know using sp_who to check for the process. But the
information is not detail enough in the "cmd" column. (Only first few chars
is shown).

Thanks!!

"Mark A. Parsons" < iron_horse@no_spamol
a_compuserve.com> wrote in message
news:43a65b8b@forums
-2-dub...[color=darkred]
> One small correction on the last test of @@trancount ... change made
> below ...
>
> Mark A. Parsons wrote:
>
Sybase.[color=darkred]
http://msdn.microsoft.com/library/d...ettransact1.asp[color=darkred]
on[color=darkred]
an[color=darkred]

Mark A. Parsons

2005-12-20, 8:25 pm

re: transaction management

I'd look at my later posting that was a little more concise on coding
(broke out the error checking into separate areas) and which tended to be a
little (?) long-winded on the explanations.

re: more blocking info

Using sp_who as a starting block, consider pulling out those columns you
don't need and adding additional columns from master..sysprocesses.
There's a good bit of info in master..sysprocesses which does not show up
in the plain-vanilla sp_who output.

You can get creative and only show 'active' spid's ... throw in some code
to pull SQL text (dbcc sqltext(), master..monProcessSQLText) and showplans
(sp_showplan) ... have the code loop a given number of times (really
convenient for seeing which spids are 'moving') ... even throw in a simple
'TimeStamp = getdate()' construct (very convenient if this new proc is part
of a 24x7 monitoring process).

Hallerence wrote:

> Hi Mark,
>
> Thank you very much for your helpful assistance!
> Now I know how should I change my stored procedure.
> But I have one more question.
> How could I check what is being executing when I found some process is
> blocking others?
> I'm not a DBA so I just know using sp_who to check for the process. But the
> information is not detail enough in the "cmd" column. (Only first few chars
> is shown).
>
> Thanks!!
>
>
> "Mark A. Parsons" < iron_horse@no_spamol
a_compuserve.com> wrote in message
> news:43a65b8b@forums
-2-dub...
>
>
> Sybase.
>
>
> http://msdn.microsoft.com/library/d...ettransact1.asp
>
>
> on
>
>
> an
>
>
>

Sponsored Links