Co-occurrence and co-occurrence transactions

I know little about transactions. I used to know the existence of transactions in the database, rather than in our C #ProgramYou can also add transactions to implement them in the program. Through my analysis, the transactions in the program seem to be biased towards the business aspect, while the transactions in the database are oriented towards the data aspect. I have not summarized the locks in the transaction here. I just summarized the concept of the transaction and the basic usage of the transaction.

Transactions in the database:

(1) transaction is the unit of concurrency control and a sequence of user-defined operations. These operations are either done or not done. They are an inseparable unit of work. Through transactions, SQL Server can bind a set of logical operations to ensure data integrity on the server.

(2): transactions generally start with begin transaction and end with commit or rollback.

Commit indicates commit, that is, all operations to commit a transaction. Specifically, all the updates to the database in the transaction are written back to the physical database on the disk, and the transaction ends normally.

Rollback indicates rollback, that is, a fault occurs during the transaction operation, and the transaction cannot continue. The system revokes all the completed operations on the database in the transaction, roll back to the starting state of the transaction.

(3) three modes of transaction operation:

A: automatically submit transactions.

Each separate statement is a transaction. Each statement implies a commit.

B: explicit transactions

Start explicitly with begin transaction and end explicitly with commit or rollback.

C: Implicit transactions

When the previous transaction was completed, the new transaction was started implicitly, but each transaction still ended explicitly with commit or rollback.

(4): ACID properties of transactions)

A: atomicity)

A transaction is the logical unit of work of a database. All operations in a transaction are either performed in full or not.

B: Consistency)

The result of transaction execution must be that the database changes from one consistent state to another consistent state. Consistency is closely related to atomicity.

C: Isolation)

The execution of a transaction cannot be disturbed by other transactions.

D: Durability)

Once a transaction is committed, its changes to the data in the database should be permanent.

 

Example of using transactions:

First:Try ...... Catch

Begin transaction T1

Begin try

Insert into [teach_info] ([teach_id], [login_name], [login_pwd], [real_name], [State]) values (@ ID, @ login_name, @ PWD, @ name, @ State );

Delete from [tmpuser] Where [login_name] = @ login_name;

Commit transaction T1

End try
Begin catch
Rollback transaction T1;
End catch;

Type 2: Use the global variable @ Error

Begin transaction T1
Insert into [teach_info] ([teach_id], [login_name], [login_pwd], [real_name], [State]) values (@ ID, @ login_name, @ PWD, @ name, @ State );

If @ error <> 0 begin

Rollback transaction T1; -- roll back the transaction

Return;

End
Delete from [tmpuser] Where [login_name] = @ login_name;

If @ error <> 0 begin

Rollback transaction T1; -- roll back the transaction

Return; -- Do not forget to return the statement. Otherwise, the following statement will continue to be executed, causing an error in the Stored Procedure

End
Commit transaction T1 -- the execution is successful and the transaction is committed.

 

C #Transactions in:

The followingCodeIn, we want to perform two update operations. In the t_user table, we subtract the credits of user a by 100 and add the credits of user B by 100. If any operation fails, we will cancel the operation. Either data is updated or not updated.

Using (sqlconnection conn = new sqlconnection (connectionstring ))
{
Conn. open ();
Sqltransaction transaction = conn. begintransaction ();
Sqlcommand cmd1 = conn. createcommand ();
Repeated 1.commandtype = commandtype. text;
Export 1.commandtext = "updata t_user set integral = integral-100 Where username = @ username ";
Parameters 1.parameters. Add (New sqlparameter ("@ username", ));
Sqlcommand cmd2 = conn. createcommand ();
Optional 2.commandtype = commandtype. text;
Export 2.commandtext = "updata t_user set integral = integral + 100 Where username = @ username ";
Parameters 2.parameters. Add (New sqlparameter ("@ username", B ));
Try
{
Statement 1.executenonquery ();
Listen 2.executenonquery ();
// If there is no error in the execution, it means that both operations are successful and we will commit the transaction to make the two updates take effect.
Transaction. Commit ();
}
Catch
{
// If the operation is executed here, it indicates that some operation has failed, so we will roll back the operation, the rollback scope is all transaction operations performed on the database connection after many begintransactions are called. The points of the two users remain unchanged.
Transaction. rollback ();
}
}