Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Unanswered: dedlocks between "update limit N" and "update by primary key"

    Hello!
    I am little confused by deadlocks and i need in advice

    Explaining:
    There is one table:
    Code:
    CREATE TABLE `Domain_Site_Sync` (
      `id_domain_site` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id_site_task` bigint(20) unsigned NOT NULL,
      `datetime_check` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `state` tinyint(4) NOT NULL DEFAULT '0',
      `lock` tinyint(4) NOT NULL DEFAULT '0',
      `datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `lock_domain` tinyint(4) unsigned NOT NULL DEFAULT '0',
      `datetime_lock_domain` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `block` tinyint(3) NOT NULL DEFAULT '0',
      `sign` bigint(20) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`id_domain_site`),
      KEY `datetime_check` (`lock`,`state`,`block`,`lock_domain`,`datetime_check`,`id_domain_site`),
      KEY `sign` (`sign`),
      KEY `lock_domain` (`lock_domain`,`datetime_lock_domain`),
      KEY `lock` (`lock`,`datetime_processed`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    and i do two update operation on it.

    1. get any 10 rows suitable by "where" conditions. Set dss.sign = _var_sign.
    _var_sign is UUID_SHORT().
    This is needed to know what rows were got, and make possible to work with it.

    Code:
    update  `Domain_Site_Sync` as dss
    force key(datetime_check)
    set 
        dss.`lock` = 1, 
        dss.datetime_processed = _var_datetime_now,
        dss.sign = _var_sign
    where 
        dss.`lock` = 0 and 
        dss.`lock_domain` = 0 and
        dss.`block` = 0 and    
        dss.state = 1 and 
        dss.datetime_check <= now()
    limit 10;
    2. Release row in Domain_Site_Sync by having _var_id_domain_site.
    This ensures that the row with _var_id_domain_site cannot be fetched in section 1 (see above), because it is being excluded logically when we set dss.`lock` = 1
    Code:
    update `Domain_Site_Sync` as dss 
    set
        dss.id_site_task = _var_id_site,
        dss.datetime_check = _var_datetime_check,
        dss.state = _var_state_domain_sync,
        dss.lock = 0
    where dss.id_domain_site = _var_id_domain_site;
    And periodically there are deadlocks
    In innotop utility i see
    Code:
    ________________________________________________________ Deadlock Transactions ________________________________________________________
    ID   Timestring           User  Host  Victim  Time   Undo  LStrcts  Query Text
    236  2012-11-06 12:02:49  root  172   No      00:00     0        9  update `Domain_Site_Sync` as dss force key(datetime_check) set dss.
    240  2012-11-06 12:02:49  root  172   Yes     00:00     1        4  update `Domain_Site_Sync` as dss set dss.id_site_task = NAME_CONST(
    
    ___________________________________ Deadlock Locks ___________________________________
    ID   Waiting  Mode  DB   Table             Index           Special          Ins Intent
    236        0  X     rts  Domain_Site_Sync  datetime_check  rec but not gap           0
    236        1  X     rts  Domain_Site_Sync  datetime_check  rec but not gap           0
    240        1  X     rts  Domain_Site_Sync  datetime_check  rec but not gap           0
    Isolation level that i use - read-committed

    Why dreadlocks are there?
    By my mind, updates use different indexes, dont use one primarykey id, and logically all rows, that fetched in section 1, cannot be fetched until they won't be released in section 2
    Attached Files Attached Files
    Last edited by boa86; 11-06-12 at 09:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •