Transaction does not return error on next statement after breaking.

[mgregur]

A transaction inserts two values into a table, and does a select on another table and returns no errors other than on commit.
After the commit finishes data is partially committed, only one row in Orders table. The transaction being broken by the select statement without a returned error in between the two insert statements.

package main

import (
	"database/sql"
	"fmt"
	"log"

	mssql "github.com/microsoft/go-mssqldb"
)

// The transaction inserts two values into Orders table, and returns no errors other than on commit.
// After the commit finisher data is partially committed, only one row in Orders table.
// The user has no way to tell there was an error in the transaction and half of it was rolled back other than committing,
// at which point it's too late.

// The error occurs because I sent the wrong data type to the query an integer rather than a string. You can fix the error by:
//  - change 12345 to "12345" on line 102
//  - remove second insert into Users, for user 'some_other_user' on line 76

// docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password1!password1' -p 1433:1433  mcr.microsoft.com/mssql/server:2019-latest

const (
	dbConnectionString = "sqlserver://sa:password1!password1@localhost:1433?database=master&encrypt=disable"
)

func main() {
	connector, err := mssql.NewConnector(dbConnectionString)
	if err != nil {
		log.Println(err)
		return
	}

	connector.SessionInitSQL = "SET XACT_ABORT ON"
	db := sql.OpenDB(connector)
	if err != nil {
		log.Fatalf("failed to connect to db: %v", err)
	}
	defer db.Close()

	// Create the necessary tables. They have no relation to each other.
	_, err = db.Exec(`
		IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Users' AND xtype='U')
		CREATE TABLE Users (
			id INT IDENTITY(1,1) PRIMARY KEY,
			username VARCHAR(50),
			code VARCHAR(50)
		)`)
	if err != nil {
		log.Fatalf("failed to create Users table: %v", err)
	}

	_, err = db.Exec(`
		IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Orders' AND xtype='U')
		CREATE TABLE Orders (
			id INT IDENTITY(1,1) PRIMARY KEY,
			order_desc VARCHAR(100)
		)`)
	if err != nil {
		log.Fatalf("failed to create Orders table: %v", err)
	}

	// Clean any previous data
	_, err = db.Exec("DELETE FROM Users")
	if err != nil {
		log.Fatalf("failed to delete from Users table: %v", err)
	}

	_, err = db.Exec("DELETE FROM Orders")
	if err != nil {
		log.Fatalf("failed to delete from Orders table: %v", err)
	}

	_, err = db.Exec(
		"INSERT INTO Users (username, code) VALUES (@username, @code)",
		sql.Named("username", "some_user"),
		sql.Named("code", "12345"),
	)
	if err != nil {
		log.Fatalf("failed to insert user: %v", err)
	}

	// This user is newer explicitly searched for but it's existence with the non int castable code column break the transaction.
	_, err = db.Exec(
		"INSERT INTO Users (username, code) VALUES (@username, @code)",
		sql.Named("username", "some_other_user"),
		sql.Named("code", "not_castable_to_int"),
	)
	if err != nil {
		log.Fatalf("failed to insert user: %v", err)
	}

	tx, err := db.Begin()
	if err != nil {
		log.Fatalf("failed to start transaction: %v", err)
	}

	_, err = tx.Exec("INSERT INTO Orders (order_desc) VALUES (@order_desc)", sql.Named("order_desc", "First order"))
	if err != nil {
		tx.Rollback()
		log.Fatalf("failed to insert first order: %v", err)
	}

	var userResult string
	// This query will break the transaction and rollback the first insert. It however does not indicate any error has occurred.
	err = tx.QueryRow("SELECT username FROM Users WHERE code = @code", sql.Named("code", 12345)).Scan(&userResult)
	log.Printf("user query, err: %v, result %v", err, userResult)
	if err != nil {
		tx.Rollback()
		log.Fatalf("failed to query user: %v", err)
	}

	_, err = tx.Exec("INSERT INTO Orders (order_desc) VALUES (@order_desc)", sql.Named("order_desc", "Second order"))
	if err != nil {
		tx.Rollback()
		log.Fatalf("failed to insert second order: %v", err)
	}

	err = tx.Commit()
	if err != nil {
		log.Printf("failed to commit transaction: %v", err)
	} else {
		fmt.Println("Transaction committed successfully")
	}

	ordersInDb, err := db.Query("SELECT * FROM Orders")
	if err != nil {
		log.Fatalf("failed to query orders: %v", err)
	}

	log.Printf("Orders in the db:")
	for ordersInDb.Next() {
		var id int
		var orderDesc string
		err = ordersInDb.Scan(&id, &orderDesc)
		if err != nil {
			log.Fatalf("failed to scan order: %v", err)
		}
		fmt.Printf("Order id: %d, Order desc: %s\n", id, orderDesc)
	}
	log.Printf("======END================")
}

[mgregur]

Note that this error is not present when doing the same thing within a stored procedure. When using the stored procedure the store procedure an exception is thrown before the second insert.

-- Drop the procedure if it already exists
IF OBJECT_ID('dbo.InsertTest', 'P') IS NOT NULL
    DROP PROCEDURE dbo.InsertTest;
GO

CREATE PROCEDURE dbo.InsertTest
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM Users;
    DELETE FROM Orders;

    INSERT INTO Users ([username], [code]) VALUES ('some_user', '12345'), ('some_other_user', 'non_parsable');
    
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Insert into Orders table
        INSERT INTO Orders ([order_desc])
            VALUES ('first order');
        PRINT 'First order inserted successfully';
        
        DECLARE @username varchar(50);
        SELECT @username = [username] FROM Users WHERE [code]=12345;
        PRINT 'Successfully fetched username: ' + @username;

        INSERT INTO Orders ([order_desc])
            VALUES ('second order');
        PRINT 'Second order inserted successfully';
        COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
        PRINT 'Exception'
        ROLLBACK TRANSACTION;
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;
GO

Exec dbo.InsertTest

-- zero rows returned, as it should be
select * from dbo.orders

Output: