ZConnection initiated transaction

[iru]

Gentlefolk,

Using Lazarus, ZEOS 7.0, FB 2.13, Win XP.

I have an application which works well for a single user.

If I have a second user the user cannot access changes made by the initial user.

I thought that this may be caused by the initial user holding a transaction open (no commit). With the help of a third party FB monitor I cleaned up some strange issues which started transactions.

No luck, the FB monitor still showed a single long transaction for each connected user.

By stepping my way through the code it appears that on performing a ZConnection.Connect a transaction is started and it stays active until I stop the application.

Is this correct?
Would this transaction inhibit access to the data for that user until a disconnect?

From my understanding of things I thought a transaction-commit around some database changes should only inhibit access for that tranaction-commit pair.

Any information, help, whatever appreciated, Ian.

[seawolf]

Hello,
in order to give you a help we need some information.
First of all , after every transaction, do you execute a commit command?
How is set ZConnection.IsolationLevel?
Have you tried ZConnection.Properties.Add('hard_commit=yes')?

[iru]

Gentlefolk,

Thank you for the response.

1. Yes, I have autocommit = true, the then StartTransaction-commit around any accesses to tables. Used TZSQLMonitor to work out start-commit pairs.

2. ZConnection.Isolation is set to tlRepeatableRead.

3. ZConnection.Properties.Add(...........). No, will try this as soon as I can.

I have been using the Sinatica FB monitor and tZSqlMonitor to view what is going on.

My main worry is that stepping though the code shows a transaction starting when I execute the ZConnection.Connect.

Thanks, Ian.

[iru]

Gentlefolk,

Tried ZConnection.Properties.Add('hard_commit=yes') .

Breakpoint the program after ZConnection.Connect, no transaction seen in Sinatica monitor.

Continued the progran until it stopped on a user prompt.

No outstanding transactions.
Lots of SQL statements I recognise which suggest that transactions-commits have come and gone.

Looks encouraging, will test further, may take some time due to commitments.......

Thanks, Ian.

[iru]

Genlefolk,

A little more chasing.

1. With the statement ZConnection.Properties.Add('hard_commit=yes')
I step my way through the TZConnection code. In module ZDBCInterbase line 556 the test is made "if not FHardCommit then StartTransaction".

The test fails and NO transaction is started (as observed in Sinatica).

I set "hard_commit=no" (default) and when "if not FHardCommit then StartTransaction" is executed the result is TRUE and "StartTransaction" is executed and appears to send something to FB which creates a transaction as seen in Sinatica.

I note that for the only zxconnection.Connect in the code the "if not FHardCommit then StartTransaction" at line 556 is executed twice.

2. With "hard_commit=true" I let my code run until a point where a StartTransaction-Commit pair is encountered.
Stepping through the code I seen nothing that would indicate to me that a transaction would be started on the FB server.

A test on FTrHandle in ZDBCInterbase6 at line 359 fails because FTrHandle is NIL therefore jumping ang "hard_commit" code.

if FTrHandle <> nil then
begin
if FHardCommit then


Looks like SQL statements are being sent to FB but no StartTransaction-Commit commands/instructions which I presumed the StartTransaction-Commit pair would generate.

If there are no transactions I will have problems with access control by multiple users.

Am I missing something? Is there something I do not understand about transactions?

I am using zeos 7 to get around some problems that occurred in 6.6.

Thanks, Ian

[iru]

Gentlefolk,

Woops, error in previous post.

Item 2 is incorrect, the following code is executed

if FTrHandle <> nil then
begin
if FHardCommit then
begin
FPlainDriver.isc_commit_transaction(@FStatusVector, @FTrHandle);
FTrHandle := nil;
end
else
FPlainDriver.isc_commit_retaining(@FStatusVector, @FTrHandle);

Commit_transaction with 'hard_commit=yes' and commit_retaining with hard_commit =no.

Sometimes I also see transactions in the Sinatica monitor, only rarely and have not been do any detailed work on that.

Thanks, Ian.

[iru]

Gentlefolk,

Put some log code in Zonnection.StartTransaction/Commit.

Executed all parts of the program slowly, while looking at Sinatica transaction display, found some code that did not commit the TX.

Fixed that and did some more work with Sinatica, see lots of SQL statements which all look OK.

I am comming to the opinion that Sinatica does not display very short transactions.

So at the moment it looks like "hard_commit=yes" stops the transaction created at ZConnection.Connect, and the transaction-commit pair mechanism appears to work.

I will as soon as possible check out transaction concurrency between a couple of versions of the program and see how my data looks.

Ian

[iru]

Gentlefolk,

I have done some work looking at he data loaded/modified on one copy of the program, checked the database, checked what I see in a second copy of the program.

Looks good, cannot see changes while a TX is open in the first copy of the program accessible in the second copy of the program. Commit the TX in the first program and I can see any changes in the second.

Looks like "hard_commit=yes" does the trick.

Looks good.

Thanks for the support, Ian.

[mdaems]

hard_commit=yes is very experimental and should not be necessary, I believe. Are you sure using another TransactionIsolationLevel isn't enough?
Remember : using hard commits ruins all open cursors. So when you're having open datasets you might run into trouble.

Mark