Dirty read, Phantom Read and non-repeatable READ + Transaction ISOLATION level _ dirty Read

Lost update: Two transactions update a row of data at the same time, the last transaction update will overwrite the first transaction update, resulting in the first transaction update data loss, which is caused by no lock;

1. Dirty reads: Dirty reading means that when a transaction is accessing the data and the data has been modified, and the modification has not been submitted to the database, another transaction accesses the data and then uses the data.
e.g.
The original salary for 1.Mary was 1000, and the treasurer changed Mary's salary to 8000 (but did not commit the transaction).
2.Mary read their wages and found their wages turned to 8000, with rapturous joy.
3. While the financial discovery was wrong and the transaction was rolled back, Mary's wages became 1000.
Like this, Mary remembered that the salary of 8000 is a dirty data.

2. Non-repeatable reading: refers to the same data read multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read by the first transaction two times may be different. The data read two times within a transaction is not the same, so it is called a non repeatable read.
e.g.
1. In transaction 1, Mary read her own salary of 1000, and the operation was not completed
2. In transaction 2, the Treasurer modified Mary's salary at 2000 and submitted a transaction.
3. In transaction 1, when Mary read her salary again, her salary changed to 2000.

Workaround: This problem can be avoided if the data can be read only after the modification of the transaction is fully committed.

3. Phantom reading: a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table that involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations.
e.g.
At present, there are 10 employees with a salary of 1000.
1. Transaction 1, read all employees with a salary of 1000.
2. At this point, transaction 2 inserts an employee record into the employee table with a salary of 1000
3. Transaction 1 read again all employees with a salary of 1000 read 11 records,

Workaround: If no other transaction can add new data until the operation transaction completes data processing, you can avoid the problem

The key to non-repeatable reads is to modify:
The same condition, the data you read, read it again and find the value is different.
The focus of phantom reading is new or deleted
The same conditions, the 1th and 2nd readings of the number of records are not the same

Reference: http://cupoy.iteye.com/blog/251796

Http://blog.csdn.net/d8111/archive/2008/06/29/2595635.aspx

http://www.iteye.com/topic/332577

2. In a program, there will be three different situations depending on the isolation level of the transaction.
  
Dirty reads: A transaction reads data that has not yet been submitted by another transaction, so you will see some data that was eventually rolled back by another transaction.

Non-repeatable reads: One transaction reads a record and another transaction changes the record and submits it, which is changed when the first transaction reads the record again.

Phantom reads: A transaction uses a WHERE clause to retrieve data from one table, another transaction inserts a new record, and the Where condition is met, so that the first transaction retrieves data with the same where condition, and then one more record is added.

3. The database provides four transaction isolation levels, and different isolation levels are implemented with different lock classes.
Of the four isolation levels, the serializable is the highest level and the Read uncommited is the lowest.
The default isolation level for most databases is: Read commited, such as SQL Server, Oracle.
A few database default isolation levels are repeatable Read, such as the MySQL InnoDB storage engine

Mechanisms for SQL Server locks
All activities in SQL Server generate locks. The smaller the locked unit, the better the concurrent processing power, but the greater the overhead of managing the lock. The difficulty of SQL Server is to find the balance point so that concurrency and performance are acceptable.
SQL Server has the following kinds of locks:
All activities in SQL Server generate locks. The smaller the locked unit, the better the concurrent processing power, but the greater the overhead of managing the lock. The difficulty of SQL Server is to find the balance point so that concurrency and performance are acceptable.

SQL Server has the following kinds of locks:

1. Shared lock
For read-only operations (SELECT), to lock shared resources. Shared locks do not prevent other users from reading, but prevent other users from writing and modifying them.

2, update the lock
An update lock is an intent lock that occurs when a transaction has requested a shared lock and attempts to request an exclusive one. For example, a deadlock occurs when two transactions use shared locks on several rows of data and attempt to acquire an exclusive lock to perform an update operation: All are waiting for the other to release the shared lock and implement an exclusive lock. The purpose of the update lock is to allow only one transaction to get an update lock to prevent this from happening.

3. Exclusive lock
Only one exclusive lock can be used on one resource at a time, and all other locks may be blocked including shared indentation. Write is exclusive lock, can effectively prevent ' dirty reading '.

4. Intent to shrink
Use an intent lock before using a shared and exclusive lock. View the intent lock from the table level to determine whether a transaction can acquire shared and exclusive locks, improve system performance, and do not need to be checked from the page or line.

5. Plan the Lock
Sch-m,sch-s. When the database structure changes with SCH-M, the query is compiled with Sch-s. Both of these locks do not block any transaction locks, including exclusive locks.
Read is a shared lock, write an exclusive lock, read first after the update of the operation is to update the lock, update the lock succeeded and changed the data Update lock upgrade to exclusive lock

L default uses the isolation level set by the database (default), which determines the isolation level by the DBA default setting.
L read_uncommitted will appear dirty read, not repeatable read, Phantom read (lowest isolation level, high concurrency performance)
L read_committed There will be no repeat read, Phantom problem (lock the row being read)
L Repeatable_read Reads (locks all rows Read)
L SERIALIZABLE Ensure that all conditions do not occur (lock the table)

ReadCommitted:
Assuming that a transaction places a shared lock on the data being read, then data cannot be overwritten by other transactions, so when the B transaction reads data, the sum a read is consistent, so that dirty reads are avoided. Because the data can be overwritten before a is committed, then a value read by B may be changed by a after it is read, causing the value not to be repeated, or the result set of the previous data, which is the Phantom data, when B uses the same WHERE clause again.

ReadUncommitted:
Assuming that a transaction does not publish a shared lock and does not accept exclusive locks, concurrent B or other transactions can overwrite the data read by a transaction, and then the state of the data read by the concurrent C transaction may not be consistent with the data of a or B, then. Dirty reads, non-repeatable reads, and phantom data can all be present.

RepeatableRead:
(Note the first sentence in the MSDN text: Lock is placed on all data used in the query, so there is no dirty read).
Suppose a transaction places a lock on all data that is read, to prevent changes to data by other transactions, the state of the data is consistent with the data in a transaction before a is submitted and the data that is read by the new concurrent transaction, if it exists, avoids the need for repeatable reads. However, before a transaction is closed, b transactions can insert new records into the table where data resides, and other transactions, when queried again with the same WHERE clause, may have the last inconsistency of the resulting number, which is the Phantom data.

Serializable:
An exclusive lock is placed on the datasheet to prevent other users from updating rows or inserting rows into the dataset before the transaction completes, which is the strictest lock. It prevents dirty reads, non repeatable reads, and phantom data.

from:http://uule.iteye.com/blog/1109647

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch