Newsgroup: it.comp.software.database
Titolo: Transazioni nelle store procedure: consiglio

Salve,
ho un problema con le transazioni e store procedure, vorrei, 
gentilmente un parere e capire se sbaglio nel ragionamento.
La store procedure, per come è concepita dovrebbe essere una scatola 
nera, e quindi nel chiamarla dovrebbe produrre un risultato, 
garantendomi al termine dell'esecuzione la consistenza della banca 
dati. 
Ovvero, la store procedure deve essere atomica e quindi allo stesso 
livello di un'istruzione SQL.
Per fare questo secondo me è necessario prevedere all'INTERNO della 
store procedure una transazione e quindi decidere se le operazioni in 
essa contenuta devono andare a bun fine o meno.
Dall'esterno chiamando la stored, se i dati passati non determinano 
errore va a buon fine o meno. 
Tuttavia, posso dall'ESTERNO inserire una transazione e all'interno di 
essa chiamare la stored (insieme eventualmente ad altre operazioni). A 
seconda del risultato posso decidere se fare commit o rollback della 
transazione esterna. 
In questo caso anche se la store procedure interna è andata a buon 
fine, il rollback esterno dovrebbe garantirmi anche quello interno.
La domanda è: il ragionamento è corretto, posso utilizzare transazioni 
innestate, SQL Server e Oracle me lo consentono? 
C'è qualche controindicazione?
Che ne pensate?
Alcuni consigliano di non mettere le transazioni nelle store procedure 
delegando il controllo a ciò che sta fuori. Ma in questo modo la store 
procedure potrebbe determinare inconsistenza, ovvero non essere 
atomica.
C'è qualcosa che sbaglio nel ragionamento?
Inviato da 
www.mynewsgate.net

 C'è qualcosa che sbaglio nel ragionamento?
- le storeD procedure sono atomiche, nel senso che in caso di errore
viene fatto un rollback all'inizio della procedura
- puoi mettere dei savepoint e fare dei rollback to savepoint
- la commit va senz'altro gestita da applicazione (fanno eccezione le
procedure con pragma autonomus transaction); per fare altrimenti devi
settare un parametro a livello di sessione (alter session ...) oracle
di default non te lo permette (per quanto ne puoi scrivere quanti ti
pare sostanzialmente vengono ignorati)

 viene fatto un rollback all'inizio della procedura
Attenzione che l'affermazione è vera SOLO se viene impostata l'opzione SET 
XACT_ABORT in maniera esplicita ad ON oppure se viene implementato un 
sistema di gestione degli errori. Per impostazione predefinita in presenza 
di un errore NON VIENE ESEGUITO ALCUN ROLLBACK.
Data una tabella di esempio definita come
=====================
CREATE TABLE MyTable
(
    ID SMALLINT NOT NULL PRIMARY KEY
)
GO
=====================
posso andare a definire una stored procedure come
=====================
CREATE PROC MyProc_NoErrHandling
    @val1 SMALLINT,
    @val2 SMALLINT
AS
BEGIN TRAN
    INSERT MyTable VALUES (@val1)
    INSERT MyTable VALUES (@val2)
COMMIT TRAN
GO
=====================
e testo la sp utilizzando l'istruzione
=====================
EXEC MyProc_NoErrHandling 1, 2
=====================
che inserisce correttamente i record 1 e 2. Se eseguo il successivo comando
=====================
EXEC MyProc_NoErrHandling 3, 1
=====================
generando quindi un errore di violazione di pk sul secondo inserimento, 
eseguendo una query sulla tabella posso notare che il record con ID 3 è 
stato correttamente inserito e, quindi, deduco che non viene fatto alcun 
ROLLBACK (e la stessa cosa sarebbe accaduta se l'errore fosse avvenuto sulla 
prima istruzione dove la seconda sarebbe stata eseguita regolarmente). Per 
annullare l'intera operazione in presenza di un errore (garantendo quindi 
l'atomicità della mia sp) dovrei creare una stored procedure che al suo 
interno preveda un controllo di flusso simile a
=====================
CREATE PROC MyProc_WithErrHandling
    @val1 SMALLINT,
    @val2 SMALLINT
AS
DECLARE @err INT
BEGIN TRAN
INSERT MyTable VALUES (@val1)
SET @err = @@error
IF @err <> 0
 GOTO Annulla
INSERT MyTable VALUES (@val2)
SET @err = @@error
IF @err <> 0
 GOTO Annulla
COMMIT TRAN
RETURN 0
Annulla:
ROLLBACK TRAN
RETURN @err
GO
=====================
Come puoi vedere la complessità della sp aumenta notevolmente ma se voglio 
garantire l'atomicità della sp non posso fare diversamente. In questo caso 
l'istruzione che segue
=====================
EXEC MyProc_WithErrHandling 4, 5
=====================
fa esattamente il proprio dovere ed entrambi i record vengono inseriti. Nel 
caso in cui si verificasse un errore in una delle 2 istruzioni, anche 
l'altra istruzione (quella corretta) viene annullata e posso testare ciò 
impartendo il comando
=====================
EXEC MyProc_WithErrHandling 6, 1
=====================
dove posso verificare che anche il record "buono" (quello con ID 6) viene 
annullato a causa dell'errore sulla seconda istruzione. In alternativa posso 
utilizzare l'opzione SET XACT_ABORT ON per abilitare l'annullamento del 
batch in presenza di un errore e quindi posso creare una sp definita come
=====================
CREATE PROC MyProc_XACT_ABORT
    @val1 SMALLINT,
    @val2 SMALLINT
AS
SET XACT_ABORT ON
BEGIN TRAN
    INSERT MyTable VALUES (@val1)
    INSERT MyTable VALUES (@val2)
COMMIT TRAN
GO
=====================
la quale ha un comportamento simile alla MyProc_WithErrHandling ma con 
limitate possibilità di gestione degli errori.
Bye
-- 
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com
http://italy.mvps.org

 atomica.
Esattamente. Questa tua considerazione non fa una piega. 
Personalmente diffido molto di chi consiglia di gestire le transazioni
da codice invece che da db. 
Ciao.
Paolo.
-- 
Posted via Mailgate.ORG Server - 
http://www.Mailgate.ORG

 ha scritto:
Il meccanismo di controllo degli errori mi permette di avere un 
controllo 
a grana fine, però, per ogni istruzione SQL devo controllare che 
l'operazine abbia prodotto un errore.
In SQL Server non ho una sorta di try catch per beccare le eccezioni?
Inviato da 
www.mynewsgate.net

 In SQL Server non ho una sorta di try catch per beccare le eccezioni?
Non in SQL Server 2000 (e precedenti). Sarà disponibile in SQL Server 
2005...


 In SQL Server non ho una sorta di try catch per beccare le eccezioni?
Ciao Enigma,
SQL Server 2000 e versioni precedenti non permettono la definizione di un
Exception Handler che invece sarà disponibile in SQL Server 2005 tramite i
classici costrutti TRY...CATCH:
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH TRAN_ABORT
{ sql_statement | statement_block }
END CATCH
Ciao!
-- 
Lorenzo Benaglia


Ho provato ad eseguire XACT_ABORT,
ma succede una cosa molto strato.
Ho inserito il seguente codice
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
ALTER  PROC MyProc_XACT_ABORT
    @val1 SMALLINT,
    @val2 SMALLINT
AS
SET XACT_ABORT ON
BEGIN TRAN
 
    INSERT logs VALUES (@val2)
 INSERT logs1 VALUES (@val1)
print 'dopo'
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
dove la tabella logs esiste e logs1 no.
La eseguo da Query Analizer e effettivamete il primo insert non viene 
fatto.
Ma la transazione non viene chiusa, anzi la tabella logs resta bloccata 
fino a quando non esco dall'analizer.
Perchè?
Mi sembra un effetto molto strano.
Inviato da 
www.mynewsgate.net