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.

SqlClient/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlInternalTransaction.cs

Lines 220 to 236 in a4f18ca

	internal void Commit()
	{
	using (TryEventScope.Create("SqlInternalTransaction.Commit | API | Object Id {0}", ObjectID))
	{
	if (_innerConnection.IsLockedForBulkCopy)
	{
	throw SQL.ConnectionLockedForBcpEvent();
	}
	_innerConnection.ValidateConnectionForExecute(null);
	// If this transaction has been completed, throw exception since it is unusable.
	try
	{
	// COMMIT ignores transaction names, and so there is no reason to pass it anything. COMMIT
	// simply commits the transaction from the most recent BEGIN, nested or otherwise.
	_innerConnection.ExecuteTransaction(SqlInternalConnection.TransactionRequest.Commit, null, IsolationLevel.Unspecified, null, false);

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs

Lines 983 to 1008 in a4f18ca

	internal override void ExecuteTransaction(TransactionRequest transactionRequest, string name, System.Data.IsolationLevel iso, SqlInternalTransaction internalTransaction, bool isDelegateControlRequest)
	{
	if (IsConnectionDoomed)
	{ // doomed means we can't do anything else...
	if (transactionRequest == TransactionRequest.Rollback
	|| transactionRequest == TransactionRequest.IfRollback)
	{
	return;
	}
	throw SQL.ConnectionDoomed();
	}
	if (transactionRequest == TransactionRequest.Commit
	|| transactionRequest == TransactionRequest.Rollback
	|| transactionRequest == TransactionRequest.IfRollback)
	{
	if (!Parser.MARSOn && Parser._physicalStateObj.BcpLock)
	{
	throw SQL.ConnectionLockedForBcpEvent();
	}
	}
	string transactionName = (null == name) ? string.Empty : name;
	ExecuteTransaction2005(transactionRequest, transactionName, iso, internalTransaction, isDelegateControlRequest);
	}

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs

Lines 1140 to 1141 in a4f18ca

	_parser.TdsExecuteTransactionManagerRequest(null, requestType, transactionName, isoLevel,
	ConnectionOptions.ConnectTimeout, internalTransaction, stateObj, isDelegateControlRequest);

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.