For a query similar to this:

UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1;

I found that innodb engine locks ALL rows matching criteria message="match msg", update single row and unlock all rows again.

Is there anyway to minimize number of locked rows to the required locked rows? The problem with this lock is that it takes long time and sometimes give me SQL deadlocks (with other update queries) .


1) Table

CREATE TABLE `myinnotable1` (
  `id` int(11) NOT NULL,
  `value` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `message` varchar(20) DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  KEY `index2` (`status`,`message`,`updated`)

2) Table has millions of rows (~10M), with status>10 && message="match msg" matches around 10K rows.

3) All rows matching status and message are equal, I want to update any one in them (that is why I don't use order by).

4) What I want to do is: - I have many worker threads, which marks a row in the table (update), select the row (select) and update it after processing (update). This is done until all rows matching status>10 && message="match msg" ends.

  • Please run SHOW CREATE TABLE myinnodbtable1\G and show the current table structure. – RolandoMySQLDBA Jun 20 '15 at 0:51
  • 1
    How many rows in the table ??? How many unique values are in field1 ? – RolandoMySQLDBA Jun 20 '15 at 0:56
  • LIMIT should almost always be preceded by ORDER BY xx. Otherwise who knows which row will be picked ! – Rick James Jun 20 '15 at 1:21
  • How did you "find" it? InnoDB actually locks index entries afaik, so locking depends on available/used indexes. And - did you expect two such queries to work concurrently? Like first query locks something and second one "skips" whats locked and picks first "free" ? thats not how it works - with the same criteria they pick the same row(s) and the second just has to wait, no skipping here. – jkavalik Jun 20 '15 at 5:52
  • @user1786423 I found it from a deadlock message. The update query tries to lock ALL rows matching the criteria (although I just want one row). I thought that mysql will lock only 1 row matching the criteria (not all of them). – ItsMe Jun 20 '15 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.