Detailed explanation of the idea of ​​distributed lock in MySQL with the help of DB

Preface

Whether it is a stand-alone lock or a distributed lock, the principle is to judge the behavior of the current operation based on shared data. For a single machine, the RAM memory is shared, and for a cluster, it can be achieved with the help of third-party components such as Redis, ZK, DB, etc. Redis and ZK provide excellent support for distributed locks and are basically ready to use out of the box. However, these components themselves need to be highly available, and the system also needs to rely heavily on these components, which adds a lot of extra costs. DB is a high-availability component by default for the system. Using DB to implement distributed locks is also a good solution for some low-frequency businesses, such as controlling the start-up of scheduled tasks under multiple machines, and processing approval callbacks. This article will give some scenarios and solutions for implementing distributed locks with DB, hoping to inspire you.

Table Design

First of all, it should be clear that DB is still the most vulnerable link in the system. Therefore, pressure issues need to be considered during design. That is, logic that can be implemented by the application should not be implemented on the DB. In other words, the locking capabilities provided by the DB should be used as little as possible. If it is a high-concurrency business, DB locks should be avoided. It is more effective to use cache locks such as Redis instead. As shown in Listing 1, the only constraint in the table is the primary key composed of lock_name, timestamp, and version. These three keys will be used below to implement business scenarios such as pessimistic locking and optimistic locking.

Listing 1: Distributed lock table structure

CREATE TABLE `lock` (
`lock_name` varchar(32) NOT NULL DEFAULT '' COMMENT 'Lock name',
`resource` bigint(20) NOT NULL COMMENT 'Business primary key',
`version` int(5) NOT NULL COMMENT 'Version',
`gmt_create` datetime NOT NULL COMMENT 'Generation time',
PRIMARY KEY (`lock_name`,`resource`,`version`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Pessimistic lock implementation

There are two common operations in pessimistic locking business:

For A:

In scenario A, after a machine acquires the lock, other machines are in a queue state. They can continue only after the lock is released. This application-level solution is quite troublesome. Therefore, the row lock capability provided by the DB is generally used, that is, select xxx from xxx for update. Scenario A is generally closely related to the business, such as inventory increase or decrease, and the business object can be used as the row lock. It should be noted that the locking pressure of this solution is essentially on the database. When too many threads are blocked and the operation is time-consuming, a large number of lock timeouts will eventually occur.

For B:

For scenario B (tryLock), let's take a specific business as an example. In the cluster, each machine has a scheduled task, but the business requires that only one machine can be scheduled normally at the same time.
The solution is to use the unique primary key constraint to insert a record for TaskA. The default version is 1. If the insertion is successful, the lock is obtained and the business operation can continue. This solution will cause deadlock when the machine hangs up, so a scheduled task is also required to regularly clean up expired locks. The cleaning dimension can set different time cleaning strategies according to lock_name.

The scheduled task cleanup strategy will bring additional complexity. Suppose machine A obtains the lock, but due to CPU resource constraints, the processing slows down. At this time, the lock is released by the scheduled task, so machine B also obtains the lock. In this case, two machines hold the lock at the same time. The solution is to set the timeout period to be much longer than the business processing time, or add a version mechanism to change to optimistic locking.

insert into lock set lock_name='TaskA' , resource='Locked business', version=1, gmt_create=now()
Success: Get the lock. Failed: Abandon the operation and release the lock.

Optimistic locking implementation

For the optimistic lock scenario, let's take a specific business as an example. In the background system, large JSON extension fields are often used to store business attributes. When it comes to partial updates, you need to first query them, merge the data, and write them to the DB. If there is concurrency in this process, it is easy to cause data loss. Therefore, locks are needed to ensure data consistency. The corresponding operations are shown below. For optimistic locks, there is no deadlock, so the business id field is directly stored here to ensure that each business id has a corresponding record and does not need to be cleared by the corresponding timer.

select * from lock where lock_name='Business name', resource='Business id';
Does not exist: insert into lock set lock_name='Business name', resource='Business id', version=1;
Get version: version
Business operations: fetch data, merge data, write data back to DB: update lock set version=version+1 where lock_name='business name' and resource='business id' and version= #{version};
Write back success: Operation successful Write back failure: Roll back the transaction and start over

If an optimistic lock write fails, the entire transaction will be rolled back. Therefore, optimistic locks are not suitable for scenarios where write conflicts are frequent. A large number of transaction rollbacks will put tremendous pressure on the DB and ultimately affect the specific business system.

Summarize

The principle of distributed locks is actually easy to understand, but the difficulty lies in how to choose the most appropriate solution for specific business scenarios. No matter which lock solution is used, it is closely related to the business. In short, there is no perfect distributed lock solution, only the lock solution that best suits the current business.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.