Transaction Processing in T-SQL, transaction processing in ADO. net, implicit transaction processing in the LINQ to SQL, Distributed Transaction Processing

Test Database

Database Name: Test

Database Table Name: Users

Table Structure:

Column name
Data Type
Allowed to be empty
Note
 
USERSID
Int
False
Primary Key, auto-incrementing ID
 
Username
Varchar (50)
False
User name, unique constraint
 
Password
Varchar (50)
False

InArticleBefore starting, we must first make it clear that a transaction is a single unit of work. If a transaction succeeds, all data modifications made in the transaction will be committed and become a permanent component of the database. If the transaction encounters an error and must be canceled or rolled back, all data modifications are cleared. That is to say, when we use SQL Server's query analyzer to execute a rowCode"Insert into T values (x, y, z)", the data is successfully added to the database, but this does not mean that transactions are not used here, instead, the system automatically handles common operations (I guess it is a proxy mode ).

The SQL Server database provides the following transaction modes:

Each separate statement of the automatically committed transaction is a transaction.

Each explicit transaction starts with a begin transaction statement and ends with a commit or rollback statement.

The implicit transaction is implicitly started when the previous transaction is completed, but each transaction is still explicitly completed using a commit or rollback statement.

Batch-level transactions can only be applied to multiple active result sets (MARS). The explicit or implicit transaction initiated by the transact-SQL statement in the Mars session becomes a batch-level transaction. When the batch processing is completed, the batch transaction that is not submitted or rolled back is automatically rolled back by SQL Server.

First: Transaction Processing in T-SQL

The code below creates an explicit transaction for a T-SQL statement

-- Start a transaction
Begin transaction
-- Use try... Catch structure catch exception
Begin try
-- Insert two data entries (same user name)
Insert into [test]. [DBO]. [users]
([Username]
, [Password])
Values
('Zhang san', '123 ')
Insert into [test]. [DBO]. [users]
([Username]
, [Password])
Values
('Zhang san', '123 ')
Commit transaction -- submit a transaction
End try
-- If an exception occurs, enter the catch code segment.
Begin catch
Rollback transaction -- roll back the transaction
Select error_message () as [Message] -- output error message
End catch

The preceding Code fails after execution. The error message is: "The unique key constraint 'uq _ users_username' is violated '. Duplicate keys cannot be inserted in the object 'dbo. users ".

Note that in SQL Server, if the transaction is not explicitly declared, the system will allocate a recessive transaction. When the database engine instance executes any of the following statements for the first time, a transaction is automatically started:

ALTER TABLE
Insert
Create
Open
Delete
Revoke
Drop
Select
Fetch
Truncate table
Grant
Update

What is the transaction processing in ADO. Net?

 

2nd. process transactions in ADO. net.

The following code demonstrates how to use the sqlclient component to create an explicit transaction:

Using (sqlconnection connection = new sqlconnection (connectionstring ))
{
Connection. open ();
Sqlcommand command = connection. createcommand ();
Sqltransaction transaction;
// Start a local transaction
Transaction = connection. begintransaction ();
// Before starting a local transaction, You need to assign the connection object and transaction object to the command object.
Command. Connection = connection;
Command. Transaction = transaction;
Try
{
Command. commandtext =
"Insert into [test]. [DBO]. [users] ([username], [Password]) values ('zhang san', '123 ')";
Command. executenonquery ();
Command. commandtext =
"Insert into [test]. [DBO]. [users] ([username], [Password]) values ('zhang san', '123 ')";
Command. executenonquery ();
// Try to submit the transaction
Transaction. Commit ();
Console. writeline ("all records have been submitted to the Database ");
}
Catch (exception ex)
{
Console. writeline ("thrown exception type: {0}", Ex. GetType ());
Console. writeline ("exception message: {0}", Ex. Message );
// Try to roll back the transaction
Try
{
Transaction. rollback ();
}
Catch (exception ex2)
{
// This catch block will handle any errors that may occur on the server and cause rollback failure. If the connection is closed.
Console. writeline ("rollback exception type: {0}", ex2.gettype ());
Console. writeline ("message: {0}", ex2.message );
}
}
}

The preceding Code fails after execution. The error message is: "The unique key constraint 'uq _ users_username' is violated '. Duplicate keys cannot be inserted in the object 'dbo. users ".

After reading ASP. NET transactions, see implicit transaction processing in LINQ to SQL.

3rd. Implicit Transaction Processing in LINQ to SQL.

The following code demonstrates how to use the LINQ to SQL technology for transaction processing:

Using (testdatacontext DB = new testdatacontext ())
{
// Output the T-SQL statement in the console
DB. log = console. out;
// Create two users objects (note that the user names are the same)
Users U1 = new users ()
{
Username = "zhangsan ",
Password = "123"
};
Users U2 = new users ()
{
Username = "zhangsan ",
Password = "456"
};

Try
{
DB. Users. insertallonsubmit (new users [] {u1, u2 });
// Try to submit the transaction
DB. submitchanges ();
Console. writeline ("all records have been submitted to the Database ");
}
Catch (exception ex)
{
Console. writeline ("thrown exception type: {0}", Ex. GetType ());
Console. writeline ("exception message: {0}", Ex. Message );
}
}

The preceding Code fails after execution. The error message is: "The unique key constraint 'uq _ users_username' is violated '. Duplicate keys cannot be inserted in the object 'dbo. users ".

In the Code, when submitchanges is called, LINQ to SQL checks whether the call is within the scope of transaction or whether the transaction attribute (idbtransaction) is set to a local transaction started by the user. If none of the two transactions are found, the local transaction (idbtransaction) is started by the LINQ to SQL statement and the generated SQL command is executed by the transaction. When all SQL commands have been successfully executed, LINQ to SQL submits local transactions and returns. This is the "implicit transaction" of LINQ to SQL ".

After introducing the above three types of transaction processing, let's look at what the Distributed Transaction processing is like?

4th Distributed Transaction Processing

This article will introduce the title content "distributed transaction processing" as a kind of operation: how to treat the commit process of two datacontext objects as a transaction?

You may not be too clear about this issue. The following Code demonstrates how this problem occurs:

// create two testdatacontext objects
testdatacontext db1 = new testdatacontext ();
testdatacontext DB2 = new testdatacontext ();
// create two users objects (note that the user name is the same)
users U1 = new users ()
{< br> username = "James ",
Password = "123"
};
Users U2 = new users ()
{< br> username = "James ",
Password = "456"
};
try
{
// Add two users objects to different testdatacontext objects.
db1.users. insertonsubmit (U1);
db2.users. insertonsubmit (U2);
// try to submit a transaction
db1.submitchanges ();
db2.submitchanges ();
console. writeline ("all records have been submitted to the Database");
}< br> catch (exception ex)
{< br> console. writeline ("thrown exception type: {0}", Ex. getType ();
console. writeline ("exception message: {0}", Ex. message);
}< br>

The preceding Code fails after execution. The error message is: "The unique key constraint 'uq _ users_username 'is violated '. Duplicate keys cannot be inserted in the object 'dbo. users ". However, it is obvious that a piece of data is successfully added to the database-the data submitted when db1.submitchanges () is executed.

So how can we make this process meet our requirements? The following code demonstrates this:

// Create two testdatacontext objects
Testdatacontext db1 = new testdatacontext ();
Testdatacontext DB2 = new testdatacontext ();
// Create two users objects (note that the user names are the same)
Users U1 = new users ()
{
Username = "zhangsan ",
Password = "123"
};
Users U2 = new users ()
{
Username = "zhangsan ",
Password = "456"
};
// Add two users objects to different testdatacontext objects.
Db1.users. insertonsubmit (U1 );
Db2.users. insertonsubmit (U2 );
// Use the transactionscope object to make the code block a transactional code.
Using (transactionscope rs = new transactionscope ())
{
Try
{
// Try to submit the transaction
Db1.submitchanges ();
Db2.submitchanges ();
Rs. Complete ();
Console. writeline ("all records have been submitted to the Database ");
}
Catch (exception ex)
{
Console. writeline ("thrown exception type: {0}", Ex. GetType ());
Console. writeline ("exception message: {0}", Ex. Message );
}
}

When you decide to use the SQL Server Distributed Transaction processing mode, you must start the Windows service item named Distributed Transaction Coordinator. You can start it in the Service Manager (Start Menu-run-services. you can also directly start it (Net start MSDTC) in the command prompt ).

At this time, the two datacontext objects contained in the transactionscope object code block will be in the transaction when they execute the submitchanges method. If an exception occurs at this time, all previous operations will be rolled back. That is to say, if an exception occurs in the transaction scope (from initializing the transactionscope object to calling its dispose method), the transaction will end and roll back to the initial state. This means that even if the complete method is executed, exceptions that occur after this will cause the transaction to fail. Therefore, do not add code that may cause transaction failure between the complete method and the dispose method.

If the transactionscope object successfully executes the complete method, all data will be submitted to the database. Note that if the complete method of the transactionscope object is not executed in the transactionscope object code block, the transaction will also terminate and roll back to the initial state.

Similarly, the transactionscope object code block applies to access other code-level databases. For example, you can place the submission process of multiple sqlconnection objects in the same transactionscope object code block, you can easily control the transaction execution mode.