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

项目
05/23/2023

使用 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。	允许	拒绝
SELECT	允许	允许
COMMIT TRANSACTION	拒绝*	拒绝*
SAVE TRANSACTION	拒绝*	拒绝*
ROLLBACK	允许*	允许*

* 取消事务，并且事务上下文的打开句柄无效。应用程序必须关闭所有打开句柄。

示例

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

A. 打开 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)