Eccentrica sparagli dopo

Buongiorno,
ho letto che la clausola NOLOCK serve per evitare l'errore "DEADLOCKED". La mia applicazione web mi restituisce il seguente errore "Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 543 601 LO SLOT 6 L/BERO via Ilbera". Attualmente la transazione utilizza "IsolationLevel.Serializable" per evitare letture sporche, se uso questa clausola vado ad annullare l'IsolationLevel, è corretto? La mia transazione fa 2 select seguite da 1 insert e da 2 update.
Grazie per l'eventuale aiuto

Ciao Giulia,
Mi dice che la sua transazione fa prima 2 select poi una insert e 2 update, c'è un motivo preciso per tenere le select dentro alla transazione?
Non vorrei che il lock di lettura che viene effettuato durante le query di select fosse quello che poi porta al deadlock, visto che non viene rilasciato se non dopo la fine della transazione.

Ciao, prima di tutto grazie per l'aiuto.
Provo a dettagliare meglio la mia situazione.

Apro transazione con isolationlevel = serializable
- eseguo stored numero uno
- eseguo stored numero due
- eseguo insert
- eseguo insert
Chiudo transazione

Stored numero 1
in totale esegue circa 10 select più 2 update e 2 insert. Ma quello che mi
ha portato ad umentare il livello di isolamento sono queste istruzioni

SELECT SOMMA= sum(A) FROM X WHERE data=@data AND id=@id AND id1=@id1
SELECT LIMITE = massimo FROM Y WHERE data=@data AND id=@id

IF SOMMA > LIMITE RETURN 1 -- Errore, fine transazione
Else
INSERT INTO ....


Più utenti che effettuavano questo blocco di operazioni sotto transazione
leggevano attraverso le SELECT valori sporchi che quindi portavano a
INSERT errate. Infatti se la SELECT non legge dati aggiornati rischio di
fare degli INSERT che non dovrei fare e quindi vado a superare il LIMITE

Grazie ancora

Ciao a tutti,

La clausola NOLOCK serve a evitare il deadlock perchè non blocca i record in fase di lettura.
Presumo che a te non serva, in quanto tu lavori dentro una transazione che normalmente blocca i record di cui fai insert o update.

Non sapendo cosa fanno le 10 select della SP 1, a occhio è sbagliato il livello di isolamento.

Facciamo che hai due transazioni (A e B). A parte per prima e inizia le 10 select.
B si trova bloccati (fino al COMMIT o il ROLLBACK fatto da A):
1) in lettura tutti i record su cui A ha fatto INSERT o UPDATE
2) in scrittura tutti i record letti da A
3) non può inserire record nelle tabelle usate da A se i record che inserirebbe "cadono" dentro la clausola WHERE specificata nelle select
In pratica B si trova tutto bloccato...

Io proverei con READ COMMITTED o meglio ancora REPEATABLE READ che non lascia leggere o scrivere dati non ancora committati da A ma permette a B di inserire record (quidi escludi il punto 3 di sopra). READ COMMITTED ti lascia fare l'UPDATE, quindi forse è meglio di no...

I record inseriti da B non sono visibili a A fino al commit, quindi se B non ha committato le tue select

SELECT SOMMA= sum(A) FROM X WHERE data=@data AND id=@id AND id1=@id1
SELECT LIMITE = massimo FROM Y WHERE data=@data AND id=@id

non li leggono. Stessa cosa per B, che le eseguirebbe senza leggere i dati inseriti da A.


Viceversa, se A committa prima delle select, le select fatte da B leggono anche i dati salvati da A. Uguale per A (se B finisce prima) che leggerebbe anche i dati salvati da B.

Sta a te vedere se può andare bene.

Quello che ti posso consigliare è velocizzare il più possibile le query (specialmente evitando i join e usando un EXISTS) e magari (se vedi che stanno troppo) creare indici appositi e specificarli nelle select...

In ogni caso, se ti serve altro, siamo qua!


LDM

Nuovamente grazie per il supporto.
Ho letto attentamente il post ma penso di non poter usare altri tipi di transazione.
Provo a dare maggiori dettagli:

SELECT SOMMA= sum(A) FROM X WHERE data=@data AND id=@id AND id1=@id1
SELECT LIMITE = massimo FROM Y WHERE data=@data AND id=@id

IF LIMITE is null LIMITE = massimo FROM Z WHERE data=@data AND id=@id


IF SOMMA > LIMITE RETURN 1 -- Errore, fine transazione
Else
2 SELECT DIVERSE per determinare un codice che sarà la mia chiave univoca per
INSERT INTO X

Se uso REPEATABLE READ temo che succeda quello che mi ha creato problemi e cioè:
il processo B che inizia subito dopo A legge SOMMA prima che il processo A abbia fatto la insert e quindi ho dei dati non reali.

B rischia di superare il limite...

E' corretto quello che dico?
Ho provato a baipassare il problema usando una specie di semaforo.

Prima di iniziare la stored inserisco in una tabella @data, @id e @id1 (le mie chiavi di ricerca). Questa tabella ha questi valori in chiave e gestisco l'errore tramite un try-catch.

Il processo A inserisce e inizia il ciclo con le stored, il processo B prova ma gli verrà restituito un errore gestito tipo "riprovare più tardi..."

Di nuovo grazie
Giulia

Salve! E provare con qualcosa tipo

DECLARE ciclo int;
SET ciclo = 1;

DECLARE tentativi int;
SET tentativi = 10;

declare errore int;
SET errore = 0;

qui apri la transazione serializable così viene aperta una volta sola


WHILE (ciclo = 1) BEGIN
BEGIN TRY

INSERT INTO TabellaPerLocK VALUES (1)

poi fai tutto quello che devi fare


DELETE FROM TabellaPerLocK

e alla fine fai commit o rollback

SET ciclo = 0;
END TRY
BEGIN CATCH
WAITFOR DELAY '00:00:05' -- Aspetto 5 secondi, magari mettere un valore leggermente maggiore del tempèo previsto per il completamento della SP
SET tentativi = tentativi -1;
if tentativi <= 0 BEGIN
ciclo = 0;
errore = 1;
END
END CATCH
END

IF errore = 1 BEGIN
rollback della transazione
RAISERROR('Riprovare più tardi') -- Eventualmente anche un codice numerico che viene passato al catch del chiamate (il programma)
END


TabellaPerLocK è una tabella che fa da semaforo, quindi appena SP A fa un insert la tabella viene bloccata, quindi tutti le altre SP (B) che cercano di inserire da un errore e entra nel catch.

Il while cicla finchè ciclo vale 1, e ciclo viene settato a 0 alla fine delle operazioni.
In questa maniera la tabella TabellaPerLocK viene bloccata dalla prima SP (A) e finchè non ha finito B resta in attesa.


nel begin catch puoi sapere l'errore tipo
print ERROR_NUMBER();
print ERROR_MESSAGE();

Non è detto che funzioni (non l'ho provato) ma potrebbe essere un idea...