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.
2. Statement of the transaction
Start things: Begin TRANSACTION
Submitting things: Commit TRANSACTION
ROLLBACK TRANSACTION: ROLLBACK TRANSACTION
3.4 Properties of a transaction
① atomicity (atomicity): All elements in a transaction are committed or rolled back as a whole, the elements of a transaction are not divided, and a 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 be used in any way or affect other transactions.
④ 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
4. Storage point of the transaction
Save TRANSACTION savepoint Name--the name and location of the custom save point
ROLLBACK TRANSACTION Save Point Name-rollback to custom savepoint
-----------------------------------------------Real------------------Cases
Begin transaction--Start a transaction
DECLARE @errorSun INT--Defining error counters
SET @errorSun = 0--yes, 0.
UPDATE a SET id=232 WHERE a=1--transaction operation SQL statement
SET @errorSun = @errorSun +@ @ERROR-Cumulative error
UPDATE aa SET id=2 WHERE a=1--transaction operation SQL statement
SET @errorSun = @errorSun +@ @ERROR-Cumulative error
IF @errorSun <>0
BEGIN
PRINT ' ERROR, rollback '
ROLLBACK transaction--Transaction ROLLBACK statement
End
ELSE
BEGIN
PRINT ' success, submitting '
Commit transaction--Transaction COMMIT statement
End
Example: Create a stored procedure to insert data into two tables at the same time
Copy Code code as follows:
Create proc RegisterUser
(@usrName varchar (), @usrPasswd varchar, @age int, @sex varchar (a), @PhoneNum varchar, @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
Classification of transactions
Transactions can be grouped into 3 categories by how they are started and executed:
Show transactions
Also called a user-defined or user-specified transaction, 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.
Hidden transactions
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.
One, display transactions
Complete by 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 of Business
Format: Commit TRAN Transaction Name or variable (transaction name is consistent with transaction name in BEGIN TRAN)
or commit work but this has no parameters
3. ROLLBACK TRANSACTION
Rollback TRAN Transaction name or variable | Savepoint_name | Savepoint_variable
or rollback work
Description: Clears all data modifications made from the start of the transaction or to a save point
4. Set the SavePoint within the transaction
Format: Save Tran Savepoint_name | Savepoint_variable
Example:
Copy Code code as follows:
Use BOOKDB
Go
BEGIN Tran Mytran
INSERT INTO book
VALUES (9, "Windows2000 ', 1, 22, ' publishers ')
Save Tran Mysave
Delete book where book_id=9
Rollback Tran Mysave
Commit Tran
Go
SELECT * FROM book
Go
As you can see, after the statement is executed, a record is inserted in the book and is not deleted. Because the action is rolled back to the savepoint before the deletion using the rollback TRAN Mysave statement.
5. Mark Transaction
Format: With Mark
Example: A statement that restores a log to a predefined point in time using a database token
A tag is placed in the transaction log. Note that the marked transaction must submit at least one update to mark the log.
BEGIN TRAN Mymark with MARK
UPDATE pubs.dbo.LastLogMark SET marktime = GETDATE ()
COMMIT TRAN Mymark
Back up the transaction log in the way you commonly use it.
BACKUP LOG pubs to disk= ' C:\Backups\Fullbackup.bak ' with INIT
You can now restore the database to the 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 restore the log to the point in time that contains the tag and make it available for use. Note that STOPAT is prevented from executing when the database is performing a bulk log.
RESTORE LOG pubs from disk=n ' C:\Backups\Logbackup.bak ' with RECOVERY,
stopat= ' 02/11/2002 17:35:00 '
5. Operations that cannot be used for transactions
Creating database Create Databases
Modify database Alter DB
Delete Database Drop db
restoring Databases Restore Database
Load Database Load DB
Backing up log files backup log
Restore log file Restore log
Update statistical Data Update Statitics
Authorization Actions Grant
Replication transaction Log Dump Tran
Diskette initialization DISK INIT
Update the system configuration after using sp_configure reconfigure
Ii. Automatic submission of services
An SQL connection will operate in autocommit mode before the START Tran statement starts an explicit transaction, or if the implicit transaction mode is set to open. When an explicit transaction is committed or rolled back, or when the implicit transaction mode is turned off, it is returned to autocommit mode.
Example:
Three inserts were not executed due to compilation errors
Copy Code code 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
Sets the implicit transaction mode to open through an API function or Transact-SQL SET IMPLICIT_TRANSACTIONS on statement. The next statement automatically starts a new transaction. When the transaction completes, the next Transact-SQL statement starts a new transaction.
When a large number of DDL and DML commands execute automatically and remain until the user explicitly commits, switching an implicit transaction can be done with the set implicit_transactions
Sets the implicit transaction mode for the connection. When set to ON, set implicit_transactions sets the connection to the implicit transaction mode. When set to OFF, causes the connection to be returned to autocommit transaction mode
Statements include:
ALTER TABLE Insert OPEN CREATE delete revoke drop
Select Fetch TRUNCATE TABLE Grant update
Example:
The following uses explicit and implicit transactions. It uses the @ @tracount function to demonstrate open transactions and closed transactions:
Copy Code code as follows:
Use test
Go
SET NOCOUNT ON
CREATE TABLE T1 (a int)
Go
INSERT into T1 VALUES (1)
Go
print ' Use an explicit transaction '
BEGIN Tran
INSERT into T1 values (2)
Number of transactions outside of print ' transaction: ' +cast (@ @trancount as char (5))
Commint Tran
Number of transactions outside of print ' transaction: ' +cast (@ @trancount as char (5))
Go
Print
Go
Set implicit_transactions on
Go
print ' Use an implicit transaction '
Go
INSERT INTO T1 values*4)
Number of transactions within print ' Transaction: ' +cast (@ @trancount as char (5))
Commint Tran
Number of transactions outside of print ' transaction: ' +cast (@ @trancount as char (5))
Go
Execution results:
Using Display transactions
Number of transactions within a transaction: 2
Number of transactions outside of transaction: 1
Using an implicit transaction
Number of transactions within a transaction: 1
Number of transactions outside of transaction: 0
Iv. Distributed Transactions
A transaction in a single SQL Server that spans two or more databases is a distributed transaction.
differs from local transaction: it must be managed by the transaction manager to minimize the occurrence of a network failure that results in a transaction being successfully committed by some resource managers but rolled back by some other resource manager.
SQL Server can support the processing of distributed transactions by DTC Microsoft Distributed Transaction Coordinator, and you can start a distributed transaction using the BEgin Distributed Transaction command Processing
Divided into two stages:
A Preparation Stage
B Submission Phase
To perform a tutorial:
1. SQL scripts or application connections Execute SQL statements that start a distributed transaction
2, the SQL executing the statement in the transaction for the master server
3. A script or application executes a distributed query against a linked server or a remote stored procedure on a remote server.
4. When a distributed query or remote procedure call is executed, the master server automatically invokes MSDTC to enlist the linked servers and remote servers in the distributed transaction
5. When a commit or ROLLBACK statement is issued by a script or application, the master SQL invokes the MSDTC management two-phase commit process, or notifies the linked server and remote server to roll back their transactions.
An example of MSSQL transactions
Copy Code code as follows:
BEGIN Tran
DECLARE @rownum1 INT--Number of empty orders not added
DECLARE @rownum2 INT--Number of empty rooms 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
