Deadlock mysql InnoDB ? Pourquoi!

Version imprimable

Salut

Voila ca fait plusieurs jours que je galere sur un probleme: j'ai un deadlock

C'est avec du Java mais je pense qu'il n'y a pas besoin de comprendre ce language pour eventuellement pouvoir m'aider ;)

J'utilise:

Mysql InnoDB: Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Serveur d'application: Glassfish v2.1

Persistance avec EJB3 - JPA - Hibernate

Sans rentrer dans les détails, au niveau applicatif, j'ai:
- Un systeme SOA par servlet qui gere les abonnements des utilisateurs a des services.
- Un systeme de jobs quartz qui permet par exemple de générer des alertes pour les utilisateurs, décrémenter journalierement leur crédit si leur compte a été actif...

Mon probleme: j'ai des deadlocks qui apparaissent lors des tests en charge (100 000 utilisateurs simulés, avec soit environ 30 requetes par seconde selon les prévisions).

Au niveau des stacks ca se présente sous cette forme:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

CF la fin de la stack net.xxx.server.dao.impl.PaymentDAOImpl.listPaymentsByStateAndCompany(PaymentDAOImpl.java:270)

Voila la fonction concernée:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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;
	}

Je précise que cette fonction marche parfaitement bien quand il n'y a pas un grand nombre de requetes utilisées lors de son execution...

Lors des tests en charge, le job qui execute cette requete est lancé toutes les 5 secondes, comme un autre. Il n'y a pas que cette fonction qui pose probleme en fait, j'ai des erreurs sur les jobs en général qui font des problemes de deadlock.

SUR MYSQL:

Exemple de deadlock affiché:

Code:

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
 
------------------------
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    E P  ;; 9: len 8; hex 8000000000000002; asc         ;; 10: len 8; hex 8000000000000003; asc         ;; 11: len 8; hex 80000000000034db; asc       4 ;;

Transaction Isolation

Apres m'etre renseigné, j'ai lu pas mal de choses sur les niveaux de transaction isolation.

Sur glassfish, il existe dans l'administrator un truc pour regler le niveau d'isolation des transactions. J'ai passé en read-uncommited.

Ca n'a pas marché, j'ai ensuite fait de meme sur mysql:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

J'ai relancé un test en charge et devinez quoi??? Toujours des deadlocks!!! Pourquoiiiiiiiiiiiii!

Svp si vous savez d'ou ca peut venir je suis bien curieux. Vous remarquerez que dans l'exemple donné, la requete qui "plante" ne fait qu'un simple select sur une table avec 2 conditions where... est-ce normal qu'en read-uncommited il puisse y avoir un deadlock sur ce type de requete??? Si oui, a quoi cela peut-il etre du?

Ai-je vraiment bien configuré glassfish et mysql pour que le mode Uncommited fonctionne???

28/04/2009, 15h23
HerQuLe

Ah oui et au passage: est ce que mysql est un bon choix pour ce genre de charge? J'ai l'impression de ne pas etre le seul a me plaindre des deadlocks sous mysql en tout cas.
28/04/2009, 18h50
SQLpro

Il est sur que MySQL n'est pas taillé pour de la forte concurrence alors avec en plus une couche de merde comme Hibernate, c'est le casse gueule assuré...

Voici ce que je dis des ORM en général dans un article à paraître prochainement :
"
6 – Pourquoi pas un ORM ?

Bonne idée, mais mauvaise pioche… Les outils actuels de mapping relationnel objet, d’Hibernate à iBatis en passant par l’inabouti Entity Framework, possèdent tous les mêmes défauts. Malgré tous les caches qu’on leur met les performances sont divisées par un facteur important par rapport à une série d’insertions faites directement depuis le code client. Pire encore lorsque l’on compare ce que fait l’ORM par rapport à une procédure stockée.
En sus les transactions durent plus longtemps car il faut se payer les temps de communications réseau entre les serveurs, ce qui augmente la durée des verrous sur les tables et diminue donc la capacité globale du système à absorber la charge, alors que c’est un des principaux argument de vente (cherchez l’erreur !). Enfin, peu de gens savent qu’une transaction applicative n’est pas l’équivalent d’une transaction de données et qu’il faut à la fois l’un et l’autre si l’on veut être rigoureux. Quant au pilotage du niveau d’isolation des transactions de données, comme les développeurs ne savent même pas de quoi il s’agit, on en constate généralement l’absence !
Finalement les seuls arguments positifs en faveur ce ces outils sont les suivants : pour les éditeurs, cela permet de vendre de la boîte (noire…), pour les développeurs cela leurs permet de rester dans le concept objet sans jamais aller voir du côté de la base de données, les enfonçant ainsi encore un peu plus dans leurs carences.
Quant à l’argument qui consiste à dire que la maintenabilité d’un code client est bien plus simple qu’un code relationnel, j’ose dire qu’elle est d’une évidente stupidité : avoir 3 à 4 fois moins de lignes de code en matière relationnelle qu’avec du code itératif induit mathématiquement le fait qu’il y aura proportionnellement moins d’intervention à y faire. De plus la maintenance d’un service se fait à froid, alors que dans la base c’est nativement à chaud. Bref l’argument de facilité de maintenance largement répandu, montre encore une fois l’étendu du désastre culturel : pour avoir, par manque de formation, laissé les développeurs dans l’ignorance des possibilités de codage dans un SGBDR, pour avoir parfois choisit les mauvais outils (MySQL, SQLite…) on a contourné le problème en y ajoutant des couches superflues, alambiquées et coûteuses, tant en licence, qu’en temps de développement ou en administration.

"

Il me semble que ce que vous faites est un parfait exemple de ce que je dis ici !

Enfin, pour vous répondre plus précisément, oui un DEADLOCK peut intervenir avec un SELECT... Il faut au moins un processus qui écrive et un autre qui lise.

A +
29/04/2009, 10h03
HerQuLe

Citation:

Envoyé par SQLpro

Il me semble que ce que vous faites est un parfait exemple de ce que je dis ici !

Enfin, pour vous répondre plus précisément, oui un DEADLOCK peut intervenir avec un SELECT... Il faut au moins un processus qui écrive et un autre qui lise.
A +

Ok merci pour les infos :)

Et est-il possible de résoudre ce probleme, du moins réduire le nombre de deadlocks, toujours en continuant a utiliser Hibernate et MySQL ?
29/04/2009, 19h28
SQLpro

Oui : surdimensionnez largement les serveurs en multipliant par 4 les ressources RAM, CPU et disque.

A +
30/04/2009, 18h14
StephaneC.

Le problème du SHOW INNODB STATUS pour les deadlocks, c'est qu'il ne montre que les dernières requêtes de tes transactions.
Si tu veux pouvoir pointer les causes de tes deadlocks, il faut que tu retrouves toutes les requêtes des transactions en question pour pouvoir regarder ce qui se passe.

Stéphane