Talking about database transactions, talking about databases

Talking about database transactions, talking about databases

I. SQL transactions

1. What is a transaction: a transaction is an inseparable logical unit of work. It is used as the smallest control unit to execute concurrent operations on the database system.

All the database operation commands contained in the database are submitted or withdrawn to the system as a whole. These database operation commands are either executed or not executed.

A transaction is an inseparable logical unit of work.

2. Transaction classification.
Transactions can be divided into three categories based on the start and execution methods of transactions:
① Display transaction: A transaction defined by the user or specified by the user, that is, a start and end transaction can be explicitly defined. Distributed transactions are display transactions.
② Automatic transaction commit: the default transaction management mode. If a statement is successfully completed, the statement is submitted. If an error occurs, the statement is rolled back.
③ Implicit transaction: when the connection is operated in this mode, SQL automatically starts a new transaction after committing or rolling back the current transaction. You do not need to describe the start of a transaction. You only need to commit or roll back each transaction. It generates a continuous transaction chain.

3. Transaction statements
Start TRANSACTION: begin transaction (tran)
Submit TRANSACTION: COMMIT TRANSACTION
Rollback transaction: ROLLBACK TRANSACTION

Note: Once the transaction is committed or rolled back, the transaction ends.

4. Four features of transactions
① Atomicity: All elements in a transaction are committed or rolled back as a whole, which cannot be broken down. A transaction is a complete operation.
② Consistemcy: when a transaction is completed, the data must be consistent, that is, the data stored in the data is in the same state before the transaction starts. Ensure data loss.
③ Isolation: multiple transactions that modify data are isolated from each other. This indicates that the transaction must be independent and should not affect other transactions in any way.
④ Durability: after the transaction is completed, its impact on the system is permanent. This modification will be retained even if a system failure occurs, and the database is actually modified.

5. Instance

@ Error:Global variables, record error numbers, and accumulate errors

Begin tran -- start transaction
Declare @ errorsum int -- defines the number of variable record errors
Set @ errorsum = 0 -- the default number of errors is 0.
Update dbo. OfficeRent set CreateTime = GETDATE () where Id = 2 -- continuous SQL statement
Set @ errorsum + =error -- indicates whether the total number is incorrect.

Update dbo. OfficeRent set Hits + = 1 where Id = 2 -- continuous SQL statement
Set @ errorsum + =error -- indicates whether the total number is incorrect.

If @ errorsum <> 0 -- determines whether an error has occurred.
Begin

Print 'error, rollback'

Rollback tran -- roll back if an error occurs
End
Else
Begin

Print 'success, submit'
Commit tran -- submit a transaction
End

6. The following operations cannot use transactions:
Create database
Alter database
Delete database drop database
Restore database
Load database
Backup log
Restore the log file restore log
Update statitics
Grant authorization
Copy transaction log dump tran
Disk initialization disk init
Update the System Configuration reconfigure after sp_configure is used