On the isolation level of database

Source: Internet
Author: User

Database transaction isolation, English is called Transaction isolation level.

Recently in the maintenance of the project to the customer, a table in two processes at the same time update and query encountered deadlock (DeadLock), the data table has millions of thousands records, the above processing was updated thousands of records,

Query the entire table.

This is the premise, in order to understand this deadlock, probably related to the deadlock, isolation level, lock escalation and trace trace content, is sorted out, today talk about isolation level.

Profile

There are 3 types of non-readable phenomena, first of all about the acid characteristics of the RDBMS I (isolation-isolation)

· Dirty Read

· Non-REPEATABLE READ

· Phantom read

Then talk about the 4 levels of the transaction isolation level.

· Read UNCOMMITTED

· Read committed

· REPEATABLE READ

· Serializable

The higher the lower the isolation level, the lower the isolation level becomes.

For the highest isolation level, the read-out in the top 3 does not occur. However, there is a significant loss in system performance.

Presumably can be summed up as [want to improve the performance of the system to accept a certain degree of reading does not fit, if even if the performance is not related to the lack of it will not occur on the read the non-fit]

Reading does not fit

Explain that the 3 reads do not fit the phenomenon

Dirty Read

A phenomenon that reads data that has not yet been committed by another transaction:

1. Transaction A to update record A to record B (not yet submitted)

2. Transaction B queries the record

3. Transaction A has been rolled back

4. Transaction B reads record b

This is to set the isolation level to the lowest level, ah, have not seen such a system.

Non-REPEATABLE READ

Read the updated data of other transactions, resulting in a non-coherent read operation.

1. Transaction a queries the record, which is called a

2. Transaction b updates the contents of record A to record B and submits it.

3. Transaction a queries the record a again, and the content obtained has become record B.

Phantom read

saw a non-coherent phenomenon caused by records inserted by other transactions.

1. Transaction A is not logged when querying records.

2. Transaction B inserts the record and submits it.

3. Transaction A records that were inserted by transaction B when querying records again.

is not the feeling and non-repetition read a bit like Ah, focus on the insert operation or update operation.

In addition, the following is also the Phantom read OH

1. Transaction a statistical records are X-pieces.

2. Transaction B inserts the record and submits it.

3. Transaction A is again recorded and the number of pieces obtained is x+1.

This is the 3 kinds of non-binding phenomena about read operations.

Transaction isolation level and read non-binding relationship

Dirty Read Non-REPEATABLE READ Phantom read
Read UNCOMMITTED Happen Happen Happen
Read committed does not occur Happen Happen
REPEATABLE READ does not occur does not occur Happen
Serializable does not occur does not occur does not occur

This is not to think that every time you choose to set the isolation level of serializable (serializable) once and for all Ah,

In fact, the isolation of high-quality is also a problem ah. This article does not explain, or another big paragraph, interested in the investigation.

About a few highlights

· Setting the isolation level does not necessarily guarantee that a corresponding error will not occur. and specific database-installed RDBMS has a large

Relationships, such as MySQL's repeatable Read's isolation level, also do not occur when Phantom reads.

· The so-called "guarantee does not occur", the processing method that is taken in order to make no mistakes is also different from the database RDBMS. When an exception occurs

Some databases report exceptions, and others wait for the transaction to end.

· The default isolation levels for database RDBMS vary, and the corresponding isolation level is different.

MySQL (InnoDB) Repeatable READ
PostgreSQL READ COMMITTED
Oracle READ COMMITTED
SQL Server READ COMMITTED

In this way, it takes time to write this, and then update the deadlock, lock escalation, and trace trace for SQL Server.

About the isolation level of the database

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.