MySQL (5)---Lock

Lock

an overview

The database locking mechanism simply means that the database is designed to ensure the consistency of the data and to make the various shared resources become orderly by concurrent access. A corresponding locking mechanism is required for any kind of database.

MySQL each storage engine uses three types (levels) of locking mechanisms: table-level locking, row-level locking , and page-level locking.

MySQL can be broadly categorized into the following 3 types of locks:

table-level lock : Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.

row-level locks : High overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.

page Locks : Overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

By the way, what is a deadlock?

The so-called deadlock: refers to two or more than two processes in the course of execution, because of the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to advance. This is called the system is in a deadlock state or the system has produced a deadlock, these are always mutually aulympic process known as the deadlock process.

second, the MySQL table-level lock lock Mode (MyISAM)

1. Read lock and Write lock

There are two modes of MySQL table-level Lock: table shared lock (read lock) and table exclusive write lock (write lock).

MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT), and will automatically write locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.).

As a result, the MyISAM table will be operated in the following situations:

A, read operations on the MyISAM table (read-lock), does not block other processes from reading requests to the same table, but blocks write requests to the same table. Write operations for other processes are performed only when the read lock is released.

b, write to the MyISAM table (write lock), will block the other process to the same table read and write operations, only when the write lock is released, the other process will perform read and write operations.

In short, reading a lock can block writing, but it does not block reading. The write lock will block both reading and writing.

2, table lock to optimize

Using table-level locking is less expensive than implementing row-level or page-level locking during lock implementations, and the lock itself consumes the least amount of resources. However, because of the large granularity of the lock, the contention of the locked resource will be more than the other lock level, which will reduce the concurrency processing power to a large extent. So table lock optimization, the most important thing is how to increase the degree of concurrency. Because the locking level is not possible to change, the first thing to do is to shorten the time to lock as much as possible, and then let the possible concurrent operations be as concurrent as possible.

1. Shorten the lockout time:

1) Minimize the complexity of the query and split the complex query into several small query steps;

2) Create an index that is efficient enough to make data retrieval faster;

3) Try to keep the MyISAM storage engine table to hold the necessary information, control the field type;

4) Optimize MyISAM table data files with reasonable opportunity.

three, InnoDB lock problem

The biggest difference between InnoDB and MyISAM is two points: one is support transaction (TRANSACTION), and the other is row-level lock . There are many differences between row-level and table-level locks, and the introduction of transactions poses some new problems.

1. Transaction (Transaction) and its acid properties

A transaction is a logical processing unit consisting of a set of SQL statements that have a 4 property, often referred to as the Acid property of a transaction.
Primitive : atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.
Consistency : If the database is a state of integrity before the transaction is executed, the database remains an integrity state after the transaction finishes regardless of whether the transaction succeeds.
Isolation : Transaction isolation is when multiple users concurrently access the database, the transaction of one user cannot be disturbed by other users ' transactions, and data between multiple concurrent transactions is isolated from each other.
persistence : Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.

2. Problems caused by concurrent transactions

Update lost : When two or more transactions select the same row, and then update the row based on the value originally selected, a missing update problem occurs because each transaction is unaware of the presence of other transactions-the last update overwrites the updates made by other firms.
dirty reads: transaction A reads the updated data of transaction B, and then B rolls back the operation, then the data that a reads to IS dirty data
non-repeatable READ: transaction A reads the same data multiple times, and transaction B updates and commits the data during transaction a multiple reads, resulting in inconsistent results when transaction a reads the same data multiple times.
Phantom reading: system administrator A changes the scores of all students in the database from the specific score to the ABCDE level, but system Administrator B inserts a record of a specific score at this time, and when system administrator a changes to the end, it turns out that there is a record that hasn't changed, as if there was a hallucination. This is called Phantom Reading.

3. Transaction ISOLATION LEVEL

Transaction ISOLATION LEVEL	Dirty Read	Non-REPEATABLE READ	Phantom reading
READ UNCOMMITTED (read-uncommitted)	Is	Is	Is
Non-repeatable read (read-committed)	Whether	Is	Is
Repeatable Read (Repeatable-read)	Whether	Whether	Is
Serialization (Serializable)	Whether	Whether	Whether

4, InnoDB row level lock

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block.

InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

InnoDB implements the following two types of row locks.

shared locks : Allows a transaction to read one line, preventing other transactions from acquiring exclusive locks on the same data set.
Exclusive Locks : Transactions that allow the acquisition of exclusive locks to update data, preventing other transactions from acquiring the same data set share read locks and exclusive write locks.

5. Clearance Lock

When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the indexed entry for the qualifying existing data, and the record for which the key value is within the condition but does not exist is called "gap".

For example, if there are only 101 records in the EMP table, the value of the Empid is,..., 100,101 respectively, the following SQL:

SELECT * from emp WHERE empid > + for UPDATE

is a retrieval of a range condition, innodb not only locks the records that meet the conditional Empid value of 101, but also locks the "gap" of Empid greater than 101 (which do not exist).

This time if you insert Empid equal to 102 of the data, if that thing has not been submitted, then you will be in a waiting state, unable to insert data

6. What happens when a row lock changes table lock

For example, a table with a varchar property, if you do not add ' name ' (quotation marks) in the query, then it is also possible to implement the query function, the single-row lock will change the table lock.

Iv. Optimization Recommendations

(1) As far as possible to make all the data through the index to complete, avoid no index, row lock up table lock.

(2) Reasonable design index, minimize the range of locks

(3) To minimize the search conditions, to avoid clearance lock

(4) Try to control the transaction size, reduce the amount of lock resources and the length of time.

MySQL (5)---Lock