Che errore è? E' grave?
fabriZio
[Microsoft][ODBC SQL Server Driver][SQL Server]Nelle risorse lock della
transazione (ID di processo 63) si è verificato un blocco critico (deadlock)
con un altro processo e la transazione è stata scelta come vittima.
Rieseguirla.
Devo preoccuparmi??
ciao
fabrizio
Lorenzo Benaglia
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Nelle risorse lock
> della transazione (ID di processo 63) si è verificato un blocco
> critico (deadlock) con un altro processo e la transazione è stata
> scelta come vittima. Rieseguirla.
Ciao fabriZio,
cominciamo a dare la definizione canonica di deadlock.
I BOL riportano:
"A deadlock occurs when two transactions have locks on separate objects and
each transaction requests a lock on the other transaction's object. Each
transaction must wait for the other to release the lock.
A deadlock can occur when several long-running transactions execute
concurrently in the same database. A deadlock also can occur as a result of
the
order in which the optimizer processes a complex query, such as a join, in
which you cannot necessarily control the order of processing."
In caso di deadlock SQL interviene "sbloccando" la situazione, eseguendo
queste azioni:
1) Effettua il rollback della transazione attiva da meno tempo;
2) Notifica l'applicazione che sta utilizzando la transazione "killata" con
l'errore 1205;
3) Permette all'altra transazione di proseguire.
E' possibile minimizzare i deadlock:
- utilizzando le risorse *sempre* nella stessa sequenza;
- minimizzando il numero di step in una transazione;
- creando transaziuoni "veloci", evitando per es. di eseguire query
particolarmente lunghe.
Vediamo ora come creare volontariamente un deadlock.
Lancia il Query Analizer e apri 2 connessioni al database Northwind.
Nella prima connessione incolla il seguente script:
<SCRIPT>
/*
** Connessione 1
*/
USE Northwind
GO
-- STEP 1
-- Imposto un Isolation Level "alto", in modo
-- che gli shared lock vengano mantenuti per
-- tutta la durata della transazione
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- STEP 2
-- Apro una transazione esplicita
BEGIN TRAN
-- STEP 3
-- Creo uno shared lock tutte le righe
-- della tabella dbo.Employees
SELECT * FROM dbo.Employees
-- STEP 4
-- Aggiorno una riga della tabella
-- dbo.Customers
UPDATE dbo.Customers
SET ContactName = 'Lorenzo Benaglia'
WHERE CustomerID = 'ALFKI'
-- STEP 5
-- Annullo la transazione
ROLLBACK TRAN
</SCRIPT>
Nella seconda connessione incolla il seguente script:
<SCRIPT>
/*
** Connessione 2
*/
USE Northwind
GO
-- STEP 1
-- Imposto un Isolation Level "alto", in modo
-- che gli shared lock vengano mantenuti per
-- tutta la durata della transazione
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- STEP 2
-- Apro una transazione esplicita
BEGIN TRAN
-- STEP 3
-- Creo uno shared lock tutte le righe
-- della tabella dbo.Customers
SELECT * FROM dbo.Customers
-- STEP 4
-- Aggiorno una riga della tabella
-- dbo.Employees
UPDATE dbo.Employees
SET LastName = 'Benaglia'
WHERE EmployeeID = 1
-- STEP 5
-- Annullo la transazione
ROLLBACK TRAN
</SCRIPT>
Ora esegui *attentamente* i seguenti passi.
1) Connessione 1: esegui lo STEP 1 per configurare l'Isolation Level;
2) Connessione 2: esegui lo STEP 1 per configurare l'Isolation Level;
3) Connessione 1: esegui lo STEP 2 per definire una transazione esplicita;
4) Connessione 2: esegui lo STEP 2 per definire una transazione esplicita;
5) Connessione 1: esegui lo STEP 3 che genera uno shared lock su tutte le
righe della tabella dbo.Employees;
6) Connessione 2: esegui lo STEP 3 che genera uno shared lock su tutte le
righe della tabella dbo.Customers;
7) Connessione 1: esegui lo STEP 4: SQL Server cercherà di aggiornare la
tabella dbo.Customers ma senza riuscirci, perché per eseguire un UPDATE
occorre avere un lock exclusive sull'oggetto, ma essendoci uno shared lock
causato dalla Connessione 2, SQL Server è costretto ad "attendere" che la
risorsa si liberi.
In questo momento *non* siamo ancora in uno stato di deadlock, ma
semplicemente di blocking.
8) Connessione 2: esegui lo STEP 4: SQL Server cercherà di aggiornare la
tabella dbo.Employees ma senza riuscirci, perché per eseguire un UPDATE
occorre avere un lock exclusive sull'oggetto, ma essendoci uno shared lock
causato dalla Connessione 1, SQL Server è costretto ad "attendere" che la
risorsa si liberi.
In questo momento entrambe le transazioni sono in blocking e quindi si
verifica il deadlock.
SQL Server deciderà di "ammazzare" una delle transazioni (effettuando un
rollback automatico), generando un'eccezione 1205, e consentendo all'altra
di proseguire.
Nel mio caso ha "ucciso" la transazione 1:
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 53) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
A questo punto puoi effettuare il rollback della transazione "sopravvissuta"
per evitare di inquinare inutilmente Northwind con il mio nome ;-)))
> Devo preoccuparmi??
Si, ti devi preoccupare di intercettare e gestire l'errore 1205, per esempio
riprovando una seconda volta ad eseguire la query oppure visualizzando
all'utente un messaggio di avvertimento.
Inoltre potresti seguire i consigli che ti ho dato per cercare di limitare
al massimo l'insorgenza di deadlock.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
UGIdotNET - http://www.ugidotnet.org
UGISS - http://www.ugiss.org
fabriZio
:))
fabrizio
"Lorenzo Benaglia" <lbenagl...@tin.it> ha scritto nel messaggio
news:b85mas$5q2oc$1...@ID-154627.news.dfncis.de...
Sy
> tabella dbo.Employees ma senza riuscirci, perché per eseguire un UPDATE
> occorre avere un lock exclusive sull'oggetto, ma essendoci uno shared lock
> causato dalla Connessione 1, SQL Server è costretto ad "attendere" che la
> risorsa si liberi.
> In questo momento entrambe le transazioni sono in blocking e quindi si
> verifica il deadlock.
> SQL Server deciderà di "ammazzare" una delle transazioni (effettuando un
> rollback automatico), generando un'eccezione 1205, e consentendo all'altra
> di proseguire.
> Nel mio caso ha "ucciso" la transazione 1:
-Volevo capire due cose...se possibile:
Se l'o Share mode esiste sulla tabella dovrebbe essere possibile ottenere un
Update se non sbaglio....;)
senza mandate in attesa !!!O no???
Existing granted mode
Requested mode IS S U IX SIX X
Intent shared (IS) Yes Yes Yes Yes Yes No
Shared (S) Yes Yes Yes No No No
Update (U) Yes Yes No No No No
-La seconda invece riguarda il livello di isolamento, ossia: se io aumento
il livello di isolamento,
aumento la granularità dei lock oppure solamente evito che vengano
rilasciati subito dopo la fine del loro
utilizzo anche se la transazione è non è finita???
Grazie e ciao
Luca Bianchi
news:OnuHvkZC...@TK2MSFTNGP11.phx.gbl...
Un "Update lock" puoi averlo anche in presenza di uno "shared lock" ma
l'update lock non ti fa aggiornare la risorsa. Ti assicura soltanto una
specie di "precedenza" nell'aggiornamento. Per aggiornare una risorsa è
indispensabile che un update lock divenga un blocco esclusivo.
> -La seconda invece riguarda il livello di isolamento, ossia: se io aumento
> il livello di isolamento,
> aumento la granularità dei lock oppure solamente evito che vengano
> rilasciati subito dopo la fine del loro
> utilizzo anche se la transazione è non è finita???
No, il livello di isolamento agisce SOLO sulla durata del lock. Per
modificare la granularità puoi impartire degli hint di query. Nell'esempio
che ha fatto Lorenzo (REPEATABLE READ) il livello di isolamento serviva a
far si che il blocco condiviso (lettura dei dati) venisse mantenuto fino al
termine della transazione cosa che invece non accade con il livello di
isolamento predefinito (READ COMMITTED) che rilascia i blocchi condivisi al
termine della lettura (i blocchi esclusivi vengono comunque mantenuti fino
al termine della transazione anche con READ COMMITTED)
> Grazie e ciao
Ciao...
--
Luca Bianchi
Lorenzo Benaglia
> Se l'o Share mode esiste sulla tabella dovrebbe essere possibile
> ottenere un Update se non sbaglio....;)
> senza mandate in attesa !!!O no???
> Existing granted mode
> Requested mode IS S U IX SIX X
> Intent shared (IS) Yes Yes Yes Yes Yes No
> Shared (S) Yes Yes Yes No No No
> Update (U) Yes Yes No No No No
Ciao Sy,
quella che riporti è la tabella che rappresenta la compatibilità tra i vari
tipi di locks.
Lo statement di UPDATE richiede in principio un Update lock nella
determinazione delle righe da aggiornare, che successivamente verrà eletto
ad Exclusive durante la modifica vera e prorpia dei dati.
Ti riporto uno stralcio da Inside SQL Server di Kalen Delaney:
"Update locks provide compatibility with other current readers of data,
allowing the process to later modify data with the assurance that the data
hasn't been changed since it was last read. An update lock is not sufficient
to allow you to change the data—all modifications require that the data
resource being modified have an exclusive lock. An update lock acts as a
serialization gate to queue future requests for the exclusive lock. (Many
processes can hold shared locks for a resource, but only one process can
hold an update lock.)"
> -La seconda invece riguarda il livello di isolamento, ossia: se io
> aumento il livello di isolamento,
> aumento la granularità dei lock oppure solamente evito che vengano
> rilasciati subito dopo la fine del loro
> utilizzo anche se la transazione è non è finita???
La seconda che hai detto :-)
"Lock Duration
The length of time that a lock is held depends primarily on the mode of the
lock and the transaction isolation level in effect. The default isolation
level for SQL Server is Read Committed. At this level, shared locks are
released as soon as SQL Server has read and processed the locked data. An
exclusive lock is held until the end of the transaction, whether it is
committed or rolled back. An update lock is also held until the end of the
transaction unless it has been promoted to an exclusive lock, in which case
the exclusive lock, as with all exclusive locks, remains for the duration of
the transaction. If your transaction isolation level is Repeatable Read or
Serializable, shared locks have the same duration as exclusive locks. That
is, they are not released until the transaction is over".
Trovi maggiori info nel paragrafo "SET TRANSACTION ISOLATION LEVEL"
URL:tsqlref.chm::/ts_set-set_74bw.htm sui Books Online.
> Grazie e ciao
Prego.