SQL Server: failed savepoint rollback hides the original error and leaves a stale transaction level, so later transactions never commit #61829

<?php

require __DIR__.'/vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as DB;

$config = [
    'driver' => 'sqlsrv',
    'host' => 'sqlserver',
    'port' => 1433,
    'database' => 'master',
    'username' => 'sa',
    'password' => 'Repro_Passw0rd!',
    'trust_server_certificate' => true,
];

$capsule = new DB;
$capsule->addConnection($config);
$capsule->setAsGlobal();

DB::unprepared("IF DB_ID('repro') IS NULL CREATE DATABASE repro");
$capsule->addConnection(['database' => 'repro'] + $config);
$capsule->getDatabaseManager()->purge('default');

DB::unprepared(<<<'SQL'
    DROP TABLE IF EXISTS tags;
    DROP TABLE IF EXISTS guarded;
    CREATE TABLE tags (id int IDENTITY PRIMARY KEY, name nvarchar(100) NOT NULL);
    CREATE UNIQUE INDEX tags_name ON tags (name);
    INSERT INTO tags (name) VALUES (N'existing');
    CREATE TABLE guarded (id int IDENTITY PRIMARY KEY, value int NOT NULL);
SQL);
DB::unprepared(<<<'SQL'
    CREATE OR ALTER TRIGGER guarded_reject_negative ON guarded AFTER INSERT AS
    BEGIN
        IF EXISTS (SELECT 1 FROM inserted WHERE value < 0)
        BEGIN
            RAISERROR('value must not be negative', 16, 1);
            ROLLBACK TRANSACTION;
        END
    END
SQL);

/**
 * Run $inner inside a nested transaction, report what the caller sees, then show whether the
 * connection can still open a transaction.
 */
function scenario(string $title, Closure $inner): void
{
    echo "\n=== {$title}\n";

    try {
        DB::transaction(function () use ($inner) {
            DB::transaction($inner);
        });
        echo "no exception\n";
    } catch (Throwable $e) {
        echo 'caught:            '.get_class($e).': '.$e->getMessage()."\n";
        echo 'previous:          '.($e->getPrevious() ? get_class($e->getPrevious()).': '.$e->getPrevious()->getMessage() : 'none')."\n";
    }

    echo 'transactionLevel(): '.DB::connection()->transactionLevel()."\n";
    echo 'server @@TRANCOUNT: '.DB::selectOne('SELECT @@TRANCOUNT AS c')->c."\n";

    // A later, unrelated unit of work on the same connection (e.g. the next job in a queue worker).
    $marker = 'written after: '.$title;
    try {
        DB::transaction(fn () => DB::table('tags')->insert(['name' => substr($marker, 0, 100)]));
        echo "next transaction(): returned normally\n";
    } catch (Throwable $e) {
        echo 'next transaction(): '.get_class($e).': '.$e->getMessage()."\n";
    }

    // Start the next scenario from a fresh connection, then check the later write was committed.
    $GLOBALS['capsule']->getDatabaseManager()->purge('default');
    $persisted = DB::table('tags')->where('name', substr($marker, 0, 100))->exists();
    echo 'its write persisted: '.($persisted ? 'yes' : 'NO (never committed)')."\n";
}

scenario('Control: statement-level error (duplicate key) inside a nested transaction', function () {
    DB::table('tags')->insert(['name' => 'existing']);
});

scenario('MERGE error 8672: upsert source repeats a key that already exists', function () {
    DB::table('tags')->upsert([['name' => 'existing'], ['name' => 'existing']], 'name');
});

scenario('Trigger issues ROLLBACK TRANSACTION', function () {
    DB::table('guarded')->insert(['value' => -1]);
});