Delete query not using index and cause deadlock
Hi Friends,
This query is not using index while deleting records from tables, even after I have dropped all indexes and foreign keys from the table and recreate it. Pl suggest.
issue is that this query is running simaltanuously two or more times from application and cause deadlock.
DELETE FROM `customgrid_grid_role` WHERE (grid_id = 10 AND grid_role_id NOT IN ('13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '45', '46', '47', '48', '49', '54', '55', '56', '57', '58', '59'))
DELETE FROM `customgrid_grid_role` WHERE (grid_id = 3 AND grid_role_id NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '25', '26', '27', '28', '52', '53', '85', '86', '87', '88', '89', '90', '91', '94', '95', '96', '100', '101', '112', '113', '114', '115', '116'))
Execution plan is :
mysql> explain DELETE FROM `customgrid_grid_role` WHERE (grid_id = 10 AND grid_role_id NOT IN ('13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '45', '46', '47', '48', '49', '54', '55', '56', '57', '58', '59'));
+----+-------------+----------------------+------+---------------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+---------------------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | customgrid_grid_role | ALL | PRIMARY,FK_CUSTOM_GRID_GRID_ROLE_GRID | NULL | NULL | NULL | 88 | Using where |
+----+-------------+----------------------+------+---------------------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-08-20 16:05:52 7fd7f7578700
*** (1) TRANSACTION:
TRANSACTION 31710910180, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 2936, 57 row lock(s), undo log entries 1
MySQL thread id 2989491, OS thread handle 0x7fd814d05700, query id 717340064 172.16.50.116 tolexo updating
DELETE FROM `customgrid_grid_role` WHERE (grid_id = 10 AND grid_role_id NOT IN ('13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '45', '46', '47', '48', '49', '54', '55', '56', '57', '58', '59'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 976 page no 7 n bits 128 index `PRIMARY` of table `magento`.`customgrid_grid_role` trx id 31710910180 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00030b09ffd4; asc ;;
2: len 7; hex 4b0003e9a60a5c; asc K \;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000001; asc ;;
5: len 30; hex 613a383a7b733a393a22637573746f6d697a65223b733a313a2232223b73; asc a:8:{s:9:"customize";s:1:"2";s; (total 220 bytes);
6: SQL NULL;
7: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 31710909919, ACTIVE 7 sec fetching rows, thread declared inside InnoDB 4988
mysql tables in use 1, locked 1
14 lock struct(s), heap size 2936, 89 row lock(s), undo log entries 1
MySQL thread id 2989427, OS thread handle 0x7fd7f7578700, query id 717340340 172.16.50.116 tolexo updating
DELETE FROM `customgrid_grid_role` WHERE (grid_id = 3 AND grid_role_id NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '25', '26', '27', '28', '52', '53', '85', '86', '87', '88', '89', '90', '91', '94', '95', '96', '100', '101', '112', '113', '114', '115', '116'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 976 page no 7 n bits 128 index `PRIMARY` of table `magento`.`customgrid_grid_role` trx id 31710909919 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 976 page no 7 n bits 128 index `PRIMARY` of table `magento`.`customgrid_grid_role` trx id 31710909919 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (1)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `customgrid_grid_role` (
`grid_role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`grid_id` int(10) unsigned NOT NULL,
`role_id` int(10) unsigned NOT NULL,
`permissions` text,
`default_profile_id` int(10) unsigned DEFAULT NULL,
`available_profiles` text,
PRIMARY KEY (`grid_role_id`),
KEY `FK_CUSTOM_GRID_GRID_ROLE_GRID` (`grid_id`),
KEY `FK_CUSTOM_GRID_GRID_ROLE_ROLE` (`role_id`),
KEY `FK_CUSTOM_GRID_GRID_ROLE_DEFAULT_PROFILE` (`default_profile_id`),
KEY `kkk` (`grid_id`,`grid_role_id`),
CONSTRAINT `FK_CUSTOM_GRID_GRID_ROLE_DEFAULT_PROFILE` FOREIGN KEY (`default_profile_id`) REFERENCES `customgrid_grid_profile` (`profile_id`) ON DELETE SET NULL,
CONSTRAINT `FK_CUSTOM_GRID_GRID_ROLE_GRID` FOREIGN KEY (`grid_id`) REFERENCES `customgrid_grid` (`grid_id`) ON DELETE CASCADE,
CONSTRAINT `FK_CUSTOM_GRID_GRID_ROLE_ROLE` FOREIGN KEY (`role_id`) REFERENCES `admin_role` (`role_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8
mysql> show index from customgrid_grid_role;
+----------------------+------------+------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customgrid_grid_role | 0 | PRIMARY | 1 | grid_role_id | A | 88 | NULL | NULL | | BTREE | | |
| customgrid_grid_role | 1 | FK_CUSTOM_GRID_GRID_ROLE_GRID | 1 | grid_id | A | 6 | NULL | NULL | | BTREE | | |
| customgrid_grid_role | 1 | FK_CUSTOM_GRID_GRID_ROLE_ROLE | 1 | role_id | A | 88 | NULL | NULL | | BTREE | | |
| customgrid_grid_role | 1 | FK_CUSTOM_GRID_GRID_ROLE_DEFAULT_PROFILE | 1 | default_profile_id | A | 2 | NULL | NULL | YES | BTREE | | |
+----------------------+------------+------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+