How to guarantee atomicity in two databases (file system and your RDBMS)?

Question

I am working on a network file management project. In which we store links to the database (sql server) and data files in the file system, in which we encountered a problem of coordination between the file system and the database while we load the file, as well as in the case of deleting the file that first we create a link in the database or store files in the file system; the problem is that if you first create a link in the database and then save the file to file system.bur, some type of error occurs when storing files in the file system. Then a link to this file is created in the database, but there are no data files in the file system ;; please give me some decision how to deal with this situation; I really need it ;; and the reason for this?

Answer 1

This is actually a little easier than you think.

First, you need to solve the "only source of truth."

That is, either the file system or the database is correct at any time, which one?

The reason for this is that it makes conflict resolution easier.

You must assume that the database is your Source and that the file system is the shadow part of the database. This seems counterintuitive, since how a record can exist in the database if it is not in the file system. Obviously this is not possible. But, basically, if the file is not in the database, then "it does not exist" in any case. Thus, the file system reflects the state of the database, and not vice versa.

Given these assumptions, you get these conflict resolution rules.

:

File Exists    DB Entry Exists   Action
   Yes            Yes            No action, normal state
   No             Yes            Error -- missing file, "should never happen"
   No             No             No action, normal state
   Yes            No             Delete the file, but no error.

- , .

, .

- , . , "in process" .

, "" . , . , , ( "" ). , , , , . , . , " ", "" ( ).

:

Start DB transaction
Rename file
Add DB record
Commit transaction

, , , . , ? № 4, . , .

, :

Start DB Transaction
Delete DB record
Commit transaction
Delete file from file system

, . , , № 4.

, , (, , ) , , . " ", .

, , " ". . , - .

- , .

Answer 2

, , , .

, , .

Answer 3

Windows Vista, Windows Server 2008 Windows NTFS.

With this tool, if you were to program in .NET, you could use the System.Transactions namespace to update the file system and update the database as a single atomic block.

I do not know if transactional file systems exist on another OS. This does not mean that they do not exist.