preheating

More like a paragraph: without a cold biting, how fragrant plum blossom, learning is boring, please adhere to! I learned this article from Mr. Dinky. Do not understand their own search ha! It takes about 35 minutes to read this article!

We’ve looked at MySQL index selection, InnoDB data structure selection, index, overwrite index, query optimization in detail. Today we introduce database global lock, table lock, row lock, two phase lock protocol and case!

I’m sure you’ve heard of such an accident. A programmer at a large Internet company added a field that crashed online services. It doesn’t matter if you haven’t heard of it. We’ll analyze it together.

Online business is paralyzed mainly due to deadlock resulting in resource exhaustion, resulting in a crash. So let’s start talking about why deadlocks happen. MySQL locks are mainly divided into global locks, table locks, row locks. Let’s talk about it in detail.

start

Global lock

define

Global lock: As the name implies, this is the lock for the entire MySQL instance. This is a locking method provided by MySQL itself. Command is

Flush tables with read lock (FTWRL)
Copy the code

You can do this when you need to lock the entire database or make the database read only. After doing this, all update modification operations, definition operations will block.

Tip: Update modify operations: Add, modify, delete. Define operations: Create a table and modify the table structure.

Application scenarios

Global locking is at the heart of a full library logical backup, that is, exporting all data to a file. Previously, this would have locked the entire instance database and put it in read-only mode. That sounds dangerous. What if someone writes a library in the meantime? Yeah, I can’t write anything but wait.

If there is a master library, a slave library. If you back up on the master, the entire business will grind to a halt. If the backup is performed in the secondary database, the secondary database cannot execute the binlog of the master database synchronization during the backup due to the relationship between the master database and the slave database, which will cause the master/slave delay and even data inconsistency.

It doesn’t seem very friendly to lock a backup globally. Why is backup locked? I think a lot of you might have guessed, in order to make sure that the data is viewed consistently at one point in time. Let’s take an example to illustrate this problem.

case

Take my personal development of the e-commerce system for example. I backed up all the data before August 27th. If a user is placing an order at this time without locking, what is involved is the parent-child table of the order table, the sub-table of goods. The inventory table. There are also user subtable operations. A new record is added to the order table, and the order subtable is added with detailed item information for this order. Purchase volume of merchandise subtable. Inventory table destocking. And the integrals of the user’s child tables. If the order sheet is completed at this time, the inventory sheet. Back up to this position at this time, there will be this very unusual situation. The order has been placed, the item has been processed, the user has paid, but the user’s points have not been added.

When the data is recovered, the user will find that they have lost a score. Such a program is wrong. If the order of the backup table is reversed, there will be more users inexplicable integral. But the order wasn’t placed. This led to more points from the boss. That’s not right either. This is the result of locking and not locking.

Backup tool

Introduction to the

Mysqldump is a logical backup tool for MySQL.

Its backup principle is to connect to the MySQL database through the protocol, query the data to be backed up, and convert the queried data into the corresponding INSERT statements. When we need to restore these data, we can restore the corresponding data as long as we execute these INSERT statements.

When mysqldump uses the — single-transaction argument, a transaction is started before the data is transferred to ensure that the consistency view is retrieved. Due to the support of MVCC, data can be updated normally during this process.

conclusion

MySQL has a backup tool called mysqldump, so why do we need to use global locks? Because this mysqldump must support isolation levels. Myisam does not support a transactional engine, so there are times when transactions are not supported and global locking is necessary for data consistency.

Read-only mode

MySQL has a read-only mode, set global readonly=true to enable the entire library to be in read-only mode. However, this method is not recommended.

Let’s start with the mechanics of read-only mode. Under normal circumstances, some business logic would use the readonly value for other logic processing. Such as determining whether a library is a master or slave. Therefore, modifying readOnly may cause unnecessary trouble.

If an exception occurs on the client, the database remains read-only. This can cause the entire library to become unwritable for a long time, which is extremely risky.

If the client fails, MySQL automatically releases the global lock and the library returns to its normal state.

The host only adds, deletes, and modifiers. The library only queries.

Table level lock

define

Business updates are not just about adding, deleting, or modifying data (DML data Manipulation Language), but also about adding fields to modify table structures (DDL Data Definition Language). Either way, once a library is globally locked, any table in it that you add a field to will be locked. Even if you are not globally locked, adding a field is not easy because you will still encounter table-level locking.

Table level locks are divided into table locks and MDL metadata locks.

The syntax for a table lock is lock T_user read/write and unlock tables manually. The lock can also be automatically released when the client is disconnected. Table locks restrict read and write operations by other threads.

For example, if you execute lock tables T1 read, T2 write on thread A; This statement blocks statements that write to or write to T1 or T2 from other threads. In addition, thread A can only read T1 and read T2 before executing unlock tables. It is not allowed to write to T1, so it is not allowed to access other tables.

In the days before more fine-grained locking, table locking was a common way to handle concurrency. For InnoDB, table locking is not the best solution. The best way to handle this is row locking. I’ll go through them all

Metadata locks (MDL) do not need to be used explicitly and are automatically applied when accessing a table. The purpose of MDL is to preserve read and write consistency. In plain English, while one thread is traversing the list looking for data, another thread comes in and modifies a field. The structure of the query must not match. So that’s the kind of problem MDL solves.

SQL > alter table read lock MDL (MySQL5.5) Add an MDL write lock to a table when making structural changes to the table.

  1. Read locks are not mutually exclusive, so you can have multiple threads adding, deleting, or modifying a table at the same time.
  1. Read/write locks and write locks are mutually exclusive to ensure the security of changing the table structure. Therefore, if two threads are adding fields to a table at the same time, one of them will wait for the other to finish before starting to execute

When using MDL locks, there is a pit. Although MDL is added by default, it is a mechanism you should not ignore. We will illustrate the following figure

When sessionA initiates a query request, a read lock is added first

SessionB also adds a read lock to a query request. Read locks are not mutually exclusive, so they do not affect each other

SessionA and sessionB do not release the read lock before sessionA and sessionB release the read lock. This time sessionC jam, this user jam is not important, the most important case all users jam.

SessionD initiates a query request. The read lock is mutually exclusive with the write lock of sessionC, resulting in deadlock of read/write contention.

If a table is frequently queried and the client has a reconnection mechanism, threads and memory can be quickly exhausted. The lock is not immediately released when the transaction is executed, but when the statement is executed. The lock is not released immediately after the statement ends, but after the transaction commits.

So the question is, how to add fields to the table safely?

  1. First we need to resolve long transactions, which will hold the MDL lock until they commit. In the Innodb_TRx table of MySQL’s Information_SCHEMA library, you can look up the transaction that is currently executing. If the table to which you want to make DDL changes happens to have a long transaction in progress, consider suspending the DDL first or killing the long transaction.
  1. If the table you want to change is a hotspot table, the data volume is small, but the request is frequent, and you have to add a field, what do you do? At this point, kill may not work because new requests are coming in.
  2. An ideal mechanism would be to set the wait time in the ALTER TABLE statement. If you can get the MDL write lock within the specified wait time, it is better not to block subsequent business statements. The process is then repeated by the developer or DBA with a retry command. MariaDB has incorporated AliSQL’s functionality, so both open source branches currently support DDL NOWAIT/WAIT N syntax.
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
Copy the code

Row locks

Definition:

After introducing table locks, let’s talk about down locks. Row locks are a more granular version of table locks. Row locking is implemented by individual engines at the MySQL engine layer. This does not mean that all engines support row locking, as MyISam does not. This means that myISAM can only handle concurrent requirements through table locks. This is one of the principles behind the choice of InnoDB as the default MySQL engine.

Row locks are locks for individual records in a data table. For example, transaction A updates ID=10, and transaction B updates ID=10. In order for transaction B to execute properly, it must wait for transaction A to commit the transaction. This is the perfect solution for concurrency.

Consider an error-prone lock: the two-phase lock

We continue our analysis through the following figure. Transaction A updates the data with ID 1,2, while transaction B updates the data with ID=1. So both of these data points are modifying this data point with ID=1. This execution process requires A sequence in which transaction B can be executed only after transaction A has completed.

Tip: After the change is complete, the lock is not released until transaction A commits. This is the two-phase lock protocol

Application Scenarios:

Next we introduce the problem of how to use, how to submit the concurrent processing capacity of the system?

If you need to lock more than one row in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible. Let me give you an example

This example is A project I designed, the cinema transaction system. Customer A wants to buy A movie ticket at cinema B. To simplify things, this business needs to involve the following operations:

  1. Deduct the movie ticket price from customer A’s account balance;
  2. Add this movie ticket to theater B’s account balance;
  3. Log a transaction.

That is, to complete the transaction, we need to update two records and insert one record. Of course, to keep the transaction atomic, we put these three operations in one transaction. So, how would you arrange the order of these three statements in a transaction?

If another customer C wants to buy a ticket at cinema B at the same time, then the part of the two transactions that conflict is statement 2. Because they need to update the balance of the same theater account, they need to modify the same line of data.

According to the two-phase locking protocol, no matter how you order statements, all operations require row locks to be released at transaction commit time. So, if you put statement 2 at the end, say 3, 1, 2, then the cinema account balance line will be locked for the least amount of time. This minimizes lock waits between transactions and improves concurrency.

If this theater does an event, it can pre-sell all the movie tickets for a year at a low price, and this event is only for one day. So at the start of the active time, your MySQL will hang. When you log on to the server, the CPU consumption is close to 100%, but the entire database is performing less than 100 transactions per second. What is the reason for this? Here, I’ll talk about deadlocks and deadlock detection.

Deadlock, deadlock detection

When different threads in a concurrent system have a circular resource dependency, and the involved threads are waiting for other threads to release resources, it will cause these threads to enter an infinite wait state, called a deadlock. I’m using a row lock in a database as an example.

ID=1; ID=1; ID=1; ID=2; ID=1;

Transaction A is waiting for transaction B to release the lock on the data whose ID=2 and transaction B is waiting for transaction A to release the lock on the data whose ID=1. Both sides are waiting and then you get into a stalemate, which leads to a deadlock.

There are two solutions to this problem

  1. Set innodb_lock_WAIT_TIMEOUT to determine the timeout mechanism, which is automatically released.
  2. When a deadlock is found, a transaction can be rolled back to allow the execution of another transaction. By setting innodb_deadlock_detect to ON (deadlock detection)

Let’s analyze the feasibility of the two schemes one by one.

  1. With the timeout mechanism, the system default supermarket mechanism is 50 seconds, which means 50 seconds before the timeout mechanism. For online business, waiting 50 seconds is obviously unrealistic. If the setting is too short, there may be a real timeout, there will be a lot of exceptions, there will be accidental injuries.
  2. Rolling back transactions is currently the best solution. The default value for Innodb_deadlock_detect is itself on. Deadlock detection takes effect immediately.

Each time a transaction is locked, it is necessary to see if the thread on which it depends has been locked by someone else, and so on. Finally, it is necessary to determine whether a waiting loop, known as a deadlock, has occurred.

What if all the transactions we talked about above update the same row?

Each new blocked thread has to determine whether it has caused a deadlock by adding it, which is an O(n) operation. Assuming 1000 concurrent threads are updating the same row at the same time, deadlock detection operations are on the order of 1 million. Although the final result of the test is no deadlock, it consumes a lot of CPU resources in the process. As a result, you can see high CPU utilization, but not many transactions per second.

The solution: We start with design. You can consider reducing lock conflicts by changing one row to logically multiple rows. Again, using the movie theater account as an example, consider placing multiple records, such as 10 records, and the total number of theater accounts is equal to the sum of the values of the 10 records. So each time you want to add money to the cinema account, select one of the records at random to add. In this way, the probability of each conflict becomes 1/10 of the original, which can reduce the number of lock waits and reduce the CPU consumption of deadlock detection. This solution appears to be lossless, but such a solution requires detailed design based on business logic. If the account balance is likely to decrease, such as the refund logic, then you need to consider special handling of the code when a portion of the line goes to zero.

At the end