SQLDB Postgresql Transactions Issue

[goodname]

When using SQLdb in the past found that odd client stability issues could be cleared up by frequently using TSQLTransaction.CommitRetaining on transactions that handled readonly queries. These readonly queries never needed to be in a transaction and it became standard practice to have two transactions per form, one for read queries and one for write queries. Just saying transactions don't always make sense and can add needless overhead.

[mse]

MSEgui version of SQLDB has an additional "transactionwrite" property for that purpose. So the normal read "Transaction" can have set "tao_fake", the write transaction can be opened while updating the database only by setting the "dso_autocommit" instead the "dso_autocommitret" flag.

[tionov]

When using SQLdb in the past found that odd client stability issues could be cleared up by frequently using TSQLTransaction.CommitRetaining on transactions that handled readonly queries. These readonly queries never needed to be in a transaction and it became standard practice to have two transactions per form, one for read queries and one for write queries. Just saying transactions don't always make sense and can add needless overhead.

As I wrote before, transactions are made for doing a couple of things in atomic (ACID) manner.

http://en.wikipedia.org/wiki/Database_transaction

Opening a transaction for reading and then keeping it open just for nothing is a really bad idea. But SQLdb does that always ...  :/

As a developer of db client software I know, that I have to use a SQL(!) transaction (I do not mean TSQLtransaction) usually only in the moment, where data is to be written and I have to keep it's time frame a short as possible. This prevents the server from unnecessary load and the danger, that two clients send each other in database deadlocks, is very small.

These stability issues become the more important as the number of clients (and/or connections) grows.

[allanregistos]

Hi tionov,

As a user of PostgreSQL and sqldb, I have this concern after reading this thread.
I used sqldb to connect to PG, components using these: TPQConnection,TSQLTransaction,TSQLQuery and TDatasource.

I dropped them on the form and use tdbgrid to display data.  My software may not involve a lot of users at least one user and at most up to 3.  Since I am no expert of how sqldb perform under the hood, may I ask for your advice if it is okay for me to use this in my few apps, since rewriting them using another components may take some time.

Also, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed.

What's your advice?

Thanks in advance.
-Allan

[tionov]

Hi allanregistos,

My software may not involve a lot of users

First: I'm no experienced Lazarus developer and I will not continue developing with Lazarus. I switched over to Gambas which suits better for my requirements.

If you work with your software together with other users on the same database and you see deadlocks (your application stops responding because it waits for the database which also waits for some action of other users) then you should consider changing the database layer of your software and use Zeoslib instead of SQLdb.

Zeoslib offers the opportunity to use no SQL transaction just for reading.

Personally I am not a fan of ORM (SQLdb and Zeoslib are kind of ORM). These add complexity and one has to understand not only SQL but also the ORM-Layer to predict, what is really happening inside the database.

In my applications I find it easier to write SQL by hand (in the code, obviously) and send it to the database on certain events. If that happens too often, I write a set of methods, which help me doing that.

In the end I am faster in application development because I know what is happening and I am not in the need of fiddling around with complex libraries which want to make things easier, but are very hard to understand (this is a major part of the last 10 % in software-development which cost 90% time).

Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that.

Also, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed.

As long as you are able to control the SQL transaction by yourself, for me it seems no problem, to use that controls. But with SQLdb you cannot control the SQL transaction (BEGIN ... COMMIT/ROLLBACK).

So my advice is: If you see deadlocks, use Zeoslib and/or do SQL by hand.

[marcov]

Personally I am not a fan of ORM (SQLdb and Zeoslib are kind of ORM).  These add complexity and one has to understand not only SQL but also the ORM-Layer to predict, what is really happening inside the database.

They are not ORM. There are no Objects or OOP principles involved.  They are just a general DB layer, and better comparable with ADO,ODBC,JDBC etc.

In my applications I find it easier to write SQL by hand (in the code, obviously) and send it to the database on certain events.

This depends on your scenario. Delphi/Lazarus are mostly geared towards being highly productive in client apps. A high percentage of work is then spent in making simple "list of items"  editors (add/delete/modify with some variants like Master/detail etc). Most of these packages are geared towards that (grid + navigator), to be both productive and less repetitive. The database matters less, and most SQL is the same.

IOW one doesn't use a generic db layer, and then start micromanaging SQL. Or at least not for the bulk of the application.

In the end I am faster in application development because I know what is happening and I am not in the need of fiddling around with complex libraries which want to make things easier, but are very hard to understand (this is a major part of the last 10 % in software-development which cost 90% time).

Then try rewiring your app to a different db.

Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that.

The problem is that this is not black or write. What you say is correct for the difficult parts of the application. But those are intensive but relatively small.

Example: in the past we had a CMS/ERP like system with 1200 tables. Only a few dozen of those were complex entities with complex relations. But all those other tables needed basic editors with some simple customized logic to making entry for the customer easy, etc etc.

[tionov]

Hi markov,

They are not ORM. There are no Objects or OOP principles involved.  They are just a general DB layer, and better comparable with ADO,ODBC,JDBC etc.

Ok, right. They are database abstraction layer. But ODBC etc don't force you into starting a transaction when you open a table. SQLdb does.

This depends on your scenario. Delphi/Lazarus are mostly geared towards being highly productive in client apps. A high percentage of work is then spent in making simple "list of items"  editors (add/delete/modify with some variants like Master/detail etc). Most of these packages are geared towards that (grid + navigator), to be both productive and less repetitive. The database matters less, and most SQL is the same.

Yes.

IOW one doesn't use a generic db layer, and then start micromanaging SQL. Or at least not for the bulk of the application.

Ok. yes. One can do both.

Then try rewiring your app to a different db.

I did this two times in my life and I know how much work that is. And I've learned from that. The most I have learned is: Use Postgresql. ;-)

The second is: Write my own db-layer.

Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that.

The problem is that this is not black or write. What you say is correct for the difficult parts of the application. But those are intensive but relatively small.

Example: in the past we had a CMS/ERP like system with 1200 tables. Only a few dozen of those were complex entities with complex relations. But all those other tables needed basic editors with some simple customized logic to making entry for the customer easy, etc etc.

Ok. My personal conclusion after 20 years of db programming is:

If I write a small application with about up to five tables it's no problem to write a few SQL-strings by hand. SQL is easy and very mighty, if you know it. But if you don't know it because you use a drag-and-drop-db-layer you normally don't know what your application does. This is bad, when your tables grow by and by. It is even worse when you are in production with your five tables and it gets slow and slower fast (for example SQLdb has no LIMIT, FetchRow x does nothing with PG). So: Learn SQL first.

If I have to write a larger application I write my own db layer and I may even write the components by my own. Because: First I understand my own db layer. Second, if I use a db-layer written by somone else it may occur that this is gonna be developped in a wrong direction. I had this situation with MS Access, when MS stopped the support for a certain procedure to connect to other dbs. A procedure, they recommended before and I have used 700 times in code over the years. Thank you, MS...

Luckily the last seldom occurs in open source software.

[allanregistos]

Hi allanregistos,

My software may not involve a lot of users

First: I'm no experienced Lazarus developer and I will not continue developing with Lazarus.

Me either, but that doesn't stop me from using Lazarus, and now I am primarily developing with it. It takes time, and now I can say that it is much easier to program in lazarus than in VB.net, my previous PL.

I switched over to Gambas which suits better for my requirements.
.

Then you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro.  Lazarus/fpc's made software will run anywhere as long as you compile it to the target OS without any needed runtime dependencies.  As a user of BASIC(VB.net) Gambas is the first choice when programming in Linux.  But I missed my Pascal days(Turbo Pascal) and began investigating if the language was still available or was upgraded. Delphi is expensive so I've found Lazarus and fpc.  There are tons of advantages of using Lazarus over Gambas, but I respect your choice, your requirements must be that your software will only run on Linux.

If you work with your software together with other users on the same database and you see deadlocks (your application stops responding because it waits for the database which also waits for some action of other users) then you should consider changing the database layer of your software and use Zeoslib instead of SQLdb.

So far, the performance is acceptable. I think I really need to replace SQLdb. I am also interested to do SQL queries by hand if it is easy.

...............
Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that.

The TIOPF approach maybe[Advertised as you can change any DB on the fly], but I've found that tool difficult to use firsthand because it is blind and cannot see PostgreSQL clients lib.

Also, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed.

As long as you are able to control the SQL transaction by yourself, for me it seems no problem, to use that controls. But with SQLdb you cannot control the SQL transaction (BEGIN ... COMMIT/ROLLBACK).

So my advice is: If you see deadlocks, use Zeoslib and/or do SQL by hand.

I will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent.

[LacaK]

See, what SQL standard says about SQL transactions:
Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction.
An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating <direct SQL statement>.


So sqlDB implementation is not so far from what SQL standard expects:
Each SQL statement is associated with transaction context, if none transaction is active new is initiated.


And about CommitRetaining, SQL standard knows COMMIT AND CHAIN:
If those statements specify AND CHAIN, then they also initiate a new SQL-transaction with the same characteristics as the SQL-transaction that was just terminated,

 

[tionov]

Then you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro. 

Yes I know. But I write software for my own firm, so that's not a problem. We work on Linux solely. And i had no "Pascal days", so for me FPC is a complete new language with much things I would have to learn from the ground up. At Gambas I'm much more "at home".

I will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent.

I f you have your business logic on the PG Server that is very good! In FPC you can use the class postgres to connect a Lazarus client to the server.  How(?) I did not really figure out because I stopped my experiments with Lazarus. But there is the class postgres (and maybe postgres3) to look at it and there are examples in

/usr/share/fpcsrc/2.6.4/packages/postgres/examples
(on my linux computer)

which can be adopted.

So I only can tell, what  I am doing in Gambas or other languages:

1) Build a static class (that is a singleton) to do only one connection per PG database at start of your program. This is your channel to talk to PG. In PGAdmin you have the server tools, there you can see the connections. If your program opens more than one you should know why, that is a source of deadlocks (maybe you used a control with SQLdb in parallel).

In Gambas I do this in a method (this is a static class/aka singleton):

Code: [Select]

Public DB As New Connection

Public Sub _init()

  startconnection

End

Private Sub startconnection()

  If Not PGdb.Opened Then
    PGdb.Type = "postgresql"
    PGdb.Host = "hostname"
    PGdb.Name = "dbname"
    PGdb.Login = "User"
    PGdb.Password = "password"
    PGdb.Open()
  Endif

Catch
  myownError.Handler

End

Ok, you have to rewrite this for FPC like in the example.

2)In your program send your query over this connection and receive the result (usually as array).

arrayresult = DB.exec("SELECT * FROM this WHERE ...")

3) Use the data in the array to fill your controls.

4) Write methods which send queries over the connection to update data in database when the user changed the data in the controls.
DB.exec("DELETE FROM this WHERE ...")
DB.exec("UPDATE this SET y = " &  blah & "  WHERE ...")

If you want to change a couple of things in the database at once, do it so:

DB.exec("BEGIN;")
send a couple of queries ...
DB.exec("COMMIT;")

Ok this is very basic, but ensures, that you get a rockstable client. For better stability use prepared statements (do not forget DEALLOCATE):
http://www.postgresql.org/docs/9.3/static/sql-prepare.html

If you work with larger tables, ensure that you use LIMIT if you query the data. Or use cursors:
http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html

So people using your client can scroll data.

[tionov]

See, what SQL standard says about SQL transactions:
Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction.
An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating <direct SQL statement>.

Yes. If you start a transaction, it is started. If you do something in SQL on the same connection, it is done within that transaction until you close the transaction.

So sqlDB implementation is not so far from what SQL standard expects:
Each SQL statement is associated with transaction context, if none transaction is active new is initiated.

Yeah SQLdb uses SQL Standard ... but the wrong way. Normally a db like PG does autocommit on each query. if you send "SELECT *" it makes "BEGIN;SELECT *;COMMIT;". For small queries this normally happens in a split second. But PG does that not, if you send a query that explicit starts with "BEGIN;". Then it waits until you send a "COMMIT;". It waits for hours and days, if you want.

Ok, SQLdb does SQL. But the problem is, as long as you see your data in a grid powered by SQLdb other users are blocked updating that data because SQLdb opened a transaction and does not close it until the end of ... maybe ... your form ... your program?

And about CommitRetaining, SQL standard knows COMMIT AND CHAIN:
If those statements specify AND CHAIN, then they also initiate a new SQL-transaction with the same characteristics as the SQL-transaction that was just terminated,

But not within that transaction. I think that's quite of a different nature. And i don't know enough of Firebird.

[allanregistos]

Then you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro. 

Yes I know. But I write software for my own firm, so that's not a problem. We work on Linux solely. And i had no "Pascal days", so for me FPC is a complete new language with much things I would have to learn from the ground up. At Gambas I'm much more "at home".

Tried Gambas, but find it difficult, really it depends on our past experience with programming languages. I am still a newbie in computer programming, even if I had those Pascal days with me.  The benefits of using Lazarus/fpc over Gambas are huge even if you only work for Linux, that is a guarantee that Lazarus/fpc people can make for you.  It is even easier to use than VB.Net, however, if you are comfortable enough with Gambas, then its ok.

I will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent.

I f you have your business logic on the PG Server that is very good! In FPC you can use the class postgres to connect a Lazarus client to the server.  How(?) I did not really figure out because I stopped my experiments with Lazarus. But there is the class postgres (and maybe postgres3) to look at it and there are examples in

/usr/share/fpcsrc/2.6.4/packages/postgres/examples
(on my linux computer)

which can be adopted.

Thanks for the tips, I'm looking at it. It is quite similar to npgsql for .NET.

So I only can tell, what  I am doing in Gambas or other languages:

1) Build a static class (that is a singleton) to do only one connection per PG database at start of your program. This is your channel to talk to PG. In PGAdmin you have the server tools, there you can see the connections. If your program opens more than one you should know why, that is a source of deadlocks (maybe you used a control with SQLdb in parallel).

In Gambas I do this in a method (this is a static class/aka singleton):

Code: [Select]

Public DB As New Connection

Public Sub _init()

  startconnection

End

Private Sub startconnection()

  If Not PGdb.Opened Then
    PGdb.Type = "postgresql"
    PGdb.Host = "hostname"
    PGdb.Name = "dbname"
    PGdb.Login = "User"
    PGdb.Password = "password"
    PGdb.Open()
  Endif

Catch
  myownError.Handler

End

Ok, you have to rewrite this for FPC like in the example.

2)In your program send your query over this connection and receive the result (usually as array).

arrayresult = DB.exec("SELECT * FROM this WHERE ...")

3) Use the data in the array to fill your controls.

4) Write methods which send queries over the connection to update data in database when the user changed the data in the controls.
DB.exec("DELETE FROM this WHERE ...")
DB.exec("UPDATE this SET y = " &  blah & "  WHERE ...")

If you want to change a couple of things in the database at once, do it so:

DB.exec("BEGIN;")
send a couple of queries ...
DB.exec("COMMIT;")

Ok this is very basic, but ensures, that you get a rockstable client. For better stability use prepared statements (do not forget DEALLOCATE):
http://www.postgresql.org/docs/9.3/static/sql-prepare.html

If you work with larger tables, ensure that you use LIMIT if you query the data. Or use cursors:
http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html

So people using your client can scroll data.

Thank you for the tips.

[allanregistos]

See, what SQL standard says about SQL transactions:
Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction.
An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating <direct SQL statement>.

Yes. If you start a transaction, it is started. If you do something in SQL on the same connection, it is done within that transaction until you close the transaction.

This is correct.

So sqlDB implementation is not so far from what SQL standard expects:
Each SQL statement is associated with transaction context, if none transaction is active new is initiated.

Yeah SQLdb uses SQL Standard ... but the wrong way. Normally a db like PG does autocommit on each query. if you send "SELECT *" it makes "BEGIN;SELECT *;COMMIT;". For small queries this normally happens in a split second. But PG does that not, if you send a query that explicit starts with "BEGIN;". Then it waits until you send a "COMMIT;". It waits for hours and days, if you want.

There is also the rollback function on which if an error occurs, whatever you are doing after the BEGIN statement would be discarded. 
http://en.wikipedia.org/wiki/ACID#Atomicity

Ok, SQLdb does SQL. But the problem is, as long as you see your data in a grid powered by SQLdb other users are blocked updating that data because SQLdb opened a transaction and does not close it until the end of ... maybe ... your form ... your program?

tionov, I need to show you something.  If that statement of yours reflects what SQLdb does under the hood, then I must be doing something right, because, what I am doing with sqldb as I stated earlier in this thread is I am dropping those controls to the form for the tdbgrid to display data. Those same data that _being_ displayed through the data grid(tdbgrid), I was able to manipulate(update/delete/insert). It is like my app have two PG users at the same time, one user that uses sqldb to display data and one user that manipulates then on the fly. To display data, here is the code:

Code: [Select]

procedure FillWholeRollsGrid(RollsGrid: TDBGrid; SQLQueryRolls: TSQLQuery; SQLTransactionRolls: TSQLTransaction;
  DatasourceRolls: TDatasource);
begin
    SQLStrg :='SELECT ' +
            'paper_trim.value || ''X'' || gsm.value || ''-'' || liner.value as description, ' +
            'CASE rolls.status_id ' +
            ' WHEN 33 THEN roll_history.weight ELSE rolls.weight END AS weight, ' +
            'CASE rolls.status_id ' +
            ' WHEN 33 THEN roll_history.diameter ELSE rolls.diameter END AS diameter, ' +
            'rolls.roll_no,'+
            'contact.organization_name as supplier,rolls.id ' +
            'FROM rolls ' +
            'JOIN paper_trim ON paper_trim.id=rolls.trim_id ' +
            'JOIN gsm ON gsm.id=rolls.gsm_id ' +
            'JOIN liner ON liner.id=rolls.liner_id ' +
            'LEFT JOIN roll_history ON roll_history.roll_id=rolls.id ' +
            'JOIN contact ON contact.id=rolls.supplier_id WHERE (status_id=30 OR status_id=33 and latest_remnant=1) ' +
            'ORDER BY rolls.id DESC;';
    try
      SQLQueryRolls.close;
      SQLQueryRolls.SQL.Text := SQLStrg;
      SQLTransactionRolls.Active:=True;
      RollsGrid.DataSource := DatasourceRolls;
      SQLQueryRolls.Open;
    Except
      ON E: Exception do
      begin
        raise exception.create('Exception Caught: ' + E.Message);
      end;
    end;
end; 
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.

[zeljko]

See, what SQL standard says about SQL transactions:
Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction.
An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating <direct SQL statement>.

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

My suff work like this:
1.Login
2.Wait some query to start (any kind of).
3.START TRANSACTION
4.COMMIT/ROLLBACK  (goto 2.).
5.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.

[taazz]

We are talking only for the second example here, Postgresql doesn't like the begin transaction on read/select operations it does not support concurrent transaction either so begin transaction select * from X locks the connection in that transaction until the user logs out of the application or the closes all open browsers.

I agree that sqldb needs to be extended to allow sql operations without a transaction and let it be required on firebird connection where the database needs them.