1 preparing the data and the several settings involved
Set TRANSACTION Isolation LEVEL READ UNCOMMITTED--Sets the isolation class of the transaction session (the default is read UNCOMMITTED) and is valid only for the current process (that is, only for open ssms Current Query window, Open another query window is invalid)
Set Xact_abort off-sets the exact end of the switch (the default is OFF) and is only valid for the current process (that is, it is only valid for opening the SSMs current window, and then opening the other one is not valid)
IF not object_id('score') is NULL DROP TABLE [score] GO IF not object_id('Student') is NULL DROP TABLE [Student] GO CREATE TABLEStudent (Stuidint not NULL PRIMARY KEY, StunameNvarchar( -) ) CREATE TABLEScore (Stuidint not NULL REFERENCESStudent (Stuid),--FOREIGN KeyScorevalueint ) GO INSERT intoStudentVALUES(101,'hu Yi Dao') INSERT intoStudentVALUES(102,'Elite') INSERT intoStudentVALUES(103,'Chen Jialou') INSERT intoStudentVALUES(104,'Zhang Sanfeng') GO
2 rollback Cases
--the simplest rollback--The following statements can be executed separately, one line at a timeBEGIN TRAN --open transaction, set transaction start point/*The command was successfully completed. */SELECT * fromscore/*(0 rows affected) Description: Because there is no data in the table, the query returns the number of bars to 0.*/INSERT intoScoreVALUES(101, -)--Insert data At this point if the message is viewed: (1 rows affected)/*(1 rows affected)*/SELECT * fromscore/*Stuid scoreValue101 59 (1 rows affected) Description: In this process (this query window) can find the data that has been inserted, but the other process (query window) is unable to find the data. Because the transaction is not committed at this time, the default isolation level for SQL Server is read COMMITTED, which is unreadable to uncommitted data in other in-progress. */ROLLBACK/*The command was successfully completed. Description: Rollback of uncommitted transactions. */SELECT * fromscore/*(0 rows affected) because it has been rolled back, so the data is not found. */
SQL SERVER Transaction-related