Database Transaction introduction and instance, Database Transaction instance

Database Transaction introduction and instance, Database Transaction instance

What is a database transaction?
Database transactions are a series of operations performed as a single logical unit of work. Specifically, Several SQL statements can be executed as a whole. When an execution error occurs, all SQL statements fail to be executed. That is, these SQL statements, as a whole, are either executed successfully or all failed. Transactions facilitate data recovery and ensure data consistency.

Transaction attributes
Transactions have ACID properties
That is, Atomic atomicity, Consistent consistency, Isolated isolation, Durable permanent

Atomicity

That is, the transaction should be taken as a unit of work, and the transaction is processed completely. All the work is either saved in the database or completely rolled back, but not retained.

Consistency
After the transaction is completed or canceled, it should be in the same state.

Isolation

Multiple transactions are performed at the same time, and they should not interfere with each other. The status of the data when the transaction is viewing the data is either the status before the transaction is modified or the status after the transaction is modified. The transaction does not view the data in the intermediate status.

Permanent
After the transaction is committed, the work is permanently saved.

Transaction instance:

Create procedure TransferMoeny
(
@ FromAccountNo varchar (50), -- Transfer Account
@ ToAccountNo varchar (50), -- Transfer Account
@ MoneyCount money -- transfer amount
)
As
-- Determine whether the account exists
If exists (select1From account table where account = @ FromAccountNo)
Begin
If exists (select1From account table where account = @ ToAccountNo)
Begin
-- Determine whether the transfer amount is greater than the current balance
If (select current balance from account table where account = @ FromAccountNo) >=@ MoneyCount
Begin
-- Start Transfer
Begin transaction
Insert into [access record table] ([account], [access type], [access amount]) values (@ FromAccountNo ,-1, @ MoneyCount)
If@ Error<>0
Begin
Rollback transaction -- roll back the transaction if an error occurs and exit l unconditionally
Return
End
Insert into [access record table] ([account], [access type], [access amount]) values (@ ToAccountNo,1, @ MoneyCount)
If@ Error<>0
Begin
Rollback tran
Return
End
Commit transaction -- both statements are completed and the transaction is committed.
End
Else
Raiserror ('transfer amount cannot exceed the balance of this account ',16,1)
End
Else
Raiserror ('transfer account does not exist ',16,1)

End
Else
Raiserror ('outgoing account does not exist ',16,1)