MySQL trigger somehow causes commit

Question

We have a simple auditing system on several tables. This all works fine, but every once in a while, we get an error like this on an operation that causes the trigger to fire:

java.sql.SQLException: Explicit or implicit commit is not allowed in stored function or trigger.

Unfortunately, we cannot figure out how our triggers could cause a commit.

These are the triggers that (sometimes) cause the error:

CREATE
TRIGGER `my_schema`.`fileDescriptorInsertTrigger`
AFTER INSERT ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
    insert into `AUDIT_EVENT`
            (`applicationId`,`classifier`,`lastModified`)
        values
            (NEW.application,'FILE_AND_DIR',NOW())
    on duplicate key
        update lastModified=NOW();

CREATE
TRIGGER `my_schema`.`fileDescriptorUpdateTrigger`
AFTER UPDATE ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
    update `AUDIT_EVENT`
      set lastModified=NOW()
      where classifier='FILE_AND_DIR'
        and applicationId=NEW.application;

CREATE
TRIGGER `my_schema`.`fileDescriptorDeleteTrigger`
AFTER DELETE ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
    update `AUDIT_EVENT`
      set lastModified=NOW()
      where classifier='FILE_AND_DIR'
        and applicationId=OLD.application;

Edit: upon request, output of show variables like '%commit%'

Variable_name   Value
innodb_commit_concurrency   0
innodb_flush_log_at_trx_commit  1

Edit 2

The error only tends to happen after an INSERT on my_schema.FILE_DESCRIPTOR, so that would narrow it down to the INSERT TRIGGER but even then I have no clue how it could cause a commit.

We do have Hibernate on top of this, so it's actually Hibernate that's doing the inserts, and we also have an entity mapped on the AUDIT_EVENT table, but Hibernate (should) never write to the AUDIT_EVENT table.

I uploaded a full stacktrace if that helps.

Answer 1

This happens because of DEADLOCK that's why explicit Commit/Rollback happens. Please try to handle this activity. Here is some links that may help you.

http://bugs.mysql.com/bug.php?id=24989

http://lists.mysql.com/commits/27471

Try to use read-committed isolation level.

transaction-isolation = READ-COMMITTED

This may resolve your problem. Here is Links for reference

http://www.toofishes.net/blog/mysql-deadlocking-simple-inserts/

http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html

Hope this will help.