SQL Server: le transazioni si ripristinano in caso di errore?

Abbiamo un’app client che esegue alcuni SQL su SQL Server 2005 come quelli seguenti:

BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; 

Viene inviato da un comando stringa lungo.

Se uno degli inserimenti ha esito negativo o una qualsiasi parte del comando non riesce, SQL Server ripristina la transazione? Se non esegue il rollback, devo inviare un secondo comando per ripristinarlo?

Posso fornire informazioni specifiche sull’api e sul linguaggio che sto utilizzando, ma penso che SQL Server dovrebbe rispondere allo stesso modo per qualsiasi lingua.

È ansible set xact_abort on prima della transazione per assicurarsi che SQL si ripristini automaticamente in caso di errore.

Hai ragione nel senso che l’intera transazione verrà ripristinata. È necessario impartire il comando per ripristinarlo.

È ansible avvolgere questo in un blocco TRY CATCH come segue

 BEGIN TRY BEGIN TRANSACTION INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN -- Transaction Success! END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1) END CATCH 

Dall’articolo MDSN, Controllo delle transazioni (Motore di database) .

Se un errore di istruzione run-time (come una violazione del vincolo) si verifica in un batch, il comportamento predefinito nel Motore di database è di eseguire il rollback solo dell’istruzione che ha generato l’errore. È ansible modificare questo comportamento utilizzando l’istruzione SET XACT_ABORT. Dopo l’esecuzione di SET XACT_ABORT ON, qualsiasi errore di dichiarazione di runtime provoca un rollback automatico della transazione corrente. Gli errori di compilazione, come gli errori di syntax, non sono influenzati da SET XACT_ABORT. Per ulteriori informazioni, vedere SET XACT_ABORT (Transact-SQL).

Nel tuo caso arretrerà la transazione completa quando uno degli inserimenti fallirà.

Qui il codice con il messaggio di errore che funziona con MSSQL Server 2016:

 BEGIN TRY BEGIN TRANSACTION -- Do your stuff that might fail here COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE() DECLARE @ErrorSeverity INT = ERROR_SEVERITY() DECLARE @ErrorState INT = ERROR_STATE() -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH 

Se uno degli inserimenti ha esito negativo o una qualsiasi parte del comando non riesce, il server SQL ripristina la transazione?

No non lo fa.

Se non esegue il rollback, devo inviare un secondo comando per ripristinarlo?

Certo, dovresti pubblicare ROLLBACK invece di COMMIT .

Se si desidera decidere se eseguire il commit o il rollback della transazione, è necessario rimuovere la frase COMMIT dall’istruzione, controllare i risultati degli inserimenti e quindi emettere COMMIT o ROLLBACK base ai risultati del controllo.