How to realize distributed lock with dB in MySQL



Whether it is a single machine lock or a distributed lock, the principle is to judge the current operation behavior based on the shared data. For a single machine, it is shared RAM memory, and for a cluster, it can be realized with the help of redis, ZK, DB and other third-party components. Redis and ZK provide good support for distributed locks, which are basically out of the box. However, if these components need to be highly available, the system also needs to rely on these components, which increases a lot of extra costs. DB is a high availability component by default for the system. It is also a good solution to use DB to implement distributed locking for some low-frequency businesses, such as controlling the start-up of scheduled tasks under multiple machines, and processing of approval callback. This paper will give some scenarios and solutions of implementing distributed locking in dB, hoping to enlighten you.

Table design

First of all, it should be clear that DB still needs to be considered as the most vulnerable link in the system. Therefore, the pressure problem should be considered in the design, that is, the logic that can be applied and implemented should not be implemented on the DB, that is to say, the locking capability provided by DB should be used as little as possible. If it is a high concurrency business, it is necessary to avoid using DB lock and replace it with redis cache lock. As shown in Listing 1, the only constraint in this table is lock_ Name, timestamp, and version combine the primary keys. We will use these three to implement pessimistic lock, optimistic lock and other business scenarios.

Listing 1: distributed lock table structure

`lock_ Name ` varchar (32) not null default 'comment' lock name ',
`Resource ` bigint (20) not null comment 'Service primary key',
`Version ` int (5) not null comment 'version',
`gmt_ Create ` datetime not null comment 'generation time',
PRIMARY KEY (`lock_name`,`resource`,`version`)

Pessimistic lock implementation

For pessimistic lock service, there are two common operations:

For a:

In scenario a, when one machine obtains the lock, other machines are in the queue state, and the other machines can continue after the lock is released. This application level solution is quite troublesome. Therefore, the row lock capability provided by DB is generally used, that is, select xxx from XXX for update. Scenario a is generally strongly associated with business, such as inventory increase or decrease. Business objects can be used as row locks. It should be noted that the locking pressure of this scheme is still on the database. When there are too many blocked threads and the operation is time-consuming, a large number of lock timeout will occur in the end.

For B:

For a specific service in the B scenario (trylock), each machine in the cluster has a scheduled task, but the business requires that only one machine can be scheduled at the same time.
The solution is to use the unique primary key constraint to insert a record for taska, and the version is 1 by default. If the insertion is successful, the lock will be obtained and the business operation will continue. In this scheme, a deadlock will appear when the machine is hung up. Therefore, a scheduled task is needed to clear the expired locks regularly. The cleaning dimension can be based on the lock_ Name sets the cleaning policy at different times.

The timing task cleaning policy will bring extra complexity. Suppose that machine a obtains the lock, but the processing becomes slow due to the shortage of CPU resources. At this time, the lock is released by the scheduled task, so machine B will also obtain the lock. Then, two machines hold the lock at the same time. Solution: set the timeout time to be much longer than the business processing time, or increase the time-out The version adding mechanism is changed to optimistic lock.

insert into lock set lock_ Name: 'taska', resource: 'locked business', version = 1, GMT_ create=now()
Success: get lock
Failed: abort the operation
Release the lock

Optimistic lock implementation

For optimistic lock scenario, for a specific business, large JSON extension fields are often used to store business attributes in the background system. When partial updates are involved, the data needs to be queried first, merged, and written to DB. In this process, if there is concurrency, it is easy to cause data loss. Therefore, it is necessary to use locks to ensure data consistency. The corresponding operations are as follows There is no deadlock, so the business ID field is directly stored here to ensure that each business ID has a corresponding record, and the corresponding timer is not required to clear.

select * from lock where lock_ Name ='business name ', resource:' business ID ';
Does not exist: insert into lock set lock_ Name ='business name ', resource ='service ID', version = 1;
Get version: version
Business operations: fetch data, merge data, write back data
Write back to DB: update lock set version = version + 1 where lock_ Name ='business name 'and resource:' service ID 'and version = ා {version};
Write back success: operation successful
Write back failure: roll back the transaction and start from scratch

Optimistic lock write failure will roll back the whole transaction. Therefore, if the scenario with frequent write conflicts is not suitable for optimistic lock, a large number of transaction rollback will put great pressure on DB, and eventually affect the specific business system.


In fact, the principle of distributed lock is easy to understand, but the difficulty is how to choose the most appropriate solution in specific business scenarios. No matter what kind of lock scheme is closely related to business, there is no perfect distributed lock scheme, only the most suitable lock scheme for current business.

Well, the above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developeppaer.

Recommended Today

Ajax simple asynchronous communication example analysis

This paper introduces the method of Ajax simple asynchronous communication. Share with you for your reference. The specific analysis is as follows: client:Make an empty request to the server. The code is as follows: <html> <head> <title>XMLHttpRequest</title> <script language=”javascript”> var xmlHttp; function createXMLHttpRequest(){ if(window.ActiveXObject) xmlHttp = new ActiveXObject(“Microsoft.XMLHTTP”); else if(window.XMLHttpRequest) xmlHttp = new XMLHttpRequest(); } […]