Knowledge of the SQL SERVER 2000 isolation LEVEL

Source: Internet
Author: User
Tags commit sql server sql
Server can resolve concurrency problems when locked as a concurrency control mechanism. This allows all transactions to run in an environment that is completely isolated from each other, but can have more than one running transaction at any time.
Serializable is the state of the database that is reached by running a set of concurrent transactions, and is equivalent to the status of the databases that this set of transactions is performing sequentially in some order.

SQL-92 Isolation Level
Although serializable is important for transactions to ensure that the data in the database is correct at all times, many transactions do not always require complete isolation. For example, multiple authors work in different chapters of the same book. New chapters can be submitted to the project at any time. However, for chapters that have already been edited, the author cannot make any changes to this section without the approval of the editor. In this way, although there are new chapters that are not edited, editors can still ensure the correctness of the book item at any time. Editors can view previously edited chapters and recently submitted chapters.

The level at which a transaction prepares to accept inconsistent data is called the isolation level. The isolation level is the degree to which a transaction must be isolated from other transactions. Lower isolation levels can increase concurrency, but at the cost of reducing data correctness. Conversely, a higher isolation level ensures that data is correct, but can have a negative impact on concurrency. The isolation level that the application requires determines the locking behavior that SQL Server uses.

SQL-92 defines the following four isolation levels,

SQL Server supports all of these isolation levels:
Uncommitted reads (the lowest level of transaction isolation, which guarantees that no physical corrupted data is read).
Commit read (SQL Server default level).
can be read repeatedly.
Serializable reads (highest level of transaction isolation, complete isolation between transactions).
If a transaction runs at the Serializable isolation level, any concurrent overlapping transactions can be guaranteed to be serial.
The following four isolation levels allow different types of behavior.

Isolation level dirty Read not repeatable READ Phantom not committed read whether is the commit read or not is repeatable read no whether is serializable

Transactions must run at a repeatable read or higher isolation level to prevent loss of updates. A lost update occurs when two transactions retrieve the same row and then update the row based on the value of the original retrieved. If two transactions update rows with an UPDATE statement and are not updated based on previously retrieved values, no loss updates occur at the default commit read isolation level.

Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.