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) .
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`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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.