You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andrus Adamchik <an...@objectstyle.org> on 2014/08/05 15:54:45 UTC

MySQL deadlock exceptions

After years working with MySQL I stumbled on a problem for the first time that seems rather old and common. MySQL would throw when 2 InnoDB transactions are trying to update the same row:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

I am wondering whether we should add a “catch/restart tx” to Cayenne to handle that. The purpose of this message is to ask how often if ever folks in the community have to deal with this exception?

Thanks,
Andrus

Re: MySQL deadlock exceptions

Posted by Andrus Adamchik <an...@objectstyle.org>.
Nice. That’s a good way to handle it outside the framework. Thanks for confirming. 

Andrus

On Aug 5, 2014, at 5:05 PM, Ramiro Aparicio <ra...@prot-on.com> wrote:

> El 05/08/2014 15:54, Andrus Adamchik escribió:
>> After years working with MySQL I stumbled on a problem for the first time that seems rather old and common. MySQL would throw when 2 InnoDB transactions are trying to update the same row:
>> 
>> com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
>> 
>> I am wondering whether we should add a “catch/restart tx” to Cayenne to handle that. The purpose of this message is to ask how often if ever folks in the community have to deal with this exception?
>> 
>> Thanks,
>> Andrus
>> 
>> 
>> 
> I have been seen that error for a while, we use an abstract Dao inherited by all daos so all commits use this funcion:
> 
>        public void commitChanges() {
>            this.commitChanges(0);
>        }
> 
>        public void commitChanges(int level) {
>            try {
>                getThreadDataContext().commitChanges();
>            } catch (RuntimeException cre) {
>                if (level < 3 &&
>                        (cre.getCause() instanceof
>   MySQLTransactionRollbackException
>                        || cre.getCause() instanceof
>   com.mysql.jdbc.exceptions.MySQLTransactionRollbackException)) {
>                    commitChanges(level+1);
>                } else {
>                    CayenneCommitException cce = new
>   CayenneCommitException(cre, getThreadDataContext());
>                    rollbackChanges();
>                    throw cce;
>                }
>            }
>        }
> 
> 
> Not sure if this helps or not but currently we only see like 1 of those errors per month and I think that previously it was more frequent.

Re: MySQL deadlock exceptions

Posted by Ramiro Aparicio <ra...@prot-on.com>.
El 05/08/2014 15:54, Andrus Adamchik escribió:
> After years working with MySQL I stumbled on a problem for the first time that seems rather old and common. MySQL would throw when 2 InnoDB transactions are trying to update the same row:
>
> com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
>
> I am wondering whether we should add a “catch/restart tx” to Cayenne to handle that. The purpose of this message is to ask how often if ever folks in the community have to deal with this exception?
>
> Thanks,
> Andrus
>
>
>
I have been seen that error for a while, we use an abstract Dao 
inherited by all daos so all commits use this funcion:

         public void commitChanges() {
             this.commitChanges(0);
         }

         public void commitChanges(int level) {
             try {
                 getThreadDataContext().commitChanges();
             } catch (RuntimeException cre) {
                 if (level < 3 &&
                         (cre.getCause() instanceof
    MySQLTransactionRollbackException
                         || cre.getCause() instanceof
    com.mysql.jdbc.exceptions.MySQLTransactionRollbackException)) {
                     commitChanges(level+1);
                 } else {
                     CayenneCommitException cce = new
    CayenneCommitException(cre, getThreadDataContext());
                     rollbackChanges();
                     throw cce;
                 }
             }
         }


Not sure if this helps or not but currently we only see like 1 of those 
errors per month and I think that previously it was more frequent.