Returning a value before committing transaction and (UPDLOCK, HOLDLOCK)

I need to check if a record exists – if yes: then returns its id, if not: creates a new record and returns its id. I am using WITH (UPDLOCK, HOLDLOCK) in SELECT to prevent from duplicates (it creates lock). I wonder if I should commit the transaction if a record exists in a database for realising the lock?

using (SqlConnection connection = new SqlConnection("..."))
{
    await connection.OpenAsync();
    using (var transaction = connection.BeginTransaction())
    {
        var locationId = await connection.QueryFirstOrDefaultAsync<int?>(
                "SELECT id 
                 FROM Locations WITH (UPDLOCK, HOLDLOCK)
                 WHERE regionId = @RegionId", new { RegionId = 1 }, transaction: transaction
            );

        if (locationId.HasValue)
        {
            //transaction.Commit(); // should I commit the transaction here?
            return locationId.Value;
        }

        var location = new Location()
        {
            Name = "test",
            RegionId = 1
        };

        var newLocationid = await connection.InsertAsync<int>(location, transaction);

        transaction.Commit();

        return newLocationid;                    
    }
}

Answer

should I commit the transaction here?

Yes. Otherwise it will roll back when the using block completes. That won’t matter in this particular case, but it’s better to be explicit. And if this transaction was part of a larger transaction, the whole thing would get rolled back.

Advertisement

Answer