1

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)

the table 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?

Improve this question
3
  • It should go without saying, a table without a primary key doesn't make sense. Add a primary key comprised of both columns (host_scsi_disk_id, storage_adapter_id) to this table. I can't say how much this will help, but it's the obvious first step.
    – Michael - sqlbot
    Oct 18, 2016 at 2:41
  • it works that the add pk for this table & thanks for @Michael-sqlbot. but i am still puzzled with the difference between pk and the generated cluster_index.
    – PazuLee
    Oct 21, 2016 at 9:20
  • 1
    You haven't really fixed it, you've just reduced the likelihood. You'd have the same issue if the primary were a non-hidden auto-increment. The thing to remember is that deadlocks are essentially a normal of database operations. It simply means that two transactions have row locks and at least one is trying to obtain an additional lock that leaves them at an impasse. Somewhat like two people locking their keys in each other's apartments. Deadlock detection prevents the second person from closing their apartment door, preventing an infinite wait for a situation that can never resolve itself.
    – Michael - sqlbot
    Oct 21, 2016 at 16:43

1 Answer 1

Reset to default
0

The lack of a PK is a serious problem. But there are other problems with the many:many table. See here for 7 tips on an efficient mapping table. In particular, you do need a composite PRIMARY KEY in order to prevent inserting duplicates. At that point, INSERT IGNORE (or other techniques) can be efficiently used.

Improve this answer
5
  • thank u for the suggestion. but what make me confused is that this problem does not always happens on different environment. as innodb engine doc says mysql docs, If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values.
    – PazuLee
    Oct 19, 2016 at 0:35
  • so is there any obvious differences between pk and the hidden clustered index? and i have no idea that according to the sql above, why fk lock has conflict with the hidden clustered index?
    – PazuLee
    Oct 19, 2016 at 0:39
  • Sorry, I am weak on whether the hidden PK acts differently in this context. However, the SELECTs will speed up if you follow my tips -- this will help keep them out of the picture, thereby making it less likely for your DELETE to deadlock.
    – Rick James
    Oct 19, 2016 at 0:47
  • Ok, thank u all the same :). Using ORM may lead to some strange problem and it's really hard to resolve.
    – PazuLee
    Oct 19, 2016 at 0:51
  • 1
    You can probably change the table definitions and indexes without impacting the ORM. (If not, then I will rant about 3rd party software getting in the way.)
    – Rick James
    Oct 19, 2016 at 1:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.