Concurrent insert freezes GUI(Windows/PostgreSQL)

Strange behavior on inserting a row in PostgreSQL.

This application 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?

  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 update 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.

Are these jobs running in their own threads? If so, try setting mPostgreSQLDB.multithreaded to true. Also, wrapping single line statements in transactions is not required, as Postgres will start an internal transaction. So, just do: mPostgreSQLDB.SQLExecute(insert_statement) and check for errors.

I also tried with threads but same result.

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 think I should find the root cause of the previous issue but no enough time now.
Sorry for not applying your advice.

Thank you.

Hi changwon lee

You can have deadlocked

Regards.
Mauricio

if there are non-deffered constraints like an UNIQUE index that needs to be checked, an INSERT may block till another transaction completes.

e.g.
session 1:

create table t(f1 integer unique); begin; insert into t values (1);
session 2:

insert into t values (1);
will block and wait for the transaction in session 1 to finish. If it rolls back, the statement will be executed, if it commits, the statement will raise an error due to a unique violation.

During this blocking / long running SQLExecute the main thread of the Xojo application my be blocked as well as detailed in <https://xojo.com/issue/43107> / <https://xojo.com/issue/37806> That could lead to a situation where the thread holding session 1 never gets to commit or rollback its transaction and the full application is kind of deadlocked.