A transaction is a mechanism and an operation sequence. It contains a group of database operation commands. All the commands will be used as a whole, and they will be submitted or revoked together to the system. The requests will either be executed or not executed.
Therefore, a transaction is an inseparable logical unit of work, similar to the concept of the operating system. When performing concurrent operations on the database system, transactions are used as the smallest control unit.
In SQL, statements that define transaction operations,
Begin transaction
Commit transaction
Rollback transaction
Features of transactions:
Atomicity, consistency, isolation, and durability.
The following example shows the application of transaction rollback:
Begin transaction my_transaction_delete
Use company -- Use Database
Go
Delete from department where dept_id = '123' -- delete
Save transaction after_delete -- set the transaction recovery point
Update employee set dept_id = '20160301' where dept_id = '20160301'
-- The employee ID of the logistics department is changed to the manager's office ID
If @ error <> 0 Ro @ rowcount = 0 then
-- Check whether the update is successful. @ error returns the status of an SQL statement. If the value is not zero, an error is returned. If the value is not zero, an error is returned. If the value is incorrect, a rollback occurs.
Begin
Rollback Tran after_delete -- roll back to the storage point
Commit tran -- if no error occurs, data can be submitted.
Print 'updating employee information is generating an error'
Return
End
Commit transaction my_transaction_delete
Go