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.

Nested transactions

7 posts in General Discussion Last posting was on 2005-12-20 11:28:12.0Z
Morley Chalmers Posted on 2005-12-11 00:28:48.0Z
Sender: 739d.439b6fad.1804289383@sybase.com
From: Morley Chalmers
Newsgroups: ianywhere.public.general
Subject: Nested transactions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <439b70a7.73a5.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 10 Dec 2005 16:19:51 -0800, 10.22.241.42
Lines: 24
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Dec 2005 16:19:52 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Dec 2005 16:28:48 -0800
X-Trace: forums-1-dub 1134260928 10.22.108.75 (10 Dec 2005 16:28:48 -0800)
X-Original-Trace: 10 Dec 2005 16:28:48 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5060
Article PK: 17493

When a user logs into our solution with the user name of
"demo" I'd like to start a transactiuon, and then when they
quit to roll back that transaction. This way the user can
create new records, change sample data, navigate, experiment
and get a real experience of the solution. When they're gone
the sample records remain unchanged.

However, we already use transactions on a number of forms,
particularly when users are in the midst of creating new
records.

We're finding these nested transactions are conflicting,
locking up the database forcing a machine reboot.

We've been advised to seek out the multiversion capabilities
of Oracle or Postgres.

Question: is there a way to achieve our objective within
Sybase ASA?

Not that it likely matters in this case, we're using Servoy
as our front end.

Kind regards,


Greg Fenton Posted on 2005-12-12 15:17:45.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Nested transactions
References: <439b70a7.73a5.1681692777@sybase.com>
In-Reply-To: <439b70a7.73a5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: gfenton-t30.sybase.com
X-Original-NNTP-Posting-Host: gfenton-t30.sybase.com
Message-ID: <439d9499$1@forums-1-dub>
Date: 12 Dec 2005 07:17:45 -0800
X-Trace: forums-1-dub 1134400665 10.25.98.144 (12 Dec 2005 07:17:45 -0800)
X-Original-Trace: 12 Dec 2005 07:17:45 -0800, gfenton-t30.sybase.com
Lines: 48
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5063
Article PK: 8653


Morley Chalmers wrote:
>
> However, we already use transactions on a number of forms,
> particularly when users are in the midst of creating new
> records.

I cannot think of a way to have one connection essentially ignore
COMMITs that *your application* is performing.

> We're finding these nested transactions are conflicting,
> locking up the database forcing a machine reboot.
>

I cannot think of any reason that you should have to reboot your
machine. If the database is "locked up", then it is likely because some
application has locked on or more resources and other connections are
blocking awaiting for the resources to be freed. To free them, you can
either disconnect the offending thread (e.g. kill the application) or
connect with DBISQL and use the DISCONNECT statement.

> We've been advised to seek out the multiversion capabilities
> of Oracle or Postgres.

Without understanding the true nature of the problem, I think it is
irresponsible of someone to blindly suggest a switch of the
configuration (in this case, switching to a new RDBMS). This is akin to
"waiving a dead chicken over the keyboard" (as Breck is so often quoted).


> Question: is there a way to achieve our objective within
> Sybase ASA?

As I mentioned, I do not believe there is any way to have ASA ignore a
client's request when a COMMIT is issued. My recommendation would be to
locate all the COMMITs within your application (including stored procs,
triggers, etc...) and determine a way to unify the COMMIT methodology.
Then you could gatekeep that unified approach in such a way that user
"demo" never does a COMMIT.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Rob Waywell Posted on 2005-12-12 16:00:25.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <439b70a7.73a5.1681692777@sybase.com>
Subject: Re: Nested transactions
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: rwaywell-d610.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-d610.sybase.com
Message-ID: <439d9e99$1@forums-1-dub>
Date: 12 Dec 2005 08:00:25 -0800
X-Trace: forums-1-dub 1134403225 10.25.98.235 (12 Dec 2005 08:00:25 -0800)
X-Original-Trace: 12 Dec 2005 08:00:25 -0800, rwaywell-d610.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5064
Article PK: 8654

Why would your Demo user *ever* work against a production database?!?


A couple of alternatives are:

1) Limit the Demo user to read-only access, don't allow them to update the
data. If they just want to see the application this is likely enough.

2) Provide a Demo *database* file that the Demo user can connect to. This
may require some changes to connection logic, but it does at least 2 things
for you:
a) It *never* lets the Demo user touch the production database, so it
reduces risk of damage to the production system.
b) Since the Demo user would be the only user of the Demo *database*, then
you user your massive transaction approach, or better yet
c) The Demo database could be copied from a template leaving the normal
application flow intact, then when the database is shut down, just copy the
original template overtop of the changed system


--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

<Morley Chalmers> wrote in message
news:439b70a7.73a5.1681692777@sybase.com...
> When a user logs into our solution with the user name of
> "demo" I'd like to start a transactiuon, and then when they
> quit to roll back that transaction. This way the user can
> create new records, change sample data, navigate, experiment
> and get a real experience of the solution. When they're gone
> the sample records remain unchanged.
>
> However, we already use transactions on a number of forms,
> particularly when users are in the midst of creating new
> records.
>
> We're finding these nested transactions are conflicting,
> locking up the database forcing a machine reboot.
>
> We've been advised to seek out the multiversion capabilities
> of Oracle or Postgres.
>
> Question: is there a way to achieve our objective within
> Sybase ASA?
>
> Not that it likely matters in this case, we're using Servoy
> as our front end.
>
> Kind regards,


krisztian pinter Posted on 2005-12-12 16:11:59.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Nested transactions
References: <439b70a7.73a5.1681692777@sybase.com>
Message-ID: <ops1ohoeo3wwfehv@karwst_pint>
From: "krisztian pinter" <pinterkr@freemail.hu>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-2
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
User-Agent: Opera M2/7.54 (Win32, build 3869)
X-Original-NNTP-Posting-Host: a0528.adsl.pool.eol.hu
X-Original-Trace: 12 Dec 2005 08:02:52 -0800, a0528.adsl.pool.eol.hu
Lines: 7
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 12 Dec 2005 08:02:53 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 12 Dec 2005 08:11:59 -0800
X-Trace: forums-1-dub 1134403919 10.22.108.75 (12 Dec 2005 08:11:59 -0800)
X-Original-Trace: 12 Dec 2005 08:11:59 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5065
Article PK: 8655


On 10 Dec 2005 16:28:48 -0800, <Morley Chalmers> wrote:

> When a user logs into our solution with the user name of
> "demo" I'd like to start a transactiuon, and then when they
> quit to roll back that transaction.

am i wrong calling this use of transactions abusive? :)


Greg Fenton Posted on 2005-12-14 02:06:31.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Nested transactions
References: <439b70a7.73a5.1681692777@sybase.com> <ops1ohoeo3wwfehv@karwst_pint>
In-Reply-To: <ops1ohoeo3wwfehv@karwst_pint>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 199.243.99.212
X-Original-NNTP-Posting-Host: 199.243.99.212
Message-ID: <439f7e27$1@forums-1-dub>
Date: 13 Dec 2005 18:06:31 -0800
X-Trace: forums-1-dub 1134525991 199.243.99.212 (13 Dec 2005 18:06:31 -0800)
X-Original-Trace: 13 Dec 2005 18:06:31 -0800, 199.243.99.212
Lines: 26
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5070
Article PK: 8657


krisztian pinter wrote:
> On 10 Dec 2005 16:28:48 -0800, <Morley Chalmers> wrote:
>
>> When a user logs into our solution with the user name of
>> "demo" I'd like to start a transactiuon, and then when they
>> quit to roll back that transaction.
>
> am i wrong calling this use of transactions abusive? :)

I think it could be argued either way. Morley would like the demo user
to make changes to the db, see the outcome of those changes, but not
make them permanent; transactions can be used for just this case...a
sort of "try before you buy" approach I guess :-)

However, I cannot think of any way of having the database ignore
explicit COMMITs (or implicit if using DDL) from within a specific
connection (while having other connections work as normal).

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


krisztian pinter Posted on 2005-12-14 09:23:42.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Nested transactions
References: <439b70a7.73a5.1681692777@sybase.com> <ops1ohoeo3wwfehv@karwst_pint> <439f7e27$1@forums-1-dub>
Message-ID: <ops1rn4bjiwwfehv@karwst_pint>
From: "krisztian pinter" <pinterkr@freemail.hu>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-2
MIME-Version: 1.0
Content-Transfer-Encoding: Quoted-Printable
User-Agent: Opera M2/7.54 (Win32, build 3869)
NNTP-Posting-Host: a0528.adsl.pool.eol.hu
X-Original-NNTP-Posting-Host: a0528.adsl.pool.eol.hu
Date: 14 Dec 2005 01:23:42 -0800
X-Trace: forums-1-dub 1134552222 81.0.84.20 (14 Dec 2005 01:23:42 -0800)
X-Original-Trace: 14 Dec 2005 01:23:42 -0800, a0528.adsl.pool.eol.hu
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5071
Article PK: 8659

On 13 Dec 2005 18:06:31 -0800, Greg Fenton

<greg.fenton_NOSPAM_@ianywhere.com> wrote:
>> am i wrong calling this use of transactions abusive? :)
>
> I think it could be argued either way. Morley would like the demo user
> to make changes to the db, see the outcome of those changes, but not

lunch-long transactions (when user goes to lunch and leaves the program
running) are not good practice.


"Pavel Karady" <pavel_ns.ns_karady Posted on 2005-12-20 11:28:12.0Z
From: "Pavel Karady" <pavel_ns.ns_karady@ns_kogerusa.com>
Newsgroups: ianywhere.public.general
References: <439b70a7.73a5.1681692777@sybase.com> <ops1ohoeo3wwfehv@karwst_pint> <439f7e27$1@forums-1-dub>
Subject: Re: Nested transactions
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 217.75.90.162
X-Original-NNTP-Posting-Host: 217.75.90.162
Message-ID: <43a7eacc@forums-1-dub>
Date: 20 Dec 2005 03:28:12 -0800
X-Trace: forums-1-dub 1135078092 217.75.90.162 (20 Dec 2005 03:28:12 -0800)
X-Original-Trace: 20 Dec 2005 03:28:12 -0800, 217.75.90.162
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5076
Article PK: 17496


> However, I cannot think of any way of having the database ignore
> explicit COMMITs (or implicit if using DDL) from within a specific
> connection (while having other connections work as normal).

There is a way.

Proceeding the statement BEGIN TRANSACTION within a transaction already,
when chained mode is ON, will cause the COMMIT statement be ignored
(althrough the ROLLBACK functionality remains).

There's a lot of stuff around this, I'm not in mood to search for a my
thread issued long before, where Breck gave a glympse, so I'll just write
down the basics:

1. There's one connection - one transaction (and thinking of anything else
is a waste of time), and the status of that transaction is only "it's
running" or "it's not running", no other status available
2. Which commands start a transaction? Any of INSERT, UPDATE, DELETE or
SELECT which acquires locks (there may be more, but this are the basics)
3. The chained mode - if it is OFF, then every of the above commands is
counted as a single transaction that is COMMITted immediately, if you need
to start a transaction, it's done explicitly by stating 'BEGIN TRANSACTION',
performing actions and then ending it with COMMIT or ROLLBACK
4. The chained mode - if it is ON, then every of the (2.) commands starts a
transaction. The 'BEGIN TRANSACTION' statement should be avoided at all
costs when chained mode is ON - because it causes of what described before -
the COMMIT statement is skipped, but the ROLLBACK works fine. Of course,
there need to be a certain amount of COMMITs or ROLLBACKs depending on how
many BEGIN TRANSACTION statements were issued (+ one COMMIT or ROLLBACK to
end the 'main' transaction). This behavior is there because of point (1.):
one connection - one transaction. It's really that simple.

I bet a few people know about this.

Please (Morley) let me know if ignoring COMMIT in this way is applicable to
you.

Pavel