I am little confused by deadlocks and i need in advice
There is one table:
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 (
datetime_processed)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and i do two update operation on it.
- 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.
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;
- 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.
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