Delayed durability support

[rgroenewoudt]

Is your feature request related to a problem? Please describe.

SQL Server 2014 supports delayed durability. This requires a slight different SQL for the commit: COMMIT WITH (DELAYED_DURABILITY = ON)

Describe the solution you'd like

A property in SqlTransaction, for example bool DelayedDurability

Describe alternatives you've considered

Manually executing the SQL BEGIN TRANSACTION and COMMIT is the alternative but it would be nice if it's builtin.

[cheenamalhotra]

Thanks for filing an issue for this request. We'll investigate and see if we can support it sometime in future.

[Grauenwolf]

Here are the two pieces that appear to be relevant.

//Pre-20005

        // For Transactions
        public const string TRANS_BEGIN = "BEGIN TRANSACTION";
        public const string TRANS_COMMIT = "COMMIT TRANSACTION";
        public const string TRANS_ROLLBACK = "ROLLBACK TRANSACTION";
        public const string TRANS_IF_ROLLBACK = "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION";
        public const string TRANS_SAVE = "SAVE TRANSACTION";

//2005 and later

        internal enum TransactionManagerRequestType
        {
            GetDTCAddress = 0,
            Propagate = 1,
            Begin = 5,
            Promote = 6,
            Commit = 7,
            Rollback = 8,
            Save = 9
        };

Looking at the TDS spec I don't see anything about DELAYED_DURABILITY in the transaction manager section.

This means that it will need custom handling for DELAYED_DURABILITY. Instead of calling _parser.TdsExecuteTransactionManagerRequest, it will need to call _parser.TdsExecuteSQLBatch.

Threading this through is going to be quite difficult, which probably explains why no one has taken up the ticket.

[rgroenewoudt]

Shouldn't this be added to the TDS spec and forwarded to the SQL Server team?

[roji]

@Grauenwolf aren't normal and distributed ("transaction manager") transactions being confused above? Delayed durability transactions don't seem to be very relevant for distributed transactions (where they always seem to be fully durable, see table).

In other words, isn't this simply about adding an option to the SqlTransction.Commit API which would cause SqlClient to send COMMIT WITH (DELAYED_DURABILITY = ON)?

[vonzshik]

@roji I'm afraid he's correct. Every transaction request (commit, rollback, etc) goes through SqlServer's transaction manager.

https://github.com/dotnet/SqlClient/blob/a4f18ca2403598518c3b7aae9715d6722bf692da/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlInternalTransaction.cs#L220-L236

https://github.com/dotnet/SqlClient/blob/a4f18ca2403598518c3b7aae9715d6722bf692da/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs#L983-L1008

https://github.com/dotnet/SqlClient/blob/a4f18ca2403598518c3b7aae9715d6722bf692da/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs#L1140-L1141

https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/0fb28ba5-ddcb-4d02-95c3-aa5b05ec6092

And currently transaction manager request lacks a field to pass DELAYED_DURABILITY.