INF: Understanding e la risoluzione dei problemi di blocco di SQL Server

Traduzione articoli Traduzione articoli
Identificativo articolo: 224453 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

In questo articolo il termine "connessione" fa riferimento a una singola sessione registrati del database. Ciascuna connessione appare come un ID di sessione (SPID). ciascuno di questi SPID viene spesso chiamato "processo", sebbene non si tratti di un contesto di processo separato nel vero senso del termine. Ogni SPID è composto da risorse del lato server e da strutture di dati necessarie per far fronte alle richieste di una singola connessione di un dato client. Una sola applicazione client può disporre di una o più connessioni,. infatti dal punto di vista di SQL Server non esiste alcuna differenza tra più connessioni avviate da una stessa applicazione client o da uno stesso computer client e più connessioni avviate da più applicazioni o da più computer client. Una connessione può bloccare un'altra connessione indipendentemente dal fatto che entrambe abbiano avuto origine dalla stessa applicazione o da applicazioni diverse presenti in due diversi computer client.

Informazioni

Una caratteristica inevitabile di qualsiasi sistema concorrente basato su blocchi è la possibilità che si verifichino problemi di blocco in presenza di determinate condizioni. ovvero quando uno SPID gestisce un blocco su una specifica risorsa e un secondo SPID tenta di acquisire un tipo di blocco sulla stessa risorsa che crea conflitto con il tipo di blocco gestito dal primo SPID. Solitamente il periodo di tempo per cui il primo SPID blocca la risorsa è estremamente limitato. e una volta rilasciato il blocco, una seconda connessione è libera di acquisire il proprio blocco su tale risorsa e proseguire l'elaborazione. Questo è un comportamento normale che può ripetersi varie volte nel corso di un giorno senza che si producano effetti visibili sulle prestazioni del sistema.

La durata e il contesto della transazione di una query sono i fattori che determinano la durata dei blocchi della query e di conseguenza l'impatto di tali blocchi su altre query. Se la query non viene eseguita all'interno di una transazione e pertanto non vengono utilizzati gli hint di blocco, i blocchi per le istruzioni SELECT vengono gestiti solo per la durata della lettura effettiva della risorsa specificata e non per l'intera durata della query. Per le istruzioni INSERT, UPDATE e DELETE i blocchi vengono invece gestiti per l'intera durata della query, al fine di garantire la coerenza dei dati e consentire l'esecuzione di un rollback, qualora sia 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 delle transazioni e bloccare o meno i parametri vengono utilizzati nella query. Per una descrizione di blocco, gli hint di blocco e livelli di isolamento delle transazioni, vedere i seguenti argomenti nella documentazione in linea:
  • Il blocco nel motore di database
  • Personalizzazione di blocco e riga Versioning
  • Modalità blocco
  • Compatibilità di blocco
  • Riga basata su Versioning Isolation Levels nel motore di database
  • Controllo di transazioni (motore di database)
Quando il numero dei blocchi e dei problemi di blocco aumenta fino a produrre un impatto negativo sulle prestazioni del sistema, la ragione è solitamente da ricercare in una delle seguenti cause:
  • Uno SPID gestisce dei blocchi su un insieme di risorse per un periodo di tempo prolungato prima di rilasciare tali blocchi. Questo tipo di problema di blocco si risolve con il tempo, ma può causare un degrado delle prestazioni.
  • Uno SPID gestisce dei blocchi su un insieme di risorse senza mai rilasciare i blocchi. Questo tipo di problema di blocco non si risolve nel tempo e impedisce l'accesso alle risorse interessate a tempo indefinito.
Nel primo scenario il problema di blocco si risolve quando lo SPID rilascia i blocchi. Questa situazione può risultare tuttavia molto varia se SPID diversi bloccano a turno risorse diverse. Per questo può risultare difficile da diagnosticare e risolvere utilizzando SQL Server Enterprise Manager o singole query SQL. La seconda situazione determina invece uno stato costante più facile da diagnosticare.

Raccolta di informazioni di blocco

Per neutralizzare le difficoltà di risoluzione dei problemi di blocco, un amministratore di database può utilizzare gli script SQL costantemente monitorizzano lo stato di blocco e blocco in SQL Server. Questi script sono infatti in grado di fornire una serie di istantanee di istanze specifiche che vengono catturate nel tempo in modo da fornire un quadro generale del problema. Per una descrizione di come monitorare i blocchi utilizzando gli script SQL, vedere i seguenti articoli della Microsoft Knowledge Base:
271509Come monitorare i blocchi in SQL Server 2005 e in SQL Server 2000
Gli script contenuti in questo articolo eseguono le operazioni descritte di seguito. Dove 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, è possibile identificare la testina della catena di blocchi utilizzando le funzionalità che vengono fornite tramite SQL Server Management Studio. Per effettuare questa operazione, utilizzare uno dei metodi descritti di seguito:
    • Fare clic con il pulsante destro del mouse sull'oggetto server, espandere report, espandere Report Standard e quindi fare clic su attività-tutte le transazioni di blocco. Questo report mostra le transazioni all'inizio della catena di blocco. Se si espande la transazione, nel report verrà visualizzate le transazioni che vengono bloccate dalla transazione head. Questo report verrà inoltre visualizzati il "blocco istruzione SQL" e "Bloccato SQL istruzione".
    • Utilizzare DBCC INPUTBUFFER(<spid>) per trovare l'ultima istruzione inviata da uno SPID.
  2. Trovare il livello di nidificazione della transazione e lo stato di processo del blocco SPID.
    Il livello di nidificazione della transazione di uno SPID è disponibile nella variabile globale @@TRANCOUNT,. Tuttavia, può essere determinato dall'esterno dello SPID interrogando la tabella sysprocesses come segue:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    						
    Il valore ritornato è il valore di @@TRANCOUNT per lo SPID,. Mostra il livello di nidificazione della transazione per lo SPID ALL'ORIGINE del blocco, che a sua volta può spiegare perché esso è gestendo dei blocchi. Ad esempio, se il valore è maggiore di zero, significa che lo SPID è nel bel mezzo di una transazione, nel qual caso è normale che ritenga determinati blocchi acquisiti, a seconda del livello di isolamento della transazione.

    È inoltre possibile verificare se esiste qualsiasi transazione aperta a lungo termine nel database utilizzando DBCC OPENTRAN database_name.

Raccolta di informazioni relative a una traccia di SQL Server Profiler

Oltre alle informazioni appena descritte, è spesso necessario catturare una traccia di Profiler relativa alle attività sul server al fine di poter analizzare più approfonditamente un problema di blocco su SQL Server. Se uno SPID esegue più istruzioni all'interno di una transazione, solo l'ultima statementthat è stato inviato verrà visualizzato nel report, buffer di input o output monitor attività. mentre in realtà potrebbe essere uno dei comandi precedenti ad aver causato i blocchi non ancora rilasciati. Una traccia di Profiler consente pertanto di vedere tutti i comandi eseguiti da uno SPID all'interno della transazione corrente. La procedura descritta di seguito consente di impostare SQL Server Profiler in modo da poterne catturare una traccia.
  1. Aprire SQL Server Profiler.
  2. Dal menu file scegliere nuovo e quindi scegliere traccia.
  3. Nella scheda Generale specificare un nome di traccia e memorizzare i dati in un nome di file.

    Importante Il file di traccia dovrà essere scritti su disco veloce locale o condiviso. Evitare l'analisi a un'unità disco o di rete lenta. Assicurarsi inoltre di elaborazione analisi 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 elencati nella tabella 1 traccia.

    Inoltre, è possibile includere i tipi di eventi aggiuntivi elencati nella tabella 2 per ulteriori informazioni. Se si esegue in un ambiente di produzione a traffico elevato, è possibile decidere di utilizzare solo gli eventi nella tabella 1 sono in genere sufficiente per risolvere la maggior parte dei problemi di blocco. Inclusi altri eventi nella tabella 2 potrebbe rendere più semplice determinare rapidamente l'origine di un problema o questi eventi potrebbero essere necessari per identificare il colpevole istruzione in una routine con più istruzioni. Tuttavia, inclusi gli eventi nella tabella 2 verrà inoltre aggiunto a carico sul sistema e aumentare le dimensioni di output di analisi.
Nella tabella 1: tipi di eventi
Riduci questa tabellaEspandi questa tabella
TitoloEvento
Errori e avvisiEccezione
Errori e avvisiAttenzione
Controllo protezioneControllo Login
Controllo protezioneControllo Logout
SessioniConnessione esistente
Stored procedureRPC: Starting
TSQLSQL: BatchStarting

Nella tabella 2: tipi di eventi aggiuntivi
Riduci questa tabellaEspandi questa tabella
TitoloEvento
TransazioniDTCTransaction
TransazioniSQLTransaction
Stored procedureRPC: Completed
TSQLSQL: BatchCompleted
Stored procedureSP: StmtStarting
Stored procedureSP: StmtCompleted

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

IDENTIFICAZIONE E RISOLUZIONE DEGLI SCENARI DI BLOCCO PIÙ COMUNI

Esaminando le informazioni raccolte, è possibile determinare la causa di gran parte dei problemi di blocco. Di seguito è presentata una discussione relativa all'utilizzo di tali informazioni per identificare e risolvere gli scenari di blocco più diffusi. Questa discussione si suppone che sia stata utilizzata gli script di blocco nell'articolo 271509 (riferimento precedente) per acquisire informazioni sul blocco SPID e una traccia di Profiler è stata stabilita con gli eventi descritti in precedenza.

Visualizzazione dell'output degli script di blocco

Esaminare l'output sys.sysprocesses per determinare le testine di catene di blocco
Se non è stata specificata la modalità veloce per gli script di blocco, sarà presente una sezione intitolata "SPID all'origine del blocco catene" che elenca gli SPID che bloccano altri SPID nello script di output.
SPIDs at the head of blocking chains
se è specificata l'opzione della modalità veloce, è comunque possibile determinare le testine di blocco, osservando l'output sys.sysprocesses e segue la gerarchia dello SPID indicato nella colonna bloccata.
Esaminare l'output sys.sysprocesses per informazioni su SPID all'origine della catena di blocchi.
È importante valutare i seguenti campi sys.sysprocesses:

Stato

In 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 inviare un'altra query o batch. Uno stato eseguibile, in cui è in esecuzione o sos_scheduler_yield indica che lo SPID sta elaborando una query. Nella tabella riportata di seguito vengono fornite brevi spiegazioni di diversi valori di stato.
Riduci questa tabellaEspandi questa tabella
StatoVale a dire
SfondoLo SPID è in esecuzione un'attività in background, quali il rilevamento blocco critico (deadlock).
InattivoLo SPID è non attualmente in esecuzione. Indica solitamente che lo SPID è in attesa di un comando dall'applicazione.
In esecuzioneLo SPID è attualmente in esecuzione su un'utilità di pianificazione.
EseguibileLo SPID si trova nella 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 relativo intervallo di tempo su Utilità di pianificazione per consentire a un altro SPID acquisire l'ora dell'utilità di pianificazione.
SospesoLo SPID è in attesa di un evento, ad esempio un blocco o di un latch.
RollbackLo SPID sta eseguendo il rollback di una transazione.
DefwakeupIndica che lo SPID è in attesa di una risorsa di processo di viene liberata. Il campo waitresource dovrebbe indicare la risorsa in questione.

Open_tran

Questo campo indica il livello di nidificazione della transazione dello SPID. Se mostra un valore maggiore di zero, significa che lo SPID è nel corso di una transazione aperta e potrebbe stare gestendo dei blocchi acquisiti da qualsiasi istruzione all'interno di una transazione.

Lastwaittype, waittype e waittime

Il campo lastwaittype è una rappresentazione in forma di stringa del campo waittype, ovvero una colonna di dati binari interna riservata. Se il waittype è 0 x 0000, lo SPID non è in attesa di un valore e il valore lastwaittype indica l'ultimo waittype dello SPID con. Se il waittype non è uguale a zero, il valore lastwaittype indica il waittype corrente dello SPID.

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

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

Waitresource

Questo campo indica la risorsa che lo SPID sta attendendo. Nella tabella riportata di seguito sono elencati i formati comuni waitresource e il relativo significato:
Riduci questa tabellaEspandi questa tabella
RisorsaFormatoEsempio
TabellaDatabaseID:ObjectID:IndexIDSCHEDA: 5:261575970:1
In questo caso, ID database 5 è il database di esempio pubs e oggetto ID 261575970 è la tabella titles e 1 è l'indice cluster.
PaginaID database:ID file:ID paginaPAGINA: 5:1:104
In questo caso, ID database 5 è il database di esempio pubs, ID 1 il file è 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 osservare il m_objId. Ad esempio:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
ChiaveDatabaseID:Hobt_id (valore hash per la chiave di indice)KEY: 5:72057594044284928 (3300a4f361aa)

In questo caso, l'ID database 5 è pubs, Hobt_ID 72057594044284928 corrisponde alla index_id 2 non cluster per l'id oggetto 261575970 (tabella titoli). Utilizzare la visualizzazione del catalogo sys.partitions associare hobt_id a un particolare indice di id e un id oggetto. Non esiste un modo per unhash hash della chiave di indice per un valore di chiave di indice specifico.
RigaID database:ID file:ID pagina:Slot(riga)ELIMINARE: 5:1:104:3

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

Le restanti colonne sys.sysprocesses possono consentono di comprendere la radice di un problema anche. 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 certe librerie di rete (net_library), quando l'ultimo batch è stato inviato da uno SPID (last_batch) e così via.
Esaminare l'output DBCC INPUTBUFFER.
Per ogni SPID all'origine di una catena di blocchi o che presenta un valore waittype diverso da zero, lo script di blocco eseguirà DBCC INPUTBUFFER per determinare la query corrente per tale SPID.

In molti casi, questa è la query all'origine dei blocchi che impediscono ad altri utenti di procedere. Tuttavia, se lo SPID è all'interno di una transazione, i blocchi potrebbero essere stati acquisiti da una query precedentemente eseguita e non dalla query corrente. Pertanto, si consiglia di visualizzare l'output di Profiler per lo SPID e non solo l'output di inputbuffer.

Nota Poiché lo script di blocco è costituito da più passaggi, è possibile che uno SPID potrebbe essere visualizzato nella prima sezione come head di una catena di blocchi, ma nel momento in cui che viene eseguita la query DBCC INPUTBUFFER non sta bloccando e non viene acquisito il INPUTBUFFER. Questa situazione indica che il blocco si è risolto da solo, il che potrebbe o meno essere sinonimo di un problema. A questo punto, è possibile utilizzare la versione rapida dello script di blocco per assicurarsi di catturare inputbuffer prima che venga cancellato, sebbene anche questo metodo non garantisca la cattura, oppure visualizzare i dati di Profiler relativi al periodo in questione per determinare le query eseguite dallo SPID.

Visualizzazione dei dati di Profiler

Visualizzare i dati di Profiler in maniera efficace è estremamente importante per risolvere le problematiche di blocco. Più importante aspetto da tenere presente è che non si dispone per la ricerca in tutti gli elementi è acquisita, essere selettiva. In Profiler sono disponibili capacità che consentono di visualizzare in modo efficiente i dati registrati. Nella finestra di dialogo proprietà (scegliere proprietà dal menu file), Profiler consente di limitare i dati visualizzati rimuovendo colonne di dati o eventi, raggruppando (ordinamento) da colonne di dati e applicazione dei filtri. È possibile cercare una specifica colonna per valori specifici o intera traccia (menu Modifica, fare clic su Trova). È inoltre possibile salvare i dati di Profiler in una tabella di SQL Server (menu file scegliere Salva con nome e quindi fare clic su tabella) e di eseguono query SQL.

Fare attenzione che eseguono 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 registrati dal momento in cui è stata avviata la traccia. Salvare una traccia attiva in un file o la prima tabella (dal menu file scegliere Salva con nome) e quindi riaprire (menu file, fare clic su Apri) prima di procedere. Quando si lavora su un file di traccia salvato, le operazioni di filtro non rimuoveranno in maniera permanente i dati filtrati, bensì consentiranno solo di visualizzare i dati filtrati. Per definire meglio la ricerca, è possibile aggiungere e rimuovere eventi e colonne di dati.

Cosa cercare:
  • I comandi eseguiti dallo SPID all'origine della catena di blocchi all'interno della transazione corrente?
    Filtrare i dati di traccia per uno particolare SPID si trova all'inizio di una catena di blocchi (dal menu file, fare clic su proprietà, quindi specificare il valore SPID nella scheda filtri). È possibile quindi esaminare i comandi che lo SPID ha eseguito prima di bloccare altri SPID. Se vengono inclusi gli eventi transazione, sarà possibile identificare con facilità quando una transazione è stata avviata. In caso contrario, è possibile eseguire una ricerca nella colonna Text BEGIN, SAVE, COMMIT o ROLLBACK TRANSACTION operazioni. Utilizzare il valore open_tran dalla tabella sysprocesses per garantire che intercetta tutti gli eventi transazione. Conoscendo i comandi eseguiti e il contesto di transazione consentirà di determinare perché uno SPID è gestendo dei blocchi.

    È possibile rimuovere eventi e colonne di dati. Invece di esaminare entrambi avvio e gli eventi completati, sceglierne uno. Se gli SPID blocchi non sono le stored procedure, rimuovere il SP: Starting o SP: Completed eventi; la SQLBatch e RPC indicherà la chiamata di routine. Visualizzare gli eventi SP solo se necessario disporre di tale livello di dettaglio.
  • Durata delle query per gli SPID all'origine della catena di blocchi?
    Se si includono gli eventi completati sopra riportati, nella colonna durata verrà visualizzato il tempo di esecuzione della query. Questa informazioni può aiutare a identificare le query con un'esecuzione prolungata che stanno causando dei problemi di blocco. Per determinare perché la query viene eseguita lentamente, visualizzare la CPU, lettura e scritture colonne, come l'evento Execution Plan.

Categorie degli scenari di blocco più comuni

La tabella riportata di seguito associa i sintomi più comuni alle probabili cause. Il numero indicato nella colonna scenario corrisponde al numero dell'articolo riportato di seguito nella sezione "Common blocco scenari e risoluzioni". Le colonne stato, Waittype e Open_Tran fare riferimento alle informazioni della tabella sysprocesses. Il risolve? colonna indica se il blocco consente di risolvere autonomamente.

Riduci questa tabellaEspandi questa tabella
ScenarioWaittypeOpen_tranStatoIn questo articolo viene fornita la soluzione?Altri sintomi
1Diverso da zero>= 0eseguibileSì, una volta completata la query.I valori delle colonne Physical_IO, CPU e/o Memusage aumenteranno con il trascorrere del tempo. La durata della query sarà elevata una volta completata.
20 x 0000>0inattivoNo, ma è possibile interrompere lo SPID.Nella traccia di Profiler potrebbe essere presente un segnale ATTENTION per questo SPID a indicare che si è verificato il timeout o l'annullamento di una query.
30 x 0000>= 0eseguibileNr. Non verrà risolto finché il client non avrà recuperato tutte le righe o non avrà chiuso la connessione. È possibile interrompere lo SPID, ma questo potrebbe richiedere fino a 30 secondi.Se open_tran = 0 e il SPID gestisce i blocchi mentre il livello di isolamento della transazione è predefinito (READ COMMMITTED), questa è una causa più probabile.
4Varia>= 0eseguibileNr. Non verrà risolto finché il client non avrà annullato le query o chiuso le connessioni. Gli SPID possono essere interrotti, ma questo potrebbe richiedere fino a 30 secondi.Colonna hostname in sysprocesses per SPID all'origine della catena di blocchi sarà uguale a uno SPID sta bloccando.
50 x 0000>0rollbackSì.Nella traccia di Profiler potrebbe essere presente un segnale ATTENTION per questo SPID a indicare che si è verificato un timeout o un annullamento di query oppure l'invio di un'istruzione di rollback.
60 x 0000>0inattivoAlla fine. Quando Windows NT determina che la sessione non è più attiva, la connessione SQL Server verrà infine interrotta.Il valore last_batch in sysprocesses è molto precedente rispetto all'ora corrente.

Scenari di blocco comuni e relative soluzioni

Gli scenari descritti di seguito presentano le caratteristiche elencate nella tabella precedente. In questa sezione vengono forniti ulteriori dettagli e possibili percorsi di soluzione.
  1. Blocco causato da una normale esecuzione query con un tempo di esecuzione lunghi

    Risoluzione:
    La soluzione di questo tipo di problema di blocco consiste nel cercare i modi per ottimizzare la query in questione. In realtà, all'origine di questo genere di problemi potrebbe esserci semplicemente un problema di prestazioni, che dovrà essere risolto in quanto tale. Per informazioni sulla risoluzione dei problemi relativi a una specifica query con esecuzione lenta, vedere l'articolo della Microsoft Knowledge Base riportato di seguito:
    243589Come risolvere i problemi relativi a query con esecuzione lenta su SQL Server 7.0 o versioni successive
    Per generali sulla risoluzione dei problemi di prestazioni delle applicazioni, vedere l'articolo della Knowledge Base riportato di seguito:
    224587HOW TO: Risolvere i problemi di prestazioni di applicazioni con SQL Server
    Per ulteriori informazioni, vedere l'argomento di monitoraggio delle prestazioni e ottimizzazione procedure 2008 documentazione sul seguente sito MSDN Web:
    http://msdn.microsoft.com/en-us/library/ms187830.aspx
    Se non è possibile ottimizzare una query lenta che blocca altri utenti, si consiglia di considerare la possibilità di spostarla da un ambiente OLTP a un sistema di supporto alle decisioni.
  2. Blocco causato da uno SPID inattivo che ha perduto traccia del livello di nidificazione della transazione

    Questo tipo di blocco può spesso essere identificato da uno SPID è inattivo o in attesa di un comando, ancora cui livello di nidificazione delle transazioni (@@ TRANCOUNT, open_tran da sysprocesses) è maggiore di zero. Ciò può verificarsi se l'applicazione si verifica un timeout di query o emette un annullamento senza inviare anche il numero richiesto di istruzioni di ROLLBACK e/o COMMIT. Quando uno SPID riceve un timeout di query o di annullamento, verrà terminare la query corrente e il batch, ma non automaticamente il rollback o il commit della transazione. È infatti l'applicazione responsabile di eseguire tali operazioni, dato che SQL Server non può presumere che sia necessario eseguire il rollback dell'intera transazione semplicemente perché è stata annullata una singola query. Il timeout o l'annullamento di query vengono registrate nella traccia di Profiler come un evento segnale ATTENTION per lo SPID.

    Per dimostrare quanto spiegato, inviare la seguente query da Query Analyzer:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    Durante l'esecuzione di query, fare clic sul pulsante rosso Annulla. Una volta annullata la query, SELECT @@TRANCOUNT indicherà che il livello di nidificazione della transazione è pari a 1. Nel caso di una query DELETE o UPDATE o se nella query SELET è stato utilizzato HOLDLOCK, tutti i blocchi acquisiti continueranno a essere conservati. Anche nel caso della query riportata sopra, se un'altra query avesse acquisito dei blocchi in precedenza nella transazione, tali blocchi continuerebbero a essere gestiti anche dopo l'annullamento della query SELECT descritta sopra.

    Risoluzioni:

    • Le applicazioni devono essere in grado di gestire correttamente i livelli di nidificazione delle transazioni o si potrebbero verificare dei problemi di blocco a seguito dell'annullamento di query. Questa operazione può essere eseguita in diversi modi:
      1. In Gestore di errori dell'applicazione client, inviare un IF @@ TRANCOUNT aprire > 0 è TRAN ROLLBACK seguente qualsiasi errore anche se l'applicazione client non ritiene che una transazione. Questa operazione è necessaria, poiché una stored procedure chiamata durante il blocco potrebbe avere avviato una transazione all'insaputa dell'applicazione client. Nota che determinate condizioni, ad esempio l'annullamento della query, impediscono la procedura di esecuzione oltre l'istruzione corrente, in tal caso, anche se la routine utilizza la logica per verificare se @@ ERROR <>0 e interruzione della transazione, questo codice di rollback non verranno eseguiti in questi casi.
      2. Utilizzare SET XACT_ABORT ON per la connessione o in qualsiasi stored procedure che iniziano le transazioni e non sono pulitura dopo un errore. In caso di un errore in fase di esecuzione, questa impostazione verrà interrompere qualsiasi transazione aperta 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 il pool di connessioni viene utilizzato in un'applicazione che apre la connessione e viene eseguito un numero ridotto di query prima di rilasciare la connessione al pool, ad esempio un'applicazione basata sul Web, disattivare temporaneamente il pool di connessioni può contribuire ad alleviare il problema fino a quando non viene modificato nell'applicazione client per gestire gli errori in modo appropriato. Disattivando il pool di connessioni, rilasciare la connessione verrà causare una disconnessione fisica della connessione SQL Server in server di eseguire il rollback delle transazioni aperte.
      4. Se è attivato il pool di connessioni 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 il 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 avviate dal server (transazioni DTC avviate da applicazione client non sono interessate), consente di ripristinare il database predefinito, le opzioni SET e così via. Si noti che la connessione non viene reimpostata fino a quando 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 potrebbe non essere riutilizzato per alcuni secondi, durante la fase di cui la transazione dovrebbe rimanere aperta. Se la connessione non viene riutilizzata, la transazione verrà interrotta quando la connessione scade e viene rimosso dal pool di connessioni. In questo modo, è ottimale per l'applicazione client interrompere le transazioni nel proprio gestore degli errori o utilizzare SET XACT_ABORT ON per evitare questo potenziale ritardo.
    • In realtà questo tipo di problema di blocchi potrebbe essere legato a un problema di prestazioni, che pertanto dovrà essere risolto quanto tale. Se è possibile ridurre il tempo di esecuzione della query, non si verificherà alcun timeout o annullamento della query. È importante che l'applicazione sia in grado di gestire gli scenari di timeout o di annullamento, sebbene sia comunque utile esaminare le prestazioni della query.
  3. Blocco causato da uno SPID cui applicazione client corrispondente non è stato recuperato completamente tutte le righe di risultato per completamento

    Dopo aver inviato una query al server, tutte le applicazioni devono recuperare immediatamente tutte le righe di risultati fino al completamento. Se un'applicazione non recupera tutte le righe di risultato, è possibile che nelle tabelle vengano lasciati dei blocchi che possono bloccare altri utenti. Se si utilizza un'applicazione che invia in maniera trasparente istruzioni SQL al server, l'applicazione deve recuperare tutte le righe di risultato. In caso contrario (e non può essere configurato a tale scopo), potrebbe essere Impossibile risolvere il problema di blocco. Per evitare il problema, è possibile limitare le applicazioni mal progettate in tal senso a un database di report o di supporto alle decisioni.

    Risoluzione:

    L'applicazione deve essere riscritta affinché recuperi completamente tutte le righe di risultato.
  4. Blocco causato da un blocco critico (deadlock) client/server distribuito

    A differenza di un blocco critico (deadlock) convenzionale, un blocco critico distribuito non è rilevabile utilizzando il gestore dei blocchi RDBMS. Questo è dovuto al fatto che solo una delle risorse coinvolte nel blocco critico è un blocco di SQL Server. L'altro lato del blocco critico si ha a livello di applicazione client, su cui SQL Server non ha alcun controllo. Di seguito sono riportati due esempi di come questo problema può verificarsi ed evitare possibili modi l'applicazione.

    1. Blocco critico (deadlock) client/server distribuito con un singolo thread client
      Se il client dispone di più connessioni aperte e di un singolo thread di esecuzione, può verificarsi il blocco critico descritto di seguito. Per ragioni di concisione viene utilizzato il termine "dbproc" per fare riferimento 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)
      								
      In questo caso, un thread di applicazione client dispone di due connessioni aperte. e invia in maniera asincrona un'operazione SQL su dbproc1,. il che significa che non attende il ritorno della chiamata per procedere. L'applicazione invia quindi un'altra operazione SQL su dbproc2 e attende i risultati prima di elaborare i dati restituiti. All'avvio di dati proveniente (qualunque dbproc risponda prima--presumere è dbproc1,) elabora completamente tutti i dati ritornati su tale dbproc. recupera i risultati da dbproc1 finché SPID1 non resta bloccato su un blocco gestito da SPID2, in quanto le due query vengono eseguite in maniera asincrona sul server. A questo punto, dbproc1 attenderà all'infinito di ottenere altri dati. SPID2, che non è bloccato su un blocco, tenta di inviare dati al proprio client, dbproc2,. il quale invece è bloccato su dbproc1 al livello dell'applicazione, in quanto il thread singolo di esecuzione per l'applicazione è utilizzato da dbproc1. Questo determina un blocco critico (deadlock) che SQL Server non è in grado di rilevare o risolvere, in quanto solo una delle risorse coinvolte è una risorsa di SQL Server.
    2. Blocco critico (deadlock) client/server distribuito con un thread per connessione

      Anche se esiste un thread separato per ogni connessione del 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)
      								
      Questo caso è simile all'esempio A con l'eccezione che dbproc2 e SPID2 stanno eseguendo un'istruzione SELECT con l'intenzione di elaborare una riga per volta e di passare ciascuna riga tramite un buffer a dbproc1 per un'istruzione di INSERT, UPDATE o DELETE nella stessa tabella. Mentre SPID1 sta eseguendo l'operazione INSERT, UPDATE o DELETE resta bloccato su un blocco gestito da SPID2, che esegue l'istruzione SELECT. SPID2 va a scrivere una riga di risultato sul dbproc2 client,. in quale tenta di passare la riga in un buffer a dbproc1, ma scopre che dbproc1 è occupato, in quanto è bloccato in attesa che SPID1 completi l'istruzione INSERT corrente, che a sua volta è bloccata su SPID2. A questo punto, dbproc2 viene bloccato a livello di applicazione da dbproc1 il cui SPID, SPID1, è bloccato a livello di database da SPID2. Di nuovo, questa situazione determina un blocco critico (deadlock) che SQL Server non è in grado di rilevare o risolvere, in quanto una delle risorse coinvolte non è una risorsa di SQL Server.
    Entrambe gli esempi A e B sono aspetti fondamentali che gli sviluppatori devono tenere presente per poter. sviluppare applicazioni in grado di gestire in maniera appropriata tali casi.

    Risoluzioni:

    Sono due soluzioni affidabili per l'utilizzo di un timeout di query o le connessioni associate.

    • Timeout query
      Quando viene fornito un timeout di query, eventuali blocchi critici distribuiti verranno interrotti al verificarsi del timeout. Vedere DB-Library oppure la documentazione di ODBC per ulteriori informazioni sull'utilizzo di un timeout di query.
    • Connessioni associate
      Questa funzione consente a un client di disporre di più connessioni da associare in un unico spazio di transazione, in modo che le connessioni non si blocchino l'un l'altra. Per ulteriori informazioni, vedere la documentazione in linea di SQL Server 7 alla voce "Using Bound Connections".
  5. Blocco causato da uno SPID che è in "Golden," o rollback, stato

    Una query di modifica dei dati che viene arrestata o annullato di fuori di una transazione definita dall'utente verrà eseguito il rollback. Questo problema può verificarsi anche come effetto collaterale del riavvio del computer client e la relativa Disconnessione sessione di rete. Allo stesso modo, verrà eseguito il rollback di una query selezionata come vittima di un blocco critico. Spesso non è possibile eseguire il rollback di una query di modifica dei dati in maniera più rapida rispetto a quanto non siano state inizialmente applicate le modifiche. Nel caso di un'operazione DELETE, INSERT o UPDATE della durata di un'ora, anche il rollback potrebbe richiedere più o meno lo stesso tempo. Si tratta di un comportamento previsto, perché le modifiche apportate devono essere completamente rollback oppure potrebbe essere compromessa l'integrità fisica e transazionale del database. Poiché ciò possa accadere, SQL Server contrassegna lo SPID con lo stato "golden" o rollback, che indica che tale SPID non potrà essere terminato o selezionato come vittima di un blocco critico. Questo spesso può essere identificato osservando l'output di sp_who, che potrebbe indicare il comando ROLLBACK. Nella colonna stato di sys.sysprocesses indicherà uno stato ROLLBACK, che verrà visualizzata anche nell'output di sp_who o nel Monitor attività di SQL Server Management Studio.
    Risoluzione:

    È necessario attendere che lo SPID termini il rollback delle modifiche apportate.

    Se il server viene arrestato nel corso di questa operazione, il database verrà riavviato in modalità di ripristino e non sarà accessibile finché tutte le transazioni aperte non saranno state elaborate. Il ripristino all'avvio richiede per ogni transazione lo stesso tempo del ripristino a run-time e comunque il database non sarà accessibile durante tale periodo. Pertanto indurre il server a risolvere uno SPID in uno stato rollback si rivela spesso controproducente.

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

    Se l'applicazione client resta intrappolata o la stazione di lavoro client viene riavviata, è possibile che in determinate condizioni la sessione di rete al server non venga immediatamente annullata. Dal punto di vista del server, il client appare ancora presente e potrebbe essere conservato qualsiasi blocco acquisito. Per ulteriori informazioni, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:
    137983Come risolvere i problemi relativi alle connessioni isolate in SQL Server

    Risoluzione:

    Se l'applicazione client si è disconnessa senza che siano state rilasciare le risorse, sarà possibile terminare lo SPID utilizzando il comando KILL. Il comando KILL assume il valore dello 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 il completamento a causa di intervallo tra i controlli per il comando KILL.

COINVOLGIMENTO DELL'APPLICAZIONE NEI PROBLEMI DI BLOCCO

Quando si verificano problemi di blocco, esiste la tendenza a concentrare la propria attenzione sul lato server e sugli aspetti di piattaforma. Tuttavia, questo non in genere portare a una risoluzione e può assorbire energia meglio mirato all'analisi dell'applicazione client e le query che viene inviato e ora. Indipendentemente dal livello di visibilità che l'applicazione mostra in relazione alle chiamate di database eseguite, un problema di blocchi prevede solitamente che si ispezionino le istruzioni SQL inviate dall'applicazione e il comportamento dell'applicazione in rapporto all'annullamento delle query, alla gestione delle connessioni, al recupero di tutti i risultati e così via. Se lo strumento di sviluppo non consente un controllo esplicito della gestione delle connessioni, dell'annullamento delle query, del timeout di query, del recupero dei risultati e così via, potrebbe non essere possibile risolvere i problemi di blocco. Questo potenziale deve essere esaminato attentamente prima di selezionare uno strumento di sviluppo di applicazioni per SQL Server, specialmente per gli ambienti OLTP businessSYMBOLcritical.

È essenziale che cura essere esercitata 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 dei percorsi di transazione sono aspetti da valutare attentamente per ciascuna query. Ogni query e ogni transazione dovrebbero infatti essere il più "leggere" possibile. È necessario esercitare una buona disciplina in termini di gestione delle connessioni. per evitare che l'applicazione mostri prestazioni accettabili con un numero ridotto di utenti, ma che tali prestazioni possano degradare significativamente all'aumentare del numero di utenti che utilizzano l'applicazione simultaneamente.

Con una progettazione adeguata delle applicazioni e delle query, Microsoft SQL Server è in grado di supportare diverse migliaia di utenti simultanei su un unico server senza che si verifichino particolari problemi di blocco.

Proprietà

Identificativo articolo: 224453 - Ultima modifica: venerdì 11 settembre 2009 - Revisione: 5.1
Le informazioni in questo articolo si applicano a:
  • 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
Chiavi: 
kbmt kbhowto kbtshoot kbexpertiseinter kbinfo KB224453 KbMtit
Traduzione automatica articoli
Il presente articolo è stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non è sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, più o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non è la sua. Microsoft non è responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 224453
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com