mardi 3 février 2015

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?





Aucun commentaire:

Enregistrer un commentaire