4 isolation levels in the database

Excerpt: https://www.cnblogs.com/snsdzjlz320/p/5761387. HTML-------------------------------------------------------This article is more detailed, especially readcommitted and Repertableread. the difference between readcommitted and RepeatableRead, is that readcommitted can read his uncommitted and the person has submitted, Repearableread can only read their uncommitted with the transaction opened before it was submitted by the person, once the transaction is opened, the content that it commits is also unreadable, which makes it possible to support repeatable reads, which are the same data that is read multiple times in the same transaction, because it cannot be read by others, and can only be seen after it has been submitted. The-------------------------------------------------------SQL standard defines 4 isolation levels, including specific rules that 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. First, we use the test database, create a new TX table, and open two command windows to manipulate the same database: level 1th: READ UNCOMMITTED (reads uncommitted content)(1) All transactions can see the execution results of other uncommitted transactions (2) This isolation level is rarely used in real-world applications because it has no better performance than other levels (3) The problem raised by this level is-dirty read (Dirty Read): READ UNCOMMITTED data #首先, modify isolation Level

Set tx_isolation= ' read-uncommitted '; select @ @tx_isolation; +------------------+| @ @tx_isolation   |+------------------+| read-uncommitted |+------------------+

#事务A: Start a transaction

Start Transaction;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+

#事务B: A transaction is started (then two transactions intersect) and the UPDATE statement is executed in transaction B without committing

Start Transaction;update TX Set num=10 where Id=1;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+

#事务A: So does transaction a see this updated data at this time?

SELECT * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten |   ---> Can see! It means that we have read the data that transaction B has not yet submitted |    2 |    2 | |    3 |    3 |+------+------+

#事务B: Transaction B rolled back, still not committed

Rollback;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+

#事务A: What you see in transaction A is also the data that B did not commit

SELECT * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 |      ---> Dirty reads mean I am in this transaction (a), transaction B Although not committed, but it any one of the data changes, I can see!|    2 |    2 | |    3 |    

　Level 2nd: Read Committed (reading submissions)

(1) This is the default isolation level for most database systems (but not MySQL default) (2) It satisfies the simple definition of isolation: A transaction can only see changes that have been committed by the firm (3) The problem with this isolation level is--non-repeatable read (Nonrepeatable Read)   　　 : Non-repeatable reading means that we may see a different result when we execute the exact same SELECT statement in the same transaction. This can be caused by: (1) A cross transaction has a new commit, which results in a change in the data; (2) When a database is manipulated by multiple instances, other instances of the same transaction may have a new commit during the instance processing #首先修改隔离级别

Set tx_isolation= ' read-committed '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| read-committed |+----------------+

#事务A: Start a transaction

Start Transaction;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+

#事务B: A transaction is started (then two transactions intersect) and the data is updated in this transaction and is not committed

Start Transaction;update TX Set num=10 where Id=1;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+

#事务A: Can we see the data changes in transaction A at this time?

SELECT * FROM TX; --------------->+------+------+                | | ID   | num  |                | +------+------+                ||    1 |    1 |---> Can't see!  ||    2 |    2 | |                |    3 |    3 |                | +------+------+                |--> The same SELECT statement, but the result is different                               |

#事务B: What if transaction B was committed?

Commit  

#事务A:

SELECT * FROM TX; --------------->+------+------+| ID   | num  |+------+------+|    1 |   ---> Because transaction B has already been submitted, we see data changes in a |    2 |    2 | |    3 |    3 |+------+------+

　　Level 3rd: Repeatable Read (can be reread)

(1) This is the default transaction isolation level for MySQL (2) It ensures that multiple instances of the same transaction will see the same data row (3) when the data is concurrently read (Phantom Read): When a user reads a range of data rows, Another transaction inserts a new row within that range, and when the user reads the data row of that range again, there is a new Phantom row (4) InnoDB and Falcon storage engine with multiple version concurrency control (mvcc,multiversion Concurrency control) mechanism resolves the problem #首先, changing the isolation level

Set tx_isolation= ' Repeatable-read '; select @ @tx_isolation; +-----------------+| @ @tx_isolation  |+-----------------+| Repeatable-read |+-----------------+

#事务A: Start a transaction

Start Transaction;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | |    2 |    2 | |    3 |    3 |+------+------+

#事务B: Open a new transaction (then the two transactions intersect) update the data in transaction B and submit

Start Transaction;update TX Set num=10 where Id=1;select * from tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+commit;

#事务A: Even if transaction B has been submitted, can a see the data change?

SELECT * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |    1 | ---> Can not be seen! (This level 2 is different, also indicates that level 3 resolves non-repeatable read issues) |    2 |    2 | |    3 |    3 |+------+------+

#事务A: Only if transaction A is committed will it be able to see data changes

Commit;select * FROM tx;+------+------+| ID   | num  |+------+------+|    1 |   Ten | |    2 |    2 | |    3 |    3 |+------+------+

　　Level 4th: Serializable (Serializable)

(1) This is the highest isolation level (2) It solves the Phantom reading problem by forcing the transaction to sort, making it impossible to conflict with each other.　　In short, it is a shared lock on every data row read. (3) At this level, a large number of timeouts and lock competitions can result #首先修改隔离界别

Set tx_isolation= ' serializable '; select @ @tx_isolation; +----------------+| @ @tx_isolation |+----------------+| SERIALIZABLE   |+----------------+

#事务A: Open a new transaction

Start transaction;

#事务B: This cross-transaction cannot change data until a commit is made to a

Start Transaction;insert TX VALUES (' 4 ', ' 4 '); ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting Transactionupdate TX set num=10 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

　　

4 isolation levels in the database