Introduction to transactions in SQL Server _mssql

1. What is a transaction: a transaction is an indivisible working logical unit that is used as the smallest control unit when performing concurrent operations on a database system.

All of the database operations commands he contains are either committed or undone together as a whole, and the set of database operations commands are executed or not executed.

A transaction is an indivisible working logical unit.

2. Classification of transactions.
transactions can be grouped into 3 categories by how they are started and executed:
① Display transactions: Also known as user-defined or user-specified transactions, you can explicitly define a startup and an end transaction. A distributed transaction belongs to a display transaction
② Auto COMMIT TRANSACTION: Default transaction management mode. If a statement completes successfully, the statement is committed, and if an error is encountered, the statement is rolled back.
③ implicit transaction: When a connection is operating in this mode, SQL automatically starts a new transaction after committing or rolling back the current transaction. You do not need to describe the beginning of a transaction, just commit or roll back each transaction. It generates a continuous transaction chain.

3. Statement of the transaction
start things: Begin TRANSACTION (abbreviated TRAN)
Submitting things: Commit TRANSACTION
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

Note: Once a transaction commits or rolls back, the transaction ends

4.4 Characteristics of a transaction
① atomicity (atomicity): All elements in a transaction are committed or rolled back as a whole, and the transaction is a complete operation.
② Consistency (CONSISTEMCY): When things are done, the data must be consistent, that is to say, the data in the data store is in a consistent state before things begin. Keep the data intact.
③ Isolation (Isolation): Multiple transactions that modify data are isolated from one another. This indicates that the transaction must be independent and should not affect other transactions in any way.
④ Persistence (Durability): After a transaction is completed, its impact on the system is permanent, and the modification is maintained even if a system failure occurs, and the database is actually modified.

5. Examples

@ @error: Global variables, logging error numbers, accumulating errors

Copy Code code as follows:

Begin tran--Start a transaction
Declare @errorsum int--defines the number of errors that the variable records
Set @errorsum =0--error number defaults to 0
UPDATE dbo. Officerent set Createtime=getdate () where id=2--persistent SQL statement
Set @errorsum +=@ @ERROR--whether the cumulative error

UPDATE dbo. Officerent set hits+=1 where id=2--persistent SQL statement
Set @errorsum +=@ @ERROR--whether the cumulative error

If @errorsum <>0--to determine if an error occurred
Begin

print ' ERROR, rollback '

Rollback tran--error occurred, rollback
End
Else
Begin

print ' success, submitting '
Commit tran--Commit a transaction
End

6. The following operations cannot be used for transactions

Create DATABASE
Modify database alter DB
Delete database drop databases
Restore database restore DB
Load Database load Databa SE
Backup log file backup log
Recovery log files restore LOG
Update STATISTICS update Statitics
Authorized Actions                Grant
Replication transaction log dump Tran
Disk initialization             DISK INIT
Update system configuration after using sp_configure             reconfigure