Postgres 'locking'

[grahamg]

Occasionally I get a complaint from users that the "system froze" for a short time - then was fine. Recently had instance where a very simple 'update' that usually take <1ms took 4 minutes - can anyone suggest why this would happen.

Screen Shot 2015-04-20 at 08.28.01.png (35.61 KiB) Viewed 4439 times

The "zsys" table has just one record and tracks various system parameters - in this instance was providing the next QuickQuoteNumber then being incremented by 1.

Servoy 7.4.3 / Postgres 9.3 / Java 1.8.0

[mboegem]

Hi Graham,

Do you use any locks/transactions yourself to avoid duplicate numbers in this area?

[grahamg]

Hi Mark

Just rechecked and no locks/transactions. Code itself is pretty simple - the vType gets passed in and vDocNum returned.

Code: Select all

   var vRecord      = foundset.getRecord(1);
   switch (vType) {
      case 'QQ':      vDocNum   = vRecord.qq + 1;      vRecord.qq = vDocNum;      break;
      case 'QT':      vDocNum   = vRecord.qt + 1;      vRecord.qt = vDocNum;      break;
           ... other vTypes ....
   }
   databaseManager.saveData(vRecord);
   
   return vDocNum;

[mboegem]

what happens if you take that update statement and execute it manually using pgadmin sql editor or equivalent tool?

[ROCLASI]

Hi Graham,

How big is your connection pool for this database?
Also is this PostgreSQL installed that comes with Servoy (like you have with Developer) or from the EDB installer (which is recommended in the installer)?

[grahamg]

Thanks for your interest guys

@Marc - works fine as a direct SQL statement in PGAdmin - and has run many hundreds of times over last few months

@Robert - always install Postgres from EDB - Connection pool is 30 Active / 10 idle and probably only 2-3 people online at time of problem as the user was in USA so most of UK staff gone home.

Noticed that I use 'Query validation' and 'Select 1' - but seem to recall that maybe this is a MySQL tweak and not required by Postgres?

[mboegem]

Noticed that I use 'Query validation' and 'Select 1' - but seem to recall that maybe this is a MySQL tweak and not required by Postgres?

Never used that with a PG backend