Shulou(Shulou.com)05/31 Report--

This article mainly explains that "locks in MySQL can be divided into several categories", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn that locks in MySQL can be divided into several categories.

According to the scope of locking, locks in MySQL can be roughly divided into three categories: global locks, table-level locks and row locks.

1. Global lock

A global lock is a lock on the entire database instance. MySQL provides a way to add a global read lock, and the command is Flush tables with read lock. You can use this command when you need to make the entire library read-only, and then the following statements from other threads will be blocked: data update statements (data additions and deletions), data definition statements (including table creation, table structure modification, etc.), and commit statements for update transactions. [related recommendation: mysql tutorial (video)]

A typical usage scenario for a global lock is to make a logical backup of the entire library. That is, select each table of the whole library and save it as text.

However, if the entire library is read-only, the following problems may occur:

If the backup is on the main database, no updates can be performed during the backup, and the business will basically have to shut down.

If you back up on the slave library, the binlog synchronized from the master library cannot be executed from the slave library during the backup, which will cause master-slave delay.

Opening a transaction under the repeatable readable isolation level can get a consistent view

The official logical backup tool is mysqldump. When mysqldump uses the parameter-single-transaction, a transaction is initiated before the import data to ensure that a consistent view is obtained. Due to the support of MVCC, the data can be updated normally during this process. Single-transaction is only applicable to all tables that use transaction engine libraries

1. Since you want the entire library to be read-only, why not use set global readonly=true?

In some systems, the value of readonly is used for other logic, such as to determine whether a library is primary or standby. So the way you modify the global variable has a greater impact.

There are differences in exception handling mechanism. If the client disconnects abnormally after executing the Flush tables with read lock command, MySQL automatically releases the global lock and the entire library returns to a state that can be updated normally. After the whole library is set to readonly, if an exception occurs on the client, the database will remain in the readonly state all the time, which will cause the whole library to be unwritable for a long time and the risk will be high.

Table level lock

There are two kinds of table level locks in MySQL: one is table lock, the other is metadata lock (meta data lock,MDL).

The syntax of a table lock is lock tables. Read/write . The lock can be released actively with unlock tables, or automatically when the client is disconnected. Lock tables syntax not only restricts the reading and writing of other threads, but also defines the next operation object of this thread.

If the statement lock tables T1 read,t2 wirte; is executed in one thread A, the statements of other threads writing T1 and reading and writing T2 will be blocked. At the same time, thread A can only read T1 and write T2 before executing unlock tables. It is not even allowed to write T1

Another type of table-level lock is MDL. MDL does not need to be used explicitly and is automatically added when accessing a table. The function of MDL is to ensure the correctness of reading and writing. If a query is traversing the data in a table and another thread changes the table structure and deletes a column during execution, the result obtained by the query thread does not match the table structure.

MDL is introduced in the MySQL5.5 version, adding MDL read locks when adding, deleting, modifying and querying a table, and MDL write locks when structural changes are to be made to the table.

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

Read-write locks and write locks are mutually exclusive, which is used to ensure the security of the operation of the change table structure. Therefore, 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 it can start execution.

To add fields to a table, or modify fields, or index, you need to scan the data of the entire table. When operating on large tables, special care should be taken so as not to affect online services.

Session A starts first, and then a MDL read lock is added to table t. Because session B also requires an MDL read lock, it can be executed normally. Sesession C will then be blocked because session A's MDL read lock has not been released, while session C needs a MDL write lock, so it can only be blocked. It doesn't matter if only session C is blocked, but then all requests for new MDL read locks on table t will also be blocked by session C. All additions, deletions, modifications and queries to the table need to be locked by applying for a MDL read lock first, which means that it is completely unreadable or writable.

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

1. How to safely add fields to a small table?

The first step is to solve the long transaction. If the transaction is not committed, it will occupy the DML lock all the time. The currently executed transaction can be found in the innodb_ box table of MySQL's information_ schema library. If the table to be changed by DDL happens to have a long transaction, consider pausing DDL first, or kill dropping the long transaction.

two。 If the table to be changed is a hot point table, although the amount of data is small, but the above requests are very frequent, and have to add a field, what should I do?

Set the wait time in the alter table statement. If you can get the MDL write lock within this specified wait time, it is best to give up if you can't get it and don't block the following business statements. Then repeat the process by retrying the command

Third, row lock

MySQL's row locks are implemented by each engine itself at the engine layer. However, not all engines support row locks. For example, MyISAM engines do not support row locks.

A row lock is a lock for a row record in a data table. For example, if transaction A updates a row, and transaction B updates the same row at this time, it must wait until the operation of transaction An is completed.

1. Two-phase locking protocol

The row locks of both records held by transaction An are only released during commit, and the update statement of transaction B will be blocked until transaction An executes commit. Transaction B can continue execution.

In InnoDB transactions, row locks are added when needed, but not immediately when they are not needed, but not until the end of the transaction. This is the two-phase locking protocol.

If multiple rows need to be locked in a transaction, put back the locks that are most likely to cause lock conflicts and affect concurrency as far as possible.

Suppose that in order to achieve a movie ticket online trading business, customer A needs to buy movie tickets at cinema B. The business needs involve the following operations:

1. Deduct the movie fare from the balance of customer An account

two。 Increase the ticket price of this movie to the account balance of Cinema B

3. Record a transaction log

In order to ensure the atomicity of the transaction, put these three operations in one transaction. How to arrange the order of these three statements in the transaction?

If at the same time another customer C wants to buy a ticket at Cinema B, then the conflict between the two transactions is sentence 2. Because they want to update the balance of the same cinema account, they need to modify the same line of data. According to the two-phase locking protocol, all row locks required for operations are released when the transaction commits. So, if you put statement 2 at the end, for example, in the order of 3, 1, and 2, then the cinema account balance line has the least lock time. This minimizes the lock waiting between transactions and increases the degree of concurrency.

2. Deadlock and deadlock detection

In a concurrent system, different threads have circular resource dependencies, and when the threads involved are waiting for other threads to release resources, it will cause these threads to enter a state of infinite waiting, which is called deadlock.

Transaction An is waiting for transaction B to release id=2 's row lock, while transaction B is waiting for transaction A to release id=1 's row lock. Transaction An and transaction B enter a deadlock state while waiting for each other's resources to be released. When a deadlock occurs, there are two strategies:

One strategy is to go straight into waiting until it times out. This timeout can be set by the parameter innodb_lock_wait_timeout

Another strategy is to initiate deadlock detection and actively roll back a transaction in the deadlock chain after the deadlock is found so that other transactions can continue to execute. Set the parameter innodb_deadlock_detect to on, indicating that this logic is enabled

In InnoDB, the default value for innodb_lock_wait_timeout is 50s, which means that if the first strategy is adopted, it will take 50s for the first locked thread to exit after a deadlock, and then other threads can continue to execute. For online services, this waiting time is often unacceptable

Normally, you still have to use an active deadlock check strategy, and the default value for innodb_deadlock_detect is on itself. Active deadlock monitoring can quickly detect and deal with deadlocks when deadlocks occur, but it has an additional burden. Whenever a transaction is locked, it is necessary to see whether the thread it depends on is locked by others, so as to determine whether there is a loop waiting, that is, a deadlock.

If all transactions have to update the scene of the same line, each new blocked thread will have to judge whether it will cause a deadlock due to its own joining, which is a time complexity of O (n).

How to solve the performance problems caused by this hot row update?

1. If you ensure that there will be no deadlocks in this business, you can turn off the deadlock detection temporarily.

two。 Control degree of concurrency

3. Change one line to logical multiple lines to reduce lock conflicts. Take the cinema account as an example, consider putting it on multiple records, such as 10 records, and the total amount of the cinema account is equal to the sum of the values of these 10 records. In this way, every time you have to add money to the cinema account, randomly select one of the records to add. In this way, the probability of each collision is changed to the original 1max 10 of the member, which can reduce the number of lock waits and the CPU consumption of deadlock detection.

Fourth, why do I check only one line of sentences and execute them so slowly?

Construct a table with two fields id and c, and insert 100000 rows of records into it

CREATE TABLE `t` (`id` int (11) NOT NULL, `c` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE DEFINER= `root` @ `% `PROCEDURE `idata` () BEGIN declare i int; set iTun1; while (I)

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.