MSSQL transaction description

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.

2. Transaction statements
Start TRANSACTION: BEGIN TRANSACTION
Submit TRANSACTION: COMMIT TRANSACTION
Rollback transaction: ROLLBACK TRANSACTION
3. Four attributes of the transaction
① Atomicity: All elements in a transaction are committed or rolled back as a whole. The transaction elements are inseparable and the 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.
4. Transaction retention point
Save transaction save point name-custom SAVE point name and location
Rollback transaction save point name -- roll back to custom save point

------------------- Real ------------------ example ----------------------------

Begin transaction -- start TRANSACTION

DECLARE @ errorSun INT -- defines the error counter
SET @ errorSun = 0 -- yes, it is 0.

UPDATE a SET id = 232 WHERE a = 1 -- SQL statement for transaction operations
SET @ errorSun = @ errorSun + @ ERROR -- indicates whether the total number is incorrect.

UPDATE aa SET id = 2 WHERE a = 1 -- SQL statement for transaction operations
SET @ errorSun = @ errorSun + @ ERROR -- indicates whether the total number is incorrect.

IF @ errorSun <> 0
BEGIN
PRINT 'error, rollback'
Rollback transaction -- transaction rollback statement
END
ELSE
BEGIN
PRINT 'success, submit'
Commit transaction -- transaction commit statement
END



Example: Create a stored procedure and insert data into both tables
Copy codeThe Code is as follows:
Create proc RegisterUser
(@ UsrName varchar (30), @ usrPasswd varchar (30), @ age int, @ sex varchar (10), @ PhoneNum varchar (20), @ Address varchar (50 ))
As begin
Begin tran
Insert into userinfo (userName, userPasswd) values (@ usrName, @ usrPasswd)
If @ error <> 0
Begin

Rollback tran

Return-1
End
Insert into userdoc (userName, age, sex, PhoneNumber, Address) values (@ Usrname, @ age, @ sex, @ PhoneNum, @ Address)
If @ error <> 0
Begin

Rollback tran
Return-1
End

Commit tran
Return 0
End

Transaction Classification
Transactions can be divided into three categories based on the start and execution methods of transactions:
Show transactions
It is also called a user-defined or user-specified transaction, that is, you can explicitly define the start and end transactions. Distributed transactions are display transactions.
Automatically submit transactions
Default transaction management mode. If a statement is successfully completed, the statement is submitted. If an error occurs, the statement is rolled back.
Implicit transactions
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.

1. Display transactions
You can use begin transacton, commit transaction, commit work, rollback transaction, or rollback work.
1. Start the transaction
Format: begin tran transaction name or variable with mark description
2. End the transaction
Format: commit tran transaction name or variable (the transaction name is consistent with the transaction name in tran
Or commit work, but there is no Parameter
3. roll back the transaction
Rollback tran transaction name or variable | savepoint_name | savepoint_variable
Or rollback work
Note: Clear all data modifications made from the starting point of the transaction or to a storage point.
4. Set the save point in the transaction
Format: save tran savepoint_name | savepoint_variable
Example:
Copy codeThe Code is as follows:
Use bookdb
Go
Begin tran mytran
Insert into book
Values (9, "windows2000', 'Press ')
Save tran mysave
Delete book where book_id = 9
Rollback tran mysave
Commit tran
Go
Select * from book
Go

As you can know, after the preceding statement is executed, a record is inserted in the book, but not deleted. Because the rollback tran mysave statement is used to roll back the operation to the Save point before deletion.
5. Mark transactions
Format: with mark
For example, the statement that uses the database tag to restore logs to a predefined time point
Add a tag to the transaction log. Note that at least one update must be submitted for the marked transaction to mark the log.

Begin tran MyMark WITH MARK
UPDATE pubs. dbo. LastLogMark SET MarkTime = GETDATE ()
Commit tran MyMark

Back up transaction logs according to your common methods.

Backup log pubs to disk = 'C: \ Backups \ Fullbackup. Bak' WITH INIT

Now you can restore the database to a log mark point. First, recover the database and make it ready to accept log recovery.

Restore database pubs from disk = n'c: \ Backups \ Fullbackup. bak 'WITH NORECOVERY

Now, the log is restored to the time point that contains the mark and made available for use. Note that STOPAT cannot be executed when the database is executing large-capacity logs.

Restore log pubs from disk = n'c: \ Backups \ Logbackup. Bak' with recovery,
STOPAT = '2014/1/0 17:35:00'

5. cannot be used for transaction operations
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

Ii. automatically submit transactions
The SQL connection is performed in the automatic commit mode before the begin tran statement starts an explicit transaction or before the implicit transaction mode is set to open. When an explicit transaction is committed or rolled back, or the implicit transaction mode is closed, it is returned to the automatic commit mode.
Example:
Due to compilation errors, the three insert statements are not executed.
Copy codeThe Code is as follows:
Use test
Go
Create table testback (cola int primary key, colb char (3 ))
Go
Insert into testback values (1, 'aaa ')
Insert into testback values (2, 'bbb ')
Insert into testback value (3, 'ccc ')
Go
Select * from testback
Go

No results returned

Iii. Implicit transactions
Use an API function or a Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement to enable the implicit transaction mode. The next statement automatically starts a new transaction. When the transaction is completed, another Transact-SQL statement starts a new transaction.
When a large number of DDL and DML commands are executed, the system starts automatically until the user explicitly commits them. You can use SET IMPLICIT_TRANSACTIONS to switch between implicit transactions.
SET the implicit transaction mode for the connection. When SET to ON, SET IMPLICIT_TRANSACTIONS sets the connection to the implicit transaction mode. When it is set to OFF, the connection is returned to the automatic commit transaction mode.
Statements include:
Alter table insert open create delete revoke drop
Select fetch truncate table grant update
Example:
Explicit and implicit transactions are used below. It uses the @ tracount function to demonstrate opened transactions and closed transactions:
Copy codeThe Code is as follows:
Use test
Go
Set nocount on
Create table t1 (a int)
Go
Insert into t1 values (1)
Go

Print 'use explicit transactions'
Begin tran
Insert into t1 values (2)
Print 'number of external transactions: '+ cast (@ trancount as char (5 ))
Commint tran
Print 'number of external transactions: '+ cast (@ trancount as char (5 ))
Go

Print
Go
Set implicit_transactions on
Go

Print 'use implicit transaction'
Go
Insert into t1 values * 4)
Print 'number of transactions in the transaction: '+ cast (@ trancount as char (5 ))
Commint tran
Print 'number of external transactions: '+ cast (@ trancount as char (5 ))
Go

Execution result:
Use display transactions
Number of transactions in a transaction: 2
Number of external transactions: 1
Use implicit transactions
Number of transactions in a transaction: 1
Number of external transactions: 0

Iv. distributed transactions
Transactions in a single SQL server that spans two or more databases are distributed transactions.
Difference from local transactions: it must be managed by the Transaction Manager to avoid situations where a transaction is successfully submitted by some resource managers due to a network failure but rolled back by other resource managers.

SQL server supports distributed transactions by DTc microsoft distributed transaction coordinator. You can use the BEgin distributed transaction command to start a distributed transaction.


Two phases:
A preparation stage
B. Submission phase

Tutorial:
1. SQL scripts or applications are connected to execute SQL statements for starting distributed transactions
2. the SQL statement is executed on the master server of the transaction.
3. scripts or applications perform distributed queries on linked servers, or execute remote stored procedures on remote servers.
4. After a distributed query or remote process call is executed, the master server automatically calls msdtc to register the servers and remote servers linked to the distributed transaction.
5. When a script or application sends a commit or rollback statement, the master SQL will call the two-phase commit process of msdtc management, or notify the linked server and remote server to roll back the transaction.

An example of mssql transactions

Copy codeThe Code is as follows:
Begin tran
Declare @ rownum1 int -- number of vacant rooms without an order added
Declare @ rownum2 int -- number of vacant places to add an order
Declare @ BookID1 int
Set @ BookID1 = 0
Insert into T_BookRoomInfo (RoomID, CustomerName, CustomerCardID, Discount,
EnterTime, DepositMoney, Memo, UserID, UpdTime)
Values (@ RoomID, @ CustomerName, @ CustomerCardID, @ Discount,
Getdate (), @ DepositMoney, @ Memo, @ UserID, getdate ())
Select @ BookID1 = @ IDENTITY
If (@ BookID1 <> 0)
Begin
Select @ rownum1 = count (1)
From T_Room
Where IsEmploy = 0
Update T_Room
Set IsEmploy = 1
Where RoomID = @ RoomID
Select @ rownum1 = count (1)
From T_Room
Where IsEmploy = 0

If (@ rownum1 <= @ rownum2)
Begin
Rollback tran
End
Else
Begin
Commit tran
End
End
Else
Begin
Rollback tran
End