none
How to do Azure Blob storage and Azure SQL Db atomic transaction

    Question

  • We have a Blob storage container in Azure for uploading application specific documents and we have Azure Sql Db where meta data for particular files are saved during the file upload process. This upload process needs to be consistent so that we should not have files in the storage for which there is no record of meta data in Sql Db and vice versa. We are uploading list of files which we get from front-end as multi-part HttpContent. From Web Api controller we call the upload service passing the httpContent, file names and a folder path where the files will be uploaded.

    The Web Api controller, service method, repository, all are asyn.

    var files = await this.uploadService.UploadFiles(httpContent, fileNames, pathName);

    Here is the service method:

    public async Task<List<FileUploadModel>> UploadFiles(HttpContent httpContent, List<string> fileNames, string folderPath)
            {
                var blobUploadProvider = this.Container.Resolve<UploadProvider>(
                    new DependencyOverride<UploadProviderModel>(new UploadProviderModel(fileNames, folderPath)));
    
                var list = await httpContent.ReadAsMultipartAsync(blobUploadProvider).ContinueWith(
                    task =>
                    {
                        if (task.IsFaulted || task.IsCanceled)
                        {
                            throw task.Exception;
                        }
    
                        var provider = task.Result;
                        return provider.Uploads.ToList();
                    });
    
                return list;
            }



    The service method uses a customized upload provider which is derived from System.Net.Http.MultipartFileStreamProvider and we resolve this using a dependency resolver. After this, we create the meta deta models for each of those files and then save in the Db using Entity framework. The full process works fine in ideal situation.

    The problem is if the upload process is successful but somehow the Db operation fails, then we have files uploaded in Blob storage but there is no corresponding entry in Sql Db, and thus there is data inconsistency.
    Following are the different technologies used in the system:

    Azure Api App
    Azure Blob Storage
    Web Api
    .Net 4.6.1
    Entity framework 6.1.3
    Azure MSSql Database (we are not using any VM)

    I have tried using TransactionScope for consistency which seems not working for Blob and Db, (works for Db only)

    How do we solve this issue?
    Is there any built in or supported feature for this?
    What are the best practices in this case?


    • Edited by mamunreza Friday, November 25, 2016 12:22 PM
    Friday, November 25, 2016 12:21 PM

Answers

  • Hi,

     

    Thank you for your time and patience!

     

    Presently there is no, atomic transaction for Azure Blob Storage.  However, they could get the effect on the following process:

     

    1)   Add a “Blob in Process” record to their database (probably in a separate table) that records the blob metadata and a timestamp.

    2)   Write the blob.  Use a unique name that will never be re-used.

    3)   In a single SQL DB transaction, delete the Blob in Process record and update the permanent metadata for the blob. 

     

    Separately:

    4)   Periodically scan the Blob in Process table for records that have been there too long (however long that is).

    5)   Delete those blobs (if they exist).

    6)   Delete the Blob in Process record.

     

    This is effectively what atomic database transactions do: first post all the data to temporary locations, then in a single action, they flip from temporary to permanent.  In this case, the blob doesn’t move, but it’s status changes from temporary to permanent.

     

    Regards,
    Sumanth BM


    Thursday, December 8, 2016 6:05 PM
    Moderator

All replies

  • Hello,

    We are checking on the query and would get back to you soon on this.I apologize for the inconvenience and appreciate your time and patience in this matter.


    Regards,
    Sumanth BM

    Friday, November 25, 2016 7:02 PM
    Moderator
  • Hi,

     

    Thank you for your time and patience!

     

    Presently there is no, atomic transaction for Azure Blob Storage.  However, they could get the effect on the following process:

     

    1)   Add a “Blob in Process” record to their database (probably in a separate table) that records the blob metadata and a timestamp.

    2)   Write the blob.  Use a unique name that will never be re-used.

    3)   In a single SQL DB transaction, delete the Blob in Process record and update the permanent metadata for the blob. 

     

    Separately:

    4)   Periodically scan the Blob in Process table for records that have been there too long (however long that is).

    5)   Delete those blobs (if they exist).

    6)   Delete the Blob in Process record.

     

    This is effectively what atomic database transactions do: first post all the data to temporary locations, then in a single action, they flip from temporary to permanent.  In this case, the blob doesn’t move, but it’s status changes from temporary to permanent.

     

    Regards,
    Sumanth BM


    Thursday, December 8, 2016 6:05 PM
    Moderator
  • Hi,

    Thanks for your reply. I'll try to implement the way you suggested.

    Regards,

    Mamun Reza

    Monday, December 12, 2016 5:49 AM