MySQL Development advanced Article series 6 lock problem

I. Overview

In the database, the data belongs to the shared resources, in order to guarantee the consistency of concurrent access, the validity of the lock is generated. Next, we focus on the features of the MySQL lock mechanism, common locking problems, and some methods or suggestions for solving the MySQL lock problem. Compared to other databases, the MySQL lock mechanism is relatively simple, and the notable feature is that different storage engines support different locking mechanisms. Row and table locks are supported in InnoDB, and the default row locks.

MySQL's three types of locks are summarized as follows:
Table-level Lock: The cost is small, lock fast, no deadlock, lock granularity, the probability of locking conflict is the highest, the least concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and highest degree of concurrency.
Page Lock: The cost and lock time bounds between the table lock and the row lock, there will be deadlock, the concurrency is general.
In different SQL statements, a different lock is used, automatically locked, unlocked, and the corresponding lock type within MySQL. Focus on MySQL table lock and InnoDB row lock. Since the MyISAM will be replaced by InnoDB, the focus is on InnoDB.

1. Introduction of Business

The two features of InnoDB are one of supporting transactions, the other is the use of row-level locks, but the introduction of transactions also brings some new problems, first introduce the background knowledge.

1.1 Transactions and their properties acid
A transaction is a logical processing unit consisting of the same set of SQL statements, with atomicity, consistency, isolation, and persistence.

1.2 Problems caused by concurrent transaction processing
Update lost, dirty read, non-repeatable read, Phantom read.

1.3 Transaction ISOLATION LEVEL
Because of the problems associated with concurrent transaction processing, the workaround is to correspond to different levels of transaction isolation. The way the database implements transaction isolation is basically divided into the following two types:
(1) One is to lock the data before it is read, and prevent other transactions from modifying the data.
(2) The other is not to add any locks, in the form of snapshot Snapshot "data version concurrency control" (multiversion Concurrency controls) referred to as MVCC.
The stricter the transaction isolation level of the database, the smaller the concurrency side-effect, the greater the cost, because in essence, the transaction is "serialized" to some extent, which is inconsistent with "concurrency".

Here are four isolation levels:

　　

2. Isolation LEVEL View

-- View transaction ISOLATION level, default is Repeatable-read, in SQL Server default is READ Committed SELECT @ @tx_isolation

About transactions, transaction concurrency issues, transaction isolation levels, and their rationale here are the details of "SQL Server lock and transaction dispel". (not finished)

MySQL Development advanced Article series 6 lock problem