Basic knowledge of transaction isolation

Basic knowledge of transaction isolation

 

 

A few days ago, I had a lot of questions about the isolation of transactions in the project. I checked the relevant materials and made some summary. I wrote this article.

In the past, we learned in database principles that transactions have the acid feature. The "I" is isolation, that is, concurrent transactions do not affect each other, which is the same as serial execution.

But do the transactions in our DBMS have this feature? I will take SQL 2000 as an example below (Other DBMS should be similar, not carefully studied)

In SQL 2000, we focus on the transaction isolation level, which is divided into the following types: (compliant with the transaction isolation level defined in SQL 92)

[1] Read uncommitted

[2] Read committed

[3] Repeatable read

[4] serializable

Pay special attention to [4] and serializable. This is consistent with the "I" we learned in database principles.

What are the other isolation levels? Let's first understand the following concepts: Dirty reading, non-repeated reading, and phantom.

[1] dirty read:

Dirty reading refers to reading uncommitted data. For example, transaction B reads data that has not been committed by transaction A. Unfortunately, transaction a rollback is called dirty read.

[2] non-repeated read:

The so-called non-repeated read operation means that a transaction reads a row multiple times, but the values of this row in multiple reads are inconsistent (this transaction makes any modification to this row ). For example, if transaction a reads a row multiple times and the row is modified by transaction B during multiple read operations, the row cannot be read repeatedly.

[3] PHANTOM:

A phantom inserts a new row or deletes an existing row in the range of rows read by an uncommitted transaction. For example, transaction B inserts a new row or deletes the row within the row range read by transaction A, and transaction a also accesses these rows (that is, the operation will be affected by these rows ), in this way, the phantom occurs in transaction.

Now we can clearly define the isolation level [1] ~ [4] Meaning:

	Dirty read allowed?	Whether it can be read repeatedly	Whether a phantom exists
Read uncommitted	Y	Y	Y
Read committed	N	Y	Y
Repeatable read	N	N	Y
Serializable	N	N	N

This table indicates from [1] ~ [4] increasing isolation and decreasing concurrency are related to the actual situation. In my previous project, I thought the default value was serializable, but in SQL2000, the default value was read committed.

This is my understanding about isolation, especially about dirty reading, non-repeated reading, and phantom. If there are any improper or incorrect ones, you are welcome to correct them.

BTW: Merry Christmas,