Database Transaction Writing simple demo (Novice insights)

Because recently in the project has been applied to the transaction several times, here simply to give a database transaction demo, I hope you criticize correct, thank you!

Well, not much nonsense, we start with the introduction of the business and a series of knowledge points about:

　　

A transaction (Transaction) is the basic unit of concurrency control. The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work.

For example, a bank transfer job: Debit from one account and add another account, both of which are either executed or not executed. Therefore, they should be regarded as a business. A transaction is a unit of data consistency maintained by a database that maintains data consistency at the end of each transaction.

As can be seen from the above description, the proposed transaction is mainly to solve the problem of maintaining data consistency in the concurrency situation.

A transaction has the following 4 basic characteristics.

Atomic (atomicity): The operations contained in a transaction are considered to be a logical unit in which the operations of the logical unit either succeed or fail altogether.

Consistency (consistency): only legitimate data can be written to the database, or the transaction should roll it back to its original state.

Isolation (Isolation): Transactions allow multiple users to concurrently access the same data without destroying the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions.

Durability (persistent): After the transaction ends, the result of the transaction must be cured.

2. Statement of the transaction　

Start things: BEGIN Transaction

Commit a thing: Commit Transaction

ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

3.4 properties of a transaction

① atomicity (atomicity): all elements in a transaction are committed or rolled back as a whole, and the elements of a transaction are non-divided, and the transaction is a complete operation.

② Consistency (CONSISTEMCY): When things are done, the data must be consistent, that is, the data in the data store is in a consistent state before the things begin. Ensure that data is lossless.

③ Isolation (Isolation): Multiple transactions that modify data are isolated from each other. This indicates that the transaction must be independent and should not be in any way or affect other transactions.

④ Persistence (Durability): After a transaction is complete, its effect on the system is permanent, and the modification persists even if a system failure occurs, and the database is actually modified

4. SavePoint of Transactions     

Save Transaction SavePoint Name --the name and location of the custom save point

Rollback TRANSACTION Save Point Name --rollback to a custom savepoint

If this is not known, there is a direct last on the top of the demo:

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------

　　 Use [moneyproduct]
GO
/****** object:storedprocedure [dbo].    [Pro_insertmoneyrecordes] Script date:07/01/2016 09:32:31 ******/
SET ansi_nulls on
GO
SET quoted_identifier on
GO
ALTER proc [dbo]. [Pro_insertmoneyrecordes]--A stored procedure, this is nothing to say
@U_Id int, @MR_money decimal, @MR_time datetime, @MR_state int, @MR_depict varchar, @MR_typeId int, @MR_numbers varchar, @Bid int, @Cy_Id int, @Cy_numbers varchar
as
begin
 
Begin Tran Tran_money--Start transaction
DECLARE @tran_error int;
set @tran_error =0;
begin try
INSERT into Moneyrecord values (@U_Id, @MR_money, @MR_time, @MR_state, cast (@MR_depict as varchar), @MR_typeId, @MR_ Numbers, @Bid, @Cy_Id, cast (@Cy_numbers as varchar));

--Add recharge information
set @tran_error [email protected][email protected] @ERROR;
--Test the error code to see if the recharge data was added successfully
--set @tran_error = 1;
SELECT @ @IDENTITY as ' identity ';---Query the identity column of the last row of the newly added data
Set @tran_error = @[email protected] @ERROR;
INSERT into Czrecord values (@ @IDENTITY);--Add a piece of information to the recharge record
Set @tran_error = @[email protected] @ERROR;
Update Capital Set [e-mail Protected]_money where c_id = (select c_id from UserInfo where u_id [email protected]_ ID)--Modify the account center balance
Set @tran_error = @[email protected] @ERROR;
End Try
begin Catch
print ' unexpected ' +convert (Varchar,error_number ()) + ', error message ' +error_message ()
set @[email protected]_error+1;
End Catch
if (@tran_error >0)
begin
--Performing an error, rolling back the transaction
ROLLBACK TRAN;
Print ' Reload failed '
End
Else
begin
--no exception, COMMIT transaction
COMMIT TRAN;
Print ' Recharge succeeded '
End
End

< Span style= "font-family: ' Comic Sans MS ', Sans-serif;" > -------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------

The above is only their own understanding of the business writing norms, may not be comprehensive and rigorous, I hope you advise!

　　

Database Transaction Writing simple demo (Novice insights)