INF: Comprensione e risoluzione dei problemi di blocco di SQL Server

IMPORTANTE: il presente articolo è stato tradotto tramite un software di traduzione automatica di Microsoft ed eventualmente revisionato dalla community Microsoft tramite la tecnologia CTF (Community Translation Framework) o da un traduttore professionista. Microsoft offre articoli tradotti manualmente e altri tradotti automaticamente e rivisti dalla community con l’obiettivo di consentire all'utente di accedere a tutti gli articoli della Knowledge Base nella propria lingua. Tuttavia, un articolo tradotto automaticamente, anche se rivisto dalla community, non sempre è perfetto. Potrebbe contenere errori di vocabolario, di sintassi o di grammatica. Microsoft declina ogni responsabilità per imprecisioni, errori o danni causati da una traduzione sbagliata o dal relativo utilizzo da parte dei clienti. Microsoft aggiorna frequentemente il software e gli strumenti di traduzione automatica per continuare a migliorare la qualità della traduzione.

Clicca qui per visualizzare la versione originale in inglese dell’articolo: 224453
Sommario
In questo articolo, il termine "connessione" si riferisce a una singola sessione effettuato del database. Ciascuna connessione appare come un ID di sessione (SPID). Ciascuno di questi SPID viene spesso considerato un processo, anche se non è un contesto di processo separato nel vero senso del termine. Piuttosto, ogni SPID è composto da risorse di server e strutture di dati necessarie per soddisfare le richieste di una singola connessione di un dato client. Una singola applicazione client può avere una o più connessioni. Dal punto di vista di SQL Server, non esiste alcuna differenza tra più connessioni da un'applicazione client singola su un singolo computer client e più connessioni da più applicazioni o più computer client. Una connessione può bloccarne un'altra connessione, indipendentemente dal fatto che entrambe abbiano avuto origine dalla stessa applicazione o da applicazioni distinte in due diversi computer client.
Informazioni
Il blocco è una caratteristica inevitabile di qualsiasi sistema di gestione di database relazionali (RDBMS) basato sul blocco della concorrenza. In SQL Server, il blocco si verifica quando uno SPID gestisce un blocco su una specifica risorsa e un secondo SPID tenta di acquisire un tipo di blocco in conflitto sulla stessa risorsa. In genere, l'intervallo di tempo per cui il primo SPID blocca la risorsa è molto piccola. Quando rilascia il blocco, la seconda connessione è libera di acquisire un blocco sulla risorsa e continuare l'elaborazione. Questo è il comportamento normale e può ripetersi varie volte nel corso di un giorno non sortisce effetti significativi sulle prestazioni del sistema.

Il contesto delle transazioni e la durata di una query determina quanto tempo i blocchi vengono mantenuti e, di conseguenza, l'impatto su altre query. Se la query non viene eseguita all'interno di una transazione e non hint di blocco vengono utilizzati, i blocchi per le istruzioni SELECT vengono gestiti solo su una risorsa in fase di che lettura effettiva, non per la durata della query. Per istruzioni INSERT, UPDATE e DELETE, i blocchi vengono mantenuti per tutta la durata della query, sia per la coerenza dei dati e per consentire alla query eseguire il rollback se necessario.

Per le query eseguite all'interno di una transazione, la durata per cui i blocchi vengono mantenuti sono determinati dal tipo di query, il livello di isolamento della transazione e bloccare o meno parametri vengono utilizzati nella query. Per una descrizione dei blocchi, hint di blocco e livelli di isolamento delle transazioni, vedere i seguenti argomenti nella documentazione in linea di SQL Server:
  • Blocco del motore di Database
  • Il blocco di personalizzazione e controllo delle versioni di riga
  • Modalità di blocco
  • Compatibilità tra i blocchi
  • Livelli di isolamento basato sul controllo delle versioni di riga nel motore di Database
  • Controllo delle transazioni (motore di Database)
Quando di blocco aumento fino al punto in cui è presente un impatto negativo sulle prestazioni del sistema, è in genere a causa di uno dei seguenti motivi:
  • Uno SPID gestisce dei blocchi su un insieme di risorse per un extendedperiod di tempo prima di rilasciarli. Questo tipo di blocco risolve itselfover tempo, ma può causare un degrado delle prestazioni.
  • Uno SPID gestisce dei blocchi su un insieme di risorse e mai releasesthem. Questo tipo di blocco non si risolve impedendo l'accesso alle risorse i prodotti a tempo indeterminato.
Nel primo scenario, il problema di blocco si risolve nel tempo come lo SPID rilascia i blocchi. Tuttavia, la situazione può essere molto varia se SPID diversi bloccano diverse risorse nel tempo, a turno. Per questo motivo, queste situazioni possono essere difficile risolvere i problemi utilizzando SQL Server Enterprise Manager o singole query SQL. La seconda situazione determina in uno stato coerente che può essere più facile da diagnosticare.

Raccolta di informazioni di blocco

Per far fronte alla difficoltà di risoluzione dei problemi di blocco, un amministratore di database può utilizzare gli script SQL che consentono di monitorare costantemente lo stato dei blocchi in SQL Server. Questi script possono fornire istantanee di istanze specifiche nel tempo, causando un quadro generale del problema. Per una descrizione di come monitorare i blocchi con gli script SQL, vedere i seguenti articoli della Microsoft Knowledge Base:
271509 Come monitorare i blocchi in SQL Server 2005 e SQL Server 2000
Gli script in questo articolo verranno eseguite le attività seguenti. Se possibile, viene assegnato il metodo per ottenere queste informazioni da SQL Server Management Studio.
  1. Identificare lo SPID (ID sessione) all'inizio della catena di blocchi e l'istruzione SQL.
    Oltre a utilizzare gli script nell'articolo della Knowledge Base menzionato in precedenza, è importante identificare l'origine la catena di blocchi utilizzando le funzionalità che vengono forniti tramite SQL Server Management Studio. A tale scopo, utilizzare uno dei seguenti metodi:
    • Fare clic sulla scheda server, espandere rapporti, espandere Report Standarde quindi fare clic sull'attività: tutte le transazioni di blocco. Questo report mostra le transazioni all'inizio della catena di blocco. Se si espande la transazione, nel report verranno visualizzate le transazioni sono bloccate dalla transazione head. Nel report verranno visualizzati anche il "blocco SQL Statement" e "Bloccato SQL Statement".
    • Utilizzare DBCC INPUTBUFFER (<spid>) per trovare l'ultima istruzione inviata da uno SPID.</spid>
  2. Trovare il livello di nidificazione della transazione e lo stato del processo dello SPID bloccante.
    Il livello di nidificazione delle transazioni di uno SPID è disponibile nella variabile globale the@@TRANCOUNT. Tuttavia, può essere determinato da theSPID esterno interrogando la tabella sysprocesses , come segue:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>go						
    Il valore restituito è il valore di @@TRANCOUNT per lo SPID. Questo showsthe livello di nidificazione transazione per lo SPID, che a sua volta explainwhy blocchi. Ad esempio, se il valore è maggiore di zero, theSPID è in fase di una transazione (in cui caso è probabile che itretains alcuni blocchi ha acquisito, a seconda del isolationlevel della transazione).

    È possibile inoltre controllare se ogni a lungo termine aprire transactionexists nel database utilizzando DBCC OPENTRANdatabase_name.

Raccolta di informazioni di traccia di SQL Server Profiler

Oltre alle informazioni di cui sopra, spesso è necessario acquisire una traccia di Profiler delle attività del server per analizzare e scoprire un problema di blocco in SQL Server. Se uno SPID esegue più istruzioni all'interno di una transazione, solo l'ultima istruzione che è stata inviata verrà visualizzata nella relazione, buffer di input o output monitor attività. Uno dei comandi precedenti, tuttavia, potrebbe essere il motivo ancora blocchi vengono mantenuti. Una traccia di Profiler consente di visualizzare tutti i comandi eseguiti da uno SPID all'interno della transazione corrente. I passaggi seguenti consentono di configurare SQL Server Profiler per acquisire una traccia.
  1. Aprire SQL Server Profiler.
  2. Dal menu File scegliere Nuovoe quindi fare clic su traccia.
  3. Nella scheda Generale , specificare un nome di traccia e un nome di file per memorizzare i dati in.

    Importante Il file di traccia dovrà essere scritti su un disco locale o condiviso rapido. Evitare di tracciatura in un'unità disco o rete lenta. Assicurarsi inoltre che il Server elabora traccia dati sono selezionati.
  4. Nella scheda Selezione eventi , fare clic per selezionare le caselle di controllo Mostra tutte le colonne e Mostra tutti gli eventi .
  5. Nella scheda Selezione eventi , aggiungere i tipi di eventi sono elencati nella tabella 1 alla traccia.

    Inoltre, è possibile includere i tipi di eventi aggiuntivi elencati nella tabella 2 per ulteriori informazioni. Se si esegue in un ambiente di produzione di volumi elevati, si può decidere di utilizzare solo gli eventi nella tabella 1, come lo sono in genere sufficienti a risolvere la maggior parte dei problemi di blocco. Inclusi gli eventi nella tabella 2 può semplificare determinare rapidamente l'origine di un problema o questi eventi potrebbero essere necessari per identificare l'istruzione a causa del problema in una routine con più istruzioni. Tuttavia, inclusi gli eventi nella tabella 2 anche il carico sul sistema e aumentare le dimensioni dell'output di traccia.
Tabella 1: Tipi di eventi
IntestazioneEvento
Errori e avvisiEccezione
Errori e avvisiAttenzione
Controllo di sicurezzaControllo accesso
Controllo di sicurezzaControllo Logout
SessioniConnessione esistente
Stored procedureRPC: avvio
TSQLSQL: BatchStarting

Tabella 2: Tipi di eventi aggiuntivi
IntestazioneEvento
TransazioniDTCTransaction
TransazioniSQLTransaction
Stored procedureRPC: completato
TSQLSQL: BatchCompleted
Stored procedureSP: StmtStarting
Stored procedureSP: StmtCompleted

Per ulteriori informazioni sull'utilizzo di SQL Server Profiler, vedere documentazione in linea di SQL Server.

Identificazione e risoluzione dei più comuni scenari di blocco

Esaminando le informazioni di cui sopra, è possibile determinare la causa della maggior parte dei problemi di blocco. Il resto di questo articolo è una discussione su come utilizzare queste informazioni per identificare e risolvere alcuni degli scenari di blocco più comuni. Questa discussione si presuppone che si sono utilizzati gli script di blocco nell'articolo 271509 (citati in precedenza) per acquisire informazioni sugli SPID bloccante e aver apportato una traccia di Profiler gli eventi descritti sopra.

Visualizzando l'Output dello Script di blocco

Esaminare l'output di sysprocesses per determinare le testine delle catene di bloccante
Se non è stata specificata la modalità veloce per gli script di blocco, sarà presente una sezione intitolata "SPIDs at the head of blocking chains" thatlists gli SPID che bloccano altri SPID nell'output dello script.
SPIDs at the head of blocking chains
Se è specificata la modalità veloce, sarà comunque possibile identificare le testine theblocking esaminando l'output sysprocesses e seguendo la gerarchia dello SPID riportato nella colonna bloccata.
Esaminare l'output di sysprocesses per informazioni sugli SPID all'origine della catena di blocchi.
È importante valutare i seguenti campi sysprocesses :

Stato

Questa colonna viene visualizzato lo stato di uno particolare SPID. In genere, lo stato inattivo indica che lo SPID ha completato l'esecuzione ed è in attesa per l'applicazione invii un'altra query o batch. Uno stato eseguibile, in esecuzioneo sos_scheduler_yield indica che lo SPID sta attualmente elaborando una query. Nella tabella che segue offre una breve spiegazione dei vari valori di stato.
StatoSignificato
SfondoLo SPID è in esecuzione un'attività in background, quali il rilevamento di deadlock.
InattivoLo SPID non è attualmente in esecuzione. In genere, ciò indica che lo SPID è in attesa di un comando dall'applicazione.
In esecuzioneLo SPID è in esecuzione in un'utilità di pianificazione.
EseguibileLo SPID è in coda eseguibile di un'utilità di pianificazione e in attesa di ottenere l'ora dell'utilità di pianificazione.
Sos_scheduler_yieldLo SPID è in esecuzione, ma ha fruttato volontariamente porzione di tempo nell'utilità di pianificazione per consentire a un altro processo acquisire l'ora dell'utilità di pianificazione.
SospesoLo SPID è in attesa di un evento, ad esempio un blocco o un latch.
RollbackLo SPID è in corso il rollback di una transazione.
DefwakeupIndica che lo SPID è in attesa di una risorsa che sta per essere liberata. Il campo waitresource dovrebbe indicare la risorsa in questione.

Open_tran

Questo campo indica il livello di nidificazione delle transazioni dello SPID. Se questo valore è maggiore di 0, lo SPID è all'interno di una transazione aperta e può contenere blocchi acquisiti da qualsiasi istruzione all'interno della transazione.

Lastwaittype, waittype e waittime

Il campo lastwaittype è una rappresentazione di stringa del campo waittype , ovvero una colonna binaria interna riservata. Se il waittype è 0x0000, lo SPID non è in attesa di qualsiasi elemento e il valore lastwaittype indica l' ultimo waittype dello SPID aveva. Se il waittype non è zero, il valore lastwaittype indica il waittype corrente dello SPID.

Per una breve descrizione dei diversi lastwaittype e waittype valori, vedere il seguente articolo della Microsoft Knowledge base:
822101 Descrizione delle colonne della tabella master.dbo.sysprocesses in SQL Server 2000 e SQL Server 2005 waittype e lastwaittype
Per ulteriori informazioni su DM os_wait_stats, vedere la documentazione in linea di SQL Server.

Il valore waittime può essere utilizzato per determinare se lo SPID sta avanzando. Quando una query nella tabella sysprocesses restituisce un valore nella colonna waittime che è inferiore al valore waittime di una query di sysprocessesprecedente, significa che il blocco precedente è stato acquisito e rilasciato e ora in attesa di un nuovo blocco (presupponendo waittime diverso da zero). Ciò può essere verificato confrontando il waitresource tra sysprocesses output.

Waitresource

Questo campo indica la risorsa su cui uno SPID è in attesa. Nella tabella seguente sono elencati i formati waitresource comuni e il relativo significato:
RisorsaFormatoEsempio
TabellaDatabaseID:ObjectID:IndexIDSCHEDA: 5:261575970:1
In questo caso, ID database 5 è il database di esempio pubs e ID oggetto 261575970 è la tabella titles e 1 è l'indice cluster.
PaginaDatabaseID:FileID:PageIDPAGINA: 5:1:104
In questo caso, ID database 5 è pubs, file ID 1 è il file di dati primario e pagina 104 è una pagina appartenente alla tabella titles .

Per identificare l'id di oggetto a cui appartiene la pagina, utilizzare il comando DBCC PAGE (dbid, fileid, pageid, output_option) e controllare il m_objId. Ad esempio:
DBCC TRACEON ( 3604 )DBCC PAGE ( 5 , 1 , 104 , 3 )
ChiaveDatabaseID:Hobt_id (valore Hash della chiave di indice)CHIAVE: 5:72057594044284928 (3300a4f361aa)

In questo caso, ID database 5 è Pubs, Hobt_ID 72057594044284928 corrisponde all'argomento index_id 2 non cluster per l'id oggetto 261575970 (tabellatitles ). Utilizzare la visualizzazione del catalogo sys.partitions per associare hobt_id a un id di indice specifico e un id di oggetto. Non esiste alcun modo per l'hash della chiave di indice per un valore di chiave di indice specifico di hashing.
RigaDatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

In questo caso, ID database 5 è pubs, file ID 1 è il file di dati primario, pagina 104 è una pagina appartenente alla tabella titles e lo slot 3 indica la posizione della riga nella pagina.
CompilazioneDatabaseID:ObjectID [[compilazione]]SCHEDA: 5:834102012 [[compilazione]] non si tratta di un blocco di tabella, ma piuttosto una compilazione di blocco su una stored procedure. ID database 5 è pubs, ID oggetto 834102012 è usp_myprocedure di stored procedure. Per ulteriori informazioni sui blocchi causati da blocchi di compilazione, vedere 263889 articolo della Knowledge Base.
Altre colonne

Le restanti colonne di sysprocesses possono fornire un'analisi la radice del problema. La loro utilità varia a seconda delle circostanze del problema. Ad esempio, è possibile determinare se il problema si verifica solo con certi client (hostname), in alcune librerie di rete (net_library), durante l'ultimo batch inviato da uno SPID (last_batch) e così via.
Esaminare l'output DBCC INPUTBUFFER.
Per ogni SPID all'origine della catena di blocchi o con zero anon waittype, lo script di blocco eseguirà DBCC INPUTBUFFER todetermine la query corrente per tale SPID.

In molti casi, questa è la query che causa il thatare di blocchi bloccare altri utenti che si terrà. Tuttavia, se lo SPID è all'interno di atransaction, i blocchi potrebbero essere stati acquisiti da una query eseguita in precedenza, non a quella corrente. Pertanto, si consiglia di visualizzare l'output di Profiler per lo SPID, non solo inputbuffer.

Nota Perché lo script di blocco è costituito da più passaggi, si ispossible uno SPID appaia nella prima sezione head di un blockingchain, ma nel momento in cui viene eseguita la query DBCC INPUTBUFFER non longerblocking e se INPUTBUFFER non viene acquisita. Indica che blockingis risolto da solo per tale SPID e può o non può essere un problema. In thispoint, è possibile utilizzare la versione rapida lo script di blocco per provare toensure catturare inputbuffer prima che venga cancellato (anche se è ancora noguarantee) o visualizzare Profiler dati da questo intervallo di tempo per determinare la whatqueries lo SPID era in esecuzione.

Visualizzazione dei dati di Profiler

Visualizzazione dei dati di Profiler in modo efficiente è estremamente importante per risolvere i problemi di blocco. La cosa più importante da tenere presente è che non è necessario osservare tutte le informazioni acquisite; essere selettivi. Profiler fornisce funzionalità per garantire efficienza consente di visualizzare i dati acquisiti. Nella finestra di dialogo proprietà (scegliere proprietàdal menu File ), Profiler consente di limitare i dati visualizzati rimuovendo colonne o eventi, raggruppando i (ordinamento) da colonne di dati e applicazione di filtri. È possibile eseguire ricerche in tutta la traccia o solo in una colonna di valori specifici (dal menu Modifica , scegliere Trova). È inoltre possibile salvare i dati di Profiler in una tabella di SQL Server (dal menu File scegliere Salva con nome e quindi fare clic su tabella) ed eseguire query SQL.

Fare attenzione che eseguire il filtraggio solo su un file di traccia salvato in precedenza. Se si esegue questa procedura su una traccia attiva, si rischia di perdere i dati sono stati acquisiti dall'avvio della traccia. Salvare una traccia attiva in un file o in una tabella (dal menu File scegliere Salva con nome) e quindi riaprirlo (dal menu File , fare clic su Apri) prima di procedere. Quando si lavora su un file di traccia salvato, il filtro non rimuove in modo permanente i dati filtrati, semplicemente non visualizza tutti i dati. È possibile aggiungere e rimuovere eventi e colonne di dati secondo necessità consentono di focalizzare le ricerche.

Cosa cercare:
  • Quali comandi è lo SPID all'origine di un blocco chainexecuted all'interno della transazione corrente?
    Filtrare i dati di traccia per aparticular SPID all'origine di una catena di blocchi (dal menu File , scegliere proprietà, quindi nella scheda filtri specificare il valore SPID). È possibile esaminare previo comandi riceve notifiche eseguite al momento in cui che bloccare altri SPID. Se si includono gli eventi theTransaction, facilmente identificabile quando è stata avviata una transazione.In caso contrario, è possibile cercare la colonna Text BEGIN, SAVE, COMMIT o ROLLBACK TRANSACTIONoperations. Utilizzare il valore open_tran della tabella sysprocesses per assicurarsi che vengano catturati tutti gli eventi di transazione.Conoscere i comandi eseguiti e il contesto di transazione consentirà di todetermine perché uno SPID sta gestendo dei blocchi.

    È importante ricordare che è possibile removeevents e colonne di dati. Anziché osservare sia di inizio e di completedevents, sceglierne uno. Se gli SPID bloccante non sono stored procedure, rimuovere ilSP: a partire da o SP: completata eventi; gli eventi SQLBatch e RPC indicherà la chiamata di procedura. Visualizzazione solo di quando gli eventi SP necessario tale livello di dettaglio.
  • Qual è la durata delle query per gli SPID all'headof catene di blocco?
    Se si includono gli eventi completati, la colonna durata mostrerà il tempo di esecuzione di query. Youidentify le query con esecuzione prolungata che causano il blocco può essere utile. Per determinare il motivo per cui interrogazione esecuzione lenta, visualizzare la CPU, letturae scrive colonne, come l'evento di Piano di esecuzione .

Suddivisione degli scenari di blocco più comuni

La tabella riportata di seguito associa i sintomi comuni alle probabili cause. Il numero indicato nella colonna Scenario corrisponde al numero nella sezione "Blocco scenari e le risoluzioni comuni" di seguito in questo articolo. Le colonne statoWaittypee Open_Tranfare riferimento alle informazioni sysprocesses . Il risolve? colonna indica o meno il blocco verrà risolto in modo autonomo.

ScenarioWaittypeOpen_tranStatoConsente di risolvere?Altri sintomi
1Diverso da zero> = 0eseguibileSì, al termine della query.Colonne Physical_IO, CPU e/o Memusage aumenteranno nel tempo. Durata della query sarà elevata una volta completata.
20x0000> 0inattivoNo, ma può interrompere lo SPID.Nella traccia di Profiler potrebbe essere presente un segnale attention per questo SPID a indicare che un timeout di query o si è verificato l'annullamento.
30x0000> = 0eseguibileNo. Non verrà risolto finché il client recupera tutte le righe o la connessione viene chiusa. Può interrompere lo SPID, ma potrebbe richiedere fino a 30 secondi.Se open_tran = 0 e lo SPID gestisce dei blocchi mentre il livello di isolamento della transazione è predefinito (leggere COMMMITTED), una probabile causa.
4Varia> = 0eseguibileNo. Non verrà risolto finché non Annulla query o non chiude le connessioni client. Gli SPID possono essere interrotti, ma potrebbero richiedere fino a 30 secondi.La colonna hostname nella tabella sysprocesses per lo SPID all'origine di una catena di blocchi sarà uguale a quella dello SPID bloccato.
50x0000> 0rollbackSì.Nella traccia di Profiler potrebbe essere presente un segnale attention per questo SPID a indicare che un timeout di query o si è verificato l'annullamento o semplicemente un'istruzione rollback è stata rilasciata.
60x0000> 0inattivoAlla fine. Quando Windows NT determina che la sessione non è più attiva, la connessione a SQL Server verrà interrotta.Il valore last_batch della tabella sysprocesses è di molto precedente rispetto all'ora corrente.

Scenari di blocco e risoluzioni comuni

Gli scenari elencati di seguito presentano le caratteristiche elencate nella tabella precedente. In questa sezione fornisce ulteriori dettagli, quando applicabile, nonché i percorsi di soluzione.
  1. Blocco causato da una Query con tempi di esecuzione lunghi

    Risoluzione:
    La soluzione a questo tipo di problema di blocco è forways per ottimizzare la query di ricerca. In realtà, questo genere di problemi può justbe un problema di prestazioni e richiesta che venga risolto come tale. Per informationon una query con esecuzione rallentata specifica per la risoluzione dei problemi, vedere il seguente articolo della Microsoft Knowledge Base:
    243589 Come risolvere le query con esecuzione lenta su SQL Server 7.0 o versioni successive
    Per performancetroubleshooting generali dell'applicazione, vedere il seguente articolo della Knowledge Base:
    224587 Procedura: Risolvere i problemi di prestazioni delle applicazioni con SQL Server
    Per ulteriori informazioni, vedere l'argomento della documentazione in linea di SQL Server 2008 monitoraggio delle prestazioni e sulle procedure di ottimizzazione sul seguente sito Web MSDN: Se si dispone di una query con esecuzione prolungata che gli utenti blockingother e non può essere ottimizzata, è consigliabile spostarlo da un OLTPenvironment a un sistema di supporto decisionale.
  2. Blocco causato da uno SPID inattivo che ha perduto traccia del livello di nidificazione delle transazioni

    Questo tipo di blocco può spesso essere identificato da un SPIDthat è inattivo o in attesa di un comando, ma il cui livello di nidificazione della transazione (@@TRANCOUNT, open_tran dalla tabella sysprocesses) è maggiore di zero. Questo problema può verificarsi se il timeout di query, applicationexperiences o senza inviare anche un numero therequired di istruzioni ROLLBACK e/o COMMIT. Quando uno SPID riceve aquery timeout o l'annullamento, terminerà la query corrente e il batch, butdoes, non automaticamente il rollback o il commit della transazione. Isresponsible l'applicazione per questo, come SQL Server non può presumere che un intero transactionmust essere rollback semplicemente a causa di una singola query viene annullata. Il querytimeout o Annulla apparirà come un evento segnale ATTENTION per lo SPID all'origine di traccia theProfiler.

    Per dimostrarlo, inviare il seguente queryfrom semplice Query Analyzer:

    BEGIN TRAN SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2-- Issue this after canceling querySELECT @@TRANCOUNTROLLBACK TRAN						
    Durante l'esecuzione di query, fare clic sul pulsante rosso Annulla . Dopo l'annullamento della query, selezionare @@TRANCOUNT indicatesthat il livello di nidificazione delle transazioni è uno. Se ciò fosse stato un'operazione di eliminazione o un UPDATEquery o era stato utilizzato HOLDLOCK nella selezione, si terrà tutti i wouldstill di blocchi acquisiti. Anche con la query precedente, se un'altra query acquisita andheld blocchi di transazione, essi continueranno a essere conservati quando il aboveSELECT è stata annullata.

    Risoluzioni:

    • Le applicazioni devono gestire correttamente i livelli di nidificazione delle transazioni, o si potrebbero verificare problemi di blocco a seguito dell'annullamento della query in questo modo. Può essere eseguita in diversi modi:
      1. Inviare un oggetto se il gestore degli errori dell'applicazione client, aprire @@TRANCOUNT > 0 è TRAN ROLLBACK seguente qualsiasi errore, anche se l'applicazione client non ritiene che una transazione. In questo caso, poiché una stored procedure chiamata durante il processo batch è stata avviata una transazione senza una conoscenza dell'applicazione client. Nota che determinate condizioni, ad esempio l'annullamento della query, impediscono la procedura di esecuzione oltre l'istruzione corrente, pertanto anche se la routine utilizza la logica per verificare se @@ERROR <> 0 e interrompere la transazione, questo codice di rollback non verrà eseguito in questi casi.
      2. Utilizzare SET XACT_ABORT ON per la connessione, o in qualsiasi stored procedure che iniziano transazioni e non sono pulitura dopo un errore. In caso di un errore di run-time, questa impostazione verrà interrompere le transazioni aperte e restituire il controllo al client. Si noti che le istruzioni T-SQL che segue l'istruzione che ha causato l'errore non verranno eseguite.
      3. Se viene utilizzato il pool di connessione in un'applicazione che apre la connessione e viene eseguito un numero limitato di query prima di rilasciare la connessione al pool, ad esempio un'applicazione basata su Web, disattivare temporaneamente il pool di connessioni può contribuire ad alleviare il problema finché non viene modificato l'applicazione client per gestire gli errori in modo appropriato. Disattivando il pool di connessioni, rilasciare la connessione causerà una disconnessione fisica della connessione di SQL Server, con il server di eseguire il rollback delle transazioni aperte.
      4. Se è attivato il pool di connessione e il server di destinazione è SQL Server 2000, può essere utile aggiornare il computer client a MDAC 2.6 o versione successiva. Questa versione dei componenti MDAC aggiunge codice per il driver ODBC e provider OLE DB in modo che la connessione dovrebbe essere "reset" prima che venga riutilizzato. Questa chiamata a sp_reset_connection interrompe tutte le transazioni server avviato (transazioni DTC avviate dall'applicazione client non sono interessate), ripristina il database predefinito, impostare le opzioni e così via. Si noti che la connessione non viene reimpostata finché non viene riutilizzato dal pool di connessioni, pertanto è possibile che un utente potrebbe aprire una transazione e quindi rilasciare la connessione al pool di connessioni, ma non può essere riutilizzato per alcuni secondi, durante il quale la transazione rimarrà aperta. Se la connessione non viene riutilizzata, la transazione verrà interrotta quando la connessione scade e viene rimossa dal pool di connessioni. In questo modo, è ottimale per l'applicazione client interrompere le transazioni nel gestore di errore oppure utilizzare SET XACT_ABORT ON per evitare questo potenziale ritardo.
    • In realtà, questo genere di problemi può anche essere un problema di prestazioni e richiedono che venga risolto come tale. Se è possibile ridurre il tempo di esecuzione di query, non si verificherà il timeout di query oppure su Annulla. È importante che l'applicazione sia in grado di gestire il timeout o annullare gli scenari che vengono rilevati, ma potrà essere utile esaminare le prestazioni della query.
  3. Blocco causato da uno SPID cui applicazione Client non ha recuperato completamente tutte le righe di risultato fino al completamento

    Dopo l'invio di una query al server, tutti i applicationsmust recuperare immediatamente tutte le righe di risultato di. Se molto un'applicazione recupera tutte le righe di risultato, possono essere lasciati blocchi sulle tabelle, otherusers di blocco. Se si utilizza un'applicazione che invia in maniera trasparente SQLstatements al server, l'applicazione deve recuperare tutte le righe di risultato. Se itdoes non e se non può essere configurato a tale scopo, potrebbe essere impossibile toresolve il problema di blocco. Per evitare il problema, è possibile sia corretta restrictpoorly applicazioni per un report o una supportdatabase decisione.

    Risoluzione:

    L'applicazione deve essere riscritta per recuperare tutte le righe del risultato di.
  4. Blocco causato da un blocco critico Client/Server distribuito

    A differenza di un deadlock convenzionale, un deadlockis distribuita non rilevabile utilizzando il sistema RDBMS bloccare manager. Ciò è dovuto il thatonly di fatto una delle risorse coinvolto nel blocco è un blocco di SQL Server. Sul lato Theother del deadlock è a livello di applicazione client, su cui SQL Server non ha alcun controllo. Di seguito sono riportati due esempi di questa situazione e l'applicazione nei modi per evitarla.

    1. Client/Server distribuito Deadlock con un singolo Thread Client
      Se il client ha più connessioni aperte e un singolo thread di esecuzione, potrebbe verificarsi il seguente blocco critico distribuito. Per ragioni di brevità, il termine "dbproc" utilizzato qui si riferisce alla struttura della connessione client.

       SPID1------blocked on lock------->SPID2  /\                         (waiting to write results           |                           back to client)  |                                 |  |                                 |                      Server side  | ================================|==================================  |     <-- single thread -->       |                      Client side  |                                 \/  dbproc1   <-------------------   dbproc2 (waiting to fetch             (effectively blocked on dbproc1, awaiting  next row)                     single thread of execution to run)								
      Nel caso illustrato sopra, un thread dell'applicazione client singola ha due connessioni aperte. Invia in modo asincrono un'operazione SQL su dbproc1. Ciò significa che non si attende sulla chiamata a restituire prima di procedere. L'applicazione invia un'altra operazione SQL su dbproc2 e attende i risultati per avviare l'elaborazione i dati restituiti. All'avvio di dati proveniente (indipendentemente da quale dbproc risponda per primo, supponiamo che dbproc1), esso elabora completamente tutti i dati restituiti da tale dbproc. Recupera i risultati da dbproc1 finché SPID1 resta bloccato su un blocco gestito da SPID2 (poiché le due query vengono eseguite in modo asincrono nel server). A questo punto, dbproc1 attenderà all'infinito più dati. SPID2 non è bloccato su un blocco, ma se si tenta di inviare dati al proprio client, dbproc2. Tuttavia, dbproc2 viene bloccato su dbproc1 al livello dell'applicazione come singolo thread di esecuzione per l'applicazione è in uso da dbproc1. Il risultato è un deadlock che SQL Server non è in grado di rilevare o risolvere, in quanto solo una delle risorse coinvolte è una risorsa di SQL Server.
    2. Client/Server distribuito Deadlock con un Thread per ogni connessione

      Anche se esiste un thread separato per ciascuna connessione nel client, una variante di questo blocco critico distribuito può verificarsi come mostrato di seguito.

      SPID1------blocked on lock-------->SPID2  /\                         (waiting on net write)        Server side  |                                 |  |                                 |  | INSERT                          |SELECT  | ================================|==================================  |     <-- thread per dbproc -->   |                      Client side  |                                 \/  dbproc1   <-----data row-------   dbproc2 (waiting on                     (blocked on dbproc1, waiting for it  insert)                         to read the row from its buffer)								
      In questo caso è simile all'esempio A, tranne dbproc2 e SPID2 esegue un'istruzione SELECT con l'intenzione di riga alla volta e di passare ciascuna riga tramite un buffer a dbproc1 per un'operazione INSERT, UPDATE, o eliminare l'istruzione nella stessa tabella. SPID1 (esecuzione di INSERT, UPDATE o DELETE) resta bloccato su un blocco gestito da SPID2 (esegue l'istruzione SELECT). SPID2 va a scrivere una riga di risultato sul dbproc2 client. Dbproc2 tenta di passare la riga in un buffer a dbproc1, ma scopre che dbproc1 è occupato (si è bloccato in attesa di SPID1 per completare l'operazione di inserimento corrente, che è bloccato su SPID2). A questo punto, dbproc2 viene bloccato a livello di applicazione da dbproc1 cui SPID, SPID1, viene bloccato a livello di database da SPID2. Nuovamente, il risultato è un deadlock che SQL Server non è in grado di rilevare o risolvere, in quanto solo una delle risorse coinvolte è una risorsa di SQL Server.
    Entrambi gli esempi A e B sono aspetti fondamentali che gli sviluppatori di thatapplication necessario essere a conoscenza. Sviluppare applicazioni per i casi di handlethese in modo appropriato.

    Risoluzioni:

    Sono due soluzioni affidabili per utilizzare un querytimeout o le connessioni associate.

    • Timeout di query
      Quando è disponibile un timeout di query, se si verifica il blocco critico distribuito, esso verranno interrotto al verificarsi del timeout. Sull'utilizzo di un timeout di query, vedere la documentazione di ODBC per ulteriori informazioni o DB-Library.
    • Connessioni associate
      Questa funzionalità consente a un client di disporre di più connessioni da riunire in un unico spazio di transazione, in modo che le connessioni si bloccano reciprocamente. Per ulteriori informazioni, vedere l'argomento "Using Bound Connections" nella documentazione in linea di SQL Server 7.0.
  5. Blocco causato da uno SPID "golden" o Rollback, stato

    Una query di modifica dei dati è KILLed o canceledoutside di una transazione definita dall'utente, verrà ripristinata. Possibile occuras un effetto collaterale il riavvio del computer client e il sessiondisconnecting di rete. Analogamente, una query selezionata come vittima del deadlock sarà il rollback della. Una query di modifica dei dati spesso non sarà possibile ripristinare le prestazioni del thechanges inizialmente sono state applicate. Ad esempio, se un DELETE, INSERT o UPDATEstatement erano in esecuzione per un'ora, può assumere almeno un'ora per eseguire il rollback. Si tratta di comportamento previsto, perché le modifiche apportate devono essere completelyrolled indietro o l'integrità fisica e transazionale del database sarebbe becompromised. Perché ciò possa accadere, SQL Server contrassegna lo SPID in uno stato "golden" o rollback (che significa che non potrà essere terminato o selezionato come un deadlockvictim). Può essere identificato spesso osservando l'output di sp_who, che potrebbe indicare il comando ROLLBACK. La colonna stato di sysprocesses indicherà uno stato ROLLBACK, che apparirà anche nell'output di sp_who o in SQL Server Management Studio Activity Monitor.
    Risoluzione:

    È necessario attendere che lo SPID termini il rollback di thechanges sono state apportate.

    Se il server viene arrestato in mezzo all'operazione ofthis, il database è in modalità di ripristino all'avvio e itwill essere inaccessibile finché non vengono elaborate tutte le transazioni aperte. Startuprecovery ha essenzialmente la stessa quantità di tempo per ogni transazione come timerecovery di esecuzione e il database è inaccessibile durante questo periodo. Pertanto, server forcingthe a risolvere uno SPID in uno stato rollback farà spesso becounterproductive.

    Per evitare questa situazione, non eseguire largebatch INSERT, UPDATE, o le operazioni di eliminazione durante i periodi in sistemi OLTP.Se possibile, eseguire tali operazioni durante i periodi di minore attività.
  6. Blocco causato da una connessione orfana

    Se viene riavviato l'applicazione client resta intrappolata o la clientworkstation, la sessione di rete al server potrebbe non beimmediately annullato in alcune condizioni. Dal punto di vista del server, theclient sembra ancora essere presenti e i blocchi acquisiti possono ancora beretained. Per ulteriori informazioni, fare clic sul seguente numero di articolo per visualizzare l'articolo della Microsoft Knowledge Base:
    137983 Risoluzione dei problemi di connessioni isolate in SQL Server

    Risoluzione:

    Se l'applicazione client è disconnesso withoutappropriately ripulire le proprie risorse, è possibile interrompere lo SPID dal comando KILL usingthe. Il comando KILL assume il valore SPID come input. Ad esempio, per interrompere lo SPID 9, è sufficiente inviare il seguente comando:

    KILL 9						

    Nota Il comando KILL potrebbe richiedere fino a 30 secondi, per l'intervallo tra i controlli per il comando KILL.

Coinvolgimento dell'applicazione nei problemi di blocco

Potrebbe esserci una tendenza a concentrarsi su problemi di ottimizzazione e la piattaforma del lato server quando che devono affrontare un problema di blocco. Tuttavia, questo non conduce solitamente ad una risoluzione e grado di assorbire tempo ed energie maggiori risultati all'esame dell'applicazione client e delle query inviate. Indipendentemente dal livello di visibilità esposti dall'applicazione per quanto riguarda le chiamate di database, un problema di blocco tuttavia spesso richiede l'ispezione esatta istruzioni SQL inviate dall'applicazione e il comportamento dell'applicazione all'annullamento delle query, gestione delle connessioni, recupero di tutte le righe di risultato e così via. Se lo strumento di sviluppo non consente il controllo esplicito sulla gestione delle connessioni, annullamento delle query, timeout di query, recupero dei risultati e così via, potrebbero non essere possibile risolvere problemi di blocco. Questo potenziale deve essere esaminato attentamente prima di selezionare uno strumento di sviluppo di applicazioni per SQL Server, soprattutto per ambienti OLTP business-critical.

È indispensabile esercitare molta attenzione durante la fase di progettazione e costruzione del database e dell'applicazione. In particolare, il consumo di risorse, il livello di isolamento e la lunghezza di percorso transazione deve essere valutati per ogni query. Ogni query e la transazione deve essere più possibile leggera. Disciplina della gestione connessione adeguata deve essere esercitato. Se questa non viene eseguita, è possibile che l'applicazione potrebbe sembrare a prestazioni accettabili con un numero ridotto di utenti, ma le prestazioni possono peggiorare significativamente come il numero di utenti che utilizzano verso l'alto.

Con la corretta applicazione e la struttura della query, è in grado di supportare migliaia di utenti simultanei su un unico server, con il piccolo blocco di Microsoft SQL Server.

Avviso: questo articolo è stato tradotto automaticamente

Proprietà

ID articolo: 224453 - Ultima revisione: 03/15/2015 05:50:00 - Revisione: 6.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup

  • kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtit
Feedback