Mysql – deadlock in mariadb: with non-index & fk table

deadlockmariadbMySQL

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?

Best Answer

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.

Related Solutions

Mysql – InnoDB deadlock with INSERT .. SELECT

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.

SQL Server Index Update Deadlock – Causes and Solutions

Is there a way to prevent the deadlock while maintaining the same queries?

The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):

Deadlock graph

As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The SELECT query needs to access the index before the table due to the RID lookup, whereas the UPDATE query modifies the table first, then the index.

Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:

Avoid the RID lookup by making the nonclustered index covering. This is probably not practical in your case because the SELECT query returns 26 columns.
Avoid the RID lookup by creating a clustered index. This would involve creating a clustered index on the column Proposal. This is worth considering, though it appears this column is of type uniqueidentifier, which may or may not be a good choice for a clustered index, depending on broader issues.
Avoid taking shared locks when reading by enabling the READ_COMMITTED_SNAPSHOT or SNAPSHOT database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.
Avoid taking shared locks when reading by using the READ UNCOMMITTED isolation level for the SELECT query. All the usual caveats apply.
Avoid concurrent execution of the two queries in question by using an exclusive application lock (see sp_getapplock).
Use table lock hints to avoid concurrency. This is a bigger hammer than option 5, as it may affect other queries, not just the two identified in the question.

Can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order

You can try this, by wrapping the update in an explicit transaction and performing a SELECT with an XLOCK hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.

In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.

Best Answer

Related Solutions

Mysql – InnoDB deadlock with INSERT .. SELECT

SQL Server Index Update Deadlock – Causes and Solutions

Related Question