List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:September 14 2009 7:08am
Subject:DEADLOCK MYSQL 5.1.37
View as plain text  
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

Thread
DEADLOCK MYSQL 5.1.37Krishna Chandra Prajapati14 Sep