适用范围：
SQL Server
使用 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 TABLEALTER TABLE。 | 允许 | 被阻止，并因超时而失败。 |
| 处理数据库中存储的数据的 DML 语句，例如 UPDATE， DELETE和 INSERT。 | 允许 | 拒绝 |
| SELECT | 允许 | 允许 |
| COMMIT 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.