Dirty read Phantom read not repeatable read

Ext: http://blog.csdn.net/fatherican/article/details/44966921

1, Dirty Read

One transaction reads another transaction, and the pending modification is dirty read. Here's the so-called modification, in addition to the update operation, do not forget, but also include
Insert and delete operations.

Consequences of dirty reading: If the latter transaction is rolled back, all changes made to it will be revoked. The data that the previous transaction reads is junk data.

For example: Book a room.
There is a reservation table that inserts a record into the table to order a room.

Transaction 1: Insert a record in the reservation table to book room 99th.

Transaction 2: Enquiries, the list of rooms not yet booked, because room 99th, has been booked by transaction 1. So it's not in the list.

Transaction 1: Credit card payment. The entire transaction was rolled back because the payment failed.
Therefore, the record inserted into the reservation table is not persisted (that is, it will be deleted).

Now, room 99th is available.
Therefore, transaction 2 uses a list of invalid rooms because room 99th is already available. If it is the last room that has not been booked, then this will be a serious mistake.

Note: The consequences of dirty reading are serious.

2, not repeatable read.

In the same transaction, reading the data again is "your select operation", and the data read is not the same as the data read for the 1th time. is not to repeat the reading.

As an example:
Business 1: Enquiries have double rooms. Room 99th, with double beds.

Business 2: Convert room 99th into a single bed room.

Transaction 1: Execute the query again, request a list of all double beds, room 99th is no longer in the list. Other words
Transaction 1, you can see changes made by other firms.

In non-repeatable reading, you can see changes made by other firms, which result in 2 of queries that are no longer the same.
The changes here are submitted. can also be uncommitted, which is also dirty read.

If, the isolation level of the database system. Allow, do not read repeatedly. Then you start a transaction and do a select query operation.
Query to the data, it is possible, and your 2nd time, 3 times ... n times, the query to the data is different. In general, you will only do it once, select
Query, and use this time query data as the basis for subsequent computations. Because it is allowed to appear, it cannot be read repeatedly. Then any
, the queried data may be updated by other transactions, and the results of the query will be indeterminate.

Note: If allowed, do not read repeatedly, your query results will be indeterminate. An uncertain outcome, can you tolerate it.

3, Phantom Reading

Transaction 1 reads some of the rows returned by the specified where clause. Then, transaction 2 inserts a new row that satisfies the query used by transaction 1
The WHERE clause. Transaction 1 then uses the same query to read the row again, but now it sees the row that the transaction 2 just inserted. This line is called an illusion,
It is inconceivable that this line of business comes into being.

As an example:
Transaction 1: Request no scheduled, double room list.
Transaction 2: Insert a new record into the reservation table to book room 99th and submit.

Transaction 1: Once again, request a list of rooms with double beds, room 99th, no longer in the list.

So what's the difference between Phantom reading and non repeatable reading?

Do not read repeatedly

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.

Example:

In transaction 1, Mary read her own salary of 1000, and the operation did not complete
Java Code con1 = getconnection ();   Select Salary from Employee EmpId = "Mary";

In transaction 2, the treasurer changed Mary's salary by 2000 and committed the transaction.
Java Code con2 = getconnection ();   Update Employee Set salary = 2000;   Con2.commit ();

In transaction 1, when Mary reads her salary again, her salary becomes 2000.
Java code//con1 Select salary from Employee EmpId = "Mary";

The result of a two read before and after a transaction results in a non repeatable read.

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

Example:

At present, there are 10 employees with a salary of 1000.
Transaction 1, read all employees with a salary of 1000.
Java Code con1 = getconnection ();   Select * FROM Employee where salary = 1000; Total 10 Records read

Then another transaction inserts an employee record into the employee table with a salary of 1000
Java Code con2 = getconnection ();   Insert into employee (empid,salary) VALUES ("Lili", 1000);   Con2.commit ();

Transaction 1 read again all employees with a payroll of 1000
Java code//con1 SELECT * FROM employee where salary = 1000;

A total of 11 records were read, which resulted in Phantom reads.

What does spring's transaction have to do with the database's transactions ...

Spring declarative transactions based on meta data:

The Isolation property supports a total of five transaction settings, as described below:

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)