Timeout exception while generating Document numbers (BUG?)

[Tomáš Švikruha]

Hi all,

I would like to discuss one possible bug which permanently appears on our production server.

We have done some processes which are triggered by scheduler or by users from Info Windows. These processes are responsible for generating documents - most of them are Distribution Orders or Inventory Movements... Sometimes happens that server struggle with strange error (error is always same) - method MSequence.getDocumentNoFromSeq returns Timeout Exception. Exception thrown by Postgres says that timeout happens because of locking database records.

I attached also log from production server. If anybody have some ideas what could be the problem, please let me know.... Currently, when this error appears, postgres server has to be restarted and after that everything works fine.

Log from postgres server can be also attached if it can be helpful.

Thanks a lot...

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

[Alan Lescano]

Check number of conections in the DB connection pool; maybe them are not getting properly closed. If you are using PreparedStatement/ResultSet in your custom plugin, try to close the conection with 'DB.close(rs, pstmt);rs=null;pstmt=null;'

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/6af52a1b-eeae-4238-9dbc-599d131d1c3e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tomáš Švikruha]

Thanks for advice, but I am always using trxName and closing connection after prepared query executions. But I realised that in some forms I'm not using trxName - but it should be fine because it is same approach like in standard forms (WCreateFromShipmentUI, WInOutGen etc..)

I'll also check the number of connections in the DB connection pool..

 
Dne středa 25. března 2015 13:49:25 UTC+1 Alan Lescano napsal(a):

[Pritesh Shah]

In order to generate the sequence, FOR UPDATE OF is used to lock the row of the sequence table, so that no other session can update it while current session is updating it. So if you are having very large number of session or request or queries are fired in parallel then many of them would be wait for the lock to get released and there is timeout of 30 sec. 

try reproducing the issue by using JMeter to fire too may queries to generate document sequence in parallel and then reduce the number of queries.

[Neil Gordon]

Another idea is to check this setting in postgresql.conf:

max_connections

-----

Neil Gordon

nTier Software Services

http://www.ntier.co.za

[mhernandezve]

Hi Tomáš,

You cah check locks for table ad_sequence in postgresql with this sentence:

SELECT pid, locktype, mode, granted, relation

   FROM pg_locks

   WHERE relation = 'ad_sequence'::regclass;

I've gotten this error on large transactions or when they have not been closed properly.

[Alan Lescano]

Tomas, did you solve this issue?

I had a lot of this recently, and discovered that one of my custom code was calling MSequence.getDocumentNoFromSeq(MSequence seq, String trxName, PO po) inside a "not so fast" process. I'm now passing null as trxName. The method then gets a new connection and commits it after select/update the documentno. Commit then release the lock very fast. This solved my timeout issues.

Maybe this can lead to gaps in sequence (if my "not so fast" process fails), but in my case this is acceptable.

Alan

--

You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/77851632-2979-422f-aa1c-140a7d06a54f%40googlegroups.com.

[Aries Chandra]

I have the same problem here, debug the problem coz when generate di Movement Line will check column DocumentNo, if the documentno not exists it will generate from MSequence to take default value,

So u can add 

line.set_ValueNoCheck("DocumentNo", "Anything");

Maybe that can help

[Alan Lescano]

Hi,

I faced this timeout issue some time ago. It's usually caused by long, time-consuming transactions involving the generation of document number. FOR UPDATE is used in query to ensure a correct sequence, thus locking the row. If you made customizations, try to review the design, especially in document workflows and/or event handlers. Maybe you are holding a lock unnecessarily, and a trx.commit() will solve this. If server is overloaded by many concurrent trx, you may consider an upgrade. Increasing the query timeout in MSequence.java may be a temporary solution to avoid exceptions.

Alan

--

You received this message because you are subscribed to the Google Groups "iDempiere" group.

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/7e084578-49ef-4cdd-a49c-9a8487cfdea4%40googlegroups.com.