Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the transaction, index, lock principle and usage of MySql knowledge points. Share with you for your reference, the details are as follows:

Transaction concept

A transaction is a set of atomic SQL queries, or a separate unit of work. If the database engine executes a set of operation statements, all operations are executed for a long time, and if any of them crashes or cannot be executed for other reasons, all statements will not be executed. In other words, either all statements within the transaction are executed successfully or all failed.

Transaction characteristics ACID atomicity (atomicity)

A transaction is regarded as the smallest unit of work and cannot be splitted. all operations of the whole transaction are either committed successfully or all failed to roll back, not only part of the execution.

Consistency (consistency)

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

Isolation (isolation)

In general, changes made by a firm are not visible to other transactions until they are finally committed. At this point, you should ensure that transactions are isolated and that transactions do not interfere with each other.

Persistence (durability)

Once the transaction commits, all changes are permanently saved to the database. Even if the system crashes, the modified data will not be lost.

Isolation level READ UNCOMMITTED of the transaction (read not committed)

Changes in transactions, even if they are not committed, are visible to other transactions, and transactions can read uncommitted data, resulting in dirty reads and non-repeatability.

READ COMMITTED (submit for read)

The default level for most databases is READ COMMITTED (MySQL default REPEATABLE READ), which deals with dirty reads, but can't be repeated because the query results are different when the same query is executed twice.

REPEATABLE READ (repeatable)

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

SERIALIZABLE (serializable)

Serializability is the highest level of isolation, which forces the serial execution of transactions and completely avoids illusory readings. To put it simply, SERIALIZABLE will lock every row read, so it will lead to a large number of wait timeouts and lock contention problems, which are rarely used in actual development.

Index concept

Index is a data structure in which storage engine users find records quickly, for example

SELECT userName FROM user WHERE userId = 1

If you add an index on a userId column, MySQL will use that index to find the row of userId, that is, MySQL looks up by value on the index and then returns all rows of data that contain that value.

Index mode B-Tree index

Use B-Tree data structures to store data, which is supported by most MySQL engines. B-Tree indexes can speed up the access to data, because B-Tree organizes and stores index columns sequentially, and range lookups are fast.

Hash index

The hash index refers to the basic hash table implementation, and 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, with a small hash code value. The hash index stores all hash codes in the index, while keeping pointers to each row of data in the hash table. Only the Memory engine in MySQL shows support for hash indexing.

Index type general index

The main task is to accelerate access to data

Unique index

An ordinary index allows data to be duplicated, and if it is determined that a column of data will not be duplicated, a unique index can be created. The unique index has two advantages, and the index is more efficient: insert new data, and if repeated, MySQL refuses to insert.

Primary key index

The primary key itself creates an index by default

Full-text index

The ordinary index on the text field can only speed up the retrieval operation of the string that appears first in the field. If the field contains a large piece of text composed of several or more words, the ordinary index is not good. It is more appropriate to use full-text index in this situation.

Query efficiency: unique index > self-increasing primary key > primary key

Insert: primary key > self-increasing primary key > unique index

Lock

Here we mainly discuss downlink locks.

Table level

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

Row level

Engine INNODB, a single line of records are locked and can be read at the same time, not written at the same time. Row-level locks are expensive and slow to add locks; deadlocks occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

InnoDB lock line

Because the InnoDB preset is Row-Level Lock, MySQL will execute Row lock only if the primary key is specified explicitly, otherwise MySQL will execute Table Lock

Example 1: (specify the primary key clearly and have 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: (specify the primary key clearly, if there is no such record, 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 is not clear, table lock)

SELECT * FROM products WHERE id'3' FOR UPDATE

Example 5: (primary key is not clear, table lock)

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

Note 1: FOR UPDATE applies only to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect.

Note 2: to test the lock condition, you can use MySQL's Command Mode to open two windows to do the test.

Readers who are interested in MySQL-related content can check out this site's special topics: "Summary of MySQL Index Operation skills", "Summary of MySQL Common functions", "Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.