Isolation level for SQL Server transactions

Isolation level for SQL Server transactions

Database is to be shared by the vast number of customers to access, then in the database operation process is likely to occur in the following kinds of uncertainty.

Update missing (Lost update)

Two transactions update a row of data at the same time, but the second transaction fails to exit, resulting in two modifications to the data that are invalidated. This is because the system does not perform any lock operations, so concurrent transactions are not isolated.

Dirty Read ( Dirty Reads )

A transaction begins reading a row of data, but another transaction has updated the data but failed to commit it in time. This is quite dangerous because it is possible that all operations are rolled back.

non-repeatable read ( non-repeatable Reads )

A transaction repeatedly reads the same row of data two times, but gets different results. It includes the following situations:

(1) After the transaction T1 reads a data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.

(2) Phantom Reading (Phantom Reads): Transactions are queried two times during operation, The result of the second query contains data that does not appear in the first query or the data that appears in the first query (the same SQL statement that does not require two queries). This is due to the fact that another transaction was inserted into the data during the two queries.

To avoid the above scenarios, in the standard SQL specification, 4 transaction Isolation levels are defined, with different isolation levels dealing differently with transactions.

Unauthorized read (READ UNCOMMITTED): Allows dirty reads, but does not allow updates to be lost. If a transaction has already started writing data, the other data does not allow simultaneous writes, but allows other transactions to read the data on this line. This isolation level can be achieved through an "exclusive write lock".

Authorization Read (read Committed): Allows non-repeatable reads, but dirty reads are not allowed. This can be achieved through "instantaneous shared read lock" and "exclusive write lock". Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

Repeatable Read (Repeatable Read): Disables non-repeatable reads and dirty reads, but sometimes phantom data can occur. This can be achieved through "shared read lock" and "exclusive write lock". Transactions that read data prohibit write transactions (but allow read transactions), and write transactions prohibit any other transactions.

serialization (Serializable): Provides strict transaction isolation. It requires the transaction to serialize execution, and the transaction can be executed one after the other, but not concurrently. If transaction serialization is not possible only through row-level locks, other mechanisms must be ensured that the newly inserted data is not accessed by the transaction that just performed the query operation.

The higher the isolation level, the greater the integrity and consistency of the data, but also the greater the impact on concurrency performance. For most applications, it is preferable to set the isolation level of the database system to read Committed, which avoids dirty reads and has good concurrency performance. Although it causes concurrency problems such as non-repeatable reads, virtual reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.

Isolation level of a transaction

SQL Server isolates transactions by using different types of locks on the lock resource. In order to develop a secure transaction, it is important to define the transaction content and the circumstances in which it should be rolled back, and how and for how long it will remain locked within the transaction is equally critical. This is determined by the isolation level. With different isolation levels applied, SQL Server gives developers the ability to define the degree of isolation from other transactions for each individual transaction. The transaction isolation level is defined as follows:

· whether to use locks when reading data

· how long the read lock lasts

· what type of lock to use when reading data

· What to do if the read operation wants to read data that has been exclusively locked by other transactions? In this case,SQL Server can:

· wait until other transactions release the lock

· read data that is not submitted

· read the final version after the data is submitted

ANSI 99 defines 4 levels of transaction isolation that SQL Server 2005 can fully support:

· Non-committed read No locks are checked or used when reading data. Therefore, data that is not committed may be read in this isolation level.

· Read Committed only Read committed data and wait for other transactions to release exclusive locks. Shared locks for read data are released as soon as the read operation is complete. Read Committed is The default isolation level for SQL Server.

· Repeatable Read reads the data as read-committed, but retains the shared lock until the transaction ends.

· Serializable works similarly to repeatable reads. But it will not only lock the affected data, but also lock the range. This prevents new data from being inserted into the scope of the query, which can lead to phantom reads.

In addition, SQL Server has two transaction levels that use row versioning to read data (these isolation levels are examined in detail later in this chapter). Row versioning allows a transaction to read the last committed version of the data after the data is exclusive locked. Query performance is greatly enhanced because you do not have to wait until the lock is released for read operations. The two isolation levels are as follows:

· Read-Committed snapshot It is a new implementation of the read-committed level. Unlike the normal commit read level,SQL Server reads the last committed version and therefore does not have to wait while the read operation is in progress until the lock is freed. This level can override the commit read level.

· Snapshot This isolation uses row versions to provide transactional-level read consistency. This means that in one transaction, because read consistency can be implemented through row versioning, the same data can always be read as if it were at the serializable level without being locked out to prevent changes from other transactions.

Regardless of the isolation level defined, changes to the data are always locked by an exclusive lock and released until the end of the transaction.

In many cases, it is not a simple decision to define the correct isolation level. As a general rule, you choose to lock the least amount of data in the shortest possible time, but at the same time you can still provide the transaction with the level of security it requires.

Read Committed

In SQL Server 2005, the Read Committed isolation level is the default isolation level when a connection is established. There are two types of this level: committed read and committed read Snapshot isolation level. Which type of application is defined by the database option. The committed read level waits before the data is read until the blocking lock is freed. The committed read snapshot level uses row versioning to read the last committed version of the data when the data is blocked by another transaction.

Use committed read level:

BEGIN TRAN

SELECT

FirstName, LastName, EmailAddress

From

Person.Contact

WHERE

ContactID = 1

return EmailAddress to [email protected] contact Gustavo Achong.

Now assume that another transaction has changed EmailAddress in the transaction open state. Open a second query window and execute the following batch to update EmailAddress without committing the transaction:

Use AdventureWorks;

BEGIN TRAN

UPDATE

Person.Contact

SET

EmailAddress = '[email protected]'

WHERE

ContactID = 1

This UPDATE statement will run correctly. A row is affected, even if the data was read by a transaction in Query window 1 before the transaction had run out. Because the Read committed level does not persist the shared lock used for the SELECT statement until the end of the transaction. Shared locks are freed by SQL Server immediately after the data is read. This will be a problem when a consistent reading is required. We will implement the following "get consistent repeatable read operations".

Now switch to query window 1 and try to read the data again:

SELECT

FirstName, LastName, EmailAddress

From

Person.Contact

WHERE

ContactID = 1

The query did not end because the SELECT statement was blocked. SQL Server tries to get a shared lock on the key of contactid= 1, but this operation is not possible because the UPDATE statement in Query window 2 has an exclusive lock on it. Although Query window 2 is at the committed read level (because you did not change the default level), an exclusive lock still exists. This blocking will persist because the exclusive lock for data changes will persist until the end of the transaction.

Switch to query window 2 and let the query in Query window 1 continue to run. Type and execute the following SELECT statement to check the authorization and waiting locks in the database.

You can see a shared lock with a status of wait. This is the query that is running in Query window 1. It waits for a query in Window 2, which has an exclusive lock on the same resource.

Execute a ROLLBACK TRAN statement in Query window 2 to roll back the update statement. Then switch back to query window 1. As you can see, the query in Query window 1 is complete and the results are the same as before. When the transaction at the end of the query window 2 ends, the lock is freed, and the query in Query window 1 is no longer blocked. Because of the transaction rollback in Query window 2, the result from Query window 1 is the original data. If the transaction in Query window 2 is committed, the Query window 1 gets the new data as the result.

Execute a COMMIT TRAN statement in Query window 1 and close all the query Windows.

As you can see, in the (default) read-committed level, SQL Server waits until the exclusive lock is freed before it reads, to get the real commit data. It can also be seen that a shared lock lasts until the data is read, and an exclusive lock lasts until after the transaction commits. This behavior can cause problems when many transactions change data almost at the same time. In these cases, the read data can be very slow due to blocking caused by an exclusive lock. In some cases, however, it is appropriate to use the last submitted version of the data. In these cases, you can change the committed read level to the committed read snapshot level.

If you want to read data in Window 1, you can use this method:

SELECT

FirstName, LastName, EmailAddress

From

Person.Contact with (NOLOCK)

WHERE

ContactID = 1

Let it cancel all locking mechanisms, then the exclusive lock will not affect the query.

Use NOLOCK Note: In SQL Server, the NOLOCK prompt will enable the "READ UNCOMMITTED" behavior. In SQL Server Mobile, using the NOLOCK hint will still give the commit read isolation level. SQL Server Mobile maintains a copy of the data to ensure that data can be read without using shared locks to help protect the data.

Using the committed read snapshot level

To activate a read-committed snapshot level

Use master;

ALTER DATABASE AdventureWorks

SET Read_committed_snapshot on

Note : When you set the Read_committed_snapshot option, only connections that perform the ALTER database command are allowed in the database. No other open connections are allowed in the database until the ALTER database is complete. The database does not have to be in single-user mode.

Now, execute the following code to start a transaction and change EmailAddress (but leave the transaction open) as before:

Use AdventureWorks;

BEGIN TRAN

UPDATE Person.Contact

SET emailaddress = '[email protected]'

WHERE ContactID = 1;

Open the Second Query window and execute the following statement to read the column name and EmailAddress column for ContactID 1.

Use AdventureWorks;

BEGIN TRAN

SELECT FirstName, LastName, EmailAddress

From Person.Contact

WHERE ContactID = 1;

Returned the contact Gustavo Achong EmailAddress [email protected], this is the last version of this line to commit. Unlike a committed read level without a snapshot, this query is not blocked. Close Query window 2 and switch to query window 1.

Execute the following statement to roll back the transaction and switch back to the committed read level (this query waits until you close the Query window 2):

ROLLBACK TRAN

GO

Use master;

ALTER DATABASE AdventureWorks

SET Read_committed_snapshot OFF

important This isolation level can be used to reduce blocking. But be aware that this is a database option. When it has changed, all transactions that will use the committed read level in the database system will also change their behavior. Therefore, it is advisable to use this level only if all of these transactions read the last submitted version of the data and the version of the data that is actually submitted is logically correct.

Get consistent repeatable read operations

One drawback of the read-committed level is that the data read by one transaction may be changed by another transaction while the transaction is running. Therefore, consistent reads are not guaranteed under two read-committed levels. Getting a consistent read means that in a transaction, the data being read is always the same.

1. A shared lock is used when reading the data, but the lock is released immediately after the read operation is completed. Therefore, other transactions can change the data that has just been read.

2. Read-committed snapshot read the last committed version of the data. When it reads the data for the second time, the last committed version may become a new version because the second transaction has committed a change to the data.

This inconsistency can cause problems when a consistent read is required (for example, a report). Imagine that your transaction calculates a number of business values through the data. When this calculation is made in the read-committed level, it is possible that the underlying data has been incorrectly computed as a result of changes in the transaction calculation. To successfully perform this calculation, you can use the snapshot isolation level. It uses row versioning to provide a committed version of the data, but unlike a committed read snapshot, it always provides the version of the data that was last committed when the transaction was started. Therefore, SQL Server always obtains the same data throughout the execution of the transaction.

Using the Snapshot Isolation level

The snapshot isolation level needs to be activated once in the database. Once activated, each connection can be used when it is needed.

Use master;

ALTER DATABASE AdventureWorks

SET allow_snapshot_isolation on;

Now let's say we want to run some reports based on the Sales.SalesOrderDetail table, but we need consistent read operations. Execute the following statement to activate the snapshot isolation level for the transaction and start a transaction that returns the order line totals. Remember the value of OrderTotal.

Use AdventureWorks;

SET TRANSACTION Isolation Level SNAPSHOT

BEGIN TRAN

SELECT SUM (LineTotal) as OrderTotal

From Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

The meaning of the parameter snapshot:

1. Specifies that the data read by any statement in the transaction will be a transactionally consistent version of the data that existed at the beginning of the transaction. Transactions can only identify data modifications that were committed before they were started. Statements executed in the current transaction will not see data modifications made by other firms after the start of the current transaction. The effect is as if the statement in the transaction obtains a snapshot of the submitted data, because the data exists at the beginning of the transaction.

2. The SNAPSHOT transaction does not request a lock when the data is being read unless the database is being recovered. SNAPSHOT transactions that read data do not prevent other transactions from writing to the data. Transactions that write data do not prevent SNAPSHOT transactions from reading data.

3. In the rollback phase of database recovery, if an attempt is made to read data locked by another transaction that is being rolled back, the SNAPSHOT transaction will request a lock. The SNAPSHOT transaction is blocked until the transaction has finished rolling back. When a transaction is authorized, the lock is released immediately.

4. The Allow_snapshot_isolation database option must be set to on to start a transaction that uses the SNAPSHOT isolation level. If you use SNAPSHOT isolation level transactions to access data from multiple databases, you must set allow_snapshot_isolation to on in each database.

5. Transactions that start with other isolation levels cannot be set to the SNAPSHOT isolation level, which will cause the transaction to abort. If a transaction starts at the SNAPSHOT isolation level, you can change it to another isolation level, and then return to SNAPSHOT. A transaction begins with the execution of a BEGIN TRANSACTION statement.

6. Transactions running under the SNAPSHOT isolation level can view changes made by the transaction. For example, if a transaction performs an UPDATE on a table and then issues a SELECT statement on the same table, the modified data is included in the result set.

Open a second Query window and update the SalesOrderDetail table to change the underlying data used in Query window 1. (If you want to repeat this example, change the value of OrderQty to a different number so that the following code can really change the data in the database):

Use AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

and ProductID = 777

Close Query window 2, switch to query window 1, and then repeat the following SELECT statement.

SELECT SUM (LineTotal) as OrderTotal

From Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

As you can see, because the snapshot isolation level ignores changes in the data during transaction runs, the results are the same as before. The last committed value at the beginning of a transaction is always provided at the snapshot level.

Commit the transaction and execute the following code to repeat the query again: Now you can see that the result has changed because the transaction is over.

COMMIT TRAN

SELECT SUM (LineTotal) as OrderTotal

From Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

Execute the following code to close the snapshot isolation level of the AdventureWorks database:

ALTER DATABASE AdventureWorks

SET allow_snapshot_isolation OFF;

Avoid simultaneous data updates

As mentioned earlier, the snapshot isolation level does not lock the data at the time of the read operation, but provides a consistent view throughout the transaction. In some cases, it is necessary to lock the data during the execution of the entire transaction to avoid changes to the data by other transactions. Suppose you want to invoice an order. You first need to get the data and examine it, and then generate an invoice for it. In this case, the data needs to be locked from the beginning of the transaction to avoid other transactions changing it. In this case, either snapshot isolation or the Read Committed isolation level is not a good choice. For this scenario, you can use the REPEATABLE read isolation level. This isolation level is similar to the working process of a read-committed level without a snapshot, but it maintains a shared lock until the end of the transaction. As a result, it prevents updates to the data.

Use REPEATABLE READ isolation level

Suppose you want to process an order with a OrderID of 43659. First, you must select the data. To prevent other transactions from changing the data being read, use REPEATABLE read isolation.

Use AdventureWorks;

SET TRANSACTION Isolation Level repeatable READ

BEGIN TRAN

SELECT SalesOrderID, Salesorderdetailid, ProductID, OrderQty

From Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

Parameters Repeatable READ the meaning of:

1. The specified statement cannot read a row that has been modified by another transaction but has not yet been committed, and specifies that no other transaction can modify the data read by the current transaction until the current transaction completes.

2. A shared lock is set on all data read by each statement in the transaction, and the shared lock remains until the transaction completes. This prevents other transactions from modifying any rows that are read by the current transaction. Other transactions can insert new rows that match the search criteria of the current transaction issuing statement. If the current transaction then retries to execute the statement, it retrieves the new row, resulting in a phantom read. The concurrency level is lower than the default READ COMMITTED isolation level because the shared lock remains at the end of the transaction instead of being released at the end of each statement. This option is used only when necessary.

Open the Second Query window and execute the following code to try to update the SalesOrderDetail table to change the underlying data to be used in Query window 1:

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

and ProductID = 777

The query will wait. Unlike the snapshot isolation level, it is not possible to update data because shared locks persist to prevent other transactions from changing data. This lock can be viewed sys.dm_tran_locks the previously used management view.

Click the Cancel Execution Query button on the toolbar to cancel the query in Query window 2. Instead, execute the following INSERT statement to add a new line item to the order.

INSERT into Sales.SalesOrderDetail

(

SalesOrderID,

Carriertrackingnumber,

OrderQty,

ProductID,

Specialofferid,

UnitPrice,

Unitpricediscount

)

VALUES (43659, ' 4911-403c-98 ', 1,758,1,874,0)

Note that this statement will execute successfully even if you are at a repeatable read isolation level. Because repeatable reads lock data to prevent updates to the data, the INSERT statement inserts new data into the database, which is allowed. The new row is in the query scope of the transaction SELECT statement in Query window 1, so it is read the next time the transaction acquires the same data. This is known as Phantom Reading.

Repeat the SELECT statement and commit the transaction as follows:

SELECT SalesOrderID, Salesorderdetailid, ProductID, OrderQty

From Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

COMMIT TRAN

As you can see, the new row is read by the SELECT statement because it is within the query scope of the statement. The repeatable read level prevents existing data from being changed, but does not prevent new data from being inserted in the query scope of the SELECT statement.

Other

There are several levels of SET transaction:

SET TRANSACTION Isolation Level

{READ UNCOMMITTED

| READ COMMITTED

| Repeatable READ

| SNAPSHOT

| SERIALIZABLE

}

[ ; ]

There are several isolation levels that are not mentioned in the example above:

1. READ Uncommitted

Specifies that the statement can read rows that have been modified by other transactions but have not yet been committed.

A transaction that runs at the read uncommitted level does not issue a shared lock to prevent other transactions from modifying the data read by the current transaction. READ UNCOMMITTED transactions are not blocked by exclusive locks, and exclusive locks prohibit the current transaction from reading rows that have been modified but not yet committed by other transactions. After you set this option, you can read uncommitted modifications, which are called dirty reads. Before the end of the transaction, you can change the values in the data, and the rows can appear in the dataset or disappear from the data set. This option works the same as setting NOLOCK on all tables in all SELECT statements within a transaction. This is the least restrictive level in the isolation level.

In SQL Server 2005, you can also use any of the following methods to minimize lock contention while protecting transactions from dirty READ UNCOMMITTED data modifications:

1. READ COMMITTED Isolation level and set the READ_COMMITTED_SNAPSHOT database option to ON.

2. SNAPSHOT isolation level.

1. READ COMMITTED

Specifies that the statement cannot read data that has been modified by another transaction but has not yet been committed. This prevents dirty reads. Other transactions can change data between the individual statements of the current transaction, resulting in non-repeatable reads and phantom data. This option is the default setting for SQL Server.

The behavior of READ COMMITTED depends on the settings of the READ_COMMITTED_SNAPSHOT database option:

1. If Read_committed_snapshot is set to OFF (the default), the database engine uses a shared lock to prevent other transactions from modifying rows during the read operation of the current transaction. Shared locks also prevent statements from reading rows modified by these transactions before other transactions are completed. A shared lock is released when the statement completes.

2. If Read_committed_snapshot is set to ON, the database engine uses row versioning to provide a transactionally consistent snapshot of the data for each statement because the data exists at the beginning of the statement. Do not use locks to prevent other transactions from updating data.

When the Read_committed_snapshot database option is set to ON, you can use the Readcommittedlock table hint to request a shared lock for each statement in a transaction running at the Read_committed isolation level, rather than row versioning.

Note: When you set the Read_committed_snapshot option, only connections that perform the ALTER database command are allowed in the database. No other open connections are allowed in the database until the ALTER database is complete. The database does not have to be in single-user mode.

1. SERIALIZABLE

Please specify the following:

1. The statement cannot read data that has been modified by another transaction but has not yet been committed.

2. No other transaction can modify the data read by the current transaction until the current transaction is complete.

3. Before the current transaction completes, the other transaction cannot insert a new row with the key value read by any statement in the current transaction.

A range lock is within the range of key values that match the search criteria for each statement executed in the transaction. This prevents other transactions from being updated or inserting any rows, thereby qualifying any statements executed by the current transaction. This means that if any of the statements in the transaction are executed again, the statements will read the same set of rows. The range lock is maintained until the transaction is complete. This is the most restrictive isolation level because it locks the entire range of keys and maintains a range lock until the transaction is complete. Because the concurrency level is low, you should use this option only when necessary. This option works the same as setting HOLDLOCK on all tables in all SELECT statements within a transaction.

Places to be aware of:

1. Only one Isolation level option can be set at a time, and the options set will always be valid for that connection until the option is explicitly changed. All read operations performed in a transaction are run under the rules of the specified isolation level, unless the table hint in the FROM clause of the statement specifies additional locking behavior or versioning behavior for the table.

2. The transaction isolation level defines the type of lock that can be obtained for a read operation. Shared locks acquired for read COMMITTED or repeatable read are typically row locks, although row locks can be promoted to page or table locks when reading a large number of rows in a page or table. If a row is modified by a transaction after it has been read, the transaction acquires an exclusive lock to protect the row, and the exclusive lock remains until the transaction completes. For example, if the repeatable READ transaction has a shared lock for a row, and the transaction subsequently modifies the row, the shared row lock is converted to an exclusive row lock.

3. During a transaction, it is possible to switch transactions from one isolation level to another at any time, with one exception. That is, you cannot do this when you change from either isolation level to SNAPSHOT isolation. Doing so will cause the transaction to fail and roll back. However, transactions initiated in SNAPSHOT isolation can be changed to any other isolation level.

4. After changing a transaction from one isolation level to another, the resources read after the change are protected according to the rules of the new level. Resources read before the change will continue to be protected at the previous level of the rule. For example, if a transaction is changed from read COMMITTED to SERIALIZABLE, the shared lock acquired after the change is persisted until the transaction ends.

5. If the SET TRANSACTION isolation level is emitted in a stored procedure or trigger, when the object returns control, the isolation levels are reset to the level that is valid when the object is called. For example, if repeatable READ is set in a batch and the batch calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level reverts to repeatable READ when the stored procedure returns control to the batch.

Category: MSSQL

Isolation level for SQL Server transactions