Principle and usage analysis of transaction index and lock of MySql knowledge points

In this paper, the principles and usage of transaction, index and lock of MySql knowledge points are described with examples. Share it for your reference, as follows:

Affairs

Transaction concept

A transaction is a set of atomic SQL queries, or a separate unit of work. If the Database Engine executes 1 set of action statements, all actions will be executed for a long time, and if any 1 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 either succeed or fail.

Transaction feature ACID Atomicity (atomicity)

1 transaction is regarded as the smallest unit of work, which cannot be split. Some operations of the whole transaction are either committed successfully or rolled back without failure, and only some operations cannot be executed.

1-induced (consistency)

The database transitions from one 1-oriented state to another 1-oriented state. The state of a database that meets all integrity constraints in a certain state.

Isolation (isolation)

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

Persistence (durability)

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

Isolation level of transaction READ UNCOMMITTED (Uncommitted Read)

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

READ COMMITTED (Commit Read)

The default level for most databases is READ COMMITTED (MySQL default REPEATABLE READ), at which level transactions resolve dirty reads, but non-repeatable reads occur because the same query is executed twice and the query results are different.

REPEATABLE READ (Repeatable Read)

This level solves dirty reading and guarantees repeatable reading, but in theory, the repeatable reading isolation level still cannot solve illusion reading. The so-called illusion reading means that when something reads a record in a certain range, another transaction inserts a new record in that range. The InnoDB and XtraDB storage engines solve the problem of phantom reading through multi-version concurrency control MVVC.

SERIALIZABLE (Serializable)

Serializability is the highest level of isolation, which forces transactions to be executed sequentially and completely avoids phantom reading. Simply put, SERIALIZABLE will lock every line read, so it will lead to a large number of waiting timeouts and lock contention problems, which are rarely used in actual development.

Index

Index concept

Index is a kind of data structure for storage engine users to find records quickly, for example

SELECT userName FROM user WHERE userId = 1;

If you add an index to an userId column, MySQL uses the index to find the rows of userId, that is, MySQL looks up the index by value and returns all data rows that contain that value.

Indexing mode B-Tree Index

The data is stored using the B-Tree data structure, which is supported by most MySQL engines. The B-Tree index speeds up access to data because B-Tree organizes storage sequentially for index columns and makes range lookups fast.

hash Index

Hash index is a basic hash table implementation, and only queries that exactly match all columns of the index are valid. For every 1 row of data, the storage engine calculates 1 hash code for all index columns, and the hash code value is small. The hash index stores all the hash codes in the index and holds a pointer to each data row in the Haas table. Only the Memory engine in MySQL shows support for hash indexing.

Index type General index

The main task is to speed up access to data

Only 1 index

Ordinary indexes allow duplication of data. If it is determined that a column of data will not be duplicated, you can create a only 1 index. The only 1 index has two advantages, and the index is more effective: insert new data, and if duplicate, MySQL rejects insertion.

Primary key index

The primary key itself creates an index by default

Full-text indexing

An ordinary index on a text field can only speed up the retrieval of the string that appears at the front of the field. If the field contains a large section of text consisting of several or more words, the ordinary index will not work. In this case, a full-text index is more appropriate

Query Efficiency: Only 1 Index > Self-increasing primary key > Primary key

Insert: Primary key > Self-increasing primary key > Only 1 index

Lock

Here we mainly discuss downlink locking

Table level

Engine MyISAM can be understood as locking the whole table, which can be read at the same time, not written at the same time. During the lock, other processes cannot write to the table, and if it is a write lock, other processes are not allowed to read it.

Row level

Engine INNODB, a single line of records is locked, which can be read at the same time, but not written at the same time. Row-level locking has high overhead and slow locking; There will be deadlocks; The lock granularity is the smallest, the probability of lock collision is the lowest, and the concurrency is the highest.

InnoDB Lock Row

Since InnoDB is Row-Level Lock by default, MySQL will execute Row lock only if the [explicit] primary key is specified, otherwise MySQL will execute Table Lock

Example 1: (The primary key is explicitly specified and there is this record, Row Lock)

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

Example 2: (explicitly specify the primary key, if there is no such record, there is no lock)

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: (Primary key ambiguous, table lock)

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

Example 5: (Primary key ambiguous, table lock)

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

Note 1: FOR UPDATE only applies to InnoDB and must be in the trading block (BEGIN/COMMIT) to take effect.
Note 2: To test the locking condition, you can use Command Mode of MySQL to open two windows for testing.

More readers interested in MySQL can see the topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Encyclopedia of MySQL Log Operation Skills", "Encyclopedia of MySQL Transaction Operation Skills", "Encyclopedia of MySQL Stored Procedure Skills" and "Encyclopedia of MySQL Database Lock Related Skills"

I hope this article is helpful to everyone's MySQL database.