The attribute transaction of a transaction has an ACID attribute: that is, atomic atomicity, consistent consistency, ISOLATED Isolation, durable permanent atomicity is that transactions should be used as a work unit, transaction processing, all work or save it in the database, Either completely rolling, all do not retain consistency completion or revocation, should be in a consistent state isolation multiple transactions, and they should not interfere with each other. It should be prevented from handling other transactions to also modify data. When the unreasonable access and incomplete reading data permanent transaction is submitted, the work done is permanently saved the problem of the secondary transaction concurrent process will lose the update when two or more transactions selection the same line. , Then update this line based on the original selected value, there is a loss of update, and each transaction does not know the existence of other transactions. The last update will rewrite the update made by other transactions, which will result in data loss. Dirty reading When the second transaction selection other transactions are being updated, unrecognized correlation issues will occur. The data that the second transaction is reading has not been confirmed and may be changed by the transaction of the update this line. It is inconsistent analysis issues that are inconsistent with multiple access to the same row multiple times and each time you read different data. Inconsistent analysis is similar to unrecognized correlation, because other transactions are also changing the data being read by the second transaction. However, in inconsistent analysis, the data read by the second transaction is submitted by a transaction that has been changed. Moreover, inconsistent analysis involves multiple (twice or more) reading the same row, and each information is changed by other transaction; thus the line is not read. Fantasy Read When inserting or deleting an action on a row, the row belongs to the range of a row that is being read, an phantom read problem occurs. The first line of transactions read is displayed in one of the lines that are not repurchable in the second reading or subsequent reading because the row has been deleted by other transactions. Similarly, due to the insertion operation of other transactions, the second or subsequent read display of the transaction has no existing readings. Triple Transaction Type Automatic Processing Transaction System The default Each T-SQL command is transaction processing by the system automatically starts and submits implicit transactions. When there is a lot of DDL and DML commands, it will start automatically, and keep it to the user clearly submitted To switch implicit transactions to use set implicit_transactions. To connect to set the hidden transaction mode. When set to ON, Set Implicit_Transactions will set the connection to the hidden transaction mode. When set to OFF, return the connection to the Auto Submit Transaction mode User-defined transaction by the user to control the start and end command of the transaction. The command is: Begin TRAN commit trrough rollback TRAN command Distributed Transaction transaction transactions transactions crossing multiple servers called distributed Transaction, SQL Server can support processing a distributed transaction by DTC Microsoft Distributed Transaction Coordinator, you can use the Begin Distributed Transaction command to launch a distributed transaction quaternary version of the isolation level using the Set Transaction Isolation Level to control all statements sent by the connection Default transaction lock behavior, from low to high, read uncommitted, perform dirty reading or 0-level isolation lock, which means that it does not send a shared lock, nor does it accept the lock. When this option is set, the execution of the data may not be submitted to read or dirty; the value within the data can be changed before the end of the transaction, and the line can also appear in the data set or disappear from the data set. The role of this option is the same as all tables in all statements in transactions. This is the minimum level in the four isolation levels.
For example, Table1 (A, B, C) A B CA1 B1 C1A2 B2 C2A3 B3 C3 New Two Connections Performing the following statement in the first connection In the second connection, performing the following statement Select * from table1 set Transaction isolation level s UNCOMMITTEDBEGIN TRAN PRINT 'Dirty' Update Table1 Set C = 'C' Select * from table1select * from table1 if @@ RowCount> 0Waitfor Delay '00: 00: 10' - Waiting for 10 seconds Begin Rollback Tran Waitfor Delay '00: 00 : 10'select * from table1 print 'Do not repeat the result of' SELECT * from Table Lead The second connection result Dirty read 'Non-repeated Read' A B C A B C A1 B1 C A1 B1 C1 A2 B2 C A2 B2 C2A3 B3 C A3 B3 C3read Committed designated Control the shared lock when reading data is to avoid dirty reading, but the data can be changed before the end of the transaction, resulting in an unusable read or phantom data. This option is the default value of SQL Server. Performing the following statement in the first connection SET Transaction Isolation Level Read CommittedBegin TRANPRINT 'Initial' Select * from table1waitfor delay '00: 00: 10' - Waiting for 10 seconds print 'Do not repeat the' SELECT * from Table1Rollback TRAN in the second Performing the following statement SET Transaction Isolation Level Read CommittedUpdate Table1 Set C = 'c' The result of the first connection result initial non-repeated read A b C A B CA1 B1 CA3 B1 CA2 B2 C2 A2 B2 CA3 B3 C3 A3 CrepeAtable READ locks all the data used in the query to prevent other users from updating data, but other users can insert new phantom rows into the data set, and the phantom row includes in subsequent reading in the current transaction.
Because it is concurrent than the default isolation level, this option should only be used if necessary. Performing the following statement in the first connection SET Transaction Isology Level Repeatable Readbegin TRANPRINT 'Initial' Select * from table1waitfor delay '00: 00: 10' - Waiting for 10 seconds print 'phantom read' Select * from table1rollback TRAN in the second The following statement is performed in the following statement. C3 A3 B3 C3A4 B4 C4Serializable places a range lock on the data set to prevent other users from updating the data set before the transaction is completed or insert the row into the data set. This is the maximum level in the four isolation levels. Because concurrent levels are lower, this option should only be used if necessary. The role of this option is set to Holdlock on all tables in all SELECT statements in the transaction.
Performing the following statement in the first connection SET Transaction Isology Level SerializableBegin TRANPRINT 'Initial' Select * from table1waitfor delay '00: 00: 10' - Waiting for 10 seconds print 'No change' SELECT * from Table1Rollback TRAN in the second connection Perform the following statement set Transaction isolation level SerializableInsert Table1 Select 'A4', 'B4', 'C4' First Connection result Initial No change A b C A B CA1 B1 C1 A1 B1 C1 A2 B2 C2 A2 B2 C2A3 B3 C3 A3 B3 C3 five transaction nesting syntax and @@ TRANCOUNT effect begin TRAN @@Trancount 1Commit TRAN @@TRANCOUNT-1ROLLBACK TRAN Make @@ TRANCOUNT Return 0SAVE TRAN does not affect @@ TRANCOUNT Example 1) SELECT 'Transaction before' @@TRANCOUNT - Value 0Begin Tran Select 'The first transaction', @@TRANCOUNT - Value 1 Select * from table1 begin TRAN SELECT 'The second transaction', @@ TRANCOUNT - value 2 delete table1 Commit Tran Select 'submits the second transaction', @@TRANCOUNT - value 1 Rollback Translection 'Roll Rolling First Transaction', @@TRANCOUNT - Value is 02) Select 'Transaction before', @@ TRANCOUNT - Value 0Begin Tran Select 'The first transaction', @@ TRAN Count - Value 1 Select * from table1 save T1 select 'Save the first transaction after', @@ TRANCOUNT - Value 1 Begin Tran Select 'second transaction', @@TRANCOUNT - value 2 delete Table1 Rollback TRAN T1 Select 'Roll to Save Point T1', @@ TRANCOUNT - Note The value of 2 if @@TRANCOUNT> 0rollback Translection ', @@ TRANCOUNT - Creating a 0SET XACT_ABORT control statement When the error is wrong, whether it is automatically rolled back to the current transaction, such as Set XACT_ABORT ONBEGIN TRAN SET XACT_ABORT OnBEGIN TRAN SET XACT_ABORT OnBEGIN TRAN SELECT * FROM A do not exist table roll backtranprint 'processing is complete,'
