SQLDB Postgresql Transactions Issue

[tionov]

TPQConnection uses for every transaction a separate connection.

Thank you, but using another connection for each transaction in PG would IMO decrease stability of a PG server (if one has a lot of clients working), as described in the other post. I want to avoid connection pooling if ever possible. And in the case I described at the beginning of this thread, it would be absolutely unneccessary, if SQLdb would not force and keep open transactions even for single queries used to display just some data of one table.

[mse]

I repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal.

[tionov]

I repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal.

Yes, thank you. I looked at that. But I'm working with Lazarus now. And as the topic tells, with Postgresql.

[taazz]

3) Create a client Side mechanism ee copy the data to a memory dataset and close the transaction. every time a change is made on the data run the insert/update/delete on sort leaved transaction.

This seems to me done by the most complicated means possible. ;-)
Compared to the fact, that if you query the data for maybe a DBGrid without implicit "BEGIN;" ... would solve that problem.

Not really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple.

Ok. What does Action->caNone in SQLdb transactions really do? Do you know it?

Action is used to decide how to handle open transactions only it does not influence how the transactions starts but when a dataset is closed with an acitve transaction action is used to execute the default eg commit rollback etc.

[mse]

Not really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple.

The mentioned tmsesqlquery option dso_offline does it.

[stoppok]

I experienced the same problems as Tionov with SQLite, the situation here is even worse: On a second PC I could not even Select the data from my database, the query resulted in a timeout.

[Zoran]

Not really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple.

The mentioned tmsesqlquery option dso_offline does it.

I repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal.

Hello, Martin,
Have you thought about contributing these SQLDB options to FCL?

[tionov]

I experienced the same problems as Tionov with SQLite, the situation here is even worse: On a second PC I could not even Select the data from my database, the query resulted in a timeout.

Do not use it. SQLite locks the whole table on a started transaction (same as Mysql did in early days). If one writes "BEGIN;" before reading, the table is locked. SQLdb does that and you can't prevent it from doing this.

In common database development, the use of "BEGIN;", "COMMIT;" or "ROLLBACK;" is in the hand of the person, which writes the code for a database-centric application. Not so here. Firebird seems to have support for nested transactions*. So SQLdb's behavior may work here.

*But nested transactions are usually bad.

[tionov]

I repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal.

What does "tao_fake" mean?

[mse]

@zoran:
The code is here:
https://gitorious.org/mseide-msegui/mseide-msegui/source/lib/common/db
Feel free to backport to FCL.

@stoppok:
MSEgui version of tsqlite3connection has options slo_transactions flag which is by default off. If it is off, it uses implicit transactions. Please note, Sqlite3 holds a file lock while reading the resultset. So if packagerecord of tsqlquery <> -1 it is possible that a pending read operation blocks other applications from writing data. Even an open design time component blocks the compiled, running and debugged application.

@tionov:
tao_fake means that for attached components (datasets, tsqlresult, tsqlstatement,tsqllookupbuffer...) tmsesqltransaction behaves as normal but actually doesn't call begin-/endtransaction.
BTW: Firebird has support for *parallel* transactions in a single connection. Another great and unique feature is "commitretaining".

[tionov]

@tionov:
tao_fake means that for attached components (datasets, tsqlresult, tsqlstatement,tsqllookupbuffer...) tmsesqltransaction behaves as normal but actually doesn't call begin-/endtransaction.
BTW: Firebird has support for *parallel* transactions in a single connection. Another great and unique feature is "commitretaining".

URGS. Ok, now I know, why I would never use Firebird.

http://www.firebirdsql.org/file/documentation/papers_presentations/html/paper-fbent-impacting.html

With Firebird (and InterBase), Commit Retaining causes transactions to remain interesting indefinitely. Garbage collection effectively ceases on the “standard” Borland RAD tools database application and any other applications that make use of Commit Retaining. Such systems are fraught with problems of progressively degrading performance that cannot be resolved except by shutting down the database and allowing these old transactions to die.

These are all ill concepts if you are used to work with a database like Postgresql, where a lot of clients hammer on the whole day long for months without deadlocks, performance drops, data lost. Postgresql is a rock you can rely on and it is absolutely transparent, if you are working with it as a developer.

Commit Retaining is not SQL Standard. And yes, Firebird is unique.

Thank you for clarification.

[mse]

You don't need to use "commitretaining" but you can. ;-)

[mangakissa]

Firebird / Interbase must have transaction to work properly with unsaved data.
Commit Retaining does nothing more than save your data to the databasefile, so other users can read the new/modified data. MySQL does it similar with savepoints.

I know enough people who uses Firebird in a multi environment

[tionov]

You don't need to use "commitretaining" but you can. ;-)

:-)

I do not want to bash Firebird here. I see, as a beginner of Lazarus programming, that I find a "traditional" context here, which led to solutions that may work good with FB. But it should be clear that SQLdb is not really useful together with SQL databases that fulfil "only" SQL standard. Ok, for me the thread is over now. I'll use Zeoslib or write my own db-layer (if I understand FPC enough, which will last for a while) or do a combination of both.

[marcov]

MySQL does it similar with savepoints.

Oracle does the same with savepoints, and iirc can use named transactions for multiple transaction/connection too.