Buongiorno,
con la presente, sono a richiederVi una consulenza per un problema che si manifesta su un nostro software in asp, che gestisce le campagne outbound. Gli operatori si connettono a un database (inizialmente Access, ora SQL Server) e il programma li guida nei vari passi dell'intervista telefonica. Il problema si manifesta solo quando l'applicativo viene utilizzato contemporaneamente da più di 40-50 postazioni e riguarda la fase in cui gli operatori interrogano il database per ottenere un nominativo da chiamare. I nominativi si trovano infatti in un'unica tabella ("nominativitelecom") e può succedere che due o più operatori tentino di selezionare e aggiornare contemporaneamente lo stesso record. In questi casi, se le queries si trovano nel codice asp il sistema si blocca e il processo dllhost.exe sul server va a occupare tutta la CPU; se invece le queries vengono spostate in una stored procedure di sql server, il sistema si blocca meno frequentemente, ma più operatori ricevono il seguente messaggio di errore:
"transaction (process id 69) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim. rerun the transaction".
Il nostro problema è come evitare che si creino conflitti o deadlocks nel momento in cui gli operatori interrogano la tabella.
Nella prima versione del programma non vi era alcun lock nelle queries, e per evitare che più operatori tentassero di aggiornare lo stesso record il mio programmatore usava una variabile di applicazione nel codice asp, secondo uno schema come questo:
------------------------------------------------------
if application("locked") = 1 then
do until application ("locked") = 0
a = a + 1
loop
end if
application("locked") = 1
codice con le queries di aggiornamento del db
application("locked") = 0
------------------------------------------------------
Ma il ciclo do-loop (abbiamo provato anche con while-wend e for-next) creava evidentemente problemi e oltre un certo numero di postazioni operative il sistema si bloccava. Abbiamo quindi tolto la variabile di applicazione e provato in più modi a impostare dei lock all'interno delle queries nel codice asp. Ecco una delle versioni che abbiamo provato, dove "OpenDbRs" è una funzione che apre un recordset, mentre "recallornotes" e "nonotes" sono due views che identificano gruppi particolari di nominativi all'interno della tabella "nominativitelecom":
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set rs = OpenDbRs(cn, "select top 1 * from recallornotes where tmkoperator=" & iduser)
if not rs.eof then
cn.execute("update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7 where id in (select top 1 id from recallornotes where tmkoperator=" & iduser & ")")
finalquery = "select top 1 * from nominativitelecom where tmkstatus=7 and tmkoperator=" & iduser
else
cn.execute("update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7, tmkoperator = " & iduser & " where id in (select top 1 id from nonotes with (UPDLOCK) order by NewID())")
finalquery = "select top 1 * from nominativitelecom where tmkstatus=7 and tmkoperator=" & iduser
end if
set rs = OpenDbRs(cn, finalquery)
------------------------------------------------------------------------------------------------------------------------------------------
In pratica, il programma controlla se ci sono nominativi disponibili nella view recallornotes o nella view nonotes. Se sì, li aggiorna flaggando il campo tmkstatus a 7, in modo che quel nominativo non sia più accessibile da altri operatori. Quella che vede è solo una delle tante versioni che abbiamo tentato di utilizzare: abbiamo provato anche a mettere il "select" prima dell'update, a togliere l'updlock e a utilizzare altri tipi di lock (es. rowlock, holdlock), a togliere l'ordinamento per newid() (che ha lo scopo di selezionare un record random), ecc. ecc. Ma, in tutti i casi, il sistema continuava a bloccarsi quando più di 40-50 operatori lavoravano contemporaneamente.
Abbiamo provato anche a trasferire le queries in una stored procedure, nel modo seguente:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE sp_assign_name
@iduser int
AS
if exists(select top 1 * from recallornotes where tmkoperator= @iduser)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7 where id in (select top 1 id from recallornotes where tmkoperator=@iduser)
else
begin
if exists(select top 1 id from nonotes)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7, tmkoperator =@iduser where id in (select top 1 id from nonotes with (UPDLOCK) order by NewID())
end
GO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ma la sostanza non è cambiata: il sistema si blocca molto raramente, ma molto spesso esce il messaggio di errore "transaction (process id 69) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim. rerun the transaction".
Il nostro programmatore ha anche provato a intercettare l'eventuale errore all'interno della stored e, in quel caso, a fare un rollback della transazione, ma non è servito.
Credo che il problema di fondo sia il seguente: A e B tentano di aggiornare lo stesso nominativo. Se A lo prende per primo, B rimane in attesa. Ma quando il nominativo viene sbloccato, sarà stato aggiornato da A e non avrà più le caratteristiche che B richiedeva nella query iniziale.Cosa succede a quel punto?

Spero che i dettagli che ho fornito (la situazione è facilmente replicabile) siano sufficienti.

Grazie in anticipo