L’introduzione delle stored procedure (SP) ha permesso di spostare buona parte della logica dell’applicazione nel DBMS, apportando notevoli benefici nello sviluppo e nella manutenzione dell’applicazione stessa. L’aspetto forse più evidente delle stored procedure è quello prestazionale, poiché il codice in esso contenuto è compilato ed ottimizzato dal DBMS e lo stesso piano d'esecuzione della SP è riusato da altre sessioni concorrenti. L’impiego più efficiente della rete rende le architetture basate su SP notevolmente scalabili. Infatti, al momento della richiesta di esecuzione di una SP, il client invia al server unicamente i parametri di input della SP piuttosto che l’intera istruzione T-SQL, producendo in tal modo un traffico di rete minore. Dal punto di vista ingegneristico, le SP garantiscono una forte centralizzazione della manutenzione della logica dell’applicazione e una distribuzione degli aggiornamenti a dir poco immediata, dal momento che modificare il codice di una SP non richiede alcun intervento sul client, il quale si aspetta soltanto di trovare lo stesso nome di procedura e stessi nomi e tipi di parametri nel database a cui è connesso. Attraverso le SP è possibile anche implementare un meccanismo di sicurezza dei dati – incapsulando definitivamente la logica racchiusa nel codice delle SP – evitando così l’interazione impropria con i dati da parte degli utenti non autorizzati. Infatti, è possibile concedere ad un utente di database il diritto d’esecuzione di una SP indipendentemente dai diritti d’accesso alle tabelle interessate, assegnati allo stesso utente. SQL Server permette di avere fino a 32 livelli di nidificazione (nesting) di SP che consentono di incapsulare in ogni procedura singole operazioni secondo i dettami della progettazione strutturata. È molto meglio scrivere N procedure articolate in una struttura “chiamante/chiamato” piuttosto che scrivere un’unica procedura che racchiude in sé l’intero codice T-SQL. La prima soluzione è più vantaggiosa per la creazione, il test e la manutenzione e garantisce il riuso più oculato dei piani di esecuzione da parte del DBMS. Ad ogni modo occorre fare sufficiente attenzione alla consistenza dei dati trattati nella SP, poiché al di là dei vincoli d’integrità referenziale applicati nel database, un controllo superficiale degli errori generati nell’esecuzione di una stored procedure può dar luogo a transazioni non consistenti. Lo scopo del presente articolo è analizzare gli strumenti a disposizione di un DBA SQL Server per la gestione degli errori in T-SQL, fino a giungere a un modello efficace di gestione e diagnostica degli errori nelle SP.
[ Transazioni e consistenza dei dati ]
Nel trattare il concetto di consistenza dei dati di un database, il pensiero va innanzitutto alla definizione dei vincoli di integrità referenziale e in secondo luogo alla salvaguardia delle transazioni implicite ed esplicite. Quelle implicite sono rappresentate dalle singole istruzioni di modifica dei dati (INSERT, UPDATE, DELETE). In presenza di transazione implicita, al verificarsi di un errore, sia esso dell’utente (ad esempio violazione di vincoli) o del sistema (ad esempio indisponibilità del sever), il DBMS si preoccupa di fare il roll back dell’istruzione, ovvero riportare lo stato dei dati all’istante immediatamente precedente all’esecuzione non riuscita, mentre se non ci sono intoppi sarà eseguito il commit della transazione. In caso di transazione implicita, la gestione della transazione viene delegata al DBMS ed essa avviene in modo del tutto trasparente all’utente, mentre attraverso le transazioni esplicite, il DBA può creare un’unica unità di lavoro (atomic unit of work) composta da istruzioni multiple che la logica dell’applicazione vuole che siano completate tutte insieme. Le transazioni esplicite devono essere dichiarate esplicitamente, racchiudendo le istruzioni coinvolte nella transazione tra un’istruzione di BEGIN TRAN[SACTION] (che ne determina l’inizio) e una COMMIT TRAN[SACTION] o ROLLBACK TRAN[SACTION] a seconda che si voglia rispettivamente dare effetto o meno a tutte le istruzioni precedenti che seguono l’istruzione di BEGIN TRAN. Ad eccezione degli errori di sistema (system failure) che causano un roll back della transazione (sia essa implicita che esplicita), il DBA dovrà preoccuparsi di controllare il verificarsi dell’errore sollevato durante una qualsiasi delle istruzioni contenute nella transazione esplicita, per ordinarne il roll back;, altrimenti sarà stato del tutto inutile aver esplicitato la transazione, poiché l’esecuzione del codice T-SQL seguente continuerà ad essere processato. In altre parole, nell’ambito di un batch o di una transazione, un errore non di sistema causa il fallimento della sola istruzione che genera l’errore senza condizionare le altre istruzioni. Anche le SP non si sottraggono a questo aspetto spesso frainteso: il fatto che la transazione sia per definizione una singola unità di lavoro lato server, non vuol dire che un errore generato da un’istruzione causi il roll back automatico della transazione. Tuttavia, impostando a ON la variabile XACT_ABORT, si ottiene questo comportamento da SQL Server (vedi Listato seguente). SET XACT_ABORT ON BEGIN TRAN /*Istruzione 1*/ INSERT INTO authors (au_id, au_lname, au_fname, phone, contract) VALUES (‘000-00-0000’,’Alberto’,’Grossi’,’801826-0752’,1) /*Istruzione 2*/ INSERT INTO authors (au_id, au_lname, au_fname,phone, contract) VALUES (‘000-00-0000’,’Franco’,’Cirri’,’800 800-0751’,1) COMMIT TRAN /*Istruzione 3*/ UPDATE authors SET contract=0 GO Attenzione, però, ai batch che includono più transazioni, in quanto XACT_ABORT influenza l’intero batch interrompendo l’esecuzione del batch al verificarsi del primo errore. Di conseguenza ogni altra transazione che seguisse quella al cui interno è stato generato l’errore non sarebbe mai eseguita. Nell’esempio del Listato 1, supponendo che l’Istruzione_2 violi un vincolo di chiave primaria, ne risulterebbe che non solo l’Istruzione 1 non avrebbe effetto, ma anche che l’Istruzione 3 non sarebbe processata. Se a prima vista XACT_ABORT può sembrare una soluzione veloce ed efficace da implementare nelle nostre SP, essa non è la migliore, poiché non consente un controllo dell’errore e quindi non dà la possibilità di specificare una reazione a un errore specifico. Inoltre, tale impostazione non ha effetto sugli errori di sintassi generati a run-time dall’esecuzione di una stringa costruita dinamicamente attraverso il comando EXECUTE (‘Stringa_Sql’). Si noti come tali errori sfuggano al controllo sintattico e si verifichino unicamente durante l’esecuzione.
[ Nested Transaction ]
Ogni SP può contenere infinite chiamate ad altre SP, mentre può chiamare sé stessa ricorsivamente fino a raggiungere 32 livelli di nidificazione. Anche le transazioni possono essere nidificate, sia attraverso SP nidificate che in un'unica sequenza di istruzioni T-SQL inclusa in uno o più batch. Un qualsiasi ROLLBACK TRAN provoca il roll back a tutti i livelli della struttura delle transazioni nidificate, mentre soltanto il COMMIT TRAN del blocco più esterno dà valore a tutti i commit di qualsiasi livello. Per tenere sotto controllo i livelli di transazione è possibile interrogare la funzione di sistema @@TRANCOUNT che viene incrementata ad ogni BEGIN TRAN e decrementata da ogni COMMIT o ROLLBACK TRAN. Si noti che solo quando @@TRANCOUNT è uguale a 1 ha effetto il commit. Dopo un ROLLBACK TRAN sarà indispensabile controllare che il valore restituito da @@TRANCOUNT sia diverso da 0, per evitare di effettuare ulteriori COMMIT o ROLLBACK TRAN delle transazione esterne – che altimenti solleverebbero un errore numero 3903 (che denota appunto l’assenza di una transazione aperta). Allo scopo di rendere più leggibile una struttura di transazioni nidificate, è buona norma dare un nome a ciascuna transazione. Se si sceglie di seguire questa norma, sarà bene ricordare che è possibile specificare il nome della transazione per ogni BEGIN e COMMIT TRAN e solo per il ROLLBACK TRAN più esterno, altrimenti verrà generato un errore numero 6401.
[ Un modello di gestione degli errori ]
Un’alternativa più laboriosa ed efficace a XACT_ABORT ON è rappresentata dall’uso congiunto della funzione @@ERROR e delle istruzioni RETURN, GOTO e RAISERROR. La funzione di sistema @@ERROR ha lo scopo di catturare il numero dell’errore intercorso nell’esecuzione di un’istruzione T-SQL. Se il valore restituito è uguale a 0, significa che è andato tutto liscio. Nel valutare il valore restituito da @@ERROR occorre fare attenzione al fatto che il suo valore viene alterato dall’esecuzione di una qualsiasi altra istruzione T-SQL che la segue (per intenderci anche un IF). Ne consegue che è buona norma assegnarne sempre il valore a una variabile, in modo da poterne valutare il contenuto anche in seguito, e soprattutto interrogare la funzione subito dopo ogni istruzione di interazione col database: INSERT INTO … SELECT @ERROR = @@ERROR IF @ERROR <> 0 ROLLBACK TRAN RETURN ordina l’uscita incondizionata da un batch e restituisce alla procedura chiamante il numero intero specificato (esempio RETURN 1). GOTO fa saltare il flusso d’esecuzione ad un’etichetta specificata, mentre RAISERROR restituisce al client un messaggio d’errore definito dall’utente, con la possibilità di registrarlo nel log degli eventi del sistema. Supponiamo di avere una procedura sp_A che chiama in ordine sp_B e sp_C. Per semplicità le procedure chiamate si intendono identiche (vedi Listato seguente). /* Procedura chiamata */ CREATE PROC sp_B AS DECLARE @ERROR int BEGIN BEGIN TRAN B INSERT INTO … SET @ERROR = @@ERROR IF @ERROR <> 0 GOTO ERRORS COMMIT TRAN B RETURN 0 ERRORS: RAISERROR (‘Procedure sp_B failed’,16,1) WITH LOG COMMIT TRAN B RETURN @ERROR END GO /* Procedura chiamante */ CREATE PROC sp_A AS DECLARE @ERROR int BEGIN BEGIN TRAN A EXEC @ERROR = sp_B IF @ERROR <> 0 GOTO ERRORS EXEC @ERROR = sp_C IF @ERROR <> 0 GOTO ERRORS COMMIT TRAN A RETURN 0 ERRORS: ROLLBACK TRAN A RAISERROR (‘Procedure sp_A failed’,16,1) WITH LOG RETURN @ERROR END GO Il modello di gestione errori proposto presenta, dal punto di vista delle transazioni, una transazione esplicita in ciascuna delle tre SP ed un solo ROLLBACK TRAN nella sp_A più esterna, mentre le sp_B e sp_C interne eseguono in ogni caso un COMMIT TRAN, che sortirà il solo effetto di decrementare il numero di transazioni attive. Quindi, sia il roll back che il commit sono delegati alla sp_A più esterna, dove il flusso di esecuzione giunge per mezzo dei due RETURN presenti nelle procedure chiamate. Inoltre, i RETURN comunicano il numero di errore a sp_A, che è quindi in grado di decide la sorte della transazione dell’intera struttura nidificata. Si noti il modo in cui viene recuperato il valore restituito dal RETURN durante l’esecuzione delle procedure interne – che avviene attraverso il comando EXEC[UTE]. In presenza di un errore, RAISERROR consente la comunicazione al client di un messaggio ad-hoc, definito dall’utente, e la registrazione dello stesso nel log degli eventi, attraverso l’opzione WITH OPTION. In questo modo fornisce al DBA uno strumento di diagnostica veloce e facile da implementare.
[ Conclusioni ]
L’inerzia della conoscenza è uno dei mali incurabili dell’informatica. In altre parole, quanto più consolidata è una tecnologia, tanto più difficile risulta modificarla, anche quando i benefici della neo-tecnologia sono palesi e evidenti anche alle menti più conservatrici. L’architettura a due livelli è una dimostrazione esemplare di tale fenomeno, tenuto conto che è sicuramente il tipo di architettura software più comune nelle applicazioni multi-utente orientate ai dati. Sovente le stored procedure non vengono sfruttate appieno ma relegate ad assolvere funzioni particolarmente laboriose e lunghe senza poterne sfruttare le qualità prestazionali, ad esempio ignorando la possibilità di nidificazione. Inoltre, laddove si fa un uso considerevole di SP, è bene non sottovalutare il controllo degli errori che potrebbero causare gravi inconsistenze dei dati e che sarebbero difficilmente rintracciabili senza un metodo di diagnostica efficace.
[ Bibliografia ]
[1] K. Delaney - – "Inside Microsoft SQL Server 2000", Mondadori Informatica, /2000
[2] L. Braidi - – "Database Design", Tecniche Nuove,/ 2004 [3] G. Drapers – “IF Statements and Stored Procedures Performance”, SQL Server Magazine/January 2005 – InstantDoc ID 44717
Nota: l'articolo è stato precedentemente pubblicato sul N. 63 di Visual Basic Journal (Mar/Apr '05)
|
©Copyright 2005 - xplayn.org