Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to achieve distributed locks in mysql". In daily operation, I believe that many people have doubts about how to achieve distributed locks in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to achieve distributed locks in mysql". Next, please follow the editor to study!

one。 Problems to be solved by distributed Lock

It can be guaranteed that in a distributed application cluster, the same method can only be executed by one thread on one machine at a time.

This lock is a reentrant lock (avoid deadlock)

This lock is preferably a blocking lock (consider this one according to business requirements)

This lock had better be a fair lock (consider this one according to business needs)

Highly available lock acquisition and release functions

The performance of acquiring and releasing locks is better

two。 Based on database implementation principle 1. Create a new lock table record CREATE TABLE `methodLock` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `method_ name` varchar (64) NOT NULL DEFAULT''COMMENT' locking method name', `lock `varchar (1024) NOT NULL DEFAULT 'remarks', `update_ time`time'to save data Automatically generate methods in', PRIMARY KEY (`id`), UNIQUE KEY `uidx_method_ name` (`method_name `) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' lock two。 The first implementation

When you want to lock a method, execute the insert method and insert a piece of data. Method_name has a unique constraint, which ensures that multiple submissions are successful only once. This time, you can assume that it has acquired the lock, and execute the delete statement to release the lock after the execution is completed.

Disadvantages:

This lock strongly depends on the availability of the database, the database is a single point, once the database is down, it will cause the business system to be unavailable.

There is no expiration time for this lock, and once the unlock operation fails, it will cause the lock record to stay in the database all the time, and other threads can no longer obtain the lock.

This lock can only be non-blocking, because the insert operation of the data will directly report an error if the insertion fails. The thread that did not acquire the lock will not enter the queue, and the lock acquisition operation will be triggered again in order to acquire the lock again.

The lock is non-reentrant, and the same thread cannot acquire the lock again until it is released. Because the data already exists.

This lock is an unfair lock, and all threads waiting for the lock compete for the lock by luck.

3. The second implementation

Or using the table structure above, the distributed lock can be realized through the exclusive lock of the database.

Add for update after the query statement, and the database adds an exclusive lock to the database table during the query. When an exclusive lock is added to a record, other threads can no longer add an exclusive lock to that record.

We can assume that the thread that acquires the exclusive lock can acquire the distributed lock. After acquiring the lock, it can execute the business logic of the method, and then release the lock through the connection.commit (); operation.

Code:

Public boolean lock () {

Connection.setAutoCommit (false)

While (true) {

Try {

Result = select * from methodLock where method_name=xxx for update

If (result==null) {

Return true

}

} catch (Exception e) {

}

Sleep (1000)

}

Return false;}

Public void unlock () {connection.commit ();} 4. Optimistic lock implementation

Generally, when the data is read out by adding a "version" field to the database table, the version number is read out together, and then when it is updated, the version number is added by 1. During the update process, the version number is compared. If it is consistent and does not change, the operation will be performed successfully; if the version number is inconsistent, the update will fail, which is actually a diff process.

Disadvantages:

(1)。 This mode of operation, so that the original update operation, must be changed into two operations: select version number once; update once. Increased the number of database operations.

(2)。 If multiple resources need to be used to ensure data consistency in a business process in a business scenario, then if all optimistic locks based on database resource tables are used, each resource should have a resource table. This must not be satisfied in the actual use scenario. And these are based on database operations, under the requirements of high concurrency, the cost of database connections must be unbearable.

(3)。 Optimistic locking mechanism is often based on the data storage logic in the system, so it may cause dirty data to be updated to the database.

At this point, the study on "how to implement distributed locks in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.