I have a table which keeps getting updated. Every 30 seconds I read the values from these table to process something and then delete it. All is good, though I am worried that between reading and deleting, new rows might get inserted, which means I havent read them yet, but it will be deleted. I was thinking of putting a lock of this table for the time i read it and delete the entry, but I see people have advised against it. Also, I need to make sure, if I lock it, the items to be inserted remains in cache somehow and gets inserted when lock is gone. I am not sure what is the best practice and how to do it.

CodePudding user response：

If you are using an auto_increment id field, you can get the last row id you read and delete only those with id <= $read_id