UPDATE waits for lock too long after SELECT in MySQL (Innodb) I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen (int(1)). Seen column is indexed.

My pseudo code:

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;


And when client requests 10+ pictures in parallel (for different Id) then some UPDATE have unexpectedly hung for 1-10 seconds. I see Innodb_row_lock_waits is increased every time in this case.

Without UPDATE my code works very fast all time.

I've tried use transaction, UPDATE DELAYED (I'm not sure that I tried it correctly).

Are there some best practices for selecting and updating same rows? What additional information should I provide to clarify the question?

UPDATE 1 Server Configuration

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41


UPDATE 2

I've changed the SQL code to this but situation did't change. Also I removed INDEX for 'seen' column without any effects to this code.

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;


In mysql-slow.log I see next entries:

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;


UPDATE 3

Amount of rows in pics table are about 70.

SHOW CREATE TABLE pics;

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8


SELECT COUNT(1) RowsReturned FROM pics WHERE id=315; (It took about 0.000035s)

RowsReturned 1
Perhaps you should impose a SELECT FOR UPDATE

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;


I have mentioned this before


Mar 08, 2013 : How are DB locks tied to connections and sessions?
Mar 12, 2013 : How I prevent deadlock occurrence in my application? (See MECHANISM #3)


UPDATE 2015-02-05 22:22 EST

There will always be row locks imposed, and there is no getting around it. Yet what is the difference between your code and my code ?

Think about it. Your running the SELECT in your code does not shield the rows from changes. Those row locks you are seeing would happen during the UPDATE. My code imposes the locks on rows and all corresponding index entries during the SELECT. This makes the UPDATE have less stress.

This is in agreement with the MySQL Documentation


  A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.


UPDATE 2015-02-10 22:05 EST

Your issue seems to be pointed out already by @RickJames: the index on seen field. 

Take a look at the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)



Please note two structures from the Picture


Insert Buffer With the InnoDB Buffer Pool (IB_IBP)
Insert Buffer With the System Tablespace (better known as ibdata1) (IB_SYSTBLSPC)


These structures are responsible for updating nonunique indexes.

Now, please note the effect of setting seen=1 on id = 315.

Assuming seen is either 0 or 1 (Cardinality of 2)


Having an index on seen with just 0 and 1 as values makes the index no different than managing two linked lists, each ordered by the PRIMARY KEY id.
Changing a 0 to 1 (or 1 to 0) will place information into IB_IBP
When it time to write the change to pics.ibd


Copy of the Changed Data Page is Written to the Double Buffer
Information Needed for Index Positioning is passed from IB_IBP into IB_SYSTBLSPC

The index entry of id 315 is moved from the side of the index where seen=0 to the other side of the index where seen=1


If you are changing the archive column in your benchmarking, these same machinations would also apply to the archive index.

In addition to the indexes being churned into and out of the InnoDB Buffer Pool, don't forget where this question started: ALL THE ROW LOCKS.

Keep something in minds about row locks. When a row lock is issued, an entire page can also be locked. The page can be the location of multiple primary key values. I have another posts where I discussed page locks (Dec 31,2012 : Queries getting stuck on very simple COUNT queries)

Please note in your comment, that you said


  And what is more important that in the most cases it works fast


Why not all cases ? Because of the reasons I just mentioned. @RickJames did mention first the seen index, and he is right to suggest removing that index. Along those same lines, I suggest removing the archive index as well (especially if the archive value is being changed in the benchmark). Without removing those indexes, look at all the stress you are imposing on the InnoDB Infrastructure even though the table has 70 rows. It would be far worse with thousands or millions of rows.