Getting Cannot roll back trans2. No transaction or savepoint of that name was found on SQL server after #48637

[crissi]

Laravel Version

From v10.29.0
Tested in v10.29.0 and v10.39.0, both fail

PHP Version

8.1.2

Database Driver & Version

Microsoft SQL Server 2022

Description

From Laravel/framework, v10.29.0 I am getting error Cannot roll back trans2. No transaction or savepoint of that name was found. when running code:

 DB::transaction(function () use ($partner, $globalLogin, &$apiToken) {
            $apiToken = ApiToken::firstOrCreate([
                'agreement_number' => $globalLogin->agreement_number,
                'api_user_id' => $partner->id
            ], [
                'token' => '111111'
            ]);
        });

Laravel/framework v10.28.0 works fine.

The error only happens if no row exist in the database for the query.

If I remove the transaction, my code generates no errors.

It is after the revival of this one:
#48637
Also failed last time this was in the code base

Steps To Reproduce

 DB::transaction(function () use ($partner, $globalLogin, &$apiToken) {
            $apiToken = ApiToken::firstOrCreate([
                'agreement_number' => $globalLogin->agreement_number,
                'api_user_id' => $partner->id
            ], [
                'token' => '111111'
            ]);
        });

Run code similar to this, with no rows that match.

[mpyw]

Cannot reproduce in the following environment. Could you provide the minimal reproducible docker-based example repository?

Versions and Environment

• PHP: php:8.2-cli (Docker)
• Laravel: 10.39.0
• SQL Server: mcr.microsoft.com/mssql/server:2019-latest (Docker)
• CPU Architecture: arm64

.env

DB_CONNECTION=sqlsrv
DB_HOST=db
DB_PORT=1433
DB_DATABASE=example
DB_USERNAME=SA
DB_PASSWORD=Passw0rd
DB_TRUST_SERVER_CERTIFICATE=true

config/database.php

// ...
        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            // 'encrypt' => env('DB_ENCRYPT', 'yes'),
            'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
        ],
// ...

compose.yaml

services:
  php:
    build: .
    volumes:
      - .:/project
    depends_on:
      - db

  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Passw0rd
    ports:
      - "1433:1433"

  db-init:
    image: mcr.microsoft.com/mssql/server:2019-latest
    network_mode: service:db
    command: bash -c 'until /opt/mssql-tools/bin/sqlcmd -U sa -P Passw0rd -Q "CREATE DATABASE example"; do echo retry && sleep 1; done'
    depends_on:
      - db

Dockerfile

FROM php:8.2-cli

ENV ACCEPT_EULA=Y

# Install prerequisites required for tools and extensions installed later on.
RUN apt-get update \
    && apt-get install -y apt-transport-https gnupg2 libpng-dev libzip-dev nano unzip \
    && rm -rf /var/lib/apt/lists/*

# Install prerequisites for the sqlsrv and pdo_sqlsrv PHP extensions.
# Some packages are pinned with lower priority to prevent build issues due to package conflicts.
# Link: https://github.com/microsoft/linux-package-repositories/issues/39
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && echo "Package: unixodbc\nPin: origin \"packages.microsoft.com\"\nPin-Priority: 100\n" >> /etc/apt/preferences.d/microsoft \
    && echo "Package: unixodbc-dev\nPin: origin \"packages.microsoft.com\"\nPin-Priority: 100\n" >> /etc/apt/preferences.d/microsoft \
    && echo "Package: libodbc1:amd64\nPin: origin \"packages.microsoft.com\"\nPin-Priority: 100\n" >> /etc/apt/preferences.d/microsoft \
    && echo "Package: odbcinst\nPin: origin \"packages.microsoft.com\"\nPin-Priority: 100\n" >> /etc/apt/preferences.d/microsoft \
    && echo "Package: odbcinst1debian2:amd64\nPin: origin \"packages.microsoft.com\"\nPin-Priority: 100\n" >> /etc/apt/preferences.d/microsoft \
    && apt-get update \
    && apt-get install -y msodbcsql18 mssql-tools18 unixodbc-dev \
    && rm -rf /var/lib/apt/lists/*

# Retrieve the script used to install PHP extensions from the source container.
COPY --from=mlocati/php-extension-installer /usr/bin/install-php-extensions /usr/bin/install-php-extensions

# Install required PHP extensions and all their prerequisites available via apt.
RUN chmod uga+x /usr/bin/install-php-extensions \
    && sync \
    && install-php-extensions bcmath ds exif gd intl opcache pcntl pcov pdo_sqlsrv redis sqlsrv zip

# Downloading composer and marking it as executable.
RUN curl -o /usr/local/bin/composer https://getcomposer.org/composer-stable.phar \
    && chmod +x /usr/local/bin/composer

# Setting the work directory.
WORKDIR /project

ENTRYPOINT []
CMD ["tail", "-F", "/dev/null"]

app/Console/Commands/TestFirstOrCreate.php

Based on default migration files and model files.

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use App\Models\User;
use Illuminate\Support\Facades\Log;

class TestFirstOrCreate extends Command
{
    protected $signature = 'test:first-or-create';

    public function handle(): void
    {
        Artisan::call('migrate:fresh --seed');

        DB::table('users')->truncate();
        $user = User::query()->firstOrCreate(
            ['email' => 'test@example.com'],
            ['name' => 'Test User', 'password' => '123'],
        );
        Log::channel('stderr')->info('User created or found: ' . $user->id);


        DB::table('users')->truncate();
        DB::transaction(function () {
            $user = User::query()->firstOrCreate(
                ['email' => 'test@example.com'],
                ['name' => 'Test User', 'password' => '123'],
            );
            Log::channel('stderr')->info('User created or found: ' . $user->id);
        });

        DB::table('users')->truncate();
        DB::transaction(function () {
            DB::transaction(function () {
                $user = User::query()->firstOrCreate(
                    ['email' => 'test@example.com'],
                    ['name' => 'Test User', 'password' => '123'],
                );
                Log::channel('stderr')->info('User created or found: ' . $user->id);
            });
        });
    }
}

---

root@264dad5a6617:/project# ./artisan test:first-or-create
[2023-12-29 16:58:24] local.INFO: User created or found: 1  
[2023-12-29 16:58:24] local.INFO: User created or found: 1  
[2023-12-29 16:58:24] local.INFO: User created or found: 1  

[crissi]

Thanks man, for trying, will try to make a reproducible build in the start of new year when I am back the office!

[driesvints]

@crissi feel free to re-open an issue once you have something to reproduce. Please mention @mpyw when you do. Thanks