SQLDB Postgresql Transactions Issue

[tionov]

And then I will call those SQL queries(del/update/insert) using a separate sqldb components that were created using code and release them after each use, for example after deleting a row of data, I will call again the procedure above to update the data grid for the change that takes place.
And that remains open for the whole session as long as the user will not close the program. I am using the TSQLQuery's afterscroll event to edit/delete/update a row within that data grid. And its perfect. See the screenshot attachment. So even if sqldb created a BEGIN transaction, I was still able to manipulate the data being displayed.

Yes you can do this (using more connections and transactions in one client and keeping transactions open) but in a multiuser-environment this will produce problems especially if the clients occasionally edit the same data, try experimenting ... Please understand that I will not invest more time, for further reading I recommend this:

http://www.postgresql.org/docs/9.2/static/transaction-iso.html

Here you can see, how PG handles concurrent transactions and the visibility of data changes.

[LacaK]

Where's link to that SQL standard ? I don't like way of:
1.Login -> START TRANSACTION (sitting in transaction on the database)
2.Do something
3.COMMIT/ROLLBACK and then START TRANSACTION again (goto 1.)
4.Logout

In 1st example all database connections are "in transaction", in 2nd example client connection is in transaction only when it's really doing something, so much better for rdbms server performance.

I do not think so. When you open connection it does not mean, that you start transaction at same time.
Transaction is not started until you execute any SQL statement.
Which is okay as far as any SQL statament must be executed in context of any transaction.

Second, I do not think, that when I start transaction and I do not perform any SQL statement, that it affect server performance.

For example when I select data for using in DBGrid with later possibility of editing. I do:
1. Open TSQLQuery
2. CommitRetaining
at this point I have active transaction, but it does not block others transactions on SQL server (nor holds locks on tables or so)
3. perform any updates
4. again CommitRetaining or Commit

[taazz]

True but since postgreSQL does not support multiple transactions on a single connection that means that I have to open a new connection on each table browser I open or on each stored procedure I run etc, making it a bit hard to predict and manage connections ee instead one per client now I have god knows how many depending on the use case of each user. As far as postgresql goes that might not be a problem (I have no idea) but what is going to happen on commercial servers where you pay per cpu/connection or you are restricted by them eg the 5 concurrent connection limit of the MSSQL express 2000, how I'm supposed to manage that (don't say very carefully)? It is imperative that the mandatory transaction requirement is lifted from the library and placed on the connection or the server used.

[swierzbicki]

I'm also surprised the way SQLDB is working.
Like other users reported, I'm working like this :
 
1.Connecting
2.Opening queries (no need any transaction here)
3.START TRANSACTION
4.Modifying some data, SQL Update Script and so on
4.COMMIT/ROLLBACK 
5.Refreshing certains queries if needed
5.Logout.

Automatic transaction should be an option and not a standard.

BR

[elidorio]

Hello,
Has a project called JDO is based on SQLDB.
Seems to me he is something like Zeos Automatic transactions.
Check the link below, suddenly, you can even take some ideas and implement in SQLDB.

https://github.com/silvioprog/jdo

[]'s

Edson

[allanregistos]

Hi,
Can we file a bug for this so that the developers will take notice?

I'm also surprised the way SQLDB is working.
Like other users reported, I'm working like this :
 
1.Connecting
2.Opening queries (no need any transaction here)
3.START TRANSACTION
4.Modifying some data, SQL Update Script and so on
4.COMMIT/ROLLBACK 
5.Refreshing certains queries if needed
5.Logout.

Automatic transaction should be an option and not a standard.

BR

[allanregistos]

Where's link to that SQL standard ? I don't like way of:
1.Login -> START TRANSACTION (sitting in transaction on the database)
2.Do something
3.COMMIT/ROLLBACK and then START TRANSACTION again (goto 1.)
4.Logout

In 1st example all database connections are "in transaction", in 2nd example client connection is in transaction only when it's really doing something, so much better for rdbms server performance.

I do not think so. When you open connection it does not mean, that you start transaction at same time.
Transaction is not started until you execute any SQL statement.

Yes, which is what sqldb behaves? I do not need to call BEGIN/COMMIT; when I only need to SELECT * data for display in a grid which is what PG wants me to do for the sake of performance.

Which is okay as far as any SQL statament must be executed in context of any transaction.

You mean any 'SELECT * FROM MyTable' must exist inside a transaction? Can you define what transaction you are talking about?

Second, I do not think, that when I start transaction and I do not perform any SQL statement, that it affect server performance.

I think it affects somehow because the database engine will see your 'session' as a 'transaction' and it allocates resources for you in that respect. It expects BEGIN <Multiple SQL statements> COMMIT; therefore it allocates resources for you based on what you need. If I only have to update one table of course it only needs a single SQL statement* and I do not need to call BEGIN/COMMIT for it so the Database engine will see and will provide me with the resources I need for that single SQL statement, so compare this to your Started Transaction<regardless if you only have one SQL statement, you still have to use Transaction?> and then can you see that the DB server will allocate more CPU cycles and memory for you than with my single SQL statement.

For example when I select data for using in DBGrid with later possibility of editing. I do:
1. Open TSQLQuery
2. CommitRetaining
at this point I have active transaction, but it does not block others transactions on SQL server (nor holds locks on tables or so)
3. perform any updates
4. again CommitRetaining or Commit

This is I think fine with Firebird as I learned from this thread. So, the more I will recommend PosgreSQL from now on. Thanks to this thread. I think it is now even more safer for me to just use PostgreSQL rather than support multiple database with my applications.

Regards,
Allan

*of course, as a DB administrator, I must decide for myself if an update of one table requires further checking or updates to other tables and this will require multiple SQL statements and therefore it needs to reside under a Transaction session for this is what Transaction was made for:
http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html

[LacaK]

From your link:
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might get the effect of transaction blocks without asking. Check the documentation for the interface you are using.

[allanregistos]

From your link:
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might get the effect of transaction blocks without asking. Check the documentation for the interface you are using.

Yes, that's correct. If this is the case as the note described the interface we discuss so far is sqldb and the OP stated that interfacing with PostgreSQL must not use the BEGIN/COMMIT automatically and need to be turned off as an option to avoid deadlocks. However, if it can be turned off as in zeosdb, but PostgreSQL treated every SQL statement as within in the realm of transaction anyway, so what's the point of improving sqldb to adapt to PG's requirements?

I still have to understand what tdbgrid was doing when it display data. I think as of this moment that whenever I see data on the grid(even if empty as long as it is connected to the datasource), it is in the context of open transaction. This is where the 'Transaction' we've been talking about differs. The single SQL statement that was treated by PG automatically with an implicit BEGIN/COMMIT block which will happen in an instant and the 'Transaction' that is open as long as the program is active, so if it is active, then deadlocks might occur in a multi-user environment. Imagine tens to hundreds of users using the same application with all of them are active(i.e., connected to the same DB and are all with active transactions).  Single SQL statements with implicit BEGIN/COMMIT might be different in this context because it will happen so fast, than calling BEGIN/COMMIT explicitly with SQL statement like this: ' SELECT * FROM MyTable;' and the interface will wait for the user for the component.close/free commands before closing the transaction.

As I understood, the OP did not like this behavior, so if we follow the OP's requirements, we must not use tdbgrid at all but instead use TStringGrid to display data.

• Query for data and store them in the array using SQL by hand (postgres unit)
  
• Loop through the data in the array and store each of them in TStringGrid

A more laborious approach of fetching data.

Regards,
Allan

[taazz]

you took a simple matter of allowing or not implicit transactions in to a different level.

1) there is no reference of locking anything inside a transaction. Where is the documentation on locking?
2) from what I can tell the OP was talking about deadlocks inside a single client which might start to happen if you use multiple threads to update the database and you want each of the threads to be enclosed in their own transaction, then you have a problem.

There is not mentioned anywhere that a transaction blocks other connections from changing or accessing data even the same record and no there is no way a single thread application could dead lock it self or other clients, as far as I can understand that is.

So we do like to see that artificial requirement lifted or to put it better we would like to have the choice to use it instead of imposing it on us. I do not think that there is going to be any noticeable difference for single threaded application other than the fact that now you know how to choose wiser, that is.

[allanregistos]

you took a simple matter of allowing or not implicit transactions in to a different level.

1) there is no reference of locking anything inside a transaction. Where is the documentation on locking?

PG Explicit locking: http://www.postgresql.org/docs/9.3/static/explicit-locking.html

2) from what I can tell the OP was talking about deadlocks inside a single client which might start to happen if you use multiple threads to update the database and you want each of the threads to be enclosed in their own transaction, then you have a problem.

There is not mentioned anywhere that a transaction blocks other connections from changing or accessing data even the same record and no there is no way a single thread application could dead lock it self or other clients, as far as I can understand that is.

Yes, as I have tested in my app.

So we do like to see that artificial requirement lifted or to put it better we would like to have the choice to use it instead of imposing it on us. I do not think that there is going to be any noticeable difference for single threaded application other than the fact that now you know how to choose wiser, that is.

[taazz]

Took a look on the page you linked it says nothing about transactions locking anything, only the commands place locks eg the select will place a lock on the table and as long that server side cursor is open ee as long as you have grid showing data it will keep that lock regardless of the transaction it was in, so over all  it has no difference (on the locking that is) with the implicit transaction the server starts for you, as long as you use a new connection for each thread you should be fine.

Non the less I would like to see your demo app showing the problem if I'm not imposing to much on your schedule.

[allanregistos]

Took a look on the page you linked it says nothing about transactions locking anything, only the commands place locks eg the select will place a lock on the table and as long that server side cursor is open ee as long as you have grid showing data it will keep that lock regardless of the transaction it was in, so over all  it has no difference (on the locking that is) with the implicit transaction the server starts for you, as long as you use a new connection for each thread you should be fine.

Yes, I am using one transaction for displaying data and another implicitly as sqldb will do for each delete/insert/update etc.