In an online environment, there are occasional “fuck you” issues, but some of them are database based. This is also a way to address problems that do not occur in the development or test environment but do occur in the online environment.

Dirty read

Dirty reads may occur when the database isolation level is read uncommitted. Read uncommitted indicates that session B can read the uncommitted data before the database operation of session A is committed. If session A rolls back for some reason, the data read by session B is incorrect, that is, dirty read. Dirty reads can be avoided when the isolation level is raised to read committed.

Unrepeatable read

A simple understanding is that the data read results are inconsistent for multiple times. In session A, the data read from the same operation for multiple times is inconsistent. For example, if session A performs two read operations and Session B commits the data, session A reads the previous data for the first time and later data for the second time. The isolation level is repeatable to solve this problem.

Phantom read

The special scenario of non-repeatable reads, in which non-repeatable reads occur when a record is read, and phantom reads refer to ranges. For example, session A first queries A person older than 18 and finds no data, but when it queries A second time, it finds data. At the isolation level of serialization, phantom reads do not occur.

The database lock

Exclusive lock

Also known as X lock, write lock. Data object O can be read and updated by a transaction with an exclusive lock on it. O cannot be locked by other transactions while the lock is being held.

A Shared lock

Also known as S lock, read lock. A transaction holds a shared lock on data object O, which can be read but cannot be updated. Other transactions can hold a shared lock on O, but cannot hold an exclusive lock.

Isolation level

Read uncommitted

If one transaction has already started a write, other transactions are not allowed to write at the same time, but other transactions are allowed to read this row.

  • Write operations are unlocked;
  • Read operations are unlocked;

Reading has been submitted

A transaction reading data allows other transactions to continue to access the row, but an uncommitted write transaction prevents other transactions from accessing the row.

  • Write operations to addExclusive lock, to the end of the transaction;
  • Lock read operationsA Shared lock, will be released immediately after the queryA Shared lockTo ensure that the data read is submitted data.

Repeatable degrees

A transaction that reads data will prohibit a write transaction (but allow a read transaction), and a write transaction will prohibit any other transaction. Ensures that multiple reads of the same data in the same transaction will result in the same result.

  • Write operations to addExclusive lock;
  • A read operation to addA Shared lockTo the end of the transaction. It does not prevent other transactions from reading, but other transactions cannot modify the data and cannot lock itinsertCaused byPhantom read);
  • InnoDB,SELECT,UPDATE,DELETEOperation of theUnrepeatable readQuestions can be sent throughMVCC(Multi-version Concurrency Control)To solve it, butINSERTOperation of thePhantom readQuestions need to be passedMVCC + Next-Key LocksTo solve it.

serialization

Greatly reduces the concurrency of the database. Read uses read locks, write uses write locks, read locks and write locks are mutually exclusive

  • Write operations to addExclusive lock;
  • A read operation to addExclusive lock;

conclusion

  1. inInnoDBIn the engine, for index scans, not only locks the scanned index, but also locks the range covered by those indexes, so the range is not allowed to insert data.
  2. useselect @@tx_isolation;Query the isolation level of the database.
  3. The higher the isolation level (Read uncommitted->Reading has been submitted->Repeatable read->serialization), the more data integrity and consistency can be guaranteed, the greater the impact on concurrency performance.
  4. MysqlThe default level isRepeatable read, the isolation level of the database system is set toReading has been submitted.

This article is formatted using MDNICE