How to work with transaction in PostgreSQL

[alexs75]

How to configure TZConnection to work in the following modes:

All reading start without explicit transaction on the server

When you call a method in the code ZConnection.StartTransaction start a transaction on the server
When ZConnection.Commit or ZConnection.Rollback respectively to complete or cancel the transaction on server.

This mode is optimal for work with Postger SQL server.

[marsupilami]

Hello alexs75,

my personal suggestion: Set your transaction isolation level to tiReadCommitted. Start explicit transactions if you need them. To read up on explicit transactions, StartTransactin, commit, Rollback and the autocommit mode have a look at this wiki page:
https://sourceforge.net/p/zeoslib/wiki/ ... utoCommit/

If you have questions, please let me know : )

With best regards,

Jan

[alexs75]

It operates in the starting mode is not explicit transaction. In 99% of cases, do not have to explicitly start the transaction. Zeos not true works ReadCommited mode. He was forced to start the transaction and all the time it keeps open. It's good for FireBird. PostgreSQL does not require it.

Now Zeos always at the end of the transaction is clearly a new start:

Code: Select all

procedure TZPostgreSQLConnection.Commit;
...
    StartTransactionSupport;

It turns out that there is always an open transaction at the time of application. For PostgreSQL is just an extra load. He is the SELECT query runs in ReadCommited mode

So I turn itNone mode.
In doing command handlers OnStartTransaction

Code: Select all

procedure TMainDM.MainDBStartTransaction (Sender: TObject);
begin
  MainDB.ExecuteDirect ( 'BEGIN');
  MainDB.ExecuteDirect ( 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
end;

In doing command handlers OnCommit

Code: Select all

procedure TMainDM.MainDBCommit (Sender: TObject);
begin
  MainDB.ExecuteDirect ( 'COMMIT');
end;

In doing command handlers OnRollback

Code: Select all

procedure TMainDM.MainDBRollback (Sender: TObject);
begin
  MainDB.ExecuteDirect ( 'ROLLBACK');
end;

In this case, PostgreSQL works most productively.

[marsupilami]

Soooo - I spent some time reading the PostgreSQL manual. Please bear with me if I get too picky about details here. Also the text got quite lenthy.

First of all: Zeos Works in Read Committed isolation [1]:

Read Committed is the default isolation level in PostgreSQL.

You simply will not get any lower transaction isolation with PostgreSQL. [2]:

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed.

But then Zeos doesn't use the PostgreSQL supplied AutoCommit and implements its own on top of PostgreSQL. This doesn't always work flawless, I have to admit. This is documented in the Wiki page that I referenced to in an earlier post.

What you do by using tiNone is bypass the Zeos transaction control, get PostgreSQL AutoCommit that way and then you implement your own transaction control on top of that by using ExecuteDirect.

Honestly I doubt that you will save much ressources by that. This is because PostgreSQL also needs to manage its transactions. I suggest you read up on MVCC. At least for firebird there is quite some documentation about MVCC and that will answer the question why MVCC will never work without transactions.
So - what can PostgreSQL do. In the first case it will create a transaction right after connection and commit that after it receives and execute a command. Afterwards it has to create the next transaction. In this model you would have a similar consumption of ressources as you would have with Zeos because there always is a transaction. In a second case it could create the transaction right when it receives the command, start the transaction, execute the command and commit or rollback that transaction immediately after the command finishes. In that case the ressource consumption on the server would be slightly lower than by the things Zeos currently does. But then - I doubt that the ressources you can save by your approach will be worth the extra time that you have to invest by building your own transaction control.

Bottom line: I have to recheck that, but as far as I know the current implementation of the Zeos Components and the DBC layer have no concept to know wether the components need to simulate AutoCommit mode for a database or wether a database can handle that on its own. So it will not be easy to extend them and make things work for all other supported databases. With the current implementation of prepared statements this extension is a must have for the release of prepared statements.
Releasing prepared statements at the beginning of a new transaction has its reasons, it was a bug fix. In an earlier implementation statements were released as soon as possible. Unfortunately this lead to errors when statements in an transaction raised an error. If a statement fails to execute properly PostgreSQL will mark that Transaction as being failed. In failed transactions one cannot do anything besides rolling them back. You can't release prepared statements. This could lead to problems in Applications using Zeos because Zeos would sometimes try to release a statement in an transaction that is in a failed state. The application then wouldn't see the original Exception but it only would see an exception that you can't do anything in a failed transaction. There was no way to find out what caused the transaction to fail.
As a counter measure I moved the release of prepared statements to the start of a new transaction, because there we could be sure that the transaction is in a good state. Case solved.
Well - it seems, that introduced a new case since tiNone will not use the PostgreSQL DBC transaction support and by this statements will never get freed. There is one way out of this for me. We need to implement a way where stetements can be released either immediately or where the release of transactions can be postponed. Then there are two ways to implement that.
Way one: Implement a way of tracking if a transaction is in a healthy state or wether it is failed. Release statements immediately while the transaction is healthy, postpone the release of statements until the start of the next transaction if the transaction is failed. This seems quite hard for me to achieve, because honesly I have no idea on how to do that in a good way where I don't need to modify one gazillion places all over the dbc driver. Also it seems error prone to me.
Way two: Always release statements in the next transaction. Only release them immediately if the PostgreSQL AutoCommit is in use currently. This seems like the better idea to me. It is more close to the current implementation, I have an idea on how to achieve that. But it is time consuming because it will require for the DBC layer and the Components to be modified to have a common view on how to handle AutoCommit mode. Ideally the DBC layer would inform the components if it can do AutoCommit on its own. Then the components would know if they had to do an emulation or not. This also has the added benefit that Zeos could learn on how to make use of the AutoCommit of the underlying databases. tiNone would translate to tiReadCommitted for PostgreSQL - they would both behave the same - as PostgreSQL does anyway.

Since both soluions require quite some time, that I cannot spend currently, what do we do in the short term? My suggestion is to disable tiNone for PostgreSQL. Raise an exception if somebody tries to use it so the programmer will know he tries to do something unsupported. Additionally create a ticket in the Bugtracker that explains on how things should be fixed in the long term. A ticket like that was already created by me because I want to have the abililty to use the database supplied AutoCommit mode, if it exists. It will save round trips to the database if it can be leveraged and should not suffer some of the bugs that the current implementation has. We could extend it to say that tiNone needs to be reenabled for PostgreSQL. The problem is - this would destroy your application in the short term.

So - what is your opinion? If you would take on the challenge of implementing a good solution, I think you and Zeos can benefit from that. Otherwise you will have to wait until I have the time to implement it myself.

[1]: https://www.postgresql.org/docs/9.6/static/transaction-iso.html#XACT-READ-COMMITTED
[2]: https://www.postgresql.org/docs/9.6/static/transaction-iso.html

[alexs75]

All examples work with ReadCommited shown only for a short time transactions. But for me the critical fact that I can not control people's behavior - they can run the application (and nachanaet transaction) for the whole day. At the same time a lot of people working (> 1000). And get a very long transaction. The load on the server when it is big enough.
On the server a lot of connections in the status "Idle in Transaction". I'm at the beginning of trying to use itReadCommited mode - I had problems with DeadLock. Perhaps it was my fault. But after switching to manual transaction rule - I have never encountered DeadLock.

I have used the scheme I was completely satisfied. I manually manage critical transactions (update multiple items at a time). For simple (updates in a single record in one table) Postgre mechanisms fully satisfied.

Maybe leave the opportunity to work in itNone mode (like now)? If you can not give the developer full control of transactions.
And Firebird can not be compared. The FB is simply not possible to work without an explicit transaction start. This is the main difference.

[marsupilami]

Hello alexs75,

as far as I know an open transaction where nothing happened should not have any influence. But then - I am not a PostgreSQL expert. Also an transaction doesn't generate a load per se. Yes - it probably needs some bytes for management and it could create a problem for the vacuum process. The problem here is - I didn't see any documentation on wether PostgreSQL in auto commit mode automatically creates a transaction right after the last transaction is committed and this transaction waits for the next statement or if it creates the transaction right before running the next statement.

For me the question wether it can be compared with firebird depends on how PostgreSQL behaves. Both databases support autocommit. But for our problem that is a more or less academic discussion because it has no influence on Zeos. So - lets focus on Zeos and how we can make things better.

The Zeos aim is to enable the developer to have control over transactions and autocommit. This requires a concept on how the DBC drivers should beave in regards to autocommit and how the components can make use of that and know wether they have to emulate autocommit or not. But this is nothing that I can solve in the short term. I have to finish projects that bring me money. If you want to step up to do that - ok - patches are welcome ; )
Keeping things like they are honestly is no viable option for me. That is because with the current implementation of tiNone prepared statements will accumulate at the server side because they never get released. Which is why you initially opened the other thread.

Suggestions?

[alexs75]

It is because of resource leaks in the latest versions ZEOS I raised the topic.
it is necessary to relieve them. Unfortunately I'm not very big expert in the ZEOS - yet not entirely understood.
And I do not get the money for this work :-)

In my opinion, the release of unused STATMENT code necessary to make a separate function and call it at each operation COMMIT / ROLLBACK not paying attention to TransactIsolationLevel

For the future - may not explicitly start a transaction for TransactIsolationLevel = itReadCommited? The default PostgreSQL always works in this mode: https://www.postgresql.org/docs/9.1/sta ... n-iso.html
And just for tiRepeatableRead and tiSerializable need to do some settings.

[marsupilami]

It is because of resource leaks in the latest versions ZEOS I raised the topic.
it is necessary to relieve them. Unfortunately I'm not very big expert in the ZEOS - yet not entirely understood.

Exactly - and wit the current implementation of tiNone I see no way to safely release prepared statements.

And I do not get the money for this work :-)

Me neither

In my opinion, the release of unused STATMENT code necessary to make a separate function and call it at each operation COMMIT / ROLLBACK not paying attention to TransactIsolationLevel

There are two problems with that - the way you are doing things now you will get statements prepared and never released, even if we would release them in the commit or rollback call - you simply never call it. This seems like no good solution.
Also even if you would call Commit / Rollback - there is no safe way for Zeos to know wether the current transaction is healthy of failed. In a failed transaction you can't do anything besides rolling it back. Trying to release a prepared statement in a failed transaction will lead to another error message. That is why I decided to move the release of prepared statements right to the start of a new transaction. At the start of a new transaction you can be sure that it is healthy and that you can release statements then.

For the future - may not explicitly start a transaction for TransactIsolationLevel = itReadCommited?

That is what I want to do. But it requires work not only on the DBC driver but also on the Components, they need to stop emulating Autocommit functionality. Unfortunately I don't have the time to do that currently.

[alexs75]

I see it as follows:
For itReadCommited mode will be 2 situations:
1. Without starting average bank explicitly in the code of my program. Then nothing at all to do. All messages from PostgreSQL reach Developer unchanged. Releasing resources to do both in the version prior to 3936.
2. With a clear start and end of the transaction - to begin a transaction on the server, and finish it. Frees resources after a successful COMMIT / ROLBACK. If there was an error - the release resources after a software error handling as follows before the first reference to the server (for example, in the first SELECT query).

[marsupilami]

Hello Hello alexs75,

I am working on a solution. But that will take some days. Stay tuned.

Jan

[marsupilami]

Hello alexs75,

check out my changes in the Zeos 7.2 SVN. But beware tiNone and tiReadUncommitted now map to tiReadCommitted. Please don't try to implement your own transaction control on top of the Zeos transaction control. Use TZConnections methods and properties please. Prepared Statements are now freed immediately if you are running in AutoCommit mode and they will be freed after committing or rolling back your transaction if you have explicit transaction control in use. If things don't work out, let me know.

With best regards,

Jan