MySQL Forums
Forum List  »  InnoDB

Deadlock leading to huge number of active connections
Posted by: Christer Nordvik
Date: October 17, 2014 02:37PM

We are running MySQL 5.5 on Google Cloud SQL and are having lots of issues with deadlocks leading to a huge number of active connections (1000+). Not sure how to solve this as every place I have read they just say to retry the operation, but the number of operations leads to the entire DB getting unavailable so that's not really an option for us.

We tried changing the innodb_lock_wait_timeout to 10 seconds and that seemed to at least help a little since it managed to remove the connections before it spiked again. Earlier we spiked at max and everything came to a halt.

The two SQLs we run are:
delete from user_notifications where pushid=? and external_id is null
delete from user_notifications where deviceid=? and external_id is null

The table structure is below


CREATE TABLE `user_notifications` (
`deviceid` varchar(500) NOT NULL,
`pushid` varchar(200) DEFAULT NULL,
`tag` varchar(100) DEFAULT NULL,
`external_id` varchar(80) DEFAULT NULL,
KEY `index1` (`tag`),
KEY `index3` (`deviceid`(255)),
KEY `index4` (`external_id`),
KEY `tag_and_push` (`tag`,`pushid`),
KEY `tag_externalid_and_push` (`tag`,`external_id`,`pushid`),
KEY `index_delete_helper` (`pushid`,`external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$


and the deadlock message is as follows:

*** (1) TRANSACTION:
TRANSACTION FC9F24, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 1672, OS thread handle 0x7fef14143700, query id 12953 localhost root updating
delete from user_notifications where pushid='APA91bGpdxV33vtJPl18czrDxVZl-52X4GQRpjFChk_wKdAr4eEI--1mcbVFiTyo5-ufAxzdjh36HcmmiGiI0QPE' and external_id is null
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 206486 n bits 128 index `GEN_CLUST_INDEX` of table `fotmobdb`.`user_notifications` trx id FC9F24 lock_mode X locks rec but not gap waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
0: len 6; hex 0000004805cc; asc H ;;
1: len 6; hex 000000fc9f23; asc #;;
2: len 7; hex 7b000fc00f2e6d; asc { .m;;
3: len 30; hex 36343034346563612d373136612d343931352d396435382d343265333261; asc 64044eca-716a-4915-9d58-42e32a; (total 36 bytes);
4: len 30; hex 4150413931624770647856333376744a506c3138637a724478565a6c2d35; asc APA91bGpdxV33vtJPl18czrDxVZl-5; (total 183 bytes);
5: len 9; hex 7465616d5f38363334; asc team_8634;;
6: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION FC9F23, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 1673, OS thread handle 0x7fef14a67700, query id 12955 localhost root updating
delete from user_notifications where deviceid='64044eca-716a-4915-9d58-42e32ab73f80' and external_id is null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 206486 n bits 128 index `GEN_CLUST_INDEX` of table `fotmobdb`.`user_notifications` trx id FC9F23 lock_mode X locks rec but not gap
Record lock, heap no 58 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
0: len 6; hex 0000004805cc; asc H ;;
1: len 6; hex 000000fc9f23; asc #;;
2: len 7; hex 7b000fc00f2e6d; asc { .m;;
3: len 30; hex 36343034346563612d373136612d343931352d396435382d343265333261; asc 64044eca-716a-4915-9d58-42e32a; (total 36 bytes);
4: len 30; hex 4150413931624770647856333376744a506c3138637a724478565a6c2d35; asc APA91bGpdxV33vtJPl18czrDxVZl-5; (total 183 bytes);
5: len 9; hex 7465616d5f38363334; asc team_8634;;
6: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 943901 n bits 152 index `index_delete_helper` of table `fotmobdb`.`user_notifications` trx id FC9F23 lock_mode X locks rec but not gap waiting
Record lock, heap no 57 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 4150413931624770647856333376744a506c3138637a724478565a6c2d35; asc APA91bGpdxV33vtJPl18czrDxVZl-5; (total 183 bytes);
1: SQL NULL;
2: len 6; hex 0000004805cc; asc H ;;

*** WE ROLL BACK TRANSACTION (1)
------------


Any help would be greatly appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock leading to huge number of active connections
2210
October 17, 2014 02:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.