Potential issues provided by Database Transaction aborted

[VuongCT]

Hi group,

After a while working with iDempiere 2.0 , i found a critical problem with database transaction handling. The problem may result from my code, or from default transaction handling, or from the combination of both. I actually learnt quite a lot of database transactions handling between iDempiere and Postgres from it. (Assuming i'm using Postgresql 9.3)
But these are the cases that really bother me and make me think of a potential bug, plz correct me if my conclusion is mistaken.

Let take C_BPartner as an example:
- We got an Index in this table on database : UNIQUE INDEX c_bpartner_value to make sure the combination of AD_Client_ID and Value in this table is unique.
- We also define this index on iDempiere's Table & Column Configuration.

Steps to reproduce on iDempiere:

     1. Create a Business Partner with Value = 'AAA'

     2. Create another one with the same value and we got the error message on client which was defined on Table & Column: 

"Could not save record - Require unique data: - Please change information.: ERROR: duplicate key value violates unique constraint "c_bpartner_value" Detail: Key (ad_client_id, value)=(1000005, 000000) already exists."

     3. In iDempiere Log :

"10:51:35.620===========> DB.saveError: SaveErrorNotUnique - ERROR: duplicate key value violates unique constraint "c_bpartner_value"

  Detail: Key (ad_client_id, value)=(1000005, 000000) already exists. [41]

10:51:35.621-----------> MBPartner.doInsert: [POSaveC_BPartner_3012ace9-641d-44e1-b524-6ec182730cd0]Not inserted - C_BPartner [41]

10:51:35.635===========> MBPartner.saveError: SaveErrorNotUnique - ERROR: duplicate key value violates unique constraint "c_bpartner_value"

  Detail: Key (ad_client_id, value)=(1000005, 000000) already exists. [41]

10:51:35.636-----------> GridTable.saveWarning: SaveErrorNotUnique - ERROR: duplicate key value violates unique constraint "c_bpartner_value"

  Detail: Key (ad_client_id, value)=(1000005, 000000) already exists. [41]"

It's true that iDempiere already caught a situation where user tried to input another record into the database that violated unique constraint as expected.

But it doesn't stop there, looking into pg_log in Postgresql data folder, i found these lines:

"2015-03-27 10:51:35 ICT ERROR:  duplicate key value violates unique constraint "c_bpartner_value"

2015-03-27 10:51:35 ICT DETAIL:  Key (ad_client_id, value)=(1000005, 000000) already exists.

2015-03-27 10:51:35 ICT STATEMENT:  INSERT INTO C_BPartner (IsCustomer,C_BPartner_UU,FlatDiscount,IsOneTime,SendEMail,SOCreditStatus,AcqusitionCost,IsSummary,IsDiscountPrinted,IsProspect,SalesVolume,DocumentCopies,ActualLifeTimeValue,ShareOfCustomer,IsEmployee,NumberEmployees,SO_CreditUsed,C_BPartner_ID,IsTaxExempt,IsPOTaxExempt,ShelfLifeMinPct,SO_CreditLimit,IsSalesRep,IsManufacturer,IsVendor,PotentialLifeTimeValue,TotalOpenBalance,CreatedBy,UpdatedBy,IsActive,Name,Value,Is1099Vendor,AD_Language,AD_Client_ID,AD_Org_ID,SalesRep_ID,C_BP_Group_ID,C_PaymentTerm_ID,M_PriceList_ID,Created,Updated) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42)

2015-03-27 10:51:35 ICT ERROR:  current transaction is aborted, commands ignored until end of transaction block

2015-03-27 10:51:35 ICT STATEMENT:  SELECT Version FROM AD_System"

Here is where i find it potentially critical bug since i've run into server hangs quite sometimes when database connections exceeded limitation due to unclosed connections.
For example when pg_log says:
"2015-03-26 10:40:00 ICT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it."

So, i'm desperately asking for help to fully understand this kind of situations when such problem thrown from database  : current transaction is aborted, commands ignored until end of transaction block

Will it leave the connection just hang there without closing it or it's just normal ?

If it doesn't rollback transaction and close the connections, is this kind of problem related with the other problem i mention above -( database connections exceeded limitation)?

Looking forwards to learning from community's deep knowledge.
Best Regards

[Hiep Lq]

my opinion.

"current transaction is aborted, commands ignored until end of transaction block"

just a warning after each exception, when you do correct action (ex: close transaction) you don't must care about message.

not sure save action do correct, must investigate code.

other issue maybe same your issue.

https://idempiere.atlassian.net/browse/IDEMPIERE-2032

--
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/027d469a-1a3a-4033-95fd-a176da29afc1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Lê Quý Hiệp
Email: hie...@hasuvimex.vn
Skype: admin.hasuvimex
Mobile: 0915277401

Company: Thanh Hoa Fishery Import - Export J.s.c  (Hasuvimex) DL 47

Add: Lot E, Le Mon Industrial Zone, Thanh Hoa, Vietnam

[Carlos Antonio Ruiz Gomez]

Hi VuongCT, on past wednesday meeting we were talking with tsvikruha about that, you can find some tips and ideas in the meeting logs:
http://www.globalqss.com/wiki/index.php/IDempiere/FullMeeting20150325

Regards,

Carlos Ruiz

[Tomáš Švikruha]

Related to our yesterday discussion about database locking...I've just checked almost whole code and all prepared statements and result sets are correctly closed in finally blocks. Also as you wrote on IRC, some processes needs to be refactored - like InvoiceGenerate where is process operating with opened pstmt.

I checked db status via pgAdmin where can be seen that there are many locks and waitings queries - so there are not closed connections. pgAdmin also shows queries which are waiting, so I checked it in code. These queries which are waiting or somehow locked are triggered by DB.ExecuteUpdate method, so why they are left open? DB.ExecuteUpdate is triggered by custom model validator, where trxName is set from validated PO.

Any other ideas what could be wrong? After updating postgres from 9.3 to 9.4 and jdbc driver issue appears more frequently...

Dne pátek 27. března 2015 14:43:03 UTC+1 Carlos Antonio Ruiz Gómez napsal(a):

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.

[Carlos Antonio Ruiz Gomez]

Another thing that helped me when researching db locks.

pgadmin shows the backend pid process that is holding the locks.

These statements add a column to ad_changelog showing which backend created the record:

alter table ad_changelog add pid int;
alter table ad_changelog alter pid set default pg_backend_pid();

So, crossing the backend holding the lock with the records it's creating you can find info about which process are locking which one.

Regards,

Carlos Ruiz

[Tomáš Švikruha]

Thanks for advice, will try it..

Dne pátek 27. března 2015 16:36:02 UTC+1 Carlos Antonio Ruiz Gómez napsal(a):

[Tomáš Švikruha]

On this very interesting link http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/ I found several useful queries.

One of them show which query is blocking whom or another one which displays current waiting and locked queries.

I found it useful to find out which are problematic queries, but what next? Updates are triggered by method DBExecuteUpdate from ModelValidator with correct TrxName. I don't see any ways what could be wrong in code..

Dne pátek 27. března 2015 16:36:02 UTC+1 Carlos Antonio Ruiz Gómez napsal(a):

[Hiep Lq]

other tool.

i use pgAdmin III, has a menu name "server status" provide same thing.

[Hiep Lq]

hi Tomas.

i doing to IDEMPIERE-2544 it also to kill leak resource.

i see a pattern relate your lock issue. maybe help you.

in FactReconcile process.

code as below 

1. pstmt = DB.prepareStatement(sql, get_TrxName());

do some db action 

2. pstmt = DB.prepareStatement(sql, get_TrxName());

do some other db action 

3. pstmt = DB.prepareStatement(sql, get_TrxName());

continue do other db action 

release resource in finally by call DB.close(rs, pstmt);

issue is only resource at step 3 release. resource at step 1, 2 still hold.

[Tomáš Švikruha]

Yes I see, it is a good example how it shouldn't be done...

My code is as below example.

String sql = "UPDATE C_Order 1...";

DB.executeUpdate (sql.toString (), trxName);

sql = "UPDATE C_Order 2 ..."

DB.executeUpdate (sql.toString (), trxName);

sql = "UPDATE C_Order 3 ..."

DB.executeUpdate (sql.toString (), trxName);

Some of these queries stays locked, it can be seen in pgAdmin when server start's to log issues with locking of records.

[Hiep Lq]

your code is a bit difference, and it think it's ok.

because DB.executeUpdate auto release resource and three command do in same transaction, it's don't make lock.
maybe it's block by other process has pattern as me description. try apply patch at IDEMPIERE-2544 and test?

--

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/7b2434ab-804c-428d-9b79-982bb18816f9%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Tomáš Švikruha]

Which patch? Because there are atrached more patches...

Issue happens only on production server, so it can be risk to "try" it...

[Hiep Lq]

i mean this patch IDEMPIERE-2544-leakResourceWarning.patch.

for test just backup, restore db to local.

On Sun, Mar 29, 2015 at 5:43 PM, Tomáš Švikruha <svikruh...@gmail.com> wrote:

Which patch?  Because there are atrached more patches...

Issue happens only on production server, so it can be risk to "try" it...

--
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/ced2dff3-f254-4f82-b2f9-c8def682d9cd%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.