Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Transaction confusion with .Net Provider

4 posts in General Discussion Last posting was on 2004-03-24 15:13:01.0Z
modan Posted on 2004-03-19 11:39:06.0Z
From: "modan" <me@here.com>
Newsgroups: ianywhere.public.general
Subject: Transaction confusion with .Net Provider
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: tap.theautomationpartnership.co.uk
Message-ID: <405adbd6@forums-2-dub>
X-Original-Trace: 19 Mar 2004 03:39:02 -0800, tap.theautomationpartnership.co.uk
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Mar 2004 03:39:04 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Mar 2004 03:39:06 -0800
X-Trace: forums-1-dub 1079696346 10.22.108.75 (19 Mar 2004 03:39:06 -0800)
X-Original-Trace: 19 Mar 2004 03:39:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2802
Article PK: 6278

I am a bit confused by what I have seen in the Sybase help about
transactions in .Net

Taken from the help:-

AsaTransaction trans =
conn.BeginTransaction(IsolationLevel.RepeatableRead );
AsaCommand cmd = new AsaCommand( stmt, conn, trans );
int rows = cmd.ExecuteNonQuery();
trans.Commit();

What I don't understand is why you have to bind the cmd to the transaction.
What does that mean the behaviour would be in this situation

AsaTransaction trans = conn.BeginTransaction(
IsolationLevel.RepeatableRead );
AsaCommand cmd1 = new AsaCommand( "Update MyTable set MyValue = MyValue +
1", conn, trans );
int rows1 = cmd1.ExecuteNonQuery();
AsaCommand cmd2 = new AsaCommand( "Update MyTable set MyValue = MyValue +
1", conn);
int rows2 = cmd2.ExecuteNonQuery();
AsaCommand cmd3 = new AsaCommand( "Update MyTable set MyValue = MyValue +
1", conn, trans );
int rows3 = cmd3.ExecuteNonQuery();
trans.Rollback();

Say the initial value of MyValue was 0, what would the final value be? 1 ,2
,0 , deadlock???

Can anyone help me to understand what is going on here?

Thanks in advance,

Modan


"Leo Tohill" <lit Posted on 2004-03-21 18:44:36.0Z
From: "Leo Tohill" <lit@spammersgoaway_cbord.com>
Newsgroups: ianywhere.public.general
References: <405adbd6@forums-2-dub>
Subject: Re: Transaction confusion with .Net Provider
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: syr-24-59-107-107.twcny.rr.com
Message-ID: <405de28f$1@forums-2-dub>
X-Original-Trace: 21 Mar 2004 10:44:31 -0800, syr-24-59-107-107.twcny.rr.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 21 Mar 2004 10:44:33 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 21 Mar 2004 10:44:36 -0800
X-Trace: forums-1-dub 1079894676 10.22.108.75 (21 Mar 2004 10:44:36 -0800)
X-Original-Trace: 21 Mar 2004 10:44:36 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2812
Article PK: 6282

I've wondered the same thing myself. It's not just ASA - the .Net framework
demands this independence of the transaction and the command. How about
running the example and letting us know what you find out? Turn on
request_level_logging for a peek at what really happens.

- leo

"modan" <me@here.com> wrote in message news:405adbd6@forums-2-dub...
> I am a bit confused by what I have seen in the Sybase help about
> transactions in .Net
>
> Taken from the help:-
>
> AsaTransaction trans =
> conn.BeginTransaction(IsolationLevel.RepeatableRead );
> AsaCommand cmd = new AsaCommand( stmt, conn, trans );
> int rows = cmd.ExecuteNonQuery();
> trans.Commit();
>
> What I don't understand is why you have to bind the cmd to the
transaction.
> What does that mean the behaviour would be in this situation
>
> AsaTransaction trans = conn.BeginTransaction(
> IsolationLevel.RepeatableRead );
> AsaCommand cmd1 = new AsaCommand( "Update MyTable set MyValue = MyValue +
> 1", conn, trans );
> int rows1 = cmd1.ExecuteNonQuery();
> AsaCommand cmd2 = new AsaCommand( "Update MyTable set MyValue = MyValue +
> 1", conn);
> int rows2 = cmd2.ExecuteNonQuery();
> AsaCommand cmd3 = new AsaCommand( "Update MyTable set MyValue = MyValue +
> 1", conn, trans );
> int rows3 = cmd3.ExecuteNonQuery();
> trans.Rollback();
>
> Say the initial value of MyValue was 0, what would the final value be? 1
,2
> ,0 , deadlock???
>
> Can anyone help me to understand what is going on here?
>
> Thanks in advance,
>
> Modan
>
>
>


modan Posted on 2004-03-24 10:16:54.0Z
From: "modan" <me@here.com>
Newsgroups: ianywhere.public.general
References: <405adbd6@forums-2-dub> <405de28f$1@forums-2-dub>
Subject: Re: Transaction confusion with .Net Provider
Lines: 79
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: tap.theautomationpartnership.co.uk
X-Original-NNTP-Posting-Host: tap.theautomationpartnership.co.uk
Message-ID: <40616016$1@forums-1-dub>
Date: 24 Mar 2004 02:16:54 -0800
X-Trace: forums-1-dub 1080123414 193.132.13.132 (24 Mar 2004 02:16:54 -0800)
X-Original-Trace: 24 Mar 2004 02:16:54 -0800, tap.theautomationpartnership.co.uk
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2833
Article PK: 6303

OK, it turns out that the answer is quite simple (and horribly nasty).

What happens is that at runtime an exception is thrown.

Since we always do things in transactions at my work our DB wrapper will
presumably encapsulate this, so that we just call

wrapper.BeginTransaction();
wrapper.DoSQL(MySQLString);
wrapper.Commit();

I just don't understand why this level of encapsulation isn't provided by
the Framework.

Another problem that still exists (which I also found while playing with
this) is that attempting to use nested transactions also throws an
exception. I guess we will have to handle this in our wrapper too.

Modan

"Leo Tohill" <lit@spammersgoaway_cbord.com> wrote in message
news:405de28f$1@forums-2-dub...
> I've wondered the same thing myself. It's not just ASA - the .Net
framework
> demands this independence of the transaction and the command. How about
> running the example and letting us know what you find out? Turn on
> request_level_logging for a peek at what really happens.
>
> - leo
>
>
> "modan" <me@here.com> wrote in message news:405adbd6@forums-2-dub...
> > I am a bit confused by what I have seen in the Sybase help about
> > transactions in .Net
> >
> > Taken from the help:-
> >
> > AsaTransaction trans =
> > conn.BeginTransaction(IsolationLevel.RepeatableRead );
> > AsaCommand cmd = new AsaCommand( stmt, conn, trans );
> > int rows = cmd.ExecuteNonQuery();
> > trans.Commit();
> >
> > What I don't understand is why you have to bind the cmd to the
> transaction.
> > What does that mean the behaviour would be in this situation
> >
> > AsaTransaction trans = conn.BeginTransaction(
> > IsolationLevel.RepeatableRead );
> > AsaCommand cmd1 = new AsaCommand( "Update MyTable set MyValue = MyValue
+
> > 1", conn, trans );
> > int rows1 = cmd1.ExecuteNonQuery();
> > AsaCommand cmd2 = new AsaCommand( "Update MyTable set MyValue = MyValue
+
> > 1", conn);
> > int rows2 = cmd2.ExecuteNonQuery();
> > AsaCommand cmd3 = new AsaCommand( "Update MyTable set MyValue = MyValue
+
> > 1", conn, trans );
> > int rows3 = cmd3.ExecuteNonQuery();
> > trans.Rollback();
> >
> > Say the initial value of MyValue was 0, what would the final value be? 1
> ,2
> > ,0 , deadlock???
> >
> > Can anyone help me to understand what is going on here?
> >
> > Thanks in advance,
> >
> > Modan
> >
> >
> >
>
>


Reg Domaratzki Posted on 2004-03-24 15:13:01.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <405adbd6@forums-2-dub> <405de28f$1@forums-2-dub> <40616016$1@forums-1-dub>
Subject: Re: Transaction confusion with .Net Provider
Lines: 127
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <4061a57b$1@forums-2-dub>
X-Original-Trace: 24 Mar 2004 07:12:59 -0800, rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Mar 2004 07:13:00 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Mar 2004 07:13:01 -0800
X-Trace: forums-1-dub 1080141181 10.22.108.75 (24 Mar 2004 07:13:01 -0800)
X-Original-Trace: 24 Mar 2004 07:13:01 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2839
Article PK: 6309

Nested transaction in ASA are implemented using SAVEPOINTS, and not just by
simply beginning another transaction while the current transaction is
active.

Doesn't work :

BEGIN TRAN;
insert into t1 values (1);
BEGIN TRAN;
insert into t1 values (2);
COMMIT; // does nothing
insert into t1 values (2);
COMMIT; // all three ops committed

Does work :

BEGIN TRAN;
SAVEPOINT s1;
insert into t1 values (1);
SAVEPOINT s2;
insert into t1 values (2);
RELEASE SAVEPOINT s2;
insert into t1 values (3);
RELEASE SAVEPOINT s1;
COMMIT;

When I released SAVEPOINT s2, I could have also ROLLBACK TO SAVEPOINT s2,
which would have rolled back the insert of "2", but had no effect on the
insert of value "1". This should be fairly simple to implement in a
wrapper.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"modan" <me@here.com> wrote in message news:40616016$1@forums-1-dub...
> OK, it turns out that the answer is quite simple (and horribly nasty).
>
> What happens is that at runtime an exception is thrown.
>
> Since we always do things in transactions at my work our DB wrapper will
> presumably encapsulate this, so that we just call
>
> wrapper.BeginTransaction();
> wrapper.DoSQL(MySQLString);
> wrapper.Commit();
>
> I just don't understand why this level of encapsulation isn't provided by
> the Framework.
>
> Another problem that still exists (which I also found while playing with
> this) is that attempting to use nested transactions also throws an
> exception. I guess we will have to handle this in our wrapper too.
>
> Modan
>
> "Leo Tohill" <lit@spammersgoaway_cbord.com> wrote in message
> news:405de28f$1@forums-2-dub...
> > I've wondered the same thing myself. It's not just ASA - the .Net
> framework
> > demands this independence of the transaction and the command. How about
> > running the example and letting us know what you find out? Turn on
> > request_level_logging for a peek at what really happens.
> >
> > - leo
> >
> >
> > "modan" <me@here.com> wrote in message news:405adbd6@forums-2-dub...
> > > I am a bit confused by what I have seen in the Sybase help about
> > > transactions in .Net
> > >
> > > Taken from the help:-
> > >
> > > AsaTransaction trans =
> > > conn.BeginTransaction(IsolationLevel.RepeatableRead );
> > > AsaCommand cmd = new AsaCommand( stmt, conn, trans );
> > > int rows = cmd.ExecuteNonQuery();
> > > trans.Commit();
> > >
> > > What I don't understand is why you have to bind the cmd to the
> > transaction.
> > > What does that mean the behaviour would be in this situation
> > >
> > > AsaTransaction trans = conn.BeginTransaction(
> > > IsolationLevel.RepeatableRead );
> > > AsaCommand cmd1 = new AsaCommand( "Update MyTable set MyValue =
MyValue
> +
> > > 1", conn, trans );
> > > int rows1 = cmd1.ExecuteNonQuery();
> > > AsaCommand cmd2 = new AsaCommand( "Update MyTable set MyValue =
MyValue
> +
> > > 1", conn);
> > > int rows2 = cmd2.ExecuteNonQuery();
> > > AsaCommand cmd3 = new AsaCommand( "Update MyTable set MyValue =
MyValue
> +
> > > 1", conn, trans );
> > > int rows3 = cmd3.ExecuteNonQuery();
> > > trans.Rollback();
> > >
> > > Say the initial value of MyValue was 0, what would the final value be?
1
> > ,2
> > > ,0 , deadlock???
> > >
> > > Can anyone help me to understand what is going on here?
> > >
> > > Thanks in advance,
> > >
> > > Modan
> > >
> > >
> > >
> >
> >
>
>