when i try to do an operation concurrently, there exists an deadlock from mariadb show engine innodb status, the deadlock log is as follows
------------------------ LATEST DETECTED DEADLOCK ------------------------ 160929 7:43:35 *** (1) TRANSACTION: TRANSACTION 151E3D3, ACTIVE 48 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 16 lock struct(s), heap size 3112, 10 row lock(s), undo log entries 9 MySQL thread id 9, OS thread handle 0x7f35529ef700, query id 9966 localhost 127.0.0.1 root update insert into t_st_storage_adapter_host_scsi_disk (storage_adapter_id, host_scsi_disk_id) values ('e487a58a5774ddf0015774e5c43d00bc', 'e487a58a5774ddf0015774e5da2800d3') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D3 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 151E3D0, ACTIVE 48 sec fetching rows mysql tables in use 1, locked 1 21 lock struct(s), heap size 3112, 35 row lock(s), undo log entries 26 MySQL thread id 7, OS thread handle 0x7f355295d700, query id 10345 localhost 127.0.0.1 root updating delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e487a58a5774ddf0015774e5c7b900c1' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 812 n bits 96 index `GEN_CLUST_INDEX` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X waiting *** WE ROLL BACK TRANSACTION (1)
t_st_storage_adapter_host_scsi_disk is a relation table for t_st_storage_adapter & t_st_host_scsi_disk. non primary key or unique key is create on this table.
| t_st_storage_adapter_host_scsi_disk | CREATE TABLE `t_st_storage_adapter_host_scsi_disk` ( `host_scsi_disk_id` varchar(36) COLLATE utf8_bin NOT NULL, `storage_adapter_id` varchar(36) COLLATE utf8_bin NOT NULL, KEY `fk_storage_adapter_host_scsi_disk_hostscsidisk` (`host_scsi_disk_id`), KEY `fk_storage_adapter_host_scsi_disk_storage_adapter` (`storage_adapter_id`), CONSTRAINT `fk_storage_adapter_host_scsi_disk_hostscsidisk` FOREIGN KEY (`host_scsi_disk_id`) REFERENCES `t_st_host_scsi_disk` (`id`), CONSTRAINT `fk_storage_adapter_host_scsi_disk_storage_adapter` FOREIGN KEY (`storage_adapter_id`) REFERENCES `t_st_storage_adapter` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
and the fk column are both uuid.
when i do a business operation, repeatable delete & insert sql(determined by orm hibernate's mechanism) on this table will be executed. when try to delete the sql is like
delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e482964a57d370280157d3726aba0037'
and deadlock will occur in most cases. so why will the deadlock happens and what can i do to prevent this deadlock?
the isolation level is RR, and the
innodb_locks_unsafe_for_binlog is OFF.
and i have no idea on:
1. Is there exists obvious difference between the
hidden clustered index generated automatically by innodb (see mysql docs) and the pk?
2. As the sql shows above, why fk lock and
hidden clustered index lock will conflict? the business code operate the table
t_st_storage_adapter_host_scsi_disk in different
storage_adapter_id in one thread. and when i do some test, the lock won't happens in such situation.
thanks a lot in advance.
i don't know the reason but after add a pk on this table, the problem has been fixed.So as the docs from the mysql above, which difference lead to the deadlock happen between pk and the auto generate cluster_index by innodb?