I use MySQL 5.7 and want to run an export task on a products table. The process basically runs like this:
SELECT * FROM products WHERE exported = 0
- Loop over the result set in my programm and write it to a file
UPDATE products SET exported = 1 WHERE exported = 0
So each product should only be exported once.
I found that I have to wrap the above in a transaction with isolation level
SERIALIZABLE because I have another process that inserts records at the same time.
Otherwhise that other process could insert rows with
exported=0 while I'm in step 2) which would be missing in the file but get updated to
exported=1 in step 3).
Is this the best way to solve this kind of problems? Or are there any drawbacks or pitfalls I'm no aware of?