This article is for my notes management only.

The index module

Mainly record and index related notes

Why use indexes

Avoid full table scan, dictionary. Greatly improve query data. Advantages:

  • Quick query of data

What kind of information can be indexed

  • Primary keys, unique keys, and normal keys

The data structure of the index

Common data structures are:

  • Generate index, resume binary search tree binary search
  • Indexes are generated and a B-tree structure is established for searching
  • The index is generated and the B+ -tree structure is established for search
  • Generate indexes and create Hash structures for searching

Four features of database transactions

The four features of database transactions can be expressed simply with four letters: ACID.

  • A Atomic means that all operations contained in A transaction are either executed or rolled back on failure.

  • C Consistency Indicates that the database changes from one consistent state to another consistent state, meeting integrity constraints. Let’s say A and B together have $2,000. So no matter how much money A transfers to B or B transfers to A, they’re always going to have $2,000.

  • I Isolation When multiple transactions are executed concurrently, the execution of one transaction does not affect the execution of other transactions.

  • D Durability means that once a transaction is committed, its modifications are permanently saved in the database. The DBMS requires that a transaction, once committed, provide some redundancy. MySQL, for example, writes all operations to a table so that it can be recovered if an error occurs.

Transaction isolation levels and concurrent access issues at each level

Problems that can be caused by concurrent access

Update the lost

MySQL isolation levels can be avoided at the database level. Focus on the procedural side.

Dirty read

Dirty reads are when one transaction reads unupdated data from another. This problem can be avoided above the Read-commited transaction isolation level.

Unrepeatable read

Transaction A reads uniform data multiple times, but during the process of transaction A reads, transaction B modifies the data so that transaction A reads inconsistent data multiple times.

This can be avoided above REPEATABLE-READ transaction isolation level.

Phantom read

Transaction A reads uniform data for many times. However, during the reading process of transaction A, transaction B adds or deletes columns to the table, so that the data columns read by transaction A for many times are inconsistent.

SERIALIZABLE transaction isolation level can be avoided.

As shown in figure:

The higher the transaction isolation level, the higher the security, the more serialized execution, and the lower the concurrency. The default value of MySQL is repetable-read.

InnoDB can avoid phantom reads at the repeat read isolation level

The current reading

Add the lock of the add delete change check statement. For example: select.. lock in share mode,select … for update,update,delete,insert

Read the snapshot

Non-blocking reads without locks, such as SELECT. Snapshot reads are only available when the isolation level is lower than SERIALIZABLE.

Although it may be the current READ of InnoDB that prevents phantom READ, it is the next-key lock (row lock + gap lock) that actually prevents phantom READ under Repetable-read and SERIALIZABLE.

What is a next-key lock?

It consists of two parts: row lock and gap lock.

Row lock: Lock on row records. Gap lock: Gap is the space in the index to insert a new record. Gap Lock locks a range, excluding the record itself. Prevents two current reads of the same thing from being cached. Not in RR and lower levels. RR and SERIABLE support gap locks by default.

  • If all where hits, a gap lock is not used, only a record lock is added. All hits, all accurate queries.

  • If the WHERE condition is partially or completely hit, a Gap lock is added.

The Gap lock is used mainly in the current read with non-unique index or no index. But do not go index should try to avoid Gap lock, because it will affect the efficiency of the table. Gap lock range is left open and right closed.