Dead Lock Issue

[urspraveen2001]

Hi Team,

We are using Hermes for POC purpose and We are observing below exception in Hermes logs quite oftenly.

Caused by:
hk.hku.cecid.piazza.commons.dao.DAOException:
Error in executing hk.hku.cecid.ebms.spa.dao.MessageDataSourceDAO:
:
Process@53d9eadf by hk.hku.cecid.piazza.commons.dao.DAOException:
Error in executing update:
UPDATE message SET ref_to_message_id=?, message_id=?, primal_message_id=?, sync_reply=?, partnership_id=?, timeout_time_stamp=?, sequence_status=?, to_party_role=?, service=?, status=?, time_to_live=?, message_type=?, message_box=?, from_party_role=?, to_party_id=?, hostname=?, sequence_group=?, dup_elimination=?, cpa_id=?, has_resend_as_new=?, status_description=?, from_party_id=?, sequence_no=?, ack_sign_requested=?, action=?, time_stamp=?, conv_id=?, ack_requested=? WHERE message_id=? AND message_box=? by hk.hku.cecid.piazza.commons.dao.DAOException:
Error occurred when executing update at row 0 of the specified parameter array by com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction at hk.hku.cecid.piazza.commons.dao.ds.DataSourceProcess.start(DataSourceProcess.java:
97) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.executeUpdate(DataSourceDAO.java:
588) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.executeUpdate(DataSourceDAO.java:
548) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.executeUpdate(DataSourceDAO.java:
528) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.persist(DataSourceDAO.java:
672) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.persist(DataSourceDAO.java:
651) at hk.hku.cecid.ebms.spa.dao.MessageDataSourceDAO.updateMessage(MessageDataSourceDAO.java:
52) at hk.hku.cecid.ebms.spa.handler.InboundMessageProcessor.processAcknowledgement(InboundMessageProcessor.java:
949) ... 29 more Caused by:
hk.hku.cecid.piazza.commons.dao.DAOException:
Error in executing update:
UPDATE message SET ref_to_message_id=?, message_id=?, primal_message_id=?, sync_reply=?, partnership_id=?, timeout_time_stamp=?, sequence_status=?, to_party_role=?, service=?, status=?, time_to_live=?, message_type=?, message_box=?, from_party_role=?, to_party_id=?, hostname=?, sequence_group=?, dup_elimination=?, cpa_id=?, has_resend_as_new=?, status_description=?, from_party_id=?, sequence_no=?, ack_sign_requested=?, action=?, time_stamp=?, conv_id=?, ack_requested=? WHERE message_id=? AND message_box=? by hk.hku.cecid.piazza.commons.dao.DAOException:
Error occurred when executing update at row 0 of the specified parameter array by com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction at hk.hku.cecid.piazza.commons.dao.ds.DataSourceUpdate.doTransaction(DataSourceUpdate.java:
110) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceProcess.start(DataSourceProcess.java:
89) ... 36 more Caused by:
hk.hku.cecid.piazza.commons.dao.DAOException:
Error occurred when executing update at row 0 of the specified parameter array by com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction at hk.hku.cecid.piazza.commons.dao.ds.DataSourceUpdate.doTransaction(DataSourceUpdate.java:
96) ... 37 more Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:
57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:
45) at java.lang.reflect.Constructor.newInstance(Constructor.java:
526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:
406) at com.mysql.jdbc.Util.getInstance(Util.java:
381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
1045) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:
3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:
3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:
1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:
2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:
2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
1734) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2019) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
1937) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
1922) at hk.hku.cecid.piazza.commons.dao.ds.DataSourceUpdate.doTransaction(DataSourceUpdate.java:
92) ... 37 more

Do you have any solution for this issue ?

It would be really helpful if you can help us in sorting out the issue.

[tomlee]

Please see if this post helps: https://groups.google.com/forum/#!msg/cecid-hermes2/_v9yb3fWRXc/-75EE25mcaQJ

[Alan-Chen-au]

Hi,

I think the reason could be that some thread in web application connects to ebms database and searches something in message table periodically. It will occupy lots of cpu resources. try the following index. It helps us.

create index message_type_status_idx on message (message_type, status, time_stamp);

Regards,

[tomlee]

@Alan-Chen-au Thanks for your suggestion. Another user solved this with adding the index as described here. @urspraveen2001 would you mind trying this suggestion and get back to us whether it works? Thank you.

[Alan-Chen-au]

Hi Tom,

In my opinion, create an index on message_type and status; it should work as well. But I have not tried to create an index on status only. Will let you know once the CVT env is available.

[Alan-Chen-au]

Hi Tom,

check the postgres : SELECT count(*) as cnt, usename, query FROM pg_stat_activity GROUP BY usename, query ORDER BY cnt DESC;

You will find the query.

hermes_ebms | select * from message where message_box='inbox' and status='PD' and message_type<>'ProcessedError' order by time_stamp LIMIT 50

I think Query Performance :

Index on message_box, status and message_type > Index on status and message_type > Index on status ;

but it will occupy more hard disk space :

Index on message_box, status and message_type > Index on status and message_type > Index on status ;

Regards,
Alan