在 FILESTREAM 应用程序中避免与数据库操作发生冲突

2017-06-13

使用 SqlOpenFilestream（）打开 Win32 文件句柄以读取或写入 FILESTREAM BLOB 数据的应用程序可能会遇到与常见事务中管理的 Transact-SQL 语句的冲突错误。这包括 Transact-SQL 或 MARS 查询，这些查询需要很长时间才能完成执行。应用程序必须经过精心设计，以帮助避免这些类型的冲突。

当 SQL Server 数据库引擎或应用程序尝试打开 FILESTREAM BLOB 时，数据库引擎会检查关联的事务上下文。数据库引擎根据打开操作是否涉及 DDL 语句、DML 语句、数据检索或事务管理来允许或拒绝请求。下表显示了数据库引擎如何根据事务中打开的文件类型来确定是否允许或拒绝 Transact-SQL 语句。

Transact-SQL 语句	已打开以供阅读	已打开用于写入
使用数据库元数据的 DDL 语句，例如 CREATE TABLE、CREATE INDEX、DROP TABLE 和 ALTER TABLE。	允许	被阻止并因失败而超时。
使用数据库中存储的数据的 DML 语句，例如 UPDATE、DELETE 和 INSERT。	允许	拒绝
选择	允许	允许
提交事务	否认*	否认*。
保存交易	否认*	否认*
反转	允许*	允许*

* 事务已取消，与事务上下文相关的打开句柄已失效。应用程序必须关闭所有打开的句柄。

例子

以下示例演示如何 Transact-SQL 语句和 FILESTREAM Win32 访问会导致冲突。

答：将 FILESTREAM BLOB 打开以进行写入访问

以下示例演示仅打开文件进行写入访问的效果。

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. 打开 FILESTREAM BLOB 进行读取访问

下面的示例演示打开文件以便只读访问的效果。

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. 打开和关闭多个 FILESTREAM BLOB 文件

如果打开了多个文件，则使用限制性最高的规则。以下示例打开两个文件。将打开第一个文件进行读取，第二个文件用于写入。在打开第二个文件之前，DML 语句将被拒绝。

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. 未关闭游标

以下示例演示未关闭的语句游标如何阻止 OpenSqlFilestream() 打开 BLOB 进行写入访问。

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.

另请参阅

使用 OpenSqlFilestream 访问 FILESTREAM 数据
 使用多个活动的结果集 (MARS)

通过