MySql knowledge points: transaction, index, lock principle and usage analysis

This article uses examples to explain the principles and usage of MySQL knowledge points such as transactions, indexes, and locks. Share with you for your reference, the details are as follows:

Transactions

• Transaction Concept

A transaction is a set of atomic SQL queries, or an independent unit of work. If the database engine executes a set of operation statements, then all operations are executed. If any of them crashes or cannot be executed for other reasons, all statements will not be executed. That is to say, all statements in a transaction are either executed successfully or all failed.

• ACID transaction characteristics
  • Atomicity

  A transaction is regarded as the smallest unit of work and cannot be split. All operations in the entire transaction must either be committed successfully or all fail and roll back. Only part of the operations cannot be executed.

  • Consistency

  The database is transformed from one consistent state to another consistent state. A state in which a database complies with all integrity constraints.

  • Isolation

  Generally speaking, changes made by one transaction are not visible to other transactions until they are finally committed. At this time, it should be ensured that each transaction is isolated and cannot interfere with each other.

  • Durability

  Once a transaction is committed, all modifications are permanently saved to the database. At this time, even if the system crashes, the modified data will not be lost.

• Transaction isolation level
  • READ UNCOMMITTED

  Modifications in a transaction are visible to other transactions even if they are not committed. Transactions can read uncommitted data, causing dirty reads and non-repeatability.

  • READ COMMITTED

  The default level of most databases is READ COMMITTED (MySQL defaults to REPEATABLE READ). Transactions at this level solve dirty reads, but non-repeatable reads may occur because the query results are different when the same query is executed twice.

  • REPEATABLE READ

  This level solves dirty reads and ensures repeatable reads. However, in theory, the repeatable read isolation level still cannot solve phantom reads. The so-called phantom read refers to the situation where when a certain transaction is reading records within a certain range, another transaction inserts new records within the same range. InnoDB and XtraDB storage engines solve the problem of phantom reads through multi-version concurrency control (MVVC).

  • SERIALIZABLE

  Serializability is the highest level of isolation. It forces transactions to be executed serially and completely avoids phantom reads. In short, SERIALIZABLE will lock each row read, which will cause a large number of wait timeouts and lock contention problems. It is rarely used in actual development.

index

• Index Concept

An index is a data structure that allows storage engine users to quickly find records. For example

SELECT userName FROM user WHERE userId = 1;

If you add an index to the userId column, MySQL will use the index to find the row for userId. That is, MySQL first searches the index by value and then returns all data rows that contain the value.

• Indexing
  • B-Tree Index

  Use B-Tree data structure to store data. Most MySQL engines support this index. B-Tree indexes can speed up data access because B-Tree index columns are stored sequentially, allowing for fast range searches.

  • Hash index

  Hash index is implemented as a basic hash table. Only queries that exactly match all columns of the index are valid. For each row of data, the storage engine calculates a hash code for all index columns, and the hash code value is smaller. A hash index stores all hash codes in the index and saves a pointer to each data row in the hash table. In MySQL, only the Memory engine explicitly supports hash indexes.

• Index Type
  • Normal index

  Main Tasks Speed ​​up access to data

  • Unique Index

  Ordinary indexes allow data to be repeated. If you are sure that the data in a column will not be repeated, you can create a unique index. A unique index has two advantages: the index is more efficient: when inserting new data, if it is repeated, MySQL refuses to insert it.

  • Primary key index

  The primary key itself is indexed by default.

  • Full-text index

  A normal index on a text field can only speed up the search for the first string in the field. If the field contains a large text consisting of several or more words, a normal index will not work. In this case, a full-text index is more appropriate.

  Query efficiency: unique index > auto-increment primary key > primary key

  Insert: primary key > auto-increment primary key > unique index

Lock

Here we mainly discuss downlink level locks

• Table Level

The engine MyISAM can be understood as locking the entire table. It can be read at the same time but not written at the same time. During the locking period, other processes cannot write to the table. If it is a write lock, other processes are not allowed to read.

• Row level

Engine INNODB, a single row of records is locked, and can be read at the same time but not written at the same time. Row-level locks have high overhead and slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

• InnoDB Lock Rows

Since InnoDB defaults to Row-Level Lock, MySQL will only perform Row Lock if the primary key is explicitly specified. Otherwise, MySQL will perform Table Lock.

Example 1: (Specify the primary key explicitly, and there is such a record, Row Lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

Example 2: (Specify the primary key explicitly. If no such record is found, no lock is performed)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

Example 3: (no primary key, table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

Example 4: (Unclear primary key, table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

Example 5: (Primary key is unclear, table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

Note 1: FOR UPDATE is only applicable to InnoDB and must be executed in a transaction block (BEGIN/COMMIT) to take effect.
Note 2: To test the locking status, you can use MySQL's Command Mode and open two windows to do the test.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.