Dirty read Phantom read non-repeatable read

1, Dirty Read

A transaction is read to another transaction, and a modification that has not yet been committed is a dirty read. Here are the so-called modifications, in addition to the update operation, do not forget that also includes
Insert and delete operations.

The consequences of dirty reads: If the latter transaction is rolled back, all of its modifications will be revoked. The data that the previous transaction read is garbage 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 for booking room 99th.

Transaction 2: Query, not yet booked room list, because room 99th, has been a transaction 1 booking. So it's not in the list.

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

Now, room 99th is available.
So, transaction 2 uses an invalid room list 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 very serious.

2, not repeatable read.

In the same transaction, when the data is read again "is your select operation", the data read, and the 1th reading of the data, is not the same. is not repeatable reading.

As an example:
Transaction 1: Query with double room. Room number 99th, with double bed.

Transaction 2: Change room 99th into a single bed room.

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

In the non-repeatable read, inside, you can see the changes made by other firms, resulting in 2 of times the query results are no longer the same.
The changes here have been submitted. It can also be uncommitted, and this situation is also dirty reading.

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

Note: If allowed, non-repeatable reading, your query results, will be indeterminate. An uncertain result, can you tolerate it?

3, Phantom Read

Transaction 1 reads some of the rows returned by the specified where clause. Transaction 2 Then inserts a new row that also satisfies the query used by transaction 1
The WHERE clause. Then transaction 1 reads the row again using the same query, but now it sees the row that the transaction 2 just inserted. This line is called an illusion,
Because of the business, the appearance of this line is inconceivable.

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

Note: Phantom read, for the insert operation. If transaction 2, the inserted record, is not committed. It is also dirty to read at the same time.

Dirty read Phantom read non-repeatable read