GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB lock wait timeout

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB lock wait timeout

    Following code cause an undetectable deadlock when run simultaneously. (Lock wait timeout exceeded

    START TRANSACTION;SELECT id into tmp from mf_banners where id=192 FOR UPDATE;UPDATE mf_banners set is_active=is_active where id=192;COMMIT;

    And I can’t understand why.
    Transaction 1 takes X lock for record 192, then Transaction 2 try to take the same lock and can’t, so T2 waits for X lock. Then T1 tries to update record (It holds X lock for that record) and it can’t! Why???

    I have serialized access with GET_LOCK and RELEASE_LOCK and that helped to avoid lock wait timeouts, but i’am not sure that they are replication safe. Are they replication safe, especially when used inside stored procedures?
    Thank you for help.

    Below is
    show innodb status


    ------------TRANSACTIONS------------Trx id counter 0 12717377Purge done for trx's n < 0 12682208 undo n < 0 0History list length 17Total number of lock structs in row lock hash table 2LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 0, not started, OS thread id 564647936MySQL thread id 3697, query id 4767431 localhost remoteshow innodb status---TRANSACTION 0 0, not started, OS thread id 488327168MySQL thread id 3681, query id 4485076 62.118.198.231 remote---TRANSACTION 0 12679021, not started, OS thread id 564648448MySQL thread id 3656, query id 4062663 localhost remote---TRANSACTION 0 12664021, not started, OS thread id 563878400MySQL thread id 3655, query id 3852613 localhost remote---TRANSACTION 0 12717376, ACTIVE 11 sec, OS thread id 568848896 starting indexreadmysql tables in use 16, locked 16LOCK WAIT 2 lock struct(s), heap size 320MySQL thread id 3690, query id 4767429 localhost remote statisticsSELECT id into tmp from mf_banners where id=192 FOR UPDATE------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 720926 n bits 120 index `PRIMARY` of table `banners2/mf_banners` trx id 0 12717376 lock_mode X locks rec but not gap waitingRecord lock, heap no 19 PHYSICAL RECORD: n_fields 60; compact format;---------------------TRANSACTION 0 12717375, ACTIVE 11 sec, OS thread id 563876864mysql tables in use 16, locked 162 lock struct(s), heap size 320MySQL thread id 3691, query id 4767430 localhost remote Table lockupdate mf_banners set is_active=is_active where id=192--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 02313 OS file reads, 152526 OS file writes, 14925 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.30 writes/s, 0.30 fsyncs/s-------------------------------------

  • #2
    Hmm, strange. Can you please also show us SHOW CREATE TABLE, and let us know MySQL version?

    Comment


    • #3
      This problem was in MySQL 5.0.21 and disappeared when we installed 5.0.56.

      CREATE TABLE `mf_banners` (
      `id` int(11) NOT NULL auto_increment,
      `partner_id` int(11) NOT NULL,
      `title` varchar(127) NOT NULL default '???????? ???????',
      `text` varchar(255) character set utf8 NOT NULL,
      `href` varchar(255) character set utf8 NOT NULL,
      `banner_type` tinyint(1) NOT NULL COMMENT '0 - clicks 1 - shows',
      `amount_per_day` double NOT NULL default '0',
      `amount_used_this_day` double NOT NULL default '0',
      `amount_last_update` date NOT NULL,
      `total_used_amount` double NOT NULL default '0',
      `total_amount_limit` double NOT NULL default '0',
      `total_shows` int(11) NOT NULL default '0',
      `total_image_shows` int(11) NOT NULL default '0',
      `total_clicks` int(11) NOT NULL default '0',
      `koef` float NOT NULL default '1',
      `start_day` date NOT NULL,
      `end_day` date NOT NULL,
      `is_active` tinyint(1) NOT NULL default '1',
      `start_hour` smallint(2) NOT NULL default '0',
      `end_hour` smallint(2) NOT NULL default '24',
      `spread_daily_amount` tinyint(1) NOT NULL default '0',
      `phone_brand` char(3) character set utf8 NOT NULL,
      `price` double(5,5) NOT NULL default '0.00000',
      `server` tinyint(2) NOT NULL default '0',
      `approved` tinyint(1) NOT NULL default '1',
      `approved_time` datetime NOT NULL,
      `disapproved_comment` text,
      `shows_block` int(11) NOT NULL default '0',
      `clicks_block` int(11) NOT NULL default '0',
      `speed_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
      `speed_last_shows` int(11) NOT NULL default '0',
      `speed_value` float NOT NULL default '0',
      `tags` varchar(100) NOT NULL,
      `dontblock` tinyint(1) NOT NULL default '0',
      `fresh_flag` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `delete_flag` tinyint(1) NOT NULL default '0',
      `non_adult` tinyint(1) NOT NULL default '0',
      `is_graphic` tinyint(1) NOT NULL default '0',
      `is_autoeconomy` tinyint(1) NOT NULL default '0',
      `max_clicks` int(11) NOT NULL default '0',
      `rnd_group_id` int(11) NOT NULL default '0',
      `date_activated` date NOT NULL,
      `clickamount_per_day` int(11) NOT NULL default '0',
      `clickamount_used_this_day` int(11) NOT NULL default '0',
      `clickamount_last_update` date NOT NULL default '0000-00-00',
      `unique_shows` int(11) NOT NULL default '0',
      `unique_shows_with_cookies` int(11) NOT NULL default '0',
      `unique_clicks` int(11) NOT NULL default '0',
      `wap_card_id` int(11) NOT NULL,
      `last_update` datetime NOT NULL,
      `black_domains` tinytext,
      `credit` tinyint(1) NOT NULL default '0',
      `system_flag` tinyint(1) NOT NULL default '0',
      `minute_div_flag` tinyint(1) NOT NULL default '1',
      `possible_shows` int(11) NOT NULL default '0',
      `very_possible_shows` int(11) NOT NULL default '0',
      `avg_koef_sum` double NOT NULL default '0',
      `avg_koef` double NOT NULL default '0',
      PRIMARY KEY (`id`),
      KEY `partner_id` (`partner_id`),
      KEY `server` (`server`),
      KEY `price` (`price`),
      KEY `is_active` (`is_active`)
      ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |

      Comment

      Working...
      X