DEADLOCK MYSQL 5.1.37

DEADLOCK MYSQL 5.1.37

am 14.09.2009 09:08:25 von Krishna Chandra Prajapati

--001636e1f972f68b6804738454aa
Content-Type: text/plain; charset=ISO-8859-1

Hi guys,

I am getting very frequent deadlock in mysql 5.1.37. I am not able to
understand why this below deadlock is coming.

*** (1) TRANSACTION:
TRANSACTION 0 1036157191, ACTIVE 0 sec, process no 4101, OS thread id
1908412736 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s)
MySQL thread id 746738, query id 515198930 127.0.0.1 smmanager updating
DELETE FROM alt_send_sms WHERE sql_id IN ( 24878942, 24878943, 24878945,
24878947, 24878949, 24878944, 24878946, 24878951, 24878953, 24878948,
24878950, 24878954, 24878955, 24878956, 24878957, 24878958, 24878959,
24878960, 24878961, 24878962, 24878963, 24878965, 24878966, 24878967,
24878968, 24878969, 24878970, 24878971, 24878972, 24878973, 24878974,
24878975, 24878976, 24878977, 24878978, 24878979, 24878980, 24878981,
24878982, 24878983, 24878985, 0 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 182 page no 5 n bits 96 index `PRIMARY` of table
`smmanager`.`alt_send_sms` trx id 0 1036157191 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl ;; 1: len 1; hex 06; asc ;; 2: len 8; hex
80000000017b9f68; asc { h;; 3: len 6; hex 00003dc28106; asc = ;; 4:
len 7; hex 000000002d0110; asc - ;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973 ; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f676574444c5231 ; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc 1157;;

*** (2) TRANSACTION:
TRANSACTION 0 1036157190, ACTIVE 0 sec, process no 4101, OS thread id
1272312128 starting index read, thread declared inside InnoDB 495
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 7 row lock(s), undo log entries 2
MySQL thread id 746823, query id 515198929 127.0.0.1 smmanager updating
DELETE FROM alt_send_sms WHERE sql_id IN ( 24878984, 24878952, 24878964,
0 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 182 page no 5 n bits 96 index `PRIMARY` of table
`smmanager`.`alt_send_sms` trx id 0 1036157190 lock_mode X locks rec but not
gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl ;; 1: len 1; hex 06; asc ;; 2: len 8; hex
80000000017b9f68; asc { h;; 3: len 6; hex 00003dc28106; asc = ;; 4:
len 7; hex 000000002d0110; asc - ;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973 ; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f676574444c5231 ; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc 1157;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 32; compact format; info
bits 32
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl ;; 1: len 1; hex 06; asc ;; 2: len 8; hex
80000000017b9f74; asc { t;; 3: len 6; hex 00003dc28106; asc = ;; 4:
len 7; hex 000000002d016b; asc - k;; 5: len 1; hex 02; asc ;; 6: len 8;
hex 4d54456463617265; asc MTEdcare;; 7: len 12; hex
393139343232393837373238; asc 919422987728;; 8: SQL NULL; 9: len 30; hex
446561722053747564656e74732048696e64692d35302054657374206973 ; asc Dear
Students Hindi-50 Test is;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f676574444c5231 ; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 82; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31313537; asc 1157;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 182 page no 5 n bits 96 index `PRIMARY` of table
`smmanager`.`alt_send_sms` trx id 0 1036157190 lock_mode X locks rec but not
gap waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 32; compact format; info
bits 0
0: len 20; hex 6d746e6c20202020202020202020202020202020; asc
mtnl ;; 1: len 1; hex 04; asc ;; 2: len 8; hex
80000000017b9f88; asc { ;; 3: len 6; hex 00003dc280ff; asc = ;; 4:
len 7; hex 80000000330110; asc 3 ;; 5: len 1; hex 02; asc ;; 6: len 6;
hex 495220534d53; asc IR SMS;; 7: len 12; hex 393139343130313030353130; asc
919410100510;; 8: SQL NULL; 9: len 30; hex
566572696669636174696f6e20636f646520666f7220796f7572204d6f62 ; asc
Verification code for your Mob;...(truncated); 10: SQL NULL; 11: len 5; hex
6170693131; asc api11;; 12: SQL NULL; 13: SQL NULL; 14: len 1; hex 02; asc
;; 15: SQL NULL; 16: SQL NULL; 17: len 1; hex 00; asc ;; 18: SQL NULL; 19:
SQL NULL; 20: SQL NULL; 21: len 1; hex 13; asc ;; 22: len 30; hex
687474703a2f2f382e362e39352e3136382f70736d732f676574444c5231 ; asc
;...(truncated); 23: SQL NULL; 24: SQL NULL; 25: SQL NULL; 26: SQL NULL; 27:
SQL NULL; 28: SQL NULL; 29: len 1; hex 81; asc ;; 30: len 4; hex 4aad025c;
asc J \;; 31: len 4; hex 31323334; asc 1234;;

*** WE ROLL BACK TRANSACTION (1)

The table structure is below.

CREATE TABLE `alt_send_sms` (
`sql_id` bigint(15) NOT NULL AUTO_INCREMENT,
`momt` enum('MO','MT','DLR') DEFAULT NULL,
`sender` varchar(20) DEFAULT NULL,
`receiver` varchar(20) DEFAULT NULL,
`udhdata` tinyblob,
`msgdata` varchar(1024) DEFAULT NULL,
`time` int(8) unsigned DEFAULT NULL,
`smsc_id` char(20) NOT NULL DEFAULT '',
`service` varchar(64) DEFAULT NULL,
`account` varchar(255) DEFAULT NULL,
`id` int(10) unsigned DEFAULT NULL,
`sms_type` tinyint(3) unsigned DEFAULT NULL,
`mclass` tinyint(3) unsigned DEFAULT NULL,
`mwi` tinyint(3) unsigned DEFAULT NULL,
`coding` tinyint(3) unsigned DEFAULT NULL,
`compress` tinyint(3) unsigned DEFAULT NULL,
`validity` int(6) unsigned DEFAULT NULL,
`deferred` int(6) unsigned DEFAULT NULL,
`dlr_mask` tinyint(3) unsigned DEFAULT NULL,
`dlr_url` varchar(512) DEFAULT NULL,
`pid` tinyint(3) unsigned DEFAULT NULL,
`alt_dcs` tinyint(3) unsigned DEFAULT NULL,
`rpi` int(6) unsigned DEFAULT NULL,
`charset` varchar(32) DEFAULT NULL,
`boxc_id` varchar(32) DEFAULT NULL,
`binfo` varchar(32) DEFAULT NULL,
`priority` tinyint(3) DEFAULT NULL,
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`sms_user` varchar(22) DEFAULT NULL,
`msg_priority` tinyint(2) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`smsc_id`,`msg_priority`,`sql_id`),
UNIQUE KEY `sql_id` (`sql_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3168162 DEFAULT CHARSET=latin1

Thanks,
Krishna

--001636e1f972f68b6804738454aa--