Avoid conflicts with database operations in FILESTREAM applications

Applies to: SQL Server

Applications that use SqlOpenFilestream() to open Win32 file handles for reading or writing FILESTREAM BLOB data can encounter conflict errors with Transact-SQL statements that are managed in a common transaction. This includes Transact-SQL or MARS queries that take a long time to finish execution. Applications must be carefully designed to help avoid these types of conflicts.

When SQL Server Database Engine or applications try to open FILESTREAM BLOBs, the Database Engine checks the associated transaction context. The Database Engine allows or denies the request based on whether the open operation is working with DDL statements, DML statements, retrieving data, or managing transactions. The following table shows how the Database Engine determines whether a Transact-SQL statement will be allowed or denied based on the type of files that are open in the transaction.

Transact-SQL statements	Opened for read	Opened for write
DDL statements that work with database metadata, such as CREATE TABLE, CREATE INDEX, DROP TABLE, and ALTER TABLE.	Allowed	Are blocked and fail with a time-out.
DML statements that work with the data that is stored in the database, such as UPDATE, DELETE, and INSERT.	Allowed	Denied
SELECT	Allowed	Allowed
COMMIT TRANSACTION	Denied*	Denied*.
SAVE TRANSACTION	Denied*	Denied*
ROLLBACK	Allowed*	Allowed*

* The transaction is canceled, and open handles for the transaction context are invalidated. The application must close all open handles.

Examples

The following examples show how Transact-SQL statements and FILESTREAM Win32 access can cause conflicts.

A. Opening a FILESTREAM BLOB for write access

The following example shows the effect of opening a file for write access only.

dstHandle =  OpenSqlFilestream(dstFilePath, Write, 0,  
    transactionToken, cbTransactionToken, 0);  
  
//Write some date to the FILESTREAM BLOB.  
WriteFile(dstHandle, updateData, ...);  
  
//DDL statements will be denied.  
//DML statements will be denied.  
//SELECT statements will be allowed. The FILESTREAM BLOB is  
//returned without the modifications that are made by  
//WriteFile(dstHandle, updateData, ...).  
CloseHandle(dstHandle);  
  
//DDL statements will be allowed.  
//DML statements will be allowed.  
//SELECT statements will be allowed. The FILESTREAM BLOB  
//is returned with the updateData applied.  

B. Opening a FILESTREAM BLOB for read access

The following example shows the effect of opening a file for read access only.

dstHandle =  OpenSqlFilestream(dstFilePath, Read, 0,  
    transactionToken, cbTransactionToken, 0);  
//DDL statements will be denied.  
//DML statements will be allowed. Any changes that are  
//made to the FILESTREAM BLOB will not be returned until  
//the dstHandle is closed.  
//SELECT statements will be allowed.  
CloseHandle(dstHandle);  
  
//DDL statements will be allowed.  
//DML statements will be allowed.  
//SELECT statements will be allowed.  

C. Opening and closing multiple FILESTREAM BLOB files

If multiple files are open, the most restrictive rule is used. The following example opens two files. The first file is opened for read and the second for write. DML statements will be denied until the second file is opened.

dstHandle =  OpenSqlFilestream(dstFilePath, Read, 0,  
    transactionToken, cbTransactionToken, 0);  
//DDL statements will be denied.  
//DML statements will be allowed.  
//SELECT statements will be allowed.  
  
dstHandle1 =  OpenSqlFilestream(dstFilePath1, Write, 0,  
    transactionToken, cbTransactionToken, 0);  
  
//DDL statements will be denied.  
//DML statements will be denied.  
//SELECT statements will be allowed.  
  
//Close the read handle. The write handle is still open.  
CloseHandle(dstHandle);  
//DML statements are still denied because the write handle is open.  
  
//DDL statements will be denied.  
//DML statements will be denied.  
//SELECT statements will be allowed.  
  
CloseHandle(dstHandle1);  
//DDL statements will be allowed.  
//DML statements will be allowed.  
//SELECT statements will be allowed.  

D. Failing to close a cursor

The following example shows how a statement cursor that is not closed can prevent OpenSqlFilestream() from opening the BLOB for write access.

TCHAR *sqlDBQuery =  
TEXT("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT(),")  
TEXT("Chart.PathName() FROM Archive.dbo.Records");  
  
//Execute a long-running Transact-SQL statement. Do not allow  
//the statement to complete before trying to  
//open the file.  
  
SQLExecDirect(hstmt, sqlDBQuery, SQL_NTS);  
  
//Before you call OpenSqlFilestream() any open files  
//that the Cursor the Transact-SQL statement is using  
// must be closed. In this example,  
//SQLCloseCursor(hstmt) is not called so that  
//the transaction will indicate that there is a file  
//open for reading. This will cause the call to  
//OpenSqlFilestream() to fail because the file is  
//still open.  
  
HANDLE srcHandle =  OpenSqlFilestream(srcFilePath,  
     Write, 0,  transactionToken,  cbTransactionToken,  0);  
  
//srcHandle will == INVALID_HANDLE_VALUE because the  
//cursor is still open.  

See Also

Access FILESTREAM Data with OpenSqlFilestream
Using Multiple Active Result Sets (MARS)