File locking…

I am building a file upload package (or actually it has been built and I am expanding it). The file should be uploaded by a third party and when it’s done, then this code should kick off and upload the file to a clob for further processing.

The problem is that the third party can still be in the process of uploading the file while the package is trying to read it to upload it into a clob. How can you be sure the file is complete before you start reading it?

One option is to check for the existance of a different file. If this one exists, then the third party is still uploading. Another option is to try to rename the file. If this fails, then the third party is still uploading.

The idea I came up with, is to try to open the file for appending, effectively locking the file. If this works out, then close the file right away and execute the normal processing. If there is an error of some sort, then just try again. To make sure I don’t end up in an endless loop, I decided to exit my check loop, when 2 minutes or more have passed. (They must be using a really slow connection when this is the case, something like an ancient 1200baud modem 😉 )

   1: ...
   2: l_StartTime := CurrentTimeInSeconds;
   3: l_CurrentTime := CurrentTimeInSeconds;
   4: -- 0. Try to acquire a lock on the file for max 2 minutes
   5: -- locking is done by trying to append to the file.
   6: while l_lock and ((l_CurrentTime - l_StartTime) <= 120) loop
   7:   begin
   8:     l_CurrentTime := CurrentTimeInSeconds;
   9:     l_Handle := utl_file.fopen( location => r_file_definitions.directory_object
  10:                               , filename => l_dir_list(i)
  11:                               , open_mode => 'A'
  12:                               );
  13:     l_lock := false;
  14:   exception
  15:     when utl_file.file_open then l_lock := true;
  16:     when utl_file.invalid_path then l_lock := true;
  17:     when utl_file.invalid_mode then l_lock := true;
  18:     when utl_file.invalid_filehandle then l_lock := true;
  19:     when utl_file.invalid_operation then l_lock := true;
  20:     when utl_file.read_error then l_lock := true;
  21:     when utl_file.write_error then l_lock := true;
  22:     when utl_file.internal_error then l_lock := true;
  23:     when utl_file.invalid_maxlinesize then l_lock := true;
  24:     when utl_file.invalid_filename then l_lock := true;
  25:     when utl_file.access_denied then l_lock := true;
  26:     when utl_file.invalid_offset then l_lock := true;
  27:     when utl_file.delete_failed then l_lock := true;
  28:     when utl_file.rename_failed then l_lock := true;
  29:     when others then l_lock := true;
  30:   end;
  31: end loop; 
  32: if not(l_lock) then
  33: -- 0b. Release the file if it has been locked
  34:   utl_file.fclose(file => l_Handle);
  35: ...

Of course, this is not ‘waterproof’ but it seems to get the job done. The l_lock variable is declared as a boolean and is initialized to true before the loop starts.

I think I don’t need this many exceptions, but during development I decided to handle all exceptions separately so I had an easy template to do some logging for which I created a separate log package. When I had it working, I removed the log messages.

3 thoughts on “File locking…

  1. An interesting, real life challenge.
    I think your approach behaves differently on different operating systems, so it is not portable (but that may be fine to you). What OS is the Oracle DBMS running on?
    As you described, a lock file is a good way to deal with this, or the last step of the incoming process could be to rename the file from a temporary name to the agreed name after completing the file creation process, assuming that the rename is more or less instantly (FTP supports a rename step).
    A whole other approach is to use a signal/message, e.g. using dbms_alert, where your package instance listens for an Oracle Alert and the writer sends that alert on completing the writing process, by logging into the database using e.g. SQL*Plus. But that may be not possible and/or not desirable. And there are many more like this, e.g. HTTP put, SOAP, JMS/AQ. The advantage of these is that the protocol deals with the ‘transaction-end’ of the ‘upload’. Oracle DBMS supports all of these.


  2. Hi Erik,

    A lock file would mean that the other party (the one sending the file) would have to put it there and remove it when they are done uploading. That was not an option. Also renaming the file should be done by the other party. Not an option too :-(. Having the sender send an alert is not possible either because the other party doesn’t have access to any code on the Oracle database. But they are definitely good ideas, of which the Alert one is one I hadn’t considered yet. Thanks for your reaction and ideas.



Leave a Reply to Erik Ykema Cancel reply

Your email address will not be published. Required fields are marked *