The corresponding principles of my 4 isolation levels are summarized as follows:
The principle of read_uncommited:
- The transaction does not lock the data currently being read;
- When a transaction updates the moment of a data (that is, the moment the update occurs), it must be added to the row-level shared lock until the end of the transaction is released.
Performance:
- Transaction 1 reads a row record, transaction 2 can also read, update, and when transaction 2 updates the record, transaction 1 reads the record again, and can read the modified version of transaction 2 to the record, even if the modification has not yet been committed.
- When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1.
The principle of read_commited:
- The transaction adds a row-level shared lock to the data currently being read (locking when read), and once the line is read, the row-level shared lock is released immediately;
- When a transaction updates the moment of a data (that is, the moment the update occurs), it must be added to the exclusive lock on the row until the end of the transaction is released.
Performance:
- Transaction 1 reads a row of records, transaction 2 can also read, update, and when transaction 2 updates the record, transaction 1 reads the record again, read only the version before the transaction 2 is updated, or the version after transaction 2 commits.
- When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1.
The repeatable READ principle:
- When a transaction reads a data in an instant (that is, the moment it begins to read), it must be shared with a row level lock until the end of the transaction is released;
- When a transaction updates the moment of a data (that is, the moment the update occurs), it must be added to the exclusive lock on the row until the end of the transaction is released.
Performance:
- Transaction 1 reads a row of records, transaction 2 can also read, update, and when transaction 2 updates the record, transaction 1 reads the record again, and the read is still the version that was read for the first time.
- When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1.
The principle of SERIALIZABLE:
- When a transaction reads data, it must first be shared with a table-level lock until the end of the transaction is released;
- When a transaction updates data, it must first add a table-level exclusive lock until the end of the transaction is released.
Performance:
- When transaction 1 is reading a record in Table A, transaction 2 can also read the a table, but cannot update, add, delete, and end the a table until transaction 1.
- When transaction 1 is updating the records in table A, transaction 2 cannot read any records of table A, and it is more unlikely that the a table will be updated, added, deleted until the end of transaction 1.
Database isolation level and its implementation principle
