Deadlock InnoDB with discreet reading! - Java - Glassfish - EJB3 (JPA / Hibernate)

Question

A few days passed when I had problems blocking a Java application with Glassfish - EJB3 with Mysql InnoDB

Config: Mysql InnoDB: version 14.12. Distribution 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Application Server: Glassfish v2.1

Persistence with EJB3 - JPA - Hibernate

To make this simple, I have - an SOA system with servlets that process user subscriptions for services, login, logout, payments and registration, etc ... - a quartz work system (cron triggers), which process the daily decline of these services, the generation of "low credit" alerts, confirmation of payments, etc.

My problem: during load checks, I found deadlocks (100,000 users - 30 requests per second)

Returned Stack Sample:

Message ID: Could not synchronize database state with session org.hibernate.exception.LockAcquisitionException Complete Message: Could not execute JDBC batch update at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109) at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244) at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2382) at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2335) at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2635) at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:115) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64) at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:996) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1141) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67) at net.xxx.server.dao.impl.PaymentDAOImpl.listPaymentsByStateAndCompany(PaymentDAOImpl.java:270) 

Pay attention to the end, this is the code I made: net.xxx.server.dao.impl.PaymentDAOImpl.listPaymentsByStateAndCompany (PaymentDAOImpl.java:270)

This function:

 private static final String QUERY_FOR_PAYMENTS_BY_STATE_AND_COMPANY = " FROM " + Payment.class.getName() + " p WHERE p.serviceDefinition.company=:company" + " AND p.state = :state"; @SuppressWarnings("unchecked") public List<Payment> listPaymentsByStateAndCompany(Company company,Constants.PaymentState state) { List<Payment> payments = this.getEntityManager() .createQuery(QUERY_FOR_PAYMENTS_BY_STATE_AND_COMPANY) .setParameter("state",state.ordinal()) .setParameter("company",company) .getResultList(); return payments; } 

This function works great when it does not load testing, and that we have 1 request every 5 seconds, for example.

During load testing, we have tasks that work with high frequencies (for example, every 5 seconds).

I do not get only this error, but some others on other tasks too (still a dead end)!

On MYSQL:

Dead end example:

 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 090428 12:21:11 *** (1) TRANSACTION: TRANSACTION 0 14286818, ACTIVE 0 sec, process no 21872, OS thread id 802850 starting index read mysql tables in use 1, locked 1 LOCK WAIT 13 lock struct(s), heap size 1024, undo log entries 2 MySQL thread id 298, query id 11843357 localhost 127.0.0.1 root Updating /* */ update service set balance=40.0, company_id=2, last_on='2009-04-28 12:19:55', modified_by='server', modified_on='2009-04-28 12:21:11', service_definition_id=3, state=1, subscriber_id=13578, valid_until='2010-02-22 12:13:52' where service_id=693 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 62 n bits 176 index `PRIMARY` of table `xxx/service` trx id 0 14286818 lock_mode X locks rec but not gap waiting Record lock, heap no 98 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 8; hex 80000000000002b5; asc ;; 1: len 6; hex 000000d9faa0; asc ;; 2: len 7; hex 0000000cc91e70; asc p;; 3: len 4; hex 00001c42; asc B;; 4: len 8; hex 80001245aad4e363; asc E c;; 5: len 6; hex 736572766572; asc server;; 6: len 8; hex 80001245aad4e3c9; asc E ;; 7: len 1; hex 81; asc ;; 8: len 8; hex 80001247f200df08; asc G ;; 9: len 8; hex 8000000000000002; asc ;; 10: len 8; hex 8000000000000003; asc ;; 11: len 8; hex 800000000000350a; asc 5 ;; *** (2) TRANSACTION: TRANSACTION 0 14286798, ACTIVE 1 sec, process no 24963, OS thread id 393239 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 17 lock struct(s), heap size 1024, undo log entries 16 MySQL thread id 253, query id 11843359 localhost 127.0.0.1 root Updating /* */ update payment set credit=1.0, currency='EUR', modified_by='9999900092', modified_on='2009-04-28 12:21:11', payment_definition_id=7, price=1.0, service_definition_id=3, state=0, subscriber_id=13578, transaction_id=11463 where payment_id=15914 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 62 n bits 176 index `PRIMARY` of table `xxx/service` trx id 0 14286798 lock mode S locks rec but not gap Record lock, heap no 47 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 8; hex 8000000000000286; asc ;; 1: len 6; hex 000000d9ffce; asc ;; 2: len 7; hex 0000000cc90683; asc ;; 3: len 4; hex 0000f841; asc A;; 4: len 8; hex 80001245aad4e3b2; asc E ;; 5: len 6; hex 736572766572; asc server;; 6: len 8; hex 80001245aad4e3ff; asc E ;; 7: len 1; hex 81; asc ;; 8: len 8; hex 80001245d450fed8; asc EP ;; 9: len 8; hex 8000000000000002; asc ;; 10: len 8; hex 8000000000000003; asc ;; 11: len 8; hex 80000000000034db; asc 4 ;; 

Transaction Isolation

I read transaction isolation information on the Internet.

On a glass planet, we can set the transaction isolation level, I put it uncommitted.

This did not work, then I installed the same level in mysql:

 mysql> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | READ-UNCOMMITTED | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec) 

SVP can someone tell me what could be the problem? I really do not know!!!!

BTW, which I saw on the Internet, you can select the transaction isolation level for each request ... Is it possible to set the transaction isolation level for methods on JPA directly? Because I think that only jobs that perform global data updates (for example, reducing 15,000 services) should be read-uncommitted, am I mistaken?

Answer 1

I do not have an exact answer to your problem, but it can help you narrow it down.

Deadlocks can occur at any level of transaction isolation, because innodb will set update locks even on "read uncommitted".

You can verify this with this simple script:

 CREATE TABLE locktest (a INT(11), b INT(11), PRIMARY KEY (a)) ENGINE=INNODB; INSERT INTO locktest VALUE (1, 1); INSERT INTO locktest VALUE (2, 1); 

Then open 2 mysql consoles (C1 and C2) and run the following commands:

 C1> BEGIN; C2> BEGIN; C1> UPDATE locktest SET b = b + 1 WHERE a = 1; C2> UPDATE locktest SET b = b + 1 WHERE a = 2; C1> UPDATE locktest SET b = b + 1 WHERE a = 2; C2> UPDATE locktest SET b = b + 1 WHERE a = 1; 

You will see a dead end on C2, and C1 completes a successful operation even when reading uncommitted. If you check the engine log, you will see a similar report.

If you delete the primary key in the table, the commands suffocate even earlier, and this is because innodb lock works better if there is an index covering the query that sets the locks.

So, back to your problem.

You should check all requests related to transactions that have ended with deadlocks and make sure that the corresponding indexes exist. If MySQL needs to perform a full table scan, it will stop locking more than necessary.

These tips helped me resolve some deadlocks in my application. A good way to prevent deadlocks is to set write locks with "SELECT ... FOR UPDATE" to lock some parent row.

So, for example, if you have several transactions trying to update some specific client data, you can issue "SELECT id FROM customer WHERE id = 123 FOR UPDATE", they will wait in order at this point instead of stopping holding locks that each other.

Answer 2

I had a similar problem with a Java application using an Oracle database.

I found that there were no indexes for foreign keys in the database, which caused the database to block as many rows as needed and resulted in deadlocks in a highly competitive test.

Here is a very good article to diagnose this. Most of the article is an oracle, but some things apply to other relational databases too: http://www.oratechinfo.co.uk/deadlocks.html

Answer 3

You must admit that deadlocks will occur in any transactional database no matter what you do. You just have to process them gracefully and repeat the failed transaction for a fixed number of times (usually normal) - there should be a parameter somewhere in Glassfish that is responsible for this.