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
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;
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)