Transaction and Isolation Levels

Dirty Read dirty reads: This event occurs when a transaction reads data that has not yet been committed. For example: Transaction 1 modifies a row of data, and Transaction 2 reads the modified row before Transaction 1 has committed the modification operation. If transaction 1 rolls back the modification operation, the data read by transaction 2 can be seen as never existed.
Non-repeatable Read non-repeatable reads: This event occurs when a transaction reads the same row of data two times, but each time the data gets different. For example: Transaction 1 reads a row of data, and then Transaction 2 modifies or deletes the row and commits the modify operation. When Transaction 1 attempts to reread the row, it gets a different data value (if the row is updated) or discovers that the row no longer exists (if the row is deleted).
Virtual Read (Phantom Read) Phantom read: When a transaction reads a range of records, another transaction inserts a new record within that range, and a magic line is generated when the transaction reads the range record again.
The transaction scenario is this:
For the same bank account A has 200 yuan, a withdrawal operation of 100 yuan, b for transfer operation 100 yuan to B account. If the transaction is not quarantined, the following issues may be concurrent:

2. First category lost updates: First a withdrawal account has 200 yuan, and b transfer is also 200 yuan, and then a second operation, a operation successfully took 100 yuan, b operation failed rollback, the final account of 200 yuan, so a operation is covered out, the bank lost 100 yuan.
4. Dirty read: A withdrawal of 100 yuan did not submit, B to transfer to find the account has left 100 yuan, which is a abort operation rollback, b normal operation submitted, the final account is 0 yuan, b read a dirty data, customer loss of 100 yuan.
6. virtual read: And dirty read similar, is for the insertion operation process of reading problems, such as the deposit of 100 yuan is not submitted, then the bank to do a statistical query account for 200 yuan, and then C submitted, then the bank again statistics found that the account is 300 yuan, can not determine exactly which to prevail? Everyone seems to think that the statistics of this thing is always updated, this is normal, but if the statistics are in a transaction is not normal, such as one of our statistical application needs to output the statistical results to the computer screen and a remote network of a computer's disk files, in order to Improve performance and user response we are divided into 2 threads, when the first completed and completed statistics may be inconsistent, we do not know which to prevail.
8. non-repeatable read: A b at the same time began to check the account for 200 yuan, a first start to withdraw 100 yuan to submit, then B in preparation for the last update when another query, found that the result is 100 yuan, then B will be very confused, do not know whether to change the account to 100 or 0. The difference between dirty reads is that dirty reads are dirty data that was not committed by the previous transaction, and non-repeatable reads are re-reading the data that was submitted by the previous transaction.
10. The second kind of missing update: is not repeatable read a special case, such as, b do not do a second query but direct operation completed, the final account of 100 yuan, a operation is covered out, the bank lost 100 yuan. The feeling is similar to the first category of missing updates.

In the case of multiple transactions concurrently doing database operations, if there is no effective mechanism to avoid, there will be a variety of problems. There are generally three types of problems, which are summed up as follows:

1. Missing updates. if two transactions are to be updated a field of the database x,x=100

Transaction a             transaction B reads x=100                       reads X=100 writes x=x+100                  writes x=x+200 transaction ends x=200                  transaction ends x=300 last x==300

　　in this case, the update of transaction A is overwritten and lost. A missing update describes a problem that may occur when a transaction is in a database write operation.
2. Non-repeatable reading . When a transaction does not update the database data itself, the result of executing two or more times with the same query operation should be the same, and if it is inconsistent, it is not repeatable. or use the example above

Transaction a             transaction B read x=100 read                       x=100 read x=100                       write x=x+100 read x=200     transaction end x=200                  transaction End x=200

in this case, the result of transaction a multiple read x is inconsistent, which is non-repeatable reading. in another case, the Phantom Transaction A reads 15 records, transaction B deletes (increases) 1 in the process of transaction A, and when transaction A is read again it becomes 14 (16), which is called Phantom Reading. It is not possible to repeat the reading to explain the problems that may arise when doing a database read operation.
3. Dirty read (unread) prevents one transaction from being read to a record that another transaction has not committed yet. such as:

Transaction a             transaction B                  reads x=100                  write x=x+100 read x=200                       transaction rollback X=100 read x=100     transaction End x=100

x Lock (Exclusive lock): The locked object can only be read and modified by the transaction holding the lock, other transactions cannot add another lock on the object, and cannot read and modify the object . s Lock (Shared lock): The locked object can be read by the lock transaction, but cannot be modified, and other transactions can be added with S lock.
Blockade protocol

Level Three lockdown protocol: when using X-locks and S-Locks to lock data objects, there are rules that need to be agreed, such as when to apply for X-lock or S-lock, lock-in time, when to release, and so on. These rules are called the blockade Agreement (Locking Protocol). The different rules governing the way in which closures are imposed have created different kinds of blockade agreements.

1. First- level blockade Agreement : Transaction T must be X-locked before modifying the data R until the end of the transaction is released. The end of the transaction consists of a normal end (COMMIT) and an abnormal end (ROLLBACK). A first-level blocking protocol prevents loss of modification and guarantees that transaction T is recoverable. You can use the first-level blocking protocol to resolve lost modification issues. in the first-level blocking protocol, if only the read data does not modify it, it does not need to be locked, it does not guarantee repeatable read and do not read "dirty" data.
2. Level two Lockdown protocol : The first level of the blocking protocol plus the transaction T before reading the data R must first add s lock, read the rear can release S lock. the level Two blocking protocol prevents the loss of modifications and further prevents the reading of "dirty" data. However, in the level two blocking protocol, the S lock can be released after reading the data, so it cannot guarantee repeatable reads.
3. level Three blockade : first -level blocking protocol plus transaction T before reading data r, it must be locked before it is released until the end of the transaction. the Level Three lockdown protocol prevents non-repeatable reads, in addition to preventing loss of modification and non-reading of "dirty" data.

The main difference between a level three protocol is what operations need to apply for blocking and when to release.

Granularity of database Locks

the so-called granularity, that is, the degree of refinement. The greater the granularity of the lock, the lower the concurrency and the overhead; the smaller the lock granularity, the higher the concurrency and the less overhead. the size of the lock is mainly in the following categories:

1. Row locks, which are the smallest resources in granularity. A row lock refers to a transaction that locks one or more rows of data while it is operating, and that other transactions cannot process data for those rows at the same time. Row-level locks consume the least amount of data resources, allowing other transactions to manipulate other data in the same table during transaction processing.
2. Page lock, one page at a time. 25 row locks can be upgraded to a single page lock.
3. Table lock to lock the entire table. When the entire data table is locked, other transactions are not able to use the other data in this table. Using table locks can make the amount of data processed by the transaction large and use less system resources. However, when table locks are used, the wait time for other transactions is delayed and system concurrency is reduced.
4. A database lock that prevents any transactions and users from accessing this database. You can control the operation of the entire database. Lock efficiency can be reduced by using table locks to reduce the use of locks to ensure efficiency.

Pessimistic lock and optimistic lock
Pessimistic lock (pessimistic lock), as the name implies, is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that others want to take this data will block until it gets the lock. Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.
Optimistic lock (optimistic lock), as the name implies, is very optimistic, every time to take the data when they think others will not be modified, so will not be locked, but in the update will be judged in the period when others have to update this data, you can use the version number and other mechanisms. Optimistic locking is useful for multi-read application types, which can improve throughput, such as the fact that a database provides an optimistic lock similar to the write_condition mechanism.
The two kinds of locks have advantages and disadvantages, not to think of one better than the other, like the optimistic lock for less write, that is, the conflict really rarely occurs, this can save the lock overhead, increase the overall system throughput. However, if there is frequent conflict, the upper application will continue to retry, which is to reduce the performance, so in this case, pessimistic locking is more appropriate.

isolation level of a transaction

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

• read uncommitted (Read UNCOMMITTED content).
• read Committed (read submissions).
• repeatable Read (can be reread).
• serializable (Serializable).

These four isolation levels are implemented with different lock types, which can be problematic if the same data is being read. For example:Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct. non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process. Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.
in MySQL, these four isolation levels are implemented, each of which can cause problems as follows:
　　 below, you will use MySQL's client program to test several isolation levels, respectively. The test database is testing, the table is TX, and the table structure:ID  int;NumInt.The two command-line clients are a-B, changing the isolation level of a, and modifying the data on the B-side.
1. Set the isolation level of a to read UNCOMMITTED (READ UNCOMMITTED)
before data is updated in B:Client A:
b Update DataClient B:
　　 Client A:
The above experiment concludes that transaction B updates a record, but does not commit, at which point a can query for uncommitted records. Cause dirty reading phenomenon. Non-committed reads are the lowest isolation level.
2. Set the transaction isolation level of client A to read committed (Read Committed)
before data is updated in B. Client A:
　　 b Update the data. Client B:
Client A:
The above experiment can conclude that the read-committed isolation level solves the problem of dirty reads, but there is a non-repeatable read problem where transaction A is inconsistent with data in two queries because transaction b updates a data between two queries. Read Committed only allows read-committed records, but does not require repeatable reads.
3. Set the isolation level of a to repeatable read (repeatable Read)
before data is updated in B.Client A:

b Update the data. Client B:
Client A:
b Insert Data. Client B:
Client A:

From the above experiment, it can be concluded that the REPEATABLE read isolation level allows only read committed records, and the other transactions update the record during a transaction that reads one record two times. However, the transaction does not require serialization with other transactions. For example, when a transaction can find a record that is updated by a committed transaction, it may produce a phantom read problem (note that it is possible because the database differs from the implementation of the isolation level). Like the above experiment, there is no problem of data phantom reading.
4. Set the isolation level of a to serializable (Serializable)
a end opens a transaction, and the B end inserts a record. transaction A-side:

Transaction B-side:
Because transaction A's isolation level is set to serializable at this point, transaction B waits only after the transaction has started and has not been committed.
Transaction a commits the transaction. transaction A-side :

Transaction B-Side

Serializable completely locks the field, and if a transaction queries the same data, it must wait until the previous transaction is complete and unlocked. Is the complete isolation level, which locks the corresponding data tables, thus making the problem more efficient.

Transaction and Isolation Levels