Example: Create a stored procedure and insert data into both tables 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
Userinfo (username, userpasswd) values (@ usrname, @ usrpasswd)
If
@ Error <> 0
Begin Rollback tran Return-1
End
Insert
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. Transaction display
is completed through 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 the tran 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
description: clear all data changes made from the transaction start point or to a save point
4. Set the save point in the transaction
Format: save Tran
savepoint_name | savepoint_variable
example:
use
bookdb
go
begin Tran mytr An
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
available, 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 a transaction.
Format: With
MARK
example: the statement that uses the database flag to restore the log to a predefined time point
sets a flag in 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
Disk = 'C: \ Backups \ fullbackup. Bak'
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'
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'
Recovery,
Stopat = '2014/1/0 17:35:00' 5. operations that cannot be used for transactions
create database
modify Database alter
database
delete database drop database
Restore database
load database
backup log
restore Log File restore log
Update Statistics
statitics
authorized operation
grant
copy the transaction log dump
Tran
disk initialization > disk
init
Update System Configuration after sp_configure is used
reconfigure Ii. automatically submit transactions
SQL connection in TRAN in tran
When the explicit transaction is started or the implicit transaction mode is set to open, the operation is performed in the automatic commit mode. 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.
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 API functions or transact-SQL set
Implicit_transactions on statement, set the implicit transaction mode to open. The next statement automatically starts a new transaction. When the transaction is completed
The 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 the set
Implicit_transactions
Sets the implicit transaction mode for the connection. When set to on, set implicit_transactions
Set 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:
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
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
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.
Differences from local transactions:It must be managed by the Transaction Manager to avoid situations where a transaction is successfully submitted by some resource managers but rolled back by other resource managers due to network faults. SQL
You can use DTC Microsoft Distributed Transaction Coordinator to process distributed transactions.
Begin Distributed Transaction command to start a Distributed Transaction Processing Two phases:
A preparation stage
B
Submission phase Tutorial:
1. SQL
Scripts or applicationsProgramConnect 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. |
