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

How to use DB to implement distributed locks in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Table design

First of all, it is necessary to make it clear that DB still needs to be considered as the most vulnerable link in the system, so the pressure should be considered in the design, that is, the logic that can be implemented should not be implemented on DB, that is, the locking capability provided by DB should be used as little as possible. if it is a highly concurrent business, we should avoid using DB locks, and it is more effective to replace them with cache locks such as Redis. As shown in listing 1, the only constraint in this table is the combination of lock_name,timestamp,version primary keys, which are used below to implement pessimistic locks, optimistic locks and other business scenarios.

Listing 1: distributed lock table structure

CREATE TABLE `lock` (`gmt_ name` varchar '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, when one machine acquires the lock, the other machines are queued, and the other machines can only continue after the lock is released. It is quite troublesome to solve this problem at the application level, so the row locking capability provided by DB, that is, select xxx from xxx for update, is generally used. A scenario is generally strongly related to the business, such as inventory increase or decrease, and use the business object as the row lock. It should be noted that the lock pressure of this scheme is still on the database in nature. When too many threads are blocked and the operation is time-consuming, a large number of lock timeouts will occur in the end.

For B:

For a specific business in scenario B (tryLock), each machine in the cluster has scheduled tasks, but business requires that only one machine can be scheduled normally at a time.

The solution is to use the unique primary key constraint to insert a record for TaskA, and the version defaults to 1. If the insertion is successful, the lock will be acquired and the business operation will continue. In this scheme, deadlocks will occur when the machine dies, so there needs to be a scheduled task to regularly clean up the expired locks. The cleaning dimension can be cleaned at different times according to the lock_name.

Scheduled task cleaning strategy will bring additional complexity. Suppose machine An acquires the lock, but the processing becomes slow due to the shortage of CPU resources, and the lock is released by the timed task, so machine B will also acquire the lock. At this time, two machines hold locks at the same time. The solution is to set the timeout period to be much longer than the business processing time, or to change the version mechanism to an optimistic lock.

Insert into lock set lock_name='TaskA', resource=' locked business', version=1,gmt_create=now () success: acquire lock failed: abandon operation to release lock

Optimistic lock implementation

For optimistic locking scenarios, for example, large json extension fields are often used in background systems to store business attributes. When partial updates are involved, you need to query out, merge data, and write to DB. If concurrency exists in this process, it is easy to cause data loss, so you need to use locks to ensure data consistency. The corresponding operation is shown below. For optimistic locks, there is no deadlock. Therefore, 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 operation: take data, merge data, write back 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 success write back failure: roll back the transaction, read the above from scratch, do you know how to use DB to achieve distributed locks in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.