SQL Server Series: transaction integrity

1. Basic concepts of transactions

A transaction is a logical unit of work composed of a series of tasks. All tasks in this logical unit must be completed or all fail as a whole.

In SQL Server, no matter whether or not begin transaction is explicitly used to mark the start of a transaction, each DDL operation is a transaction.

To encapsulate multiple commands in a transaction, you only need to use two tags to define the scope of the entire transaction: one marked at the beginning of the transaction, and the other at the completion of the transaction, that is to say, commit the transaction's modifications to the data to the disk. IfCodeIf an error is detected, you can roll back or cancel the entire transaction.

◊ Begin transaction

Commit commit transaction

◊ Rollback transaction

 Begin   Transaction      Insert   Into Product (productno, productname) Values ( '  1001 ' , '  Producta  '  )  If   @ Error   <>   0          Begin              Rollback   Transaction              Raiserror ( '  Error  ' ,16 , 1  )  Return          End      Insert   Into Product (productno, productname) Values ( '  1002  ' , '  Productb  '  ) If   @ Error   <>   0          Begin              Rollback   Transaction              Raiserror ( '  Error  ' , 16 , 1  )  Return         End  Commit   Transaction 

1.1. transaction integrity

Transaction integrity uses the acid feature to measure the quality of transactions. Transaction integrity violations include dirty read, nonrepeatable read, and phantom rows ). To solve these three problems, you need to use different levels of integrity or isolation between transactions.

1> ACID properties

The quality of a database product is measured by the degree of support provided by the transaction processing mechanism for acid features. Acid is the abbreviation of four independent features: atomicity, consistency, isolation, and durability ).

Atomicity

The transaction must be atomic. That is to say, at the end of the transaction, the operations in the transaction are either completed or all failed. If some operations in the transaction are written to the disk, but others are not, it violates atomicity.

Token consistency

Transactions must ensure Database Consistency. The database should be in the consistent state before the transaction is executed, and the database will return to the consistent State at the end of the transaction. From the purpose of the acid feature, consistency means that each row and value in the database must be consistent with the reality described and meet the requirements of all constraints. If the order line is written to the disk but the order details are not written, the consistency between the order and orderdetail tables is damaged.

Isolation

Each transaction must be isolated from the results produced by other transactions. No matter whether another transaction is being executed, the transaction must use the data set at the moment it starts to run. Isolation is the barrier between two transactions. One way to test the isolation is to check whether the database has the ability to execute a special set of transactions repeatedly on the same initial dataset, and get the same result every time. Isolation is more important in multi-user databases.

Continuity

Transaction continuity means that the transaction processing result is permanent no matter whether the system is faulty or not. Once a transaction is committed, it remains in the committed State.

2> transaction Defects

The lack of isolation between transactions is manifested in the following three aspects: Dirty read, non-repeated read, and phantom rows. These transaction defects are risks that affect transaction integrity.

Dirty read (dirty reads)

The most obvious defect of a transaction is that before the transaction is committed, its modifications to the data are shown in other transactions. If a transaction reads an update not committed by another transaction, it is called dirty read.

 Begin   Transaction      Update Product Set Productname =  '  Transaction dirty read '   Where Productid =  1      Set   Transaction   Isolation   Level   Read   Uncommitted      Select Productname From Product Where Productid =  1 Commit   Transaction 

The execution result is as follows:

The first transaction has not completed the modification of the data, but the second transaction can read the Modification result, which violates the transaction integrity.

Secondary cannot be read repeatedly (non-repeatable reads)

Repeatable reading is similar to dirty reading, but it occurs when the transaction sees data updates committed by other transactions. True isolation means that one transaction will not affect another transaction. If the isolation is complete, a transaction should not be able to see data updates other than this transaction. When performing the same read operation in a transaction, the same result should be obtained each time. If different results are obtained in two read operations, it means that the non-repeated read transaction defect occurs.

 Set   Transaction   Isolation   Level   Read   Committed  Begin   Transaction      -- Update product set productname = 'dirtyread' where productid = 1      Select Productname From Product Where Productid =  1      Begin   Transaction          Update Product Set Productname =  '  Non-Repeatable read  '  Where Productid =  1  Commit   Transaction  Select Productname From Product Where Productid =  1 

The execution result is as follows:

Phantom rows)

The phantom line is the minimal transaction integrity defect. Similar to non-repeated reads, the phantom row refers to the situation where the update result of a transaction affects another transaction, but what is different from non-repeated reads is that it not only affects the data values in the result set of another firm, but also enables the SELECT statement to return other different record rows.

 Begin  Transaction      Select Productid, productno From Product Where Productno =  '  1000  '      Begin   Transaction          Update Product Set Productno =  '  1000 '   Where Productid =  2  Commit   Transaction  Select Productid, productno From Product Where Productno =  '  1000  ' 

The execution result is as follows:

Among all the preceding transaction defects, dirty reads are the most harmful, secondary non-repeated reads are not allowed, and phantom rows are the least harmful.

3> isolation level

Database products also process these three transaction defects by establishing isolation between transactions. The isolation level is the height of the isolation band between transactions. It can be adjusted according to specific requirements to control the transaction defects that can occur.

The ANSI SQL-92 defines four isolation levels:

SQL Server uses locks for isolation. Given that the lock affects performance, you must weigh the isolation level and performance. The default isolation level of SQL Server is read committed, which is applicable to most OLTP projects.

Partition Level 1 -- read uncommitted

The strictest isolation level is read uncommitted, which cannot prevent any transaction defect because it does not provide isolation between transactions at all. Setting the SQL Server isolation level to read uncommitted is equivalent to setting the SQL Server lock to nolock. This setting is suitable for reports or read-only applications.ProgramAt this time, SQL Server only provides enough locks to prevent data crashes, instead of providing enough locks for row competition. This is not suitable for systems where data is often updated.

Publish Level 2 -- Read committed

Read committed prevents the most serious transaction defects, and won't be a quagmire where the system is stuck in excessive lock contention. For this reason, SQL server uses her as the default isolation level, which is an ideal choice for most OLTP projects.

Upload Level 3 -- Repeatable read

Repeatable read can prevent dirty reads and non-repeated reads. It increases the isolation level of transactions, and the lock contention pressure is not as serious as the serializable isolation level.

Protocol Level 4 -- serializable

This is the strictest isolation level, which prevents all transaction defects and passes the serial transaction test mentioned in the isolation definition above. This mode is suitable for situations where absolute transaction integrity requirements are more important than performance. Banks, accounting systems, and highly competitive sales databases (such as stock markets) usually use serializable isolation levels.

Using serializable isolation level is equivalent to setting the lock to holdlock, which will keep the transaction locked throughout the execution, and even include the shared lock. Although this setting provides full transaction isolation, it will cause bad lock contention and lower performance.

2. SQL Server Lock Mechanism

SQL Server uses locks to isolate transactions. This prevents data operated by a transaction from being affected by another transaction. Each has the following three features:

Granularity-lock size

Unlock mode-lock type

Duration-lock Isolation Mode

2.1. Lock Granularity

The SQL Server lock manager tries to strike a balance between the lock size and number to achieve better performance. The conflicting focus is on concurrency (smaller locks allow more transactions to access data at the same time) and performance (the faster the lock is ). To achieve a balance, the lock manager dynamically switches from a lock group to another lock group.

1> 25 row locks may be upgraded to one page lock.

2> if more than 25 locked rows are distributed on four other pages in the same extended area, the above page lock and the 25 row-level locks may be upgraded to an extended disk lock because more than 50% of the pages in the extended disk are locked.

3> if enough extended disk areas are locked, all these locks may be upgraded to a table lock.

The dynamically adjusted lock policy brings significant benefits to SQL server developers:

Failover automatically achieves the optimal balance between performance and concurrency without any programming;

As the database grows, the lock manager will use the matching lock granularity accordingly to ensure the database has good performance;

◊ Dynamic locking simplifies management.

2.2 lock mode

In addition to the lock granularity, that is, the lock size attribute, the lock also has the lock mode attribute, which determines the purpose of the lock. SQL Server has a variety of lock modes.

1> lock contention

In SQL Server, the interaction and compatibility of locks have an important impact on transaction integrity and performance. Some lock modes will reject some other lock modes. The lock compatibility is as follows:

 

 

 

 

 

 

 

2> shared lock (s)

So far, the most common and abuse lock is the shared lock, which is a simple "read lock ". When a transaction gets a shared lock, it is like declaring that "I am viewing this data ". Generally, multiple transactions can view the same group of data at the same time, but ultimately depends on the Isolation Mode.

3> exclusive lock (X)

Using the exclusive lock means that the transaction is writing data. For the same data, only one transaction can hold the exclusive lock at a time. Other transactions cannot view the data during the duration of the exclusive lock.

4> Update lock (u)

The update lock is not the lock used when the transaction executes the update. The update lock means that the transaction is about to use the exclusive lock. It is currently scanning data to determine the rows that are locked using the exclusive lock. Update locks can be used as shared locks to be converted to exclusive locks.

To avoid deadlocks, only one transaction holds the update lock at the same time.

5> intention lock

The intention lock is a warning lock that warns other transactions about something to happen. Intention locks are mainly designed to improve performance.

2.3 view locks

 
Sp_lock