1. Sivaprakash BKR
  2. PowerBuilder
  3. Friday, 8 April 2022 13:38 PM UTC

Hello,

Using PB 2019 R3, PostgreSQL 11

Need to prevent Deadlock.  Would like to know what others do to prevent it.   

Rarely one users gets deadlock situation, where we currently kill that process in PGAdmin.  This cannot be a permanent solution, as for every users we need to login, find the process and then kill it.   

Is there a better approach to prevent deadlock situation?  

Happiness Always
BKR Sivaprakash

 

mike S Accepted Answer Pending Moderation
  1. Monday, 11 April 2022 12:44 PM UTC
  2. PowerBuilder
  3. # 1

" Killing the process(es) relevant to that computer solves that issue that time."

Postgresql should kill one of the deadlocks automatically after the deadlock timeout occurs, 'solving' the problem.  Are you sure you have a deadlock?  From your description it really sounds like you have an open transaction that is waiting for user input that is blocking another user/process.

 

PostgreSQL Documentation: deadlock_timeout parameter (postgresqlco.nf)

 

ERROR: deadlock detected | Understanding deadlocks - CYBERTEC (cybertec-postgresql.com)

Comment
  1. Sivaprakash BKR
  2. Tuesday, 12 April 2022 07:38 AM UTC
Sorry for the delay mike.

I did go through that note about deadlock, that PG kills deadlock process. Trying to collect more information by studying the log file. It occurred only in one POS system, 3 times in the span of 6 months. Will come back with more details or solution / issue, if found.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 8 April 2022 14:56 PM UTC
  2. PowerBuilder
  3. # 2

Hi BKR ;

   As with any DBMS, you need to pay close attention to how your particular performs locking (all DBMS vendors are different) and then develop an App strategy on your App(s) best approach to make use of that locking scheme that the DB vendor provides. That includes in-line and DWO SQL and DWO update property settings.

Regards .. Chris

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Friday, 8 April 2022 16:38 PM UTC
  2. PowerBuilder
  3. # 3

In addition to checking your locking, you need to make sure that when you update/insert data in multiple tables within a single transaction that you do so in the exact same order in all parts of your system.   

 

It is also possible that index updates may cause deadlocks.  that is a database issue and you would need to go to the postgresql user community to find help.  It sounds like postgresql doesn't automatically kill one of the deadlocked processes to automatically allow the other to finish?  

 

 

 

 

Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 9 April 2022 06:10 AM UTC
  2. PowerBuilder
  3. # 4

Thanks Chris, Armeen, mike

Further information collected:

1.  Deadlock happens randomly with one computer among POS computers.   That user uses only one entry screen. He can add records, cannot edit or delete records.
2.  That entry screen insert records in 5 tables (datawindows) and updates 6th (summary) table through trigger.
3.  Mostly that deadlock doesn't affect workings of other users (other POS / computers).   Other uses could operate normally without any issues.
4.  Killing the process(es) relevant to that computer solves that issue that time.

I didn't change the default isolation level which is READ COMMITTED.  I don't think I need to change this as this looks fine for me.

mike,
So far, this deadlock occurred only in POS computer, which inserts records in the same order (same window).  

Happiness Always
BKR Sivaprakash

 

Comment
  1. Chris Pollach @Appeon
  2. Saturday, 9 April 2022 13:26 PM UTC
It could also be a PostGreSQL DBMS locking issue (bug).
  1. Helpful 1
  1. mike S
  2. Saturday, 9 April 2022 16:16 PM UTC
review your code to find any place else that updates/inserts/deletes any 2 or more of those 6 tables in a single transaction. also look at all other triggers that may be in your system



my gut says that your table trigger is most likely an issue - try to disable that to see what happens.



i'd find a postgressql tool that allows you to see what processes are deadlocked and what sql statements caused it.



and also make sure you don't have a messagebox or similar that waits for user interaction prior to commit or rollback
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.