SQL Anywhere database - Nested transactions

Author

Nested transactions

Morley Chalmers

2005-12-12, 11:23 am

When a user logs into our solution with the user name of "demo" I'd like
to start a transaction, and then when they quit to roll back that
transaction. Allow the demo user to 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,

Glenn Paulley

2005-12-12, 1:23 pm

Please don't cross-post.

Glenn

Morley Chalmers <morley@7office.com> wrote in news:morley-
7D8C73. 12125512122005@vip10
6.sybase.com:

> When a user logs into our solution with the user name of "demo" I'd
like
> to start a transaction, and then when they quit to roll back that
> transaction. Allow the demo user to 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,

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

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

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

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

Pavel Karady

2005-12-12, 1:23 pm

There is no nested transaction in ASA. One connection - one transaction, and
the status of that transaction at a specified time is only "it runs" or "it
runs not". The transaction workaround is very complicated; I suggest the book
of Breck Carter.

To partially cover your malfunction, I'd suggest creating a mirror DB, setting
up one-way replication (from consolidated - main DB to mirror - demo DB) and
let the demo users automatically work only with the demo database. Why is this
solution partial? Because even if on separate DB, if multiple demo users are
connected at the same time, their transactions (very probably) will end in
locking conflicts. Is allowing one demo user be logged at a time very
restrictive to you?

An another solution might be creating a temporary set of tables when the demo
user logs in. He will work with them only, so his transaction does not affect
any other demo user (nor normal user) transactions. After the user has been
logged out, the set of temp tables can be purged.

I hope this helped at least a bit.

Pavel

In article <morley-7D8C73. 12125512122005@vip10
6.sybase.com>,
morley@7office.com says...
>
>When a user logs into our solution with the user name of "demo" I'd like
>to start a transaction, and then when they quit to roll back that
>transaction. Allow the demo user to 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,

Morley Chalmers

2005-12-12, 8:23 pm

In article <439dbc51@forums-1-dub>,
pavel. karady_ns@ns_kogerds
.com (Pavel Karady) wrote:

> There is no nested transaction in ASA. One connection - one transaction, and
> the status of that transaction at a specified time is only "it runs" or "it
> runs not". The transaction workaround is very complicated; I suggest the book
> of Breck Carter.
>
> To partially cover your malfunction, I'd suggest creating a mirror DB,
> setting
> up one-way replication (from consolidated - main DB to mirror - demo DB) and
> let the demo users automatically work only with the demo database. Why is
> this
> solution partial? Because even if on separate DB, if multiple demo users are
> connected at the same time, their transactions (very probably) will end in
> locking conflicts. Is allowing one demo user be logged at a time very
> restrictive to you?

No, too restrictive, not realistic marketing.

> An another solution might be creating a temporary set of tables when the demo
> user logs in. He will work with them only, so his transaction does not affect
> any other demo user (nor normal user) transactions. After the user has been
> logged out, the set of temp tables can be purged.

I'm only partially understanding this strategy. For compatibility with
the code the table names need to remain the same, so I presume you mean
to create a second database with the same tables, fields, etc.

My understanding is still muddy, particularly the concept of temporary
tables. It would be fatal to the demo to keep the user waiting while
temporary tables and data are setup thus giving a false impression on
the responsiveness of the system.

Here's a concept that might work. Let me know if I'm on the right track.

1. Create a set of 10 identical sample companies.
2. When the user logs on as "demo", check with a table to see which
sample companies are not in current use.
3. Behind the scenes relog them on again as "Demo1" and direct them to
that set of demo records.
4. Start a transaction when they begin, roll back the transaction when
they leave.

They're seen as a distinct user viewing a distinct set of records. The
transaction lock would be seen as unique to that user.

Would this approach work or am I still not understanding locks?

Kind regards,

David Kerber

2005-12-12, 8:23 pm

In article <morley-01D363. 14335912122005@vip10
6.sybase.com>, morley@
7office.com says...

....

> My understanding is still muddy, particularly the concept of temporary
> tables. It would be fatal to the demo to keep the user waiting while
> temporary tables and data are setup thus giving a false impression on
> the responsiveness of the system.

Unless your demo data are pretty big (MegaBytes), copying data around
into temp tables goes pretty fast. If you did it during the user login
procedure so that the rest of the operations worked from pre-populated
tables, they would never notice.

> Here's a concept that might work. Let me know if I'm on the right track.
>
> 1. Create a set of 10 identical sample companies.
> 2. When the user logs on as "demo", check with a table to see which
> sample companies are not in current use.
> 3. Behind the scenes relog them on again as "Demo1" and direct them to
> that set of demo records.
> 4. Start a transaction when they begin, roll back the transaction when
> they leave.
>
> They're seen as a distinct user viewing a distinct set of records. The
> transaction lock would be seen as unique to that user.
>
> Would this approach work or am I still not understanding locks?

Unless your current production system is structured as a single
transaction, it seems unlikely that you could easily keep your demo
transactions from committing at the same points as your production does.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Pavel Karady

2005-12-13, 3:23 am

>
>I'm only partially understanding this strategy. For compatibility with
>the code the table names need to remain the same, so I presume you mean
>to create a second database with the same tables, fields, etc.

No, I was thinking of creating a set of tables that we can call temporary, in the
very same database. Creating a second database - this is an unreal, time and
resources consuming "solution"; as David wrote, creating a set of new tables can be
done in a fraction of second, but there's more. I'll describe later in this article.

>
>Here's a concept that might work. Let me know if I'm on the right track.
>
>1. Create a set of 10 identical sample companies.
>2. When the user logs on as "demo", check with a table to see which
>sample companies are not in current use.
>3. Behind the scenes relog them on again as "Demo1" and direct them to
>that set of demo records.
>4. Start a transaction when they begin, roll back the transaction when
>they leave.
>

Yes, you are basically on the right track, but I'd still not recommend using several
databases. Often, altering the code to work with different table copies for each user
is not much of a problem.

The deeper, 1 database concept:
- if creating a set of temporary tables for the demo user can be done in a fraction
of second (they're not too big - I suggest creating a simple script that will measure
it precisely)
- user demo is trying to log in
- check a free name for that user, if 5 demo users are already logged in, then
create a "demo6" user
- create a copy of tables - add the user name to their names (e.g. business table
"accounts" -> demo table "accounts_demo6", ...)
- log in the demo6 user
- let him work
- after logging him out, there is no difference between commit and rollback - drop
all "%_demo6" tables

- if creating a set of temporary tables for the demo user cannot be done in a
fraction of second, there are too many tables (that even are very big)
- have a sets of tables pre-created! The usage measurement follows; if you know
that there aren't more than 3-4 demo users logged in at a time, 8 pre-created table
sets will be enough
- user demo is trying to log in
- assign a free user name to him as in previous example (e.g. demo6)
- let him work on "accounts_demo6" and other tables, that are already created
- after disconnecting, rollback his transaction and "%_demo6" table set is ready to
be used again

Please keep in mind, that even if this framework looks simple, it may be more
complicated when applying it into real business. You need to check many things, but
this might work.

>They're seen as a distinct user viewing a distinct set of records. The
>transaction lock would be seen as unique to that user.
>
>Would this approach work or am I still not understanding locks?

You are basically right again, in your unique databases - unique demo user model, one
transaction (demo user) per database - we don't need to think about any locks :) In
one database - many demo users model, the demo users work each with his own set of
tables, so locks are created, released, etc. on separate tables for each connection
(= for each transaction), so they will never block.

Hope this helped.

Pavel

Morley Chalmers

2005-12-13, 11:23 am

To summarize Pavel and David, there appear to be two options.

1. Create a temporary set of tables when the demo user logs on.
"accounts_demo6", etc. Dump the tables when they quit.

2. Create several sets of demo records in advance. When the demo user
arrives direct them to an unoccupied set and start a transaction. Roll
back the transaction when they quit.

Still experiencing difficulties wrapping my head around managing forms,
relationships, etc. in the presence of temporary tables. So the second
approach feels easier.

But there are still problems. I'm using Servoy as the front end.
Servoy's security system doesn't offer options to change the user id
once logged on.

A member of the Servoy community said this about my problem:

"Transactions also lock data so if you use them in multi user systems
always use them with caution, if you use them a lot use a database
with multiversion capabilities like Oracle or Postgres."

Although Servoy works with both Oracle and Postgres as well as Sybase
ASA, I'm not enthusiastic about switching databases.

The Servoy community member goes on to say:

"For demos transactions are not the way to go.
You can consider using the sql plugin to perform delete and insert
statements upon log-off or use a stored procedure for this.
Another option is to reload the data nightly for example using a sql
script."

The SQL plugin referred to here is internal to Servoy, accepts any SQL
instruction. Instinct suggests this plugin may be key to a solution but
exactly how I'm not sure.

Situation still muddy.

Greg Fenton

2005-12-13, 8:24 pm

Morley Chalmers wrote:
>
> But there are still problems. I'm using Servoy as the front end.
> Servoy's security system doesn't offer options to change the user id
> once logged on.
>

I can't speak about Servoy as I know only how to spell it (assuming I
copied your spelling correctly... :-) )

How many "demo" users do you think will be running at the same time?

One option I have (off the top of my head) is to have a "demo" database
created and run NNN copies of that database. Let Servoy log into an
"unused" copy, let the demo user muck about, and when they exit then
stop the database, make a new copy of the demo database and start that up.

But a good portion of this thread is trying to answer your "transaction
without transactions" question. Maybe if you gave us a list of the
parameters of what you are trying to do, we could offer solutions that
completely work 'outside of the box' ??

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

Morley Chalmers

2005-12-14, 11:23 am

In article <439f7e28$1@forums-1-dub>,
Greg Fenton <greg. fenton_NOSPAM_@ianyw
here.com> wrote:

> I can't speak about Servoy as I know only how to spell it (assuming I
> copied your spelling correctly... :-) )
>
> How many "demo" users do you think will be running at the same time?
>
> One option I have (off the top of my head) is to have a "demo" database
> created and run NNN copies of that database. Let Servoy log into an
> "unused" copy, let the demo user muck about, and when they exit then
> stop the database, make a new copy of the demo database and start that up.
>
> But a good portion of this thread is trying to answer your "transaction
> without transactions" question. Maybe if you gave us a list of the
> parameters of what you are trying to do, we could offer solutions that
> completely work 'outside of the box' ??

All right, here's full disclosure on this project.

I'll start with Servoy since much of the Sybase community seems to be
unaware of it. Sybase ASA comes bundled with Servoy. Servoy is a front
end to generic SQL. It works with all of them -- Oracle, MS SQL Server,
MySQL, Postgres and of course Sybase. For just over a year now Servoy
comes bundled with Sybase ASA as the default database.
http://www.servoy.com.

I'm a refugee from the FileMaker Pro world. FMP made such a radical
format jump to its product I was forced to rewrite my solution from
scratch. Faced with that situation I chose to rewrite with Servoy. It
has a very FMP-like interface and uses JavaScript as the internal
scripting language. Servoy is Java-based, so serves all platforms.

As an FMP-refugee I don't have great depth in SQL and as a Servoy-user I
don't have to. I can write SQL calls when I need to, otherwise Servoy
will do it for me. I'm not a frequent user of Sybase Central. Don't have
to be.

My solution is a CRM for small biz. We'll do the hosting rather than the
clients. In other words there will be many different clients on the same
server. Clients work from a small Java applet. On launch they're asked
for a user name and password. That user name is associated with a
particular client ID, so they see only their records.

We've been in beta since July, the bugs seem to be squashed. I'm
currently focused on integration of the marketing website with the
server (auto registration of new clients) and with a demo.

From a marketing point of view I'd like prospects to have an obvious
user name and password which will always work, no fuss -- "demo". And
able to go to the demo at any time, no special procedures. Show it to a
colleague, show it to the boss, go there at 3 in the morning, etc.

When anyone logs on with that user name they should see a set of sample
records. They should have a full experience -- create records, alter
existing ones, navigate around, experiment. And when they're gone the
set of records remains unchanged.

I plan to go into test marketing as soon as the demo is working. Initial
test marketing will be to invited prospects. Once that run is
comfortable we'll open the website and start driving traffic to the site.

How many concurrent demo testers? Hard to say, but it's safe bet we
could have several, should be prepared for several. Because we're going
at this gradually we'll be closely monitoring -- the purpose of test
marketing.

Servoy permits concurrent log-ons. In other words, I can (and have) used
the Servoy Client applet to log on to one of my beta tester's set of
records while he is also logged on. To emphasize, I can (and have) used
the same user name and password as he. I plan to block this behaviour,
but it's important to realize Servoy itself doesn't automatically block
this.

Therefore it's possible for several different computers with different
IPs to log on as "demo". It's my understanding all users logging on as
"demo" will be known to the Servoy security area as "demo" and share a
common security user id. But have very different IP addresses.

The deeper I go into this issue the more I'm seeing this as a flaw in
the structure of Servoy -- as excellent as it has turned out to be in
all other respects.

I discovered if a transaction is started right at the beginning of a
demo run, this locks all subsequent users.

I should confess here how I discovered this problem. Deep within the
solution there are a couple of forms that start transactions on arrival,
commit or roll back on exit. During experiments I successfully locked
ALL users, ALL clients from logging on. I can and will block demo users
from access to those forms and am contemplating changing the interface
so transactions on those forms are not required.

Someone on the Servoy discussion forum suggested:
"Transactions also lock data so if you use them in multi user
systems always use them with caution, if you use them a lot
use a database with multiversion capabilities like Oracle
or Postgres."

Another Servoy user who happens to use Postgres sent me this:
"Even though PostgreSQL has multiversion capabilities I
still have sometimes a lock like you describe."

His problems with Postgres could be due to his level of skill. Dunno.
However I'm less than keen to switch databases. Sybase ASA has behaved
well thus far.

I'm right on the cusp of going to market. I'm eager to get this problem
solved. What solutions would work in this circumstance?

Chris Gruber

2005-12-14, 1:23 pm

Morley,

You could leverage sp_auto_login procedure which are invoked upon login to
populate global temporary tables (likely in conjunction with the "on commit
preserve rows"). As previously mentioned temporary table are very quick as
the operations on these tables do not get logged. The auto_login procedure
could populate the global temporary tables. A disconnect event could then
remove the records from the temporary tables. Also global temporary tables
enable users to only see their records, so there would be

Servoy's architecture is based on TomCat so I would imagine that each user
would have their own session. With that being said, sessions may have a
connection pool as TomCat/Servoy may implement a connection pool. So it is
very important to complete your transactions as connection pools need to be
cleaned up (committed/rolled back/resetting of options) if there is pooling
happening. Disconnect events and auto_login stored procedures would not
work with connection pools.

I am very interested to hear more about your dilemma. If you wish to
further discuss please contact me at chris.gruber@ianywhere.com

Chris Gruber
iAnywhere Solutions

"Morley Chalmers" <morley@7office.com> wrote in message
news:morley-7D8C73. 12125512122005@vip10
6.sybase.com...
> When a user logs into our solution with the user name of "demo" I'd like
> to start a transaction, and then when they quit to roll back that
> transaction. Allow the demo user to 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,

Morley Chalmers

2005-12-14, 8:24 pm

In article <43a05c80$1@forums-2-dub>,
"Chris Gruber" <chris.gruber@ianywhere.com> wrote:

> You could leverage sp_auto_login procedure which are invoked upon login to
> populate global temporary tables (likely in conjunction with the "on commit
> preserve rows"). As previously mentioned temporary table are very quick as
> the operations on these tables do not get logged. The auto_login procedure
> could populate the global temporary tables. A disconnect event could then
> remove the records from the temporary tables. Also global temporary tables
> enable users to only see their records, so there would be

I'm a refugee from the world of FileMaker Pro, so my depth in SQL is not
strong. Servoy is an excellent environment for someone like me.
Nevertheless I've succeeded in writing several SQL queries and
implementing them in a batch processor. I've got my feet wet but am very
aware of my relative inexperience.

I'm puzzled how forms would be able to display fields from these
temporary tables. And how relationships would function. Without
wholesale rewriting of the existing logic. I'm missing an important
piece of the puzzle.

> Servoy's architecture is based on TomCat so I would imagine that each user
> would have their own session. With that being said, sessions may have a
> connection pool as TomCat/Servoy may implement a connection pool. So it is
> very important to complete your transactions as connection pools need to be
> cleaned up (committed/rolled back/resetting of options) if there is pooling
> happening. Disconnect events and auto_login stored procedures would not
> work with connection pools.

Meaning, I assume, if the demo user abruptly disconnects from the
internet there will be trouble. That kind of cavalier behaviour can be
expected by some, so it must be planned for.

> I am very interested to hear more about your dilemma. If you wish to
> further discuss please contact me at chris.gruber@ianywhere.com

Sponsored Links