3 Replies Latest reply on Dec 12, 2011 10:54 AM by Randall Hauch

    JPA source, retryLimit and deadlock

    jamat Newbie

      Hello,

      setup: jboss as 6, latest modeshape, jpa source with mysql.

       

      Simple test. I have a node with 2 children. All are nt:unstructured

      Now I delete both children in 2 different threads (each thread delete one child).

      One of the deletion will fail:

      javax.persistence.PersistenceException: org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

       

      (see below the mysql status)

       

      As we have this retryLimit parameter, I have tried to increase it but it did not help.

      So the first question is: what is this retryLimit for ? I have looked at the code to try to understand but could not find where it was used.

       

      Second question: is my use case error expected ? I would think that such simple scenario would work.

      And as it does not, what is the way to make it work ?

      Should the application catch such exception and retry ?

       

      TIA

       

      show engine innodb status:

      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      111211  8:08:07
      *** (1) TRANSACTION:
      TRANSACTION 0 44589313, ACTIVE 0 sec, process no 1680, OS thread id 7375760 starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 5 lock struct(s), heap size 1024, 2 row lock(s), undo log entries 3
      MySQL thread id 74957, query id 6191078 lnx-axsdev4-eth0-1 135.13.27.242 root Updating
      update MODE_SIMPLE_NODE set ALLOWS_SNS=0, CHILD_NAME_LOCAL='JA1', CHILD_NAME_NS_ID=1, COMPRESSED=null, DATA=_binary'??\0w\0\0\0t\0authorw\0\0\0\0St\0admint\0\'{http://www.jcp.org/jcr/1.0}primaryTypew\0\0\0\0Nt\0+{http://www.jcp.org/jcr/nt/1.0}unstructuredt\0namew\0\0\0\0St\0ja1t\0!updateDatew\0\0\0\0Tt\02011-12-11T08:06:13.897-08:00t\05{http://www.modeshape.org/internal/1.0}nodeDefinitionw\0\0\0\0St\0k{http???www.jcp.org?jcr?nt?1.0}unstructured/?/{http???www.jcp.org?jcr?nt?1.0}baset\0$attributeNamew\0\0\0\0St\0Modelt\0#creationDatew\0\0\0\0Tt\02011-12-11T08:06:13.897-08:00', CHILD_INDEX=0, NODE_UUID='ec996a4b-a0fc-4dba-8330-ba2d8c051e49', PARENT_ID=3370, NUM_PROPS=8, ENFORCEREFINTEG=0, SNS_INDEX=1, WORKSPACE_ID=1 where ID=3372
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 0 page no 125981 n bits 88 index `PRIMARY` of table `modeshapeja`.`MODE_SIMPLE_NODE` trx id 0 44589313 lock_mode X locks rec but not gap waiting
      Record lock, heap no 20 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
      0: len 8; hex 8000000000000d2c; asc        ,;; 1: len 6; hex 000002a86100; asc     a ;; 2: len 7; hex 000002c0191c9e; asc        ;; 3: len 1; hex 00; asc  ;; 4: len 3; hex 4a4131; asc JA1;; 5: SQL NULL; 6: len 30; hex aced000577040000000774001d7b687474703a2f2f636f6d2e6d6f746976; asc     w     t  ;...(truncated); 7: len 4; hex 80000001; asc     ;; 8: len 30; hex 65633939366134622d613066632d346462612d383333302d626132643863; asc ec996a4b-a0fc-4dba-8330-ba2d8c;...(truncated); 9: len 4; hex 80000008; asc     ;; 10: len 1; hex 00; asc  ;; 11: len 4; hex 80000001; asc     ;; 12: len 8; hex 8000000000000001; asc         ;; 13: len 8; hex 8000000000000001; asc         ;; 14: SQL NULL;

       


      *** (2) TRANSACTION:
      TRANSACTION 0 44589312, ACTIVE 0 sec, process no 1680, OS thread id 11623312 fetching rows, thread declared inside InnoDB 212
      mysql tables in use 2, locked 1
      103 lock struct(s), heap size 5504, 2 row lock(s), undo log entries 3
      MySQL thread id 74948, query id 6191081 lnx-axsdev4-eth0-1 135.13.27.242 root Sending data
      update MODE_SIMPLE_NODE set PARENT_ID=null where WORKSPACE_ID=1 and (NODE_UUID in (select subgraphno1_.UUID from MODE_SUBGRAPH_NODES subgraphno1_ where subgraphno1_.QUERY_ID=16562 and subgraphno1_.DEPTH>=0))
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 0 page no 125981 n bits 88 index `PRIMARY` of table `modeshapeja`.`MODE_SIMPLE_NODE` trx id 0 44589312 lock_mode X locks rec but not gap
      Record lock, heap no 20 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
      0: len 8; hex 8000000000000d2c; asc        ,;; 1: len 6; hex 000002a86100; asc     a ;; 2: len 7; hex 000002c0191c9e; asc        ;; 3: len 1; hex 00; asc  ;; 4: len 3; hex 4a4131; asc JA1;; 5: SQL NULL; 6: len 30; hex aced000577040000000774001d7b687474703a2f2f636f6d2e6d6f746976; asc     w     t  ;...(truncated); 7: len 4; hex 80000001; asc     ;; 8: len 30; hex 65633939366134622d613066632d346462612d383333302d626132643863; asc ec996a4b-a0fc-4dba-8330-ba2d8c;...(truncated); 9: len 4; hex 80000008; asc     ;; 10: len 1; hex 00; asc  ;; 11: len 4; hex 80000001; asc     ;; 12: len 8; hex 8000000000000001; asc         ;; 13: len 8; hex 8000000000000001; asc         ;; 14: SQL NULL;

      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 0 page no 125981 n bits 88 index `PRIMARY` of table `modeshapeja`.`MODE_SIMPLE_NODE` trx id 0 44589312 lock_mode X locks rec but not gap waiting
      Record lock, heap no 19 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
      0: len 8; hex 8000000000000d2b; asc        +;; 1: len 6; hex 000002a86101; asc     a ;; 2: len 7; hex 000002c0070110; asc        ;; 3: len 1; hex 00; asc  ;; 4: len 3; hex 4a4130; asc JA0;; 5: SQL NULL; 6: len 30; hex aced000577040000000774001d7b687474703a2f2f636f6d2e6d6f746976; asc     w     t  ;...(truncated); 7: len 4; hex 80000000; asc     ;; 8: len 30; hex 35346439306537352d373933632d346537352d613738632d616463623665; asc 54d90e75-793c-4e75-a78c-adcb6e;...(truncated); 9: len 4; hex 80000008; asc     ;; 10: len 1; hex 00; asc  ;; 11: len 4; hex 80000001; asc     ;; 12: len 8; hex 8000000000000001; asc         ;; 13: len 8; hex 8000000000000001; asc         ;; 14: SQL NULL;

      *** WE ROLL BACK TRANSACTION (1)