Client-side vs server-side database transactions

Question

In SQL Server transactions can either be performed in client or server code. A database expert once told me that client-side transactions should be avoided due to locking issues. Now, several years later, I want to know if that still holds true.

Certainly, there are scenarios where client-side transactions are necessary, but let's assume that a specific problem can be solved using either client or server side transactions.

In terms of performance, which of the two techniques is best? Why?

C# (client):

using (var transaction = new TransactionScope())
{
    // Insert data into database.

    transaction.Complete();
}

T-SQL (server):

CREATE PROCEDURE [dbo].[my_proc]
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TransactionCount [int]

    BEGIN TRY
        SET @TransactionCount = @@TRANCOUNT

        IF @TransactionCount = 0
            BEGIN TRANSACTION

        -- Insert data

        IF @TransactionCount = 0
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 AND @TransactionCount = 0 
            ROLLBACK TRANSACTION

        ; THROW
    END CATCH
END
GO

Answer 1

Server side transaction are more efficient. Think of it this way. If you start a transaction client-side then the server has to know that a transaction is in progress in order to commit or rollback. This also results in more communication between the client application and the database server.