Announcement

Announcement Module
Collapse
No announcement yet.

dedlocks between "update limit N" and "update ?? primary key"

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

  • dedlocks between "update limit N" and "update ?? primary key"

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

    Explaining:
    There is one table:

    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_ch eck`,`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.


    update `Domain_Site_Sync` as dssforce key(datetime_check)set dss.`lock` = 1, dss.datetime_processed = _var_datetime_now, dss.sign = _var_signwhere 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

    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 = 0where dss.id_domain_site = _var_id_domain_site;


    And periodically there are deadlocks
    In innotop utility i see
    __________________________________________________ ______ Deadlock Transactions __________________________________________________ ______ID Timestring User Host Victim Time Undo LStrcts Query Text236 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 Intent236 0 X rts Domain_Site_Sync datetime_check rec but not gap 0236 1 X rts Domain_Site_Sync datetime_check rec but not gap 0240 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

    ps: attached INNODB MONITOR OUTPUT
Working...
X