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

**URL:** <https://forums.percona.com/t/dedlocks-between-update-limit-n-and-update-primary-key/1924>\
**Category:** Other MySQL® Questions\
**Created:** [November 6, 2012, 7:40am UTC](https://forums.percona.com/t/dedlocks-between-update-limit-n-and-update-primary-key/1924 "2012-11-06T07:40:00Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![boaa](https://avatars.discourse-cdn.com/v4/letter/b/8dc957/32.png) [@boaa](https://forums.percona.com/u/boaa)\
**Post date:** [November 6, 2012, 7:40am UTC](https://forums.percona.com/t/dedlocks-between-update-limit-n-and-update-primary-key/1924/1 "2012-11-06T07:40:00Z")

</div>

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_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.

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;

1. 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