Can I use SQL Server transaction without using a table?

Question

I have two servers that may both try to perform some action. I want to have some form of distributed lock so that only one will perform this action at a time.

I understand that I could use something like ZooKeeper, however that is a lot of infrastructure to setup for one small thing.

I was thinking I could create a table in SQL Server that I access in a transaction. This would work, but seems a but hacky/messy (what if someone later finds the table with nothing in it and decides to delete it etc.)

I was wondering if there was a way of creating 'named' transactions (my made up term) in T-SQL/SQL Server?

FWIW I am using Entity Framework for data access, but I could always use raw ADO.net if needed.

Answer 1

Turns out it is a built in thing

DECLARE @TranName VARCHAR(20);  
SELECT @TranName = 'MyTransaction';  

BEGIN TRANSACTION @TranName;  
USE AdventureWorks2012;  
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
WHERE JobCandidateID = 13;  

COMMIT TRANSACTION @TranName;  

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017#c-naming-a-transaction

And in C# land, using ADO.Net

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Start named  transaction.
    transaction = connection.BeginTransaction("MyTransaction");

    try
    {      
        //Do work you want locked globally
    }
    finally{
        transaction.Commit();
    }
}