INSERT and UPDATE the same row in the same TRANSACTION?
So here's my problem:
I have an article submission form with an optional image upload field.
The submission uses three steps:
1: Transaction starts
2: $_POST array is processed, new row added - id generated by UUID()
3: $_FILES array is processed, image file resized and saved with the name of the inserted row and the appropriate image extension. If this fails - then the transaction is rolled back, and the function returns false.
4: The original row is updated with the name of the image. If this fails, the transaction is rolled back.
I've designated a composite UNIQUE key ('blog_title','post_url')
However, I'm getting a DUPLICATE exception on the new ID generated in step 1.
Is this an inappropriate use of transactions?
OK - just for the record, this is entirely possible. The problem was in my application architecture. I was catching Exceptions in my Mapper classes that were handling persistence - and then querying them to return boolean states and thus interrupt the process. This was in turn breaking the try/catch loop which was preventing the insert/update from working correctly. To summarise - Yes - you CAN insert and update the same row in a single transaction.