hibernate - MySQL trigger somehow causes commit -

hibernate - MySQL trigger somehow causes commit -

we have simple auditing scheme on several tables. works fine, every 1 time in while, error on operation causes trigger fire:

java.sql.sqlexception: explicit or implicit commit not allowed in stored function or trigger.

unfortunately, cannot figure out how our triggers cause commit.

these triggers (sometimes) cause error:

create trigger `my_schema`.`filedescriptorinserttrigger` after insert on `my_schema`.`file_descriptor` each row insert `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` each row update `audit_event` set lastmodified=now() classifier='file_and_dir' , applicationid=new.application; create trigger `my_schema`.`filedescriptordeletetrigger` after delete on `my_schema`.`file_descriptor` each row update `audit_event` set lastmodified=now() classifier='file_and_dir' , applicationid=old.application;

edit: upon request, output of show variables '%commit%'

variable_name value innodb_commit_concurrency 0 innodb_flush_log_at_trx_commit 1

edit 2

the error tends happen after insert on my_schema.file_descriptor, narrow downwards insert trigger have no clue how cause commit.

we have hibernate on top of this, it's hibernate that's doing inserts, , have entity mapped on audit_event table, hibernate (should) never write audit_event table.

i uploaded full stacktrace if helps.

this happens because of deadlock that's why explicit commit/rollback happens. please seek handle activity. here links may help you.

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

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

try utilize read-committed isolation level.

transaction-isolation = read-committed

this may resolve problem. here links reference

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

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

hope help.

mysql hibernate triggers