Strategy a property of a transaction
Transactions have ACID properties: Atomic atomicity, consistent consistency, isolated isolation, durable permanent
Atomicity is that a transaction should be a unit of work, the transaction completes, all work is either saved in the database, or completely rolled back, and all is not preserved
Consistency transaction should be in a consistent state after it has been completed or revoked
Isolation multiple transactions take place at the same time, they should not interfere with each other. Should prevent a transaction from processing other transactions also to modify the data when unreasonable access and incomplete read data
After a permanent transaction is committed, the work done is permanently preserved.
Problems arising from concurrent processing of two transactions
Lost updates when two or more transactions select the same row and then update the row based on the initially selected value, a loss update problem occurs, and each transaction is unaware of the presence of other transactions. The final update overrides updates made by other transactions, which results in data loss.
Dirty read an unconfirmed dependency problem occurs when the second transaction selects rows that are being updated by another transaction. The data being read by the second transaction has not been confirmed and may be changed by the transaction that updated the row.
Non-repeatable read when the second transaction accesses the same row multiple times and each time a different data is read, an inconsistent analysis problem occurs. Inconsistent analysis is similar to unconfirmed dependencies because other transactions are also changing the data that the second transaction is reading. In an inconsistent analysis, however, the data read by the second transaction is committed by a transaction that has changed. Also, inconsistent analysis involves reading the same row multiple times (two or more), and each information is changed by another transaction, and the row is not read repeatedly.
Phantom Read the problem occurs when you perform an INSERT or delete operation on a row that belongs to the range of rows that a transaction is reading. The row range for the first read of the transaction shows that one row has ceased to exist in the second or subsequent reads because the row was deleted by another transaction. Similarly, because of the insert operation of another transaction, the second or subsequent read of the transaction shows that a row does not exist in the original read.
Three transaction processing types
Automatic processing transaction system default each T-SQL command is transacted by the system to automatically start and submit
Implicit transactions start automatically when a large number of DDL and DML commands are executed, and are persisted until the user explicitly commits, switching implicit transactions can be 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
User-defined transactions are controlled by the user for the start and end commands of the transaction: BEGIN Tran Commit tran ROLLBACK TRAN command
A distributed transaction that spans multiple servers is called a distributed transaction, and SQL Server can be supported by DTC Microsoft Distributed Transaction Coordinator to handle distributed transactions, which can be performed using the BEgin Distributed transaction command to start a distributed transaction
Isolation level for four transaction processing
Use the set TRANSACTION isolation level to control the default transaction locking behavior of all statements issued by a connection, from lowest to highest in sequence
READ UNCOMMITTED
Performs a dirty read or level 0 isolation lock, which means that no shared locks are issued and exclusive locks are not accepted. When this option is set, uncommitted read or dirty reads can be performed on the data, and values within the data can be changed before the transaction ends, and rows can also appear in the dataset or disappear from the dataset. This option has the same effect as setting NOLOCK on all the tables in all statements within a transaction. This is the least restrictive level in the four isolation levels.
Example
Set table1 (a,b,c)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
Create a new two connection
Executing the following statement in the first connection executes the following statement in the second connection
SELECT * FROM table1 SET TRANSACTION isolation Level READ UNCOMMITTED
Begin TRAN print ' dirty read '
Update table1 set c= ' C ' select * FROM table1
SELECT * FROM table1 if @ @rowcount >0
WAITFOR DELAY ' 00:00:10 '-wait 10 seconds to begin
Rollback TRAN WAITFOR DELAY ' 00:00:10 '
SELECT * FROM table1 print ' Do not repeat '
SELECT * FROM table1
End
The result of a second connection
Dirty read ' Do not repeat '
A b c a B C
A1 B1 C A1 B1 C1
A2 B2 c A2 B2 C2
A3 B3 c A3 B3 C3
READ committed
Specifies that the shared lock is controlled when reading data to avoid dirty reads, but the data can be changed before the transaction ends, resulting in unreadable or phantom data. This option is the default value for SQL Server.
Execute the following statement in the first connection
SET TRANSACTION Isolation Level READ committed
BEGIN Tran
print ' initial '
SELECT * FROM table1
WAITFOR DELAY ' 00:00:10 '--wait 10 seconds
print ' Do not repeat '
SELECT * FROM table1
Rollback Tran
Execute the following statement in the second connection
SET TRANSACTION Isolation Level READ committed
Update table1 set c= ' C '
The result of the first connection
Initial no repetition
A b c a B C
A1 B1 C1 A1 B1 C
A2 B2 C2 A2 B2 C
A3 B3 C3 A3 B3 C
Repeatable READ
Locks all the data used in the query to prevent other users from updating the data, but other users can insert new Phantom rows into the dataset, and phantom rows are included in subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, you should only use this option if necessary.
Execute the following statement in the first connection
SET TRANSACTION Isolation Level repeatable READ
BEGIN Tran
print ' initial '
SELECT * FROM table1
WAITFOR DELAY ' 00:00:10 '--wait 10 seconds
print ' Phantom read '
SELECT * FROM table1
Rollback Tran
Execute the following statement in the second connection
SET TRANSACTION Isolation Level repeatable READ
Insert table1 select ' A4 ', ' b4 ', ' C4 '
The result of the first connection
Initial Phantom Reading
A b c a B C
A1 B1 C1 A1 B1 C1
A2 B2 C2 A2 B2 C2
A3 B3 C3 A3 B3 C3
A4 B4 C4
SERIALIZABLE
Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction completes. This is the most restrictive level in the four isolation levels. Because the concurrency level is low, this option should be used only if necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements within a transaction.
Execute the following statement in the first connection
SET TRANSACTION Isolation Level SERIALIZABLE
BEGIN Tran
print ' initial '
SELECT * FROM table1
WAITFOR DELAY ' 00:00:10 '--wait 10 seconds
print ' No change '
SELECT * FROM table1
Rollback Tran
Execute the following statement in the second connection
SET TRANSACTION Isolation Level SERIALIZABLE
Insert table1 select ' A4 ', ' b4 ', ' C4 '
The result of the first connection
Initial no change
A b c a B C
A1 B1 C1 A1 B1 C1
A2 B2 C2 A2 B2 C2
A3 B3 C3 A3 B3 C3
Five
