4

I have service that contains a processor running, and it do two things:

1- Create a file in a directory.
2- Set your own status to "Processed".

But, when the service is stopped exactly in the middle of processing, the file is created in the directory but, the process is not finalized, like this:

  1- Create a file in a directory.
   -----SERVICE STOPPED-----
  2- Set your own status to "Processed".

I need a way to transact the IO operations with the database commands, how to do this?

EDIT - IMPORTANT

The problem is that the file created is captured by another application, so the file needs to be really created only if the commands are executed successfully. Because if the file be created and the another application capture him, and after an database error occurs, the problem to be continued.

OBS: I'm using c# to develop.

Only a Curious Mind
  • 2,807
  • 23
  • 39
  • 2
    You are writing this service yourself? Are you handling [`OnStop`](https://msdn.microsoft.com/en-us/library/system.serviceprocess.servicebase.onstop(v=vs.110).aspx) anywhere? What does a database has to do with your code? – Lou Jan 29 '15 at 13:35
  • Yes I'm writing, `onstop` is not a good way because suddenly the computer might stop – Only a Curious Mind Jan 29 '15 at 13:35
  • You can use a 3 way flag, like: `Stoped` `Processing` `Processed`. This way everytime you start the service, you will set the flag to `Processing`. If the service stops suddently, you will know about that. – Nicke Manarin Feb 04 '15 at 14:01
  • You mean, you want to use BeginTransaction, Commit and Rollback - or to create methods that perform "similar" things for your service? – Miroslav Lazovich Feb 04 '15 at 14:51
  • @NickeManarin this is my actual structure. But the problem is rollback the filecreations with the database execution. – Only a Curious Mind Feb 04 '15 at 15:08
  • @MiroslavLazovich begintransaction, commit and rollback only works to database commands, I need a way to perform a "transaction" between file creations and database commands – Only a Curious Mind Feb 04 '15 at 15:09
  • @OnlyaCuriousMind yes, I think I understand - that's why I wrote "similar". Basically, you want to treat file creation in similar way the database treats transaction? So either file is created, or (if something goes wrong) nothing is created (you are performing something similar to rollback)? – Miroslav Lazovich Feb 04 '15 at 15:14
  • @MiroslavLazovich Yes! – Only a Curious Mind Feb 04 '15 at 15:54
  • why cant you performs the DB Ops and then create the file afterwards if, and only if, the process succeeded? Is there some additional feedback loop involved? – Ňɏssa Pøngjǣrdenlarp Feb 04 '15 at 23:19
  • @Plutonix And if I perform all database operations and exactly in the moment that writing the file, the service stop unexpectedly? how to know if the file was really created? (in the database says that is already created, but it was not.) – Only a Curious Mind Feb 05 '15 at 10:44
  • 1
    @OnlyaCuriousMind why create file? why not use database itself? Database provides MVCC transaction to handle this scenario very well. You want your operation to be ACID compliant, then you must either implement proper MVCC transaction strategy for your operation or use one that exists, here database itself provides everything. Instead of other process to watch for file, why not watch db, or else, simplest approach will be use two step write, first write to temporary, verify db and rename file. – Akash Kava Feb 10 '15 at 08:49
  • Possible duplicate of [How to write a transaction to cover Moving a file and Inserting record in database?](https://stackoverflow.com/questions/7939339/how-to-write-a-transaction-to-cover-moving-a-file-and-inserting-record-in-databa) – Ozkan Mar 19 '19 at 11:20

2 Answers2

6

You can use Transactional NTFS (TxF). This provides the ability to perform actions that are fully atomic, consistent, isolated, and durable for file operations.

It can be intergrated to work with a large number of other transactional technologies. Because TxF uses the new Kernel Transaction Manager (KTM) features, and because the new KTM can work directly with the Microsoft® Distributed Transaction Coordinator (DTC).

Any technology that can work with DTC as a transaction coordinator can use transacted file operations within a single transaction. This means that you can now enlist transacted file operations within the same transaction as SQL operations, Web service calls via WS-AtomicTransaction, Windows Communication Foundation services via the OleTransactionProtocol, or even transacted MSMQ operations.

An example of file and database atomic transaction:

using (connectionDb)
{
    connectionDb.Open();
    using (var ts = new System.Transactions.TransactionScope())
    {
        try
        {
            File.Copy(sourceFileName, destFileName, overwrite);
            connectionDb.ExecuteNonQuery();
            ts.Complete();
        }
        catch (Exception)
        {
            throw;
        }
        finally
        { }
    }
}

See the following links for more information:

TxF on Codeplex

Msdn reference

Note: Remember DTC comes with a heavy performance penalty.

1

You didn't specify the database server, but Microsoft SQL Server 2008 R2 supports streaming file data as part of a transaction.

See: https://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx

Transactional Durability
With FILESTREAM, upon transaction commit, the Database Engine ensures transaction durability for FILESTREAM BLOB data that is modified from the file system streaming access.

For very large files, I wouldn't recommend it, because you often want the transaction to be as quick as possible when you have a lot of simultaneous transactions.

I'd normally use a compensation behaviour, e.g. storing status in a database and when a service is restarted, get it to first check for operations which have started but not completed and finish them off.

  1. Operation started on Server x at datetime y
  2. Operation completed on Server x at datetime y
a-h
  • 4,244
  • 2
  • 23
  • 29
  • Thanks by the answer, but, I need to use a way that works for many databases, such as sql server, Oracle and Informix. However is a feature that I did not know of the sql server. +1 – Only a Curious Mind Feb 11 '15 at 13:43