Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

Mastering Distributed Transactions and TransactionScope: Questions and Answers

Q.1. How Does SQL Server Support Distributed Transactions?

Ans: The support for distributed transactions in SQL Server is facilitated by the use of the BEGIN DISTRIBUTED TRANSACTION statement. This statement employs the following syntax to initiate a T-SQL distributed transaction, which is managed by the MS DTC:

BEGIN DISTRIBUTED { TRAN | TRANSACTION }

[ transaction_name | @tran_name_variable ]

One straightforward approach to include isolated instances of the SQL Server Database Engine within a distributed transaction is by executing a distributed query that references a linked server. For instance, consider connecting to ServerB and running the following query:

DELETE FROM ServerB.TestDB.TestTable WHERE TestID = 1

Incorporating the above query into a distributed transaction is easily accomplished as shown below:

BEGIN DISTRIBUTED TRANSACTION
DELETE TestDB.TestTable WHERE TestID = 1
DELETE ServerB.TestDB.TestTable WHERE TestID = 1
COMMIT

However, a notable drawback in this implementation is that the second query does not allow for explicit enlistment. While this might seem inconsequential, it becomes significant when the second query involves a stored procedure called 'ServerC', which in turn calls ServerD. In such a scenario, all the entities become linked in a single, expensive transaction overseen by the same MS DTC on the initiating server.

To circumvent this issue, there are two ways to configure the default behavior to prevent the promotion of linked server queries to the MS DTC. Firstly, at the server level, you can use the T-SQL command:

sp_configure remote proc trans 0

Alternatively, at the connection level, you can achieve this with the following syntax:

SET REMOTE_PROC_TRANSACTIONS OFF

By implementing either of these approaches, the SQL Server Database Engine will manage transactions automatically when you use the BEGIN TRANSACTION statement. If the setting is set to 1 or ON, employing a BEGIN TRANSACTION statement involving linked servers will result in MS DTC involvement, but this represents an all-or-nothing approach.

Q.2. What is The Purpose of The System.EnterpriseServices Namespace? What Are Its Limitations?

Ans: The System.EnterpriseServices namespace has been utilized since the advent of the .NET Framework 1.0 to enable the participation in distributed transactions. Essentially, it wraps the COM+ infrastructure within the context of System.EnterpriseServices.

However, employing solutions based on EnterpriseServices posed certain challenges. Your operations needed to be implemented as class libraries, adorned with the TransactionOption attribute, strongly named, and then registered in the global assembly cache (GAC). These requirements introduced complexities in both debugging and deployment. Additionally, the TransactionOption attribute bound your operation's transactional behavior to specific predetermined values:

  • Disabled: The operation doesn't partake in transactions; this behavior is pre-defined.
  • NotSupported: The operation runs independently, detached from the transactional context.
  • Supported: The operation participates in an existing transaction if one is present. If no transaction exists, it won't initiate or create one.
  • Required: The operation demands a transaction. If none exists, a new one is initiated. If a transaction is ongoing, the operation joins it.
  • RequiresNew: The operation necessitates a transaction and establishes a fresh one exclusively for itself.

During the era of .NET Framework 1.0, enlisting on demand was infeasible, and the intricacies of debugging and deployment remained formidable. The .NET Framework 1.1 introduced a slightly improved solution through the ServiceConfig class. This approach, known as "services without components," eliminated the need for GAC registration or strong labeling of assemblies. Nonetheless, its adoption was constrained to Microsoft Windows 2003 and Windows XP, including Service Pack 2, thus limiting its scope. Subsequently, its applicability was extended to other Windows-based operating systems, yet it continued to remain relatively obscure. It's important to note that concepts like promotable enlistment could not be employed in this context.

If you desire a more comprehensive understanding of these limitations, you can explore in-depth insights through various Online SQL certification courses.

Q.3. Explaining The Behavior of a System.Transactions-based Transaction With an Example

Ans: To comprehend the behavior of a transaction built upon the System.Transactions framework, let's delve into a practical example. This illustrative scenario involves creating two databases and executing a query in each, all while encapsulated within a transaction. Follow the steps below to witness the behavior in action:Start by executing the script provided in Script establishes two databases named Test1 and Test2. Subsequently, it crafts a table called FromTable in Test1 and another table called ToTable in Test2. These tables comprise a single int column labeled Amount. The script inserts a row with a value of 100 into FromTable and another row with a value of 0 into ToTable.

CREATE DATABASE Test1
GO
USE Test1
GO
CREATE TABLE From Table (Amount int)
CO
INSERT INTO FromTable (Amount) VALUES (100)
CREATE DATABASE Test2
GO
USE Test2
GO
CREATE TABLE ToTable (Amount int)
GO
INSERT INTO ToTable (Amount) VALUES (0) GO

Create a C# console application named "DistributedTrans." For this endeavor, it's imperative to include the System.Transactions assembly, as it interfaces with the Transaction Management API. Add the System.Transactions component by right-clicking the project in the Solution Explorer, selecting "Add Reference," navigating to the.NET tab in the dialog, locating System.Transactions, and then double-clicking it.

In the file "Program.cs," insert the code snippet featured.

using System;
using System.Data;
using System.Data.SqlClient; using System. Transactions;
namespace DistributedTrans
{
class Program

static void Main(string[] args)
{
const string ConnStr1 =
"Data Source=(local): Initial Catalog=Test1; Integrated Security-SSPI;"; const string ConnStr2 =
"Data Source=(local); Initial Catalog=Test2; Integrated Security-SSPI;";
const string CndText1= "UPDATE FromTable SET Amount = Amount - 50"; const string CmdText2 = "UPDATE ToTable SET Amount = Amount + 50":
using (var tsc = new TransactionScope())
{
using (var conn1 = new SqlConnection (ConnStr1))
{
}
SqlCommand cmdl-conn1.CreateCommand(); cmd1.CommandText = CndText1;
conn1.Open();
cmd1.ExecuteNonQuery();
// Operation #1 is done, going to Operation #2
using (var conn2 = new SqlConnection (ConnStr2))
{
}
SqlCommand cmd2 = conn2.CreateCommand(); cmd2.CommandText = CmdText2;
conn2.Open();
cmd2.ExecuteNonQuery();
tsc.Complete();

The primary objective of this example is to execute two distinct queries—one in each database. In Test1, a query subtracts 50 from the FromTable, while the other query adds 50 to the ToTable. It's essential that both operations are executed within the confines of the same transaction.Before proceeding, ensure that the Distributed Transaction Coordinator (DTC) service is operational. Initiate this by clicking "Start" and entering "services" into the search. Locate "Distributed Transaction Coordinator" within the service list. If it's not active, right-click it and select "Start."Run the provided code. Two command objects, cmd1 and cmd2, function within a distributed transaction. If the second query encounters an issue, the first query is automatically rolled back. This process highlights the inherent safeguarding mechanisms.

Q.4. How to Develop Your Own Resource Manager?

Ans: Creating your own Resource Manager involves implementing the IEnlistmentNotification interface. This interface requires the implementation of specific methods that are invoked by the Transaction Coordinator (TC) at the appropriate stages of the two-phase execution process.

Here are the methods that IEnlistmentNotification mandates you to implement:

  • Commit: Notifies the Resource Manager (RM) that the transaction has been committed, and the changes made need to be made permanent by the RM.
  • Rollback: Informs the RM that the transaction has been rolled back. Subsequently, the RM reverts to its previous stable state.
  • Prepare: During the initial (prepare) phase of a distributed transaction, the Transaction Manager (TM) queries the participants about their readiness to commit. If the TM receives successful notifications from all participating RMs, the Commit methods are triggered.
  • InDoubt: This method is used when the TM loses contact with one or more participants during the operation, resulting in uncertainty about the transaction's status. In such cases, the application logic must decide whether to revert to a consistent state or remain in an inconsistent state.

Implementing Your Resource Manager

public class VolatileRM: IEnlistmentNotification
{
private string _whoAmI = ""; public VolatileRM(string whoAmI)
{
}
this._whoAmI - whoAmI;
private int _memberValue = 0; private int _oldMemberValue = 0; public int MemberValue
{
get
{
return this._memberValue;
}
set
{
Transaction tran Transaction.Current;
if (tran != null)
{
}
Console.WriteLine(
this._whoAmI + ": MemberValue setter EnlistVolatile"); tran.EnlistVolatile(this, EnlistmentOptions.None);
this._oldMemberValue = this._memberValue;
this._memberValue = value;
#region IEnlistmentNotification Members
public void Commit(Enlistment enlistment)
Console.WriteLine(this._whoAmI + ": Commit");
// Clear out oldMemberValue this._oldMemberValue = 0; enlistment.Done():
public void InDoubt (Enlistment enlistment)
{
}
Console.WriteLine(this._whoAmI + ": InDoubt");
enlistment.Done(:
public void Prepare(PreparingEnlistment preparingEnlistment)
{
}
Console.WriteLine(this._whoAmI + ": Prepare"); preparingEnlistment.Prepared();
public void Rollback(Enlistment enlistment)
}
Console.WriteLine(this._whoAmI + ": Rollback");
// Restore previous state
this._memberValue = this. old MemberValue;
this._oldMemberValue = 0;
enlistment.Done():
#endregion

Subsequently, the code defines two class-level variables, _memberValue and _oldMemberValue, along with a MemberValue property. This class is designed because System.Int32 cannot interact with an RM or preserve historical values for rolling back integers. The MemberValue property's getter exposes _memberValue, and the setter assigns a new value to _memberValue and enlists in the ongoing transaction. The _oldMemberValue stores the historical value for potential rollback scenarios:

private int _memberValue = 0;

private int _oldMemberValue = 0;

public int MemberValue

{

    get { return _memberValue; }

    set

    {

        Transaction tran = Transaction.Current;

        if (tran != null)

        {

            Console.WriteLine(tran._whoAmI + ": MemberValue setter - EnlistVolatile");

            tran.EnlistVolatile(this, EnlistmentOptions.None);

        }

        this._oldMemberValue = this._memberValue;

        this._memberValue = value;

    }

}

The code attempts to locate the current transaction using Transaction.Current, and then employs the EnlistVolatile method to enlist in the current transaction in a volatile manner. For this example, a volatile enlistment suffices. If dealing with a durable resource, the EnlistDurable method would be used instead. Finally, the code executes the logic of assigning the new value while retaining the old value.

Upon creating the class and its data, you need to link it to the implementation, enabling it to join a currently running transaction with the RM and perform appropriate actions based on received notifications. This is facilitated through the four methods mandated by the IEnlistmentNotification interface. The TM invokes the relevant methods (Commit, Rollback, Prepare, and InDoubt), passing a System.Transactions.Enlistment variable as a parameter. After successfully completing each step, the enlistment.Done() method is called to signal completion. The exception is the Prepare method, which receives a specialized Enlistment known as System.Transactions.PreparingEnlistment, inheriting from System.Transactions.Enlistment class.

Q.5. What Enlistment Methods does PreparingEnlistment Add?

Ans: PreparingEnlistment extends the Enlistment class and introduces the following methods:

  • ForceRollBack() or ForceRollBack(Exception): These methods notify the Transaction Manager (TM) about an error occurrence, indicating the desire of the participating Resource Manager (RM) to perform a rollback. Optionally, you can generate your own exception for this purpose.
  • Prepared(): This method informs the TM that the current RM has successfully completed its part of the transaction, specifically referring to the prepare phase within the two-phase commit process.
  • Byte[] RecoveryInformation: This method serves to supply essential information to the TM in scenarios of reenlistment, enabling a smooth recovery process in instances like RM crashes. As an alternative, you can also employ the base class method Done() to observe the transaction without active participation, akin to an onlooker. When Done() is invoked during the prepare phase, the TM skips the second phase (commit) of the two-phase notification process.

Q.6. How can The Resource Manager be Utilized Within Transactional Code?

Ans: Incorporating the RM into transactional code is remarkably straightforward. Merely envelop it within a TransactionScope, as demonstrated below:

var vrm = new VolatileRM("RM1");
Console.WriteLine("Member Value: " + vrm.MemberValue);

using (var tsc = new TransactionScope())
{
    vrm.MemberValue = 3;
    tsc.Complete();
}

Console.WriteLine("Member Value: " + vrm.MemberValue);

Upon executing this code, the resulting output should reflect the successful involvement of the resource manager in a transaction:

Member Value: 0
RM1: MemberValue setter – EnlistVolatile
RM1: Prepare
RM1: Commit
Member Value: 3

Evidently, the RM actively participates in both the prepare and commit phases of the two-phase commit process. To delve deeper into this, consider exploring an Online SQL certification for a more comprehensive understanding.

Q.7. How to Utilize a Resource Manager When a Rollback is Initiated by The Caller

Ans: Continuing from our previous example, let's explore how to adapt the code. Remove the comment from the tsc.Complete() statement and rerun the application. The anticipated output should appear as follows, signifying the engagement of the resource manager within a rolled-back transaction:

Member Value: 0
RM1: MemberValue setter – EnlistVolatile
RM1: Rollback
Member Value: 0

By omitting the comment on the tsc.Complete() statement, you replicate a scenario in which the application utilizing the RMs compels a rollback.It is considered a best practice to consistently position the tsc.Complete() statement at the conclusion of your TransactionScope blocks. This practice guarantees that if an exception arises anywhere within the block, the statement will not be executed.Rather than overseeing the prepare and commit phases, the code responds to the rollback phase, thereby preserving the member variable's initial value unchanged.

Q.8. How to Utilize the Resource Manager When it Initiates a Rollback.

Ans: Continuing from our previous example, let's delve into the process. Reintroduce the tsc.Complete statement into the code and make adjustments to the RM's Prepare method. Replace the Prepared method call with a ForceRollBack call, as demonstrated below:

public void Prepare(PreparingEnlistment preparingEnlistment)
{  
    Console.WriteLine(_whoAmI + ": Prepare");
    // preparingEnlistment.Prepared();
    preparingEnlistment.ForceRollback();
}

With these changes, the RM is now set to perform a rollback. When you execute the application with the tsc.Complete statement in place, it will throw a TransactionAbortedException exception due to the resource manager's rollback action.

For further insights, you might consider exploring Online SQL certification courses to enhance your understanding of this topic.

Q.9. How to Utilize the Resource Manager with Another Resource Manager.

Ans: Let's revert the RM's Prepare method to its original state to prevent a rollback. Now, let's modify the initial code within the host application to incorporate a second RM in a similar transaction, as depicted below:

var vrm = new VolatileRM("RM1");
var vrm2 = new VolatileRM("RM2");
Console.WriteLine("Member Value 1: " + vrm.MemberValue);
Console.WriteLine("Member Value 2: " + vrm2.MemberValue);

using (var tsc = new TransactionScope())
{
    // ...
    vrm.MemberValue = 3;
    vrm2.MemberValue = 5;
    tsc.Complete();
}

Console.WriteLine("Member Value 1: " + vrm.MemberValue);
Console.WriteLine("Member Value 2: " + vrm2.MemberValue);

As evident, this code simply introduces another instance of the RM into the transaction. Upon execution, the output demonstrates that two instances of the resource manager operate autonomously within the same transaction:

Member Value 1: 0
Member Value 2: 0
RM1: MemberValue setter – EnlistVolatile
RM2: MemberValue setter – EnlistVolatile
RM1: Prepare
RM2: Prepare
RM1: Commit
RM2: Commit
Member Value 1: 3
Member Value 2: 5

This example highlights that when multiple RMs are involved in a transaction, the appropriate prepare, commit, or rollback phases are executed for each RM in sequence. As an exercise, you could modify the RM code to invoke a ForceRollBack and observe the outcome if one RM initiates a rollback for the entire transaction.

The culmination of this exploration is especially intriguing. Since SqlConnection is also an RM, you can replicate this experiment with an instance of SqlConnection, a SqlCommand, and a database query, all executed within the same transaction enlisted by VolatileRM. To achieve this, modify the code as follows:

var vrm = new VolatileRM("RM1");
Console.WriteLine("Member Value: " + vrm.MemberValue);

const string connStr = "Data Source=(local);Initial Catalog=Test1;Integrated Security=SSPI;";
const string cmdText = "UPDATE FromTable SET Amount = Amount - 50";

using (var tsc = new TransactionScope())
{
    vrm.MemberValue = 3;

    using (SqlConnection conn1 = new SqlConnection(connStr))
    {
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = cmdText;
        conn1.Open();
        cmd1.ExecuteNonQuery();
    }

    tsc.Complete();
}

Console.WriteLine("Member Value: " + vrm.MemberValue);

Notice that now your VolatileRM is integrated into the same transaction as a database query. This synergy is unattainable with BEGIN DISTRIBUTED TRANSACTION, which interacts with database queries incapable of non-database tasks, except in cases where SQL CLR is utilized, leading to potential complexities.

Q.10. Creating a Basic SQL CLR Stored Procedure to Demonstrate The Behavior of SQL CLR Objects Within an Enclosing Transaction.

Ans: The construction of the stored procedure is quite straightforward, requiring no parameters. Its primary function is to insert a row into the "TestTable" table, as depicted below:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
    using (SqlConnection contextConn = new SqlConnection("context connection = true"))
    {
        SqlCommand insertCmd = contextConn.CreateCommand();
        insertCmd.CommandText = "INSERT INTO TestTable(TestColumn) VALUES(100)";
        contextConn.Open();
        insertCmd.ExecuteNonQuery();
        contextConn.Close();
    }
}

The provided code executes a SqlCommand through a context connection. This connection is employed to perform a basic INSERT operation, adding a row into the "TestTable." After being registered within SQL Server, the SQL CLR stored procedure can be invoked using the subsequent T-SQL command:

EXEC InsertRow

Upon execution, the stored procedure effectively inserts a row into the corresponding "TestTable" within the applicable database.However, an intriguing scenario emerges when this T-SQL code is enclosed within a BEGIN TRANSACTION/ROLLBACK block:

BEGIN TRANSACTION
    INSERT INTO TestTable(TestColumn) VALUES (200)
    EXEC InsertRow
ROLLBACK

Surprisingly, the row that would have been inserted by the "InsertRow" stored procedure is reverted during the ROLLBACK phase. Consequently, the "InsertRow" stored procedure effectively aligns itself with the encompassing transaction.

Q.11. How to Execute a ROLLBACK from a SQL Stored Procedure.

Ans: You can initiate a ROLLBACK operation from within a SQL CLR by utilizing the Transaction command, which allows you to access the current transaction state. By employing the Current property of the transaction and within its scope, you can execute the following rollback operation:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
    using (SqlConnection contextConn = new SqlConnection("context connection = true")) 
    {
        SqlCommand insertCmd = contextConn.CreateCommand();   
        insertCmd.CommandText = "INSERT INTO TestTable(TestColumn) VALUES(100)";
        contextConn.Open();
        insertCmd.ExecuteNonQuery();
        contextConn.Close();
    }
    Transaction.Current.Rollback();
}

To experiment further, modify the T-SQL block as indicated below, aiming to commit instead of rolling back:

BEGIN TRANSACTION
   INSERT INTO TestTable(TestColumn) VALUES (200)
   EXEC InsertRow
COMMIT

When attempting to execute this revised T-SQL code block, you will encounter an ambiguous exception message due to the transaction being rolled back. To address this, consider enclosing such errors within custom BEGIN TRY... CATCH or try/catch/finally blocks. Doing so provides a more detailed error message compared to what the framework offers.

Up to this point, you have been inserting a row into the database through a context connection. Now, make a slight adjustment to the connection string as shown below:

Data Source=RemoteMachine;Initial Catalog=OtherDB;Integrated Security=SSPI;

In this modified connection string, a subtle distinction is introduced. Previously, the T-SQL code was linked to the local database (the Test database in the case of the provided sample code). However, the updated connection string establishes a connection with an entirely different database located on a separate server. Remove the ROLLBACK statement from the SQL CLR stored procedure, rebuild, and redeploy it within the original database. Then execute the following T-SQL code block:

BEGIN TRANSACTION
   INSERT INTO TestTable(TestColumn) VALUES (200)
   EXEC InsertRow
COMMIT

This action extends the transaction's scope across two distinct databases. In essence, SQL CLR intelligently recognizes that the SQL CLR object is being invoked within a transaction and subsequently elevates that transaction to a distributed state. This is due to the involvement of an external resource. If you were to connect to an Oracle database, the SQL CLR function would remain active within the same transaction. To modify the default behavior of non-enlistment within the same transaction, add "enlist=false" to the fully qualified connection string. For a more comprehensive understanding, consider exploring Online SQL certification programs.

Q.12. What is TransactionScope? Explaining Its Usage With an Example.

Ans: TransactionScope is a powerful feature that simplifies the management of transactions in code. It handles intricate transaction details, often rendering the need to directly interact with the current transaction using Transaction.Current unnecessary. However, there are specific scenarios where direct handling of the transaction might be required:

  • Rolling back an external transaction through a call to Transaction.Current.Rollback.
  • Enlisting resources that haven't auto-enlisted for various reasons, achieved by utilizing Transaction.Current.EnlistDurable or EnlistVolatile.
  • Manually joining the ongoing transaction or altering default behavior by actively listening to different callbacks during the two-phase commit process.

In most other cases, it's recommended to write transactional code in a more straightforward manner, either by enclosing it in a using block for automated management or employing TransactionScope. This concept is easily demonstrated through the modification of SQL CLR stored procedure code, as illustrated below:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
    using (var tsc = new TransactionScope())
    {
        using (SqlConnection contextConn =
            new SqlConnection("context connection = true"))
        {
            SqlCommand insertCmd = contextConn.CreateCommand();
            insertCmd.CommandText = 
              "INSERT INTO TestTable(TestColumn) VALUES(100)";
            contextConn.Open();
            insertCmd.ExecuteNonQuery();
            contextConn.Close();
        }
        tsc.Complete();
    }
}

A noteworthy aspect to consider is that the TransactionScope encapsulates a context connection. While this is generally discouraged in SQL CLR, it's advantageous when dealing with multiple databases or resource managers, as TransactionScope neatly includes everything within a single transaction. The advantage of this approach lies in its transparent handling of active transactions. If an active transaction exists, TransactionScope employs it; if not, it initiates a new one. This enhances code readability and manageability.However, when the tsc.Complete() statement within the SQL CLR stored procedure code is omitted, errors arise. Building and deploying the stored procedure to the SQL Server database and testing it with transactional T-SQL code yield an anticipated error message:

Msg 8520, Level 16, State 1, Line 4
Internal Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed to commit: 0x8004d019(XACT_E_ABORTED).

This outcome is expected, signifying that the transaction was aborted due to the incomplete status of the TransactionScope. What may be surprising is the message originating from MS DTC. Despite a solitary database connection (the context connection), the transaction was elevated to MS DTC. This underscores a vital point concerning SQL CLR transactions: when operating within SQL CLR, the TransactionScope invariably promotes the transaction to MS DTC, even when solely utilizing context connections.

As a result, it's advisable to avoid TransactionScope in SQL CLR and instead favor alternatives like SqlTransaction or System.Transactions.Transaction. It's also beneficial to acquaint oneself with SQL CLR practices if solely employing context connections. For comprehensive knowledge, exploring online resources about SQL is highly recommended.

Q.13. Calling The SQL CLR Stored Procedure from a Console Application.

Ans: To seamlessly integrate your SQL CLR stored procedure with a console application, it's essential to handle the usage of a context connection wisely. By avoiding the use of TransactionScope within the SQL CLR stored procedure, you prevent unnecessary promotion of the transaction to MS DTC. Here's how your SQL CLR stored procedure should be structured:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertRow()
{
    using (SqlConnection contextConn = 
        new SqlConnection("context connection = true"))
    {
        SqlCommand insertCmd = contextConn.CreateCommand();
        insertCmd.CommandText = 
            "INSERT INTO TestTable(TestColumn) VALUES(100)";
        contextConn.Open();
        insertCmd.ExecuteNonQuery();
        contextConn.Close();
    }
}

Once you've created and deployed this stored procedure to a SQL Server database, proceed to build a console application named "BigBang" (available on the companion website). Ensure to include the necessary System references. Recall that you've previously constructed the transactions and the RM project called "YourOwnRM" in an earlier chapter. Now, insert the code provided into the Program.cs file of your console application:

// Combining SQL CLR and a customized resource manager within a single transaction.
// (Insert the code here)

Upon running the application, you should observe the following output, indicating the successful commitment of the resource manager's member value (along with the committed SQL CLR procedure InsertRow):

Member Value: 0
RM1: MemberValue setter – EnlistVolatile
RM1: Prepare
Member Value: 3

An important point to note is that the transaction remains within its scope and doesn't get elevated to MS DTC. As an additional exercise, consider inserting more resource managers (such as another SqlConnection) or a non-context connection within the SQL CLR stored procedure. By observing the behavior of the code, you can learn how System.Transactions seamlessly integrates operations across T-SQL, ADO.NET, and SQL CLR, promoting the transaction to MS DTC when necessary.

Conclusion

We explored the behavior of transactions built upon the System.Transactions framework through practical examples. We witnessed the initiation, preparation, commit, and rollback phases, highlighting how resource managers interact within these stages. Furthermore, we learned how to develop our own resource manager using IEnlistmentNotification and how to effectively utilize it within transactional code.

The TransactionScope class emerged as a powerful tool for simplifying transaction management. We examined its usage, including scenarios where direct interaction with the current transaction might be necessary. Through examples and insights, we gained a comprehensive understanding of handling transactions involving SQL CLR stored procedures and resource managers.

By mastering distributed transactions and TransactionScope, you're equipped to navigate complex transaction scenarios, optimize code efficiency, and ensure data integrity across different resources within the SQL Server ecosystem.

Trending Courses

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models

Upcoming Class

-0 day 14 Jun 2024

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

-0 day 14 Jun 2024

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

-0 day 14 Jun 2024

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

-0 day 14 Jun 2024

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

-0 day 14 Jun 2024

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

7 days 21 Jun 2024

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

1 day 15 Jun 2024

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 21 Jun 2024

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-0 day 14 Jun 2024

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

15 days 29 Jun 2024

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

28 days 12 Jul 2024

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

7 days 21 Jun 2024