Transactions and concurrency (2)

Transactions and concurrency (2)

A transaction consists of a single command or a group of commands executed as a package. Transactions can merge multiple operations into a single unit of work.

If a fault occurs at a point in the transaction, all updates can be rolled back to the status before the transaction.

Transactions must comply with acid attributes (atomicity, consistency, isolation, and durability) to ensure data consistency.

Most relational database systems (such as Microsoft SQL Server) support transactions by providing transaction locking, logging, and transaction management functions when client applications perform updates, inserts, or deletes.

You can use Transact-SQLBegintransaction,CommittransactionAndRollback
TransactionStatement to create a transaction in the SQL server stored procedure.

In the stored procedure of the database, transactions are simply used as follows:

(Introduction: how to operate data in multiple tables in one operation)

Create procedure [DBO]. [proc_demo] @ ID varchar (20), @ name varchar (20), @ Sid numeric (10, 1), @ desvarchar (20), @ money varchar (20 ), @ date varchar (20) As begin -- transaction setxact_abort on; -- when using a stored procedure to execute a transaction, you must enable the xact_abort parameter (default value: Off) and set this parameter to on, indicates that when a transaction is executed, if an error occurs, the transcation is set to the uncommittable state, all operations will be rolled back after the block batch processing of the statement is completed; if this parameter is set to off, indicates that when a transaction is executed, if an error occurs, the wrong statement will not be executed, and other correct operations will continue to be executed. Set nocount on; -- when set nocount is on, no count is returned (count indicates the number of rows affected by the transact-SQL statement. For example, after performing a delete operation in the SQL Server Query analyzer, the following window will prompt rows affected ). When set nocount is off, the returned count is returned. If set nocount on is added to the header of the stored procedure and set nocount off is added to exit the stored procedure, to optimize the stored procedure, begin tran -- start a transaction -- operate insertinto t_card (ID, name) values (@ ID, @ name) on table 1) -- insert into t_student (SID, des) values (@ Sid, @ des) for table 2 -- add the recharge table insertinto t_recharge (money, date) values (@ money, @ date) if @ error = 0 -- if there is no error in the data operation committran -- commit the transaction (the committed record is written to the log. This means that the transaction changes are permanent. By default, this write operation is synchronized to the disk, so the submitted records will be first reached the log file.) else rollbacktran -- roll back the transaction (roll back to the state before the transaction) End

 

After the stored procedure with transaction processing is completed, it can be used in the program.

Public Function Demo(ByValaccountInfoMOdel As Model.AccountEntity) As Integer        Dim strSql As String ="PROC_Demo"        Dim meter() AsSqlParameter = {New SqlParameter("@ID", accountInfoMOdel.ID), NewSqlParameter("@Name", accountInfoMOdel.Name),New SqlParameter("@SID",accountInfoMOdel.SID),New SqlParameter("@Des", accountInfoMOdel.Des),New SqlParameter("@Money",accountInfoMOdel.Money),New SqlParameter("@Date", accountInfoMOdel.Date)}        Dim sqlServerHelperEg As DBHelper.SqlServerHelper = NewDBHelper.SqlServerHelper       Demo =sqlServerHelperEg.ExecuteNonQuerySql(strSql, CommandType.StoredProcedure,meter) End Function

In fact, the transaction function is very powerful. This is only about small applications in the database. Of course, there are many other usage methods that I will sort out one after another.