SQL Server-transakcje cofają się po błędzie?

Masz rację, że cała transakcja zostanie wycofana. Powinieneś wydać polecenie, aby to cofnąć.

Możesz zawinąć to w TRY CATCH blok w następujący sposób

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

Z artykułu MDSN, Controlling Transactions (Database Engine) .

Jeśli w partii wystąpi błąd instrukcji run-time (taki jak naruszenie ograniczeń), domyślnym zachowaniem w silniku bazy danych jest cofanie tylko instrukcji, która wygenerowała błąd. Można zmienić to zachowanie używając instrukcji SET XACT_ABORT. Po uruchomieniu SET XACT_ABORT on każdy błąd instrukcji run-time powoduje automatyczne wycofanie bieżącej transakcji. Błędy kompilacji, takie jak na Błędy składniowe nie wpływa SET XACT_ABORT. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).

W Twoim przypadku cofnie pełną transakcję, gdy którykolwiek z wstawek zawiedzie.

Tutaj kod z otrzymaniem Komunikatu o błędzie pracy z 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

Jeśli jedna z wstawek nie powiedzie się lub jakakolwiek część polecenia nie powiedzie się, czy SQL server wycofuje transakcję?

Jeśli nie wycofa się, czy muszę wysłać drugie polecenie, aby go cofnąć?

Jasne, powinieneś wydać ROLLBACK zamiast COMMIT.

Jeśli chcesz zdecydować, czy zatwierdzić lub wycofać transakcję, powinieneś usunąć COMMIT zdanie z instrukcji, sprawdzić wyniki wstawek, a następnie Wydanie COMMIT lub ROLLBACK w zależności od wyników kontroli.