Evitar conflitos com operações de banco de dados em aplicativos de FILESTREAMAvoid Conflicts with Database Operations in FILESTREAM Applications

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Os aplicativos que usam SqlOpenFilestream() para abrir identificadores de arquivo do Win32 para ler ou gravar dados BLOB FILESTREAM podem apresentar erros de conflito com instruções Transact-SQLTransact-SQL gerenciadas em uma transação em comum.Applications that use SqlOpenFilestream() to open Win32 file handles for reading or writing FILESTREAM BLOB data can encounter conflict errors with Transact-SQLTransact-SQL statements that are managed in a common transaction. Isso inclui consultas Transact-SQLTransact-SQL ou MARS cuja execução demora muito tempo para ser concluída.This includes Transact-SQLTransact-SQL or MARS queries that take a long time to finish execution. Os aplicativos devem ser criados cautelosamente para evitar esses tipos de conflitos.Applications must be carefully designed to help avoid these types of conflicts.

Quando o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine ou os aplicativos tentam abrir FILESTREAM BLOBs, o Mecanismo de Banco de DadosDatabase Engine verifica o contexto de transação associado.When Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine or applications try to open FILESTREAM BLOBs, the Mecanismo de Banco de DadosDatabase Engine checks the associated transaction context. O Mecanismo de Banco de DadosDatabase Engine permite ou nega as solicitações, dependendo se a operação em aberto funciona com instruções DDL e DML, transações de recuperação de dados ou de gerenciamento.The Mecanismo de Banco de DadosDatabase Engine allows or denies the request based on whether the open operation is working with DDL statements, DML statements, retrieving data, or managing transactions. A tabela a seguir mostra como o Mecanismo de Banco de DadosDatabase Engine determina se uma instrução Transact-SQLTransact-SQL será permitida ou negada com base no tipo de arquivos abertos na transação.The following table shows how the Mecanismo de Banco de DadosDatabase Engine determines whether a Transact-SQLTransact-SQL statement will be allowed or denied based on the type of files that are open in the transaction.

instruções Transact-SQLTransact-SQL statements Abertas para leituraOpened for read Abertas para gravaçãoOpened for write
Instruções de DDL que funcionam com metadados de banco de dados, como CREATE TABLE, CREATE INDEX, DROP TABLE e ALTER TABLE.DDL statements that work with database metadata, such as CREATE TABLE, CREATE INDEX, DROP TABLE, and ALTER TABLE. Allowed (permitido)Allowed São bloqueadas e falham devido ao tempo limite esgotado.Are blocked and fail with a time-out.
Instruções DML que funcionam com os dados armazenados no banco de dados, como UPDATE, DELETE e INSERT.DML statements that work with the data that is stored in the database, such as UPDATE, DELETE, and INSERT. Allowed (permitido)Allowed NegadasDenied
SELECTSELECT Allowed (permitido)Allowed Allowed (permitido)Allowed
COMMIT TRANSACTIONCOMMIT TRANSACTION Negadas*Denied* Negadas*.Denied*.
SAVE TRANSACTIONSAVE TRANSACTION Negadas*Denied* Negadas*Denied*
ROLLBACKROLLBACK Permitidas*Allowed* Permitidas*Allowed*

* A transação é cancelada e os identificadores em aberto do contexto da transação são invalidados.* The transaction is canceled, and open handles for the transaction context are invalidated. O aplicativo deve fechar todos os identificadores abertos.The application must close all open handles.

ExemplosExamples

Os exemplos a seguir mostram como as instruções Transact-SQLTransact-SQL e o acesso de FILESTREAM do Win32 podem causar conflitos.The following examples show how Transact-SQLTransact-SQL statements and FILESTREAM Win32 access can cause conflicts.

A.A. Abrindo um BLOB FILESTREAM para acesso de gravaçãoOpening a FILESTREAM BLOB for write access

O exemplo a seguir mostra o efeito de abrir um arquivo para acesso apenas de gravação.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.B. Abrindo um BLOB FILESTREAM para acesso de leituraOpening a FILESTREAM BLOB for read access

O exemplo a seguir mostra o efeito de abrir um arquivo para acesso apenas de leitura.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.C. Abrindo e fechando vários arquivos de BLOB FILESTREAMOpening and closing multiple FILESTREAM BLOB files

Se vários arquivos estiverem abertos, será usada a regra mais restritiva.If multiple files are open, the most restrictive rule is used. O exemplo a seguir abre dois arquivos.The following example opens two files. O primeiro é aberto para leitura e o segundo, para gravação.The first file is opened for read and the second for write. As instruções DML serão negadas até que o segundo arquivo seja aberto.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.D. Falha ao fechar um cursorFailing to close a cursor

O exemplo a seguir mostra como um cursor de instrução que não está fechado pode impedir OpenSqlFilestream() de abrir o BLOB para acesso de gravação.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.  

Consulte TambémSee Also

Acessar dados do FILESTREAM com OpenSqlFilestream Access FILESTREAM Data with OpenSqlFilestream
Usando MARS (Multiple Active Result Sets)Using Multiple Active Result Sets (MARS)