+++ Parallel Transaction / PrepareTransaction() ? +++

[Lutz.H]

What i try to do:
o Only ONE ZConnection in the projects main form
o all other component - like ZTable - refer to this ZConnection
as i dont want to waste time opening more connections in
other forms
o some events are called by external hardware like
for example barcode-scanners

What might happen:
o A transaction begins in form X
o Before the transaction of form X ends the routine
is interrupted by the external event
o The external event itself begins and ends a transaction
o later the form X ends its transaction

If i only use ONE ZComponent that is what happens:
o Assume transaction of form X is rollbacked
o transaction of the external event is commited
BUT AS the transaction of the external event is
INCLUDED within the transaction of the form X
(because of the timing) the transaction of the
external event gets rollbacked as well ...

I know that it is possible to solve this problem with
independent ZConnections.
Is there another way ? I saw a method called
o PrepareTransaction(String)

Can this method be used to handle my problem ?
If so, how is it used ? I dont find e description of it

Thanx for your replies ...

Lutz

[Lutz.H]

Maybe another important information:
I use firebird 2.x as database

[seawolf]

For every transaction you can specify an isolation level, so take a look at the "set transaction" firebird command.

Moreover ZConnnection has a "transaction isolation" property.

[Lutz.H]

[quote="seawolf"]For every transaction you can specify an isolation level, so take a look at the "set transaction" firebird command.

Moreover ZConnnection has a "transaction isolation" property.[/quote]


I am not sure, how this should help:
t+0: Transaction 1 begins
t+1: Transaction 2 begins
t+2: Transaction 2 is commited
t+3: Transaction 1 is rollbacked

As Transaction 2 is "part of" transaction 1
(some kind of "subtransaction") Transaction2
is rollbacked as well because they use the
same connection

I would like a behaviour that makes Transaction 2
independent of transaction 1, although using the
same ZConnection

BUT: The connection cannot know whether i want
a "subtransaction" or an independant one ...

This behaviour cannot be set by transaction isolation level

Any more proposals ?

[trupka]

I believe its not possible with current ZEOS versions, at least without larger changes, because ZConnection doesn't have separate transaction component. Currently 1 Connection = 1 Transaction so, obviously, "parallel transactions" are impossible. To achieve that kind of functionality TZConnection must have capability to handle some kind of TZTransaction pool and TZTransaction as separate component - something like IBX TIBDatabase/TIBTransaction. This would be nice feature...

[DarylC]

I know that it is possible to solve this problem with
independent ZConnections. Lutz

This seems like an easy solution. Why do you want to avoid it? You could have several ZConnection components. One for the barcoder, one for the external event...as many as you need at one time.

[Lutz.H]

[quote="DarylC"][quote="Lutz.H"]I know that it is possible to solve this problem with
independent ZConnections. Lutz[/quote]

This seems like an easy solution. Why do you want to avoid it? You could have several ZConnection components. One for the barcoder, one for the external event...as many as you need at one time.[/quote]


I want to avoid to many connections, because we use Firebird-Server
With Firebird there are two different concepts:
The classic and the super-server.
http://www.ibphoenix.com/main.nfs?a=ibp ... vs_classic

The classic server creates one new process for every connection !
If i make a new connection for - maybe - each form of my application
this might increase the number of processes 10 or twenty times
on the server. Even with the super server this increases the number
of threads linear to the number of connections

This might slow down the server sooner or later and that is the reason,
why we try to avoid more connections than necessary.

[mdaems]

I don't know how many databases support different simultaneous transactions within one connection. But I know at least oracle and mysql do NOT support it. (Except for the special case of autonomous transactions in Oracle, which could help you out here)
Does FB/IB support this? Or at least the stricter case of nested transactions?

In the case you describe I would just use two connection components to do the job. Depending on the number of times you really have the 2 transactions occuring together, you can decide if you want to
- keep both connections open all the time
- just open and close the second connection when an other transaction is still going on. In case the first connection is idle you can reuse it.
- open and close each connection only when it's needed.

Warning : using 2 connections means the second connection may or may not know what changes the first connections did to the database, depending on the transaction isolation levels.

Mark

[Lutz.H]

I don't know how many databases support different simultaneous transactions within one connection. But I know at least oracle and mysql do NOT support it. (Except for the special case of autonomous transactions in Oracle, which could help you out here)
Does FB/IB support this? Or at least the stricter case of nested transactions?
Mark

Yes Firebird supports this.
Thats why Interbase / Firebird-specific components like
MDO ( http://sourceforge.net/projects/mdo )
have an extra component called TMDOTransaction
Several TMDOTransactions can be linked to one TMDODatabase (Connection) and objects like TMDOTable or TMDOQuery can be
linked to one of these transactions

[gto]

I've used MDO a long time ago, but I did remember that feature. And other libs use this, too.

Let's see if I undertood correctly:

TZConnection will have a TZTransaction property, where you can configure things abour transactions, and this will enable retro-compatibility for those people who don't mess with several transactions. This will be the "usual" way of work.

The TZTransaction component will have a TZConnection property, which can be linked to a working connection and work on a different transaction than the one into TZConnection. This outside transaction will have it's own isolation level and ID. Several TZTransaction can be linked to a sinle TZConnection (there's some limit?).

The TZQuery, TZReadOnlyQuery and TZTable (and other, possibly) will have a transaction property, initially null. When not filled with a transaction, the transaction into TZConnection will be used. Otherwise, the specific TZTransaction will be used.

I can do this modification in some time. We only need to see if it really worth the time spent and what databases support this feature. To me, looks good and usefull, as I do use firebird and it will help me in some ways.

[Lutz.H]

...
Let's see if I undertood correctly:
...
I can do this modification in some time. We only need to see if it really worth the time spent and what databases support this feature. To me, looks good and usefull, as I do use firebird and it will help me in some ways.

Hey gto,

yes, you understood it perfect. Unfortunatelly i can not give you a hint
which other databases support this feature, as i am most familiar with
firebird and dont know the other databases that much

[mdaems]

mysql doesn't.

From a scan of the plain driver sources I guess
- ADA doesn't
- DBLib doesn't
- Oracle MAY support it
- Postgres doesn't
- SQLite doesn't

I think gto may be a little too optimistic on this.
The only way I see it possible is by making the IB transaction a special kind of connection object.

Mark

[Lutz.H]

mysql doesn't.
...
I think gto may be a little too optimistic on this.
The only way I see it possible is by making the IB transaction a special kind of connection object.

Mark

So as long as there is no solution for the described problem
i have to use several Connections :-(

To make this easier, i made a change in ZConnection.pas:
There is a new property "ParentConnection".
When setting ParentConnection to an existing ZConnection object
ALL the settings of this object are inherited. Thus i dont have to
copy the settings all the time.
Code is in the attachment (if someone likes)

[shmarchiori]

A long time ago, I got the same "trouble", where T1 MIGHT have been completed BEFORE T2, no interruptions were acceptable. So, pooling is unique solution to this case.
If your T1 and T2 came from different places, AND the execution order doesn't matter, I think using two connection is obviuos.
Else, implementation of component like TZTransaction ou TZTransactionPool would be necessary.
How do I solved my problem? Queuing SQL statements. As simple as it is:

- T1 pool comes from place1
- T2 pool comes from another
- T1 SQL can be executed IF THERE ARE NO associated items on pool 2. If there's, handle it (recursive)
- Thinking about creating components, each transaction should have an ID, and a Parent ID. So, I create a master transaction, and many associates as I want...Of course, too much parents/children can become lazy (put some limits?).

[alexis]

Any work in process about this?
Would be possible using parallel transaction with Zeos-Firebird in a near future?
Thanks