Dirty read, Phantom Read, and non-repeatable READ + transaction ISOLATION LEVEL

Dirty Read, Phantom Read, and non-repeatable READ + transaction ISOLATION level

1. Dirty read: Dirty read refers to when a transaction is accessing the data, and the data has been modified, and this modification has not been committed to the database, then another transaction also accesses the data, and then used this data.
e.g.
The original salary of 1.Mary was 1000, and the finance staff changed Mary's salary to 8000 (but not the transaction)
2.Mary read his own wages and found that his wages had changed to 8000, with a rapturous.
3. While the financial discovery was wrong, and the transaction was rolled back, Mary's salary changed to 1000.
Like this, Mary's 8000 salary is a dirty data.

2. Non-repeatable reads: Refers to the same data that is 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, the data that the first transaction two reads may be different because of the modification of the second transaction. This way, the data that is read two times within a transaction is not the same, so it is called non-repeatable read.
e.g.
1. In transaction 1, Mary reads her own salary of 1000, and the operation is not completed
2. In transaction 2, the Financial officer modified Mary's salary to 2000 and submitted the transaction.
3. In transaction 1, when Mary reads her salary again, the salary becomes 2000

WORKAROUND: You can avoid this problem if you can only read the data after the transaction has been fully committed.

3. Phantom reads: A phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.
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 reads all employees with a salary of 1000 to read 11 records again.

Workaround: If no other transaction can add new data before the operation transaction finishes processing, the problem can be avoided

the key to non-repeatable reading is to modify :
The same condition that you read the data, read it again and find that the value is different
The focus of Phantom reading is to add or delete
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, according to the isolation level of the transaction will be three kinds of situation occurs.
　　
Dirty reads: a transaction is read into data that has not yet been committed by another transaction, so you will see some data that was finally rolled back by another transaction.

Non-repeatable READ: one transaction reads into a record, another transaction changes the record and commits, and when the first transaction reads the record again, it has changed .

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 uses the same where condition to retrieve the data, and a record is added .

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

mechanism of SQL Server lock
Locks are generated for all SQL Server activities. The smaller the locked unit, the more capable of increasing concurrency, but the greater the overhead of managing locks. How to find the balance point, so that concurrency and performance are acceptable is the difficulty of SQL Server.
There are several types of SQL Server:
Locks are generated for all SQL Server activities. The smaller the locked unit, the more capable of increasing concurrency, but the greater the overhead of managing locks. How to find the balance point, so that concurrency and performance are acceptable is the difficulty of SQL Server.
There are several types of SQL Server:

1. Shared lock
Used for read-only operations (SELECT) to lock a shared resource. Shared locks do not prevent other users from reading, but they 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, when two transactions use a shared lock on a few rows of data, and while attempting to acquire an exclusive lock to perform an update operation, a deadlock occurs: both wait for the other party to release the shared lock and implement an exclusive lock. The purpose of the update lock is to allow only one transaction to obtain 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 are blocked including shared indents. Write is exclusive lock, can effectively prevent ' dirty read '.

4. Intention to shrink
Use intent locks before using shared and exclusive locks. Viewing intent locks from a table's hierarchy to determine whether a transaction can acquire shared and exclusive locks improves the performance of the system and does not need to be checked from the page or line.

5. Program Lock
Sch-m,sch-s. When the database structure changes with SCH-M, the query is compiled with Sch-s. These two locks do not block any transaction locks, including exclusive locks.
Read is a shared lock, write is an exclusive lock, read first after the updated operation is update lock, update lock succeeds and change the data when update lock upgrade to exclusive lock

L default uses the isolation level set by the database (default), which is determined by the DBA's default setting to determine the isolation level.
L read_uncommitted Dirty Read, non-repeatable read, Phantom Read ( lowest isolation level, high concurrency )
L read_committed will appear non-repeatable read, Phantom read problem ( lock the row being read )
L Repeatable_read will read ( lock all rows read )
L SERIALIZABLE Ensure that all situations do not occur ( lock table )

ReadCommitted:
Suppose that a transaction places a shared lock on the data being read, then data cannot be overwritten by other transactions, so it avoids dirty reads when the B transaction reads the data from the sum a is consistent. Since data can be overwritten before a is committed, a value read by B may be changed by a after it has been read, resulting in a value that cannot be repeated, or when B uses the same WHERE clause again to get the result set of the data that was not the same as the previous one, which is the Phantom data.

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

RepeatableRead:
(Note the first sentence in the original MSDN text: Place the lock 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 other transactions from making changes to data, and before a does commit, the data that is read by the new concurrent transaction is consistent with the data in the a transaction, thus avoiding non-repeatable reads. But before a transaction is finished, a B transaction can insert a new record into the table where data is located, and when another transaction is queried again with the same WHERE clause, the resulting number may be the last inconsistency, that is, the Phantom data.

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

Source: http://www.cnblogs.com/kinghuhua/archive/2011/08/17/2142902.html (focus on the original article)