How to minimize blocking, but maintain the database is atomic

Question

I have a general question on how to minimize database locks, but to ensure that the operation is atomic. My case specifically relates to C # / SQL Server, but this is probably a language agnostic issue.

We must periodically record sales revenues in a file and send them to another server via FTP. Here is our current process:

• Get raw total sales from the sales table for the last hour.
• Enter total sales / profit / miscellaneous information in files
• Start transaction DB
• Refresh the sales table showing that the sales have been processed.
• Upload files to FTP
• Commit transaction

The problem is that we send a large number of files to the FTP server, and this process takes a lot of time. This causes some blocking problems when our customers cannot register or change sales. However, if FTP transfer or database update occurs for any reason, we need to cancel the db operation and delete all previously sent files.

What is the recommended way to ensure that the operation is atomic but minimizes blocking?

Answer 1

You can add more value to the status field of the Sales table:

• Not processed
• Treatment
• Processed

Thus, you do not need to block lines (and pages in MS SQL Server) for the entire time required to send files via FTP. You simply lock the corresponding records when they are selected, then update the status to "Processing" and, finally, you release the lock.

After completing the FTP job, you will update the corresponding entries as "Processed".

You must also develop a planned process to check if the FTP job has failed or not. The status of entries related to failed FTP processes must be updated to Not Processed again.

Edit: you can find the SQL script below

begin tran declare @toBeProcessed table(saleid int); insert into @tobeProcessed select saleid from Sales (rowlock) where status = 'NotProcessed' update Sales set status = 'Processing' where saleid in (select saleid from @toBeProcessed) commit select * from @toBeProcessed