|
|
Pubblicato su 20/07/2016 12:37:53
|
|
|
Panoramica delle chiuse
1. Perché introdurre le serrature
Quando più utenti eseguono operazioni simultanee sul database, si verificano le seguenti incongruenze nei dati:
Aggiornamenti mancanti
Due utenti, A e B, leggono gli stessi dati e li modificano, e il risultato della modifica di un utente distrugge quello dell'altra, come il sistema di prenotazione dei biglietti
Lettura sporca
L'utente A modifica i dati, poi l'utente B li legge, ma l'utente A annulla la modifica dei dati per qualche motivo, e i dati tornano al loro valore originale
Non leggere ripetutamente
L'Utente A legge i dati, poi l'Utente B li legge e li modifica
Il principale metodo di controllo della concorrenza è il blocco, che consiste nel vietare agli utenti di eseguire determinate operazioni per un certo periodo di tempo per evitare incongruenze nei dati
2. Classificazione delle chiuse
Ci sono due divisioni in categorie di serrature:
1 . Dal punto di vista del sistema di database: è diviso in lock esclusivi (cioè locks esclusivi), lock condivisi e lock di aggiornamento
MS - SQL Server utilizza i seguenti pattern di blocco delle risorse.
Descrizione della modalità di blocco
Share/e è utilizzato per operazioni che non modificano o aggiornano i dati (operazioni di sola lettura), come le istruzioni SELECT.
Update (U) è utilizzato nelle risorse aggiornabili. Previene forme comuni di deadlock quando più sessioni vengono lette, bloccate e possibilmente un aggiornamento delle risorse che può verificarsi.
Esclusivo (X) è utilizzato per operazioni di modifica dei dati, come INSERIMENTO, AGGIORNAMENTO o CANCELLA. Assicurati che non vengano eseguiti più aggiornamenti contemporaneamente sulla stessa risorsa.
Le Intent Lock sono usate per stabilire una gerarchia di lock. I tipi di blocchi d'intento sono: Intent Shared (IS), Intent Exclusive (IX) e Intent Exclusive (SIX).
I blocchi di schema vengono utilizzati quando si eseguono operazioni che dipendono dallo schema della tabella. I tipi di blocchi di schema sono: modifica dello schema (Sch -M) e stabilità dello schema (Sch -S).
Gli aggiornamenti in blocco (BU) sono utilizzati quando grandi volumi di dati vengono copiati su una tabella e viene specificato un suggerimento TABLOCK.
Serrature condivise
Un lock condiviso permette alle transazioni concorrenti di leggere (SELEZIONARE) una risorsa. Quando esiste un lock condiviso (S) su una risorsa, nessun'altra transazione può modificare i dati. Rilascia il lock condiviso (S) sulla risorsa non appena i dati sono stati letti, a meno che il livello di isolamento della transazione sia impostato su ripetibile o superiore, o il lock condiviso (S) venga mantenuto con un lock hint per tutta la durata della transazione.
Aggiornamento blocco
Le serrature di aggiornamento (U) prevengono i deadlock nella loro forma abituale. Un tipico pattern di aggiornamento consiste in una transazione che legge un record, ottiene un lock condiviso (S) per una risorsa (pagina o riga) e poi modifica una riga, il che richiede che il lock venga convertito in un lock esclusivo (X). Se due transazioni acquisiscono un lock in modalità condivisa su una risorsa e poi cercano di aggiornare i dati contemporaneamente, una transazione tenta di convertire il lock in un lock esclusivo (X). La transizione dalla modalità condivisa a quella esclusiva deve aspettare un po' perché il blocco esclusivo di una transazione non è compatibile con il blocco in modalità condivisa di un'altra transazione; Si verifica un'attesa di chiusura. La seconda transazione tenta di ottenere un blocco esclusivo (X) per un aggiornamento. Si verifica un deadlock perché entrambe le transazioni vengono convertite in lock esclusivi (X) e ogni transazione attende che l'altra transazione liberi il lock in modalità condivisa.
Per evitare questo potenziale problema di deadlock, usa una serratura (U) aggiornata. Solo una transazione alla volta può ottenere un blocco (U) aggiornato per una risorsa. Se la transazione modifica la risorsa, il lock di aggiornamento (U) viene convertito in un lock esclusivo (X). Altrimenti, la serratura viene convertita in una serratura condivisa.
Serrature esclusive
I locks esclusivi (X) impediscono alle transazioni concorrenti di accedere alle risorse. Altre transazioni non possono leggere o modificare i dati bloccati dal blocco esclusivo (X).
Blocco di intenzione
Un intent lock indica che SQL Server deve acquisire un lock condiviso (S) o un esclusivo (X) lock su alcune delle risorse sottostanti nella gerarchia. Ad esempio, un blocco share-intent posizionato a livello di tabella indica che la transazione intende inserire un blocco di condivisione su una pagina o riga della tabella. Impostare un blocco di intento a livello di tabella impedisce a un'altra transazione di acquisire successivamente un blocco esclusivo (X) sulla tabella che contiene quella pagina. I blocchi di intento possono migliorare le prestazioni perché SQL Server controlla il blocco di intento solo a livello di tabella per determinare se una transazione può acquisire in sicurezza un blocco su quella tabella. Invece di controllare i blocchi su ogni riga o pagina della tabella per determinare se una transazione può bloccare l'intera tabella.
I blocchi di intento includono la Condivisione dell'Intento (IS), l'Esclusiva dell'Intento (IX) e la Condivisione Esclusiva dell'Intento (SIX).
Descrizione della modalità di blocco
La Condivisione dell'Intento (IS) indica che l'intento della transazione è alcune, non tutte, delle risorse sottostanti nella gerarchia di lettura, posizionando S-lock su ciascuna risorsa.
Intent Exclusive (IX) indica che l'intento della transazione è modificare alcune, ma non tutte, le risorse sottostanti nella gerarchia posizionando un X-lock su ciascuna risorsa. IX è un superinsieme dell'IS.
La condivisione esclusiva con intento (SIX) indica che l'intento della transazione è leggere tutte le risorse sottostanti nella gerarchia e modificare alcune, ma non tutte, le risorse sottostanti imponendo lock IX su ciascuna risorsa. Consentire blocchi IS concorrenti sulle risorse di alto livello. Ad esempio, il blocco SIX di una tabella posiziona un lock SIX sulla tabella (permettendo locks IS concorrenti) e un lock IX sulla pagina attualmente modificata (un lock X sulla riga modificata). Sebbene ogni risorsa possa avere solo un blocco SIX per un certo periodo di tempo per impedire ad altre transazioni di aggiornare la risorsa, altre transazioni possono leggere le risorse sottostanti nella gerarchia acquisendo blocchi IS a livello di tabella.
Blocco esclusivo: Solo il programma che esegue l'operazione di blocco può utilizzarlo, e qualsiasi altra operazione su di esso non sarà accettata. Quando esegui un comando di aggiornamento dati, SQL Server utilizza automaticamente un blocco esclusivo. Quando esistono altri blocchi su un oggetto, non puoi aggiungere un blocco esclusivo.
Blocco condiviso: La risorsa bloccata dal blocco condiviso può essere letta da altri utenti, ma altri utenti non possono modificarla.
Blocco aggiornamento: Quando SQL Server è pronto ad aggiornare i dati, prima blocca l'oggetto dati in modo che i dati non possano essere modificati ma letti. Quando SQL Server determina di voler aggiornare i dati, sostituirà automaticamente il lock di aggiornamento con un lock esclusivo, e non potrà aggiungere un lock di aggiornamento quando esistono altri lock sull'oggetto.
2 . Dal punto di vista del programmatore: è diviso in blocco ottimista e blocco pessimista.
Blocco Ottimista: Si basa interamente sul database per gestire il funzionamento della serratura.
Serrature pessimistiche: I programmatori gestiscono la gestione dei lock su dati o oggetti stessi.
MS - SQLSERVER utilizza i locks per implementare un controllo pessimistico della concorrenza tra più utenti che effettuano modifiche contemporaneamente al database
3. La dimensione delle particelle della serratura
La granularità del blocco è la dimensione del bersaglio bloccato, la piccola granularità di blocco è alta concorrenza, ma l'overhead è grande, e la grande granularità di blocco è bassa concorrenza mentre il sovraccarico è piccolo
SQL Server supporta la granularità dei lock per righe, pagine, chiavi, intervalli di chiavi, indici, tabelle o database
Descrizione della risorsa
Identificatore di riga RID. Usato per bloccare una riga in una tabella singolarmente.
Blocco della fila chiave nell'indice. Utilizzato per proteggere l'intervallo di chiavi nelle transazioni serializzabili.
8 kilobyte (KB) di pagine dati o pagine indice.
Disco esteso Un insieme di otto pagine dati o pagine indice adiacenti.
Tabella L'intera tabella inclusi tutti i dati e gli indici.
Database DB.
4. La durata del tempo di blocco
La durata di una chiusa è il tempo necessario per proteggere la risorsa al livello richiesto.
Il tempo di mantenimento del lock condiviso utilizzato per proteggere le operazioni di lettura dipende dal livello di isolamento della transazione. Con il livello predefinito di isolamento delle transazioni READ COMMITTED, il blocco condiviso è controllato solo per la durata della pagina di lettura. In una scansione, il lucchetto non viene rilasciato fino a quando non viene acquisito nella pagina successiva della scansione. Se specifichi un prompt HOLDLOCK o imposti il livello di isolamento della transazione su REPEATABLE READ o SERIALIZABLE, il lock non viene rilasciato fino alla fine della transazione.
A seconda dell'opzione di concorrenza impostata per il cursore, il cursore può acquisire un blocco di scorrimento in modalità condivisa per proteggere l'estrazione. Quando è necessario un blocco a scorrimento, il blocco a scorrimento non viene rilasciato fino alla prossima estrazione o chiusura del cursore, a seconda di quale caso si verifichi per primo. Tuttavia, se specifichi un HOLDLOCK, il blocco scroll non viene rilasciato fino alla fine della transazione.
Il blocco esclusivo utilizzato per proteggere l'aggiornamento non sarà rilasciato fino alla fine della transazione.
Se una connessione tenta di acquisire un blocco che entra in conflitto con un blocco controllato da un'altra connessione, la connessione che tenta di ottenere il blocco verrà bloccata fino a:
Il blocco in conflitto viene rilasciato e la connessione acquisisce il blocco richiesto.
Il timeout della connessione è scaduto. Non esiste un intervallo di timeout di default, ma alcune app impostano intervalli di timeout per evitare attese indefinite
Cinque personalizzazioni delle serrature in SQL Server
1 Gestire gli stalli e impostare priorità di stalli
Il blocco è l'attesa infinita causata da più utenti che fanno domanda per blocchi diversi, perché il richiedente ha una parte del diritto di blocco e in attesa del blocco parziale di proprietà di altri utenti
Puoi usare il DEADLOCK_PRIORITY SET per controllare come la sessione reagisce in caso di uno sblocco. Se entrambi i processi bloccano i dati e ciascuno processo non può liberare il proprio lock finché l'altro non rilascia il proprio lock, si verifica una situazione di deadlock.
2 Gestire i timeout e impostare le durate dei timeout del blocco.
@@LOCK_TIMEOUT Restituisce l'impostazione attuale di timeout per il blocco della sessione corrente in millisecondi
L'impostazione SET LOCK_TIMEOUT permette all'applicazione di impostare il tempo massimo che l'istruzione aspetta per bloccare la risorsa. Quando il tempo di attesa dell'istruzione è superiore all'impostazione LOCK_TIMEOUT, il sistema annulla automaticamente l'istruzione bloccante e restituisce all'applicazione un messaggio di errore 1222 che indica che il timeout della richiesta di blocco è stato superato
esempio
Nel seguente esempio, il periodo di timeout del blocco è impostato a 1.800 millisecondi.
SET LOCK_TIMEOUT1800
3) Impostare il livello di isolamento delle transazioni.
4) Utilizzare suggerimenti di blocco a livello di tabella per le istruzioni SELECT, INSERT, UPDATE e DELETE.
5) Configurare la granularità di bloccaggio dell'indice
Puoi usare sp_indexoption stored procedure di sistema per impostare la granularità del lock per l'indicizzazione
6. Visualizza le informazioni della serratura
1 Eseguire l'EXEC SP_LOCK riportare informazioni sulla serratura
2 Premi Ctrl + 2 nell'analizzatore di query per vedere le informazioni sul lock
7. Precauzioni per l'uso
Come evitare gli stalli
1. Quando si utilizzano transazioni, cerca di accorciare il processo logico di elaborazione delle transazioni e invia o annulli le transazioni in anticipo.
2 Impostare il parametro di timeout di deadlock a un intervallo ragionevole, ad esempio: 3 minuti - 10 minuti; Dopo quel tempo, l'operazione verrà automaticamente abbandonata per evitare che il processo si blocchi;
3. Ottimizzare il programma, controllare ed evitare il fenomeno di blocco;
4. Testare attentamente tutti gli script e i SP prima della versione esatta.
5 Tutti i SP devono avere la gestione degli errori (tramite @error)
6 Non modificare il livello predefinito delle transazioni SQL SERVER. Non è consigliata la chiusura forzata
Risolvere il problema Come bloccare un database a tabelle a righe
8. Diverse domande sulle serrature
1 Come bloccare una riga di una tabella
IMPOSTARE TRANSACTIONISOLATION LEVEL READUNCOMMITTED
SELECT *FROM table ROWLOCKWHEREWHERE id = 1
2 Blocca una tabella nel database
SELEZIONA *DALLA tabella CON( HOLDLOCK )
Dichiarazione sulla serratura:
sybase:
Aggiornamento tabella: set col1 = col1 dove1= 0 ;
MSSQL:
seleziona col1 dalla tabella (tablockx) dove 1= 0 ;
oracle:
TABELLA DI BLOCCO IN MODALITÀ ESCLUSIVA ;
Dopo che il blocco è stato bloccato, nessun altro può usarlo finché l'utente bloccato non lo sblocca, e viene sbloccato con il commit o il rollback
Alcuni esempi ti aiutano a approfondire la tua impressione
Tabella di preparazione1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
A3 B3 C3
1) Blocco esclusivo
Crea due nuove connessioni
Esegui la seguente istruzione nella prima connessione
Inizio tran
Aggiorna Tabella 1
insieme: A= 'aa'
dove B= ' b2 '
aspetta il ritardo' 00:00:30' --aspetta 30 secondi
commit tran
Esegui la seguente istruzione nella seconda connessione
Inizio tran
seleziona *da Tabella1
dove B= ' b2 '
commit tran
Se le due istruzioni sopra vengono eseguite contemporaneamente, la query select deve attendere l'esecuzione dell'aggiornamento, cioè aspettare 30 secondi
2) Serratura condivisa
Esegui la seguente istruzione nella prima connessione
Inizio tran
seleziona *da Tabella1 blocchatura - La serratura viene aggiunta artificialmente alla serratura
dove B= ' b2 '
aspetta il ritardo' 00:00:30' --aspetta 30 secondi
commit tran
Esegui la seguente istruzione nella seconda connessione
Inizio tran
seleziona A,C dalla tabella 1
dove B= ' b2 '
Aggiorna Tabella 1
insieme: A= 'aa'
dove B= ' b2 '
commit tran
Se le due istruzioni sopra vengono eseguite contemporaneamente, la query select nella seconda connessione può essere eseguita
L'aggiornamento deve attendere la prima transazione per rilasciare il lock condiviso e convertirlo in un lock esclusivo prima di poter essere eseguito, cioè aspettare 30 secondi
3) Stallo
Aggiunta tabella2(D,E)
D E
d1 e1
d2 e2
Esegui la seguente istruzione nella prima connessione
Inizio tran
Aggiorna Tabella 1
insieme: A= 'aa'
dove B= ' b2 '
aspetta un ritardo' 00:00:30'
Tabella di aggiornamento2
set D= ' d5'
dove E= ' e1 '
commit tran
Esegui la seguente istruzione nella seconda connessione
Inizio tran
Tabella di aggiornamento2
set D= ' d5'
dove E= ' e1 '
aspetta il ritardo' 00:00:10'
Aggiorna Tabella 1
insieme: A= 'aa'
dove B= ' b2 '
commit tran
Allo stesso tempo, il sistema rileva il deadlock e interrompe il processo
Per aggiungere:
Suggerimenti per il blocco a livello di tabella supportati da SQL Server 2000
HOLDLOCK mantiene il blocco condiviso fino al completamento dell'intera transazione e dovrebbe essere rilasciato non appena l'oggetto bloccato non è più necessario, pari al livello di isolamento delle transazioni SERIALIZABLE
L'istruzione NOLOCK viene eseguita senza emettere un blocco condiviso, permettendo letture sporche, che corrispondono al livello di isolamento della transazione READ UNCOMMITTED
PAGLOCK utilizza più blocchi di pagina in cui viene utilizzato un blocco di tabella
READPAST permette al server SQL di saltare qualsiasi linea bloccata ed eseguire transazioni, e per i livelli di isolamento delle transazioni READ UNCOMMITTED, saltare solo i lock RID, non i blocchi di pagina, zona e tabella
ROWLOCK impone l'uso dei rowlock
TABLOCKX impone l'uso di un blocco esclusivo a livello di tabella, che impedisce a qualsiasi altra transazione di utilizzare la tabella durante la transazione
UPLOCK impone l'uso di aggiornamenti quando si legge una tabella senza un blocco condiviso
Blocco dell'app:
Un blocco applicativo è un blocco generato dal codice client, non un blocco generato direttamente da SQL Server
Due processi per gestire i blocchi applicativo
sp_getapplock Blocca risorse applicative
sp_releaseapplock Sblocca le risorse dell'applicazione
Nota: La differenza tra bloccare una tabella in un database
SELECT *FROM tabella WITH( HOLDLOCK ) Altre transazioni possono leggere la tabella, ma non possono aggiornare e cancellare
SELECT *FROM tabella WITH(TABLOCKX) Altre transazioni non possono leggere, aggiornare e eliminare la tabella
|
Precedente:Non c'era nessun punto finale che ascoltasse http://localhost:111/xxx.svc che c...Prossimo:Blocchi SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
