Is there an alternative to isolation level SERIALIZABLE? 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?
Give this a try:

BEGIN;
SELECT * FROM products WHERE exported = 0 INTO OUTFILE "x/y/z" FOR UPDATE;
UPDATE products SET exported = 1 WHERE exported = 0;
COMMIT;


And I don't think it matters what the isolation mode is.

I am not sure of mixing INTO OUTPUT with FOR UPDATE; if it give you trouble, make it two SELECTs.

Note that this does obviate your program with the loop and write.  (And it will probably run faster.)

Another approach may be to ping-pong 2 tables.  One is receiving new rows; the other is being exported.  No need to have the exported flag.  More details:  http://mysql.rjweb.org/doc.php/staging_table (especially the section on flip-flop staging).