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

Lock is a mechanism by which a computer coordinates multiple processes or pure threads to access a resource concurrently.

In the database, in addition to the contention of the traditional computing resources (CPU, RAM, Icano), the data is also a kind of resource shared by many users.

How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in the existing database, and lock conflict is also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases.

Why is there a lock?

The use of database can not avoid the concurrency problem, when concurrent transactions access a resource at the same time, it may lead to data inconsistency, so we need a mechanism to serialize data access to ensure the consistency of database data.

Lock is one of the mechanisms.

We can use public toilets as an analogy.

Public toilets are available to multiple consumers, so there may be situations where multiple people need to use toilets at the same time.

However, there is only one toilet, so we can't all work together, can we?

In order to avoid conflict, locks are installed in the toilet. when one person takes the test, he can lock it on the inside, and the others can no longer open it from the outside and have to wait.

Wait for the people inside to come out and open the lock from the inside so that the people outside can go in.

Next, I will take you to sort out the lock management mechanism and lock execution process of MySQL. First, there is a general context.

Lock Management Mechanism of MySQL

1. Global read lock-FLUSH TABLES WITH READ LOCK (SQL layer)

2. Table-level table-level data lock (SQL layer)

3. Meta-data metadata lock: implemented in table cache cache to provide isolation operation for DDL (Data Definition Language).

4. Storage engine specific mechanism-row locks row lock, page locks page lock, table locks table level, version control (implemented in the engine)

Compared with other databases, the locking mechanism of MySQL is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms.

Lock execution flow of MySQL

1. All tables used in the calculation statement

2. In each table: open the table, get the TABLE object from the table cache cache, and add a meta-data metadata lock to the table

3. Change the data after waiting for the global read lock

4. In each table: lock the table and add a table-level data lock to the table

5. Execute the statement: call: handler::write_row () / read_rnd () / read_index (), etc.; implicitly call the engine-level engine-level lock mechanism

6. in each table: release the data lock of the table

7. In each table: release the DDL lock of the table and put the table back into the table cache cache

Next, let's start with a brief look at each lock to see what features it has.

Global lock

When a global lock is added, the entire library becomes read-only, and all write operations are blocked, including:

Addition, deletion and modification of data

Creation and modification of table structure

Update transaction

The command to add a global lock: Flush tables with read lock, or FTWRL.

The main usage scenario of global lock is the logical backup of the whole database. There is a certain risk when a global lock is added for backup:

1. If you back up in the main database and read only during the backup, the business will be affected.

2. If the slave database is backed up and the slave library is read-only, the master can not be updated in time, resulting in inconsistency of the master.

Mysqldump-single-transaction

As you may recall, when we talked about transactions before, there was an isolation level called repeatable read, that is, after the isolation level was set to enter the transaction, other transactions changed the data without affecting the current read.

Using the mysqldump command, combined with the-- single-transaction parameter, you can set the isolation level to: REPEATABLE READ.

And then execute a START TRANSACTION statement to make the entire data consistent during the dump process, which is useful for InnoDB's data tables and does not lock the tables.

To ensure the validity of the final dump file when using the-- single-transaction command. There is no need for the following statements ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, because consistent reads cannot isolate the above statements. Therefore, if you use the above statement during the dump process, the file data from the dump may be inconsistent or unavailable.

Why not just use mysqldump-- single-transaction to back up?

Because, some engines do not support transactions, such as the MyISAM engine, so now everyone is pushing to replace MyISAM with InnoDB.

Set global readonly=true?

Set global readonly=true can also make global tables read-only. What's the difference?

First of all, the way you modify the global variable has a greater impact and is not recommended.

In addition, the exception handling mechanism is different from FTWRL:

FTWRL command: if the client is disconnected abnormally, MySQL will automatically release the global lock, and the whole library will return to the state of normal update.

In the readonly state, if an exception occurs on the client, the database will remain in the readonly state all the time, resulting in the whole library being unwritable for a long time

Pay attention

If there is a read and write before the FTWRL, the FTWRL will wait for the read and write to be completed before execution.

When FTWRL executes, the data of dirty pages should be brushed to disk, and the consistency of data should be maintained.

When executing FTWRL, it waits for all transactions to be committed.

Table level lock

Grammar

LOCK TABLES tbl_name; # does not affect the write operation of other tables

Unlocking is also:

UNLOCK TABLES

Note:

Both statements need to pay attention to a feature when executing, that is, implicitly submitted statements will implicitly automatically execute unlock tables when exiting the mysql terminal, that is, if you want to make the table lock effective, you must always maintain the dialogue.

Lock tables not only restricts the reading and writing of other threads, but also restricts the next operation objects of this thread.

The influence of locking the whole table is greater.

Read lock and wirte lock of P.S. MYSQL

Read-lock: allows other concurrent read requests, but blocks write requests, that is, you can read at the same time, but does not allow any writes, also known as shared locks

Write-lock: does not allow other concurrent read and write requests, which are exclusive (exclusive), also known as exclusive locks

Metadata lock (MDL:metadata lock)

Metadata locks do not need to be used explicitly and are automatically added when a table is accessed.

Its main function is to ensure the correctness of reading and writing.

For the operation of adding, deleting, changing and querying a table, you need to add a MDL read lock first.

Table structure change operation, need to add MDL write lock first

MDL read locks are not mutually exclusive, and multiple threads can add, delete, modify and query a table at the same time.

MDL read-write locks and write locks are mutually exclusive, which is used to ensure the security of changing table structure operations.

If two threads want to add fields to a table at the same time, one of them will have to wait for the other to finish execution before starting execution.

The MDL lock in a transaction is applied at the beginning of statement execution, but is not released immediately at the end of the statement, but is not released until the entire transaction is committed.

Therefore, long transactions need to be avoided, because long transactions will cause locks not to be released, subsequent operations will accumulate, and threads in this library will soon be full.

Row lock

Row locks are implemented at the engine level, such as MyISAM engines do not support row locks directly, and these engines can only use table locks in concurrency control!

InnoDB's row lock

Two-phase agreement:

Add when needed

Release at the end of the transaction

When you need to lock multiple rows, put the locks that affect concurrency back as far as possible, which can minimize the lock waiting between transactions and improve the degree of concurrency.

In addition, InnoDB's row lock is based on the index, and the lock is the index. Therefore, if the updated column is not indexed, the entire table is locked.

Deadlock

Different threads have circular resource dependencies, and the threads involved are waiting for other threads to release resources.

Deadlock game

1. The active wait timeout is set by parameter innodb_lock_wait_timeout, but the business cannot wait.

2. Active deadlock detection (innodb_deadlock_detect=on)

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock and roll back, while the other can acquire the lock to complete the transaction.

In addition, we can avoid deadlocks in the following ways:

Reduce the probability of deadlock through table-level lock

Multiple programs try to agree to access the tables in the same order (this is also a way to solve the philosopher dining problem in concurrency theory).

Try to lock all the resources needed by the same transaction as much as possible.

In addition, deadlock detection is also very resource-consuming, to determine whether it will lead to deadlock due to its own joining, which is a time complexity of O (n) operation.

For example, if there are 1000 concurrent threads updating the same row at the same time, the deadlock detection operation is of the order of 1 million, which consumes a lot of CPU resources.

How to solve the resource-consuming situation of deadlock detection?

1. Turn off deadlock detection. You need to ensure that deadlock will not occur.

2. Control concurrency, correspond to peer updates, and queue up before entering the engine

Database server implementation, middleware implementation

Do not implement it on the client side because the number of clients is unknown

Change the MySQL source code

Split hot updated row data into logical multiple rows to reduce lock conflicts, but business complexity may be greatly increased

When updating a record, exactly when to use a row lock and when it is a table lock

The engine supports row locks, such as innodb

The engine does not support row-and-table locks, such as myisam

The process of Online DDL

In MySQL5.6, more alter table type operations are supported to avoid copy data, and DML operations are not blocked in the process of online DDL, which really implements Online DDL.

1. Take the MDL write lock

2. Downgrade to MDL read lock

3. Really be a DDL

4. Upgrade to MDL write lock

5. Release the MDL lock

1, 2, 4, 5 if there are no lock conflicts, the execution time is very short.

Step 3 takes up most of the time of DDL, during which the table can read and write data normally, so it is called "online"

Summary

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

These storage engines avoid deadlocks by always acquiring all the required locks at once and always acquiring table locks in the same order.

Table-level locks are more suitable for applications that are mainly query-based, with few concurrent users, and only a few update data according to index conditions, such as Web applications.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

It not only supports concurrency to the maximum extent, but also brings the maximum lock overhead.

In InnoDB, locks are acquired step by step, except for transactions consisting of a single SQL, which determines that deadlocks are possible in InnoDB.

Row-level locking is implemented only at the storage engine layer, but not at the Mysql server layer.

Row-level locks are more suitable for applications with a large number of concurrent updates of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

Judging from the above characteristics, it is difficult to say which kind of lock is better, and we can only choose a more suitable locking mechanism relative to the business scenario.

From the point of view of locks, table-level locks are more suitable for query-based application scenarios, while row-level locks are more suitable for application scenarios where a large number of concurrent updates of a small amount of data are based on index conditions.

For the commonly used storage engine, MyISAM uses table-level locks, while InnoDB uses row-level locks plus table-level locks.

Reference:

Https://dwz.cn/oudQ7cM9

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.