1

While I develop a GUI window application, I see strange behavior on inserting a row in PostgreSQL.

My application(built by Xojo) builds a JOB meta information and inserts that into PostgreSQL, and I have tried to run 5 jobs inserting the one row each. It freeze the GUI application. When I run 4 jobs, I don't see any issue but 5 causes this issue.

Tried to logging codes, and I see that one of jobs couldn't get passed on "BEGIN TRANSACTION" statement. There is no lock information in database, that is why I am stuck.

Probably, there is some limit to insert rows concurrently in the same table? At times, due to the small number of ini_trans(Oracle), concurrent DML could get stuck. I am not sure whether PostgreSQL has that feature or not.

The each job should go through below steps to commit the one row.

  mPostgreSQLDB.SQLExecute("BEGIN TRANSACTION")
  mPostgreSQLDB.SQLExecute (insert_statement)

  If mPostgreSQLDB.Error then
    Logging("DML failed. Error: " + mPostgreSQLDB.ErrorMessage + " Rollbacked " )
    mPostgreSQLDB.Rollback
  Else
    mPostgreSQLDB.Commit
  End If

Correction.

I noticed that there is another UPDATE statement to date the same table. However, INSERT and UPDATE treats a different row, but it seems that they block each other even though there is no lock information.

Sigularity
  • 917
  • 2
  • 12
  • 28

1 Answers1

1

You should check for any errors after the BEGIN TRANSACTION step. Anything that uses SQLExecute could potentially create a database error. I'm betting that you'll get an error message that will probably tell you exactly what's going on.

BKeeney Software
  • 1,299
  • 6
  • 8
  • I think the issue is a little bit complex and actually jobs are running with DataAvailable event of Shell. I thought DataAvailable event might cause more contention of Jobs so tried to remove DataAvailable events, and implemented new one to check the job processing. After that , I don't see any GUI freezing issue. I will try to find the previous error. Thank you. – Sigularity Aug 07 '16 at 14:47