I am building a process to upload a file to a file server and catalog the file attributes in SQL Server. I am using Hibernate to access the databases. I wonder if there is a way to include the IO call in the transaction, so if either the IO or the database call dies, they both get rolled back. Any ideas?
File systems are not transactional, so you won't get the rollback behaviour you want from a transaction. There are I believe transactional file system implementations out there, though I've never used one.
You might put the files into the database instead of separate file server then.
Though this solution is not favoured by everyone as it has pros and cons of its own, that way you get the transactional integrity plus auditing and security capabilities if need be, and ease of administration - one backup for all, ability to do point in time recovery if required, no need to maintain a file server, reduction of possible point of failures, and so on.
On the other hand, in the database the files might take slightly more space than in a file server and also volumes of undo/redo logs or other administrative areas would be increased.
Nitin Verma Nverma
Joined: Jun 06, 2010
If you wish to combine more than one resources inside a single transaction, you will have to use XA/JTA transactions. Though databases support both normal and XA transactions, but since filesystems do not support transactions (none of normal and XA transactions), you can rely on XADisk which exposes APIs for file/directory operations to enable transactions (both normal and XA) over filesystems.