Background Information

I have a web app that provides the user with the usual CRUD functionality for "widgets" let's say. But I also have give them the option of creating widgets in bulk via importing csv files. The logic I have right now works, but the scope of the app just grew. In addition to writing the records to the main db, I have now been asked to write widget data to 4 other databases used by a different application. (the 4 databases all belong to one business app, which doesn't have to be separate, but I cannot change it at this point in time).

Code

The code I'm using right now looks something like this (pseudocode):

open file
loop through all records in csv file
for each record 
       connect to main db
       insert into main db
        for each widget_manager app server
           connect 
           insert
           disconnect

           if at any time there is a failure with any insert
                rollback all inserts. 
           end
         end
        disconnect main db
 end

Problem

Currently, depending on the size of the csv file, while the import is running the database is locked. I can't select anything or use other pages in the app.

Question / Goals

I have three goals in mind:

  1. prevent locking if at all possible.
  2. provide real time feedback to the end user to show that something is happening... can i do this for each record as its being added? right now, i use an Ajax call but of course, the results only show up at the end of the for loop, when all records have been accounted for. I do have an animated gif that runs for the duration of the import, which is helpful. But it'd be cool if I could show the records being added somehow. I'm currently reviewing the following post: https://stackoverflow.com/questions/28172863/show-a-modal-window-with-a-progress-during-ajax-call

to see how things would work if I drove the import logic on the client side... So far, I'm not sure of what the benefits / risks would be.

  1. simplify the code by breaking it down into multiple functions. So for example, instead of just having one button called "import" that does it all, have two buttons. One called "import to main db" and the other "import to widget manager" or something. The later is what will update the 4 new databases that i have to write to. Note: it's OK if the record exists in the main app, but the insert to the widget manager fails. I just need to make a note of it in logs. Again, another bad business requirement, but I have no control over that.
  2. The other idea I had was to programmatically limit the file size to "n" number of records. But I'm not sure if this will annoy the end users or not.

If you see a problem with the approach I'm considering, please let me know. Also if you have any ideas on how I can provide real time feedback... that'd be great.

   open file
    loop through all records in csv file
    for each record 
           connect to main db
           START TX on main db *****
           insert into main db
            for each widget_manager app server
               connect 
               insert
               disconnect

               if at any time there is a failure with any insert
                    rollback all inserts. 
               end
             end
            END TX on main db *****
            disconnect main db
     end
  • 3
    You might try parsing the csv file and loading it into a second table (perhaps a memory table), and then do a single bulk insert statement into the final table there. That would at least cut out the csv processing time. Also, make sure you are doing the whole thing within a single transactions - that seems to make a big difference. – GrandmasterB May 6 '15 at 21:44
  • 5
    This is probably a good time to move to a different database like PostGres, which will support these kinds of operations without locking the entire database. – Robert Harvey May 7 '15 at 1:30
  • @GrandmasterB so the transaction should start with the insert into the main database record, wrap all the other 4 inserts and commit tx to main database only after all the other 4 inserts worked, right? Just want to make sure I understand clearly. Please see edit 2 – dot May 7 '15 at 12:23
  • @RobertHarvey yes, I think that's a good suggestion. that's on the long term to do list. – dot May 7 '15 at 13:17

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.