0

I use MySQL 5.7 and want to run an export task on a products table. The process basically runs like this:

  1. SELECT * FROM products WHERE exported = 0
  2. Loop over the result set in my programm and write it to a file
  3. 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?

1

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

  • Thanks. Is FOR UPDATE preferable to SERIALIZABLE? I thougth FOR UPDATE only locks the selected rows and protects them from modification. The docs don't mention that it will also prevent concurrent inserts of new rows. Even if it works in a test: I want to be confident about the solution and not rely on try & error (that's what brought me here ;) ). Regarding INTO OUTFILE: Yes, that would speed things up but it's not applicable in my case as I need to postprocess the data before writing. The example is really simplified. – Michael Härtl Nov 8 '19 at 7:23
  • @MichaelHärtl - I can't answer your question without devising a test. Meanwhile, I added another approach. – Rick James Nov 8 '19 at 21:37
  • Actually I ended up with a similar approach to the ping pong table: The export rows are inserted with INSERT INTO ... SELECT ... into a temporary table first and exported from there. Afterwards I update the exported flag (which we need) with an INNER JOIN on this temporary table and finally drop it. I just found the original setup to be an interesting use case to study the effect of isolation levels and locking and wanted a better understanding. – Michael Härtl Nov 9 '19 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.