I have a record that includes URLs to several BLOBs that are tied to that record. So I would like to have a single transaction that wraps the 3 BLOB creations and the DB record insertions.
This is all on Azure - SQL Database and Azure BLOB Storage.
Is there a way to wrap all this in a single transaction? And if so, does the transaction have minimal performance impact if there are no problems?
Is there a way to wrap all this in a single transaction?
No, these two operations cannot be put in a single transaction as they are two separate services.
What you would need to do is handle the failure scenarios in your application code. A few things you could do:
- Use retry mechanism to deal with transient errors.
- Make your code idempotent so that running the code multiple times would produce the same output.
- Implement your own rollback mechanism. For example, if you are uploading blobs first and then inserting the data in SQL DB (which fails), manual rollback mechanism would be to delete the blobs (please keep in mind that this again can fail) if SQL operation fails.
- Devise a strategy to deal with orphan data. Again, if you are uploading blobs first and then inserting data in SQL DB (which fails), you would need to identify the blobs that are not associated with any record in SQL DB and delete them periodically through another process.