Database locks and databases

Database locks and databases
Preface

Some time ago, I encountered a lot of problems online and in the project about using the GET_LOCK () function of mysql to obtain the lock. There are two main problems:

1. Two locks cannot be obtained at the same time in a connection, because the previous lock is automatically released when the last lock is obtained. In addition, if the connection used to obtain or release the lock is different, if the connection pool recovers the lock, the first lock may be automatically released. In the end, your business has not been fully processed, and others will also process the same business at the same time, this eventually leads to service inconsistencies.

2. To solve the first problem, many people put the lock acquisition and lock release in a transaction, A large amount of time-consuming business logic is implemented in the process of obtaining and Releasing locks (even these business logics do not involve database operations at all). For example, the tr service of several external systems is called, or the batch processing function, resulting in a connection taking up too long. If the concurrency is large, it will not only cause the interface performance to decline, but also lead to insufficient database connections, which directly causes the system to crash in a large area, very dangerous.

The following is a detailed description of the GET_LOCK () function in the mysql official documentation. If you have carefully studied this function, I believe that it will not be easy to use GET_LOCK.

Lock: "SELECTGET_LOCK ('{$ key}', {$ timeout}) AS get_lock ";
Unlock: "SELECTRELEASE_LOCK ('{$ key}') AS release_lock ";

• GET_LOCK (Str,Timeout)

Try to use stringStrThe given name gets a lock, timeout isTimeoutSeconds. If the lock is obtained successfully, 1 is returned. If the operation times out, 0 is returned (for example, because the name of another client has been blocked in advance ), if an error occurs, NULL is returned (such as a lack of memory or threads ).Mysqladmin killDisconnected ). Assume that you have a lock obtained by using GET_LOCK (). When you execute RELEASE_LOCK () or your connection is disconnected (normal or abnormal), the lock is released.

This function can be used to execute application locks or simulate record locks. The name is locked within the server range. If a name has been blocked by a client, GET_LOCK () will block any requests from another client requesting to block the same name. This allows the client that has reached an agreement on a blocking name to use this name for cooperation to execute the recommended lock. However, you need to know that it also allows a client block name in different combinations of clients, whether intentionally or unintentionally, to prevent any client in cooperation from blocking this name. One way to reduce this is to use database-specific or application-specific blocking names. For exampleDb_name.strOrApp_name.strBlock name.

Mysql>SELECT GET_LOCK ('lock1', 10 );

-> 1

Mysql>SELECT IS_FREE_LOCK ('lock2 ');

-> 1

Mysql>SELECT GET_LOCK ('lock2', 10 );

-> 1

Mysql>SELECT RELEASE_LOCK ('lock2 ');

-> 1

Mysql>SELECT RELEASE_LOCK ('lock1 ');

-> NULL

Note that the second RELEASE_LOCK () call returns NULL because the lock 'lock1' is unlocked by the second GET_LOCK () call.

Use another method to solve and avoid the above problems

In addition to the above problem, the GET_LOCK () function of mysql also has the following problems:

• The system depends heavily on the database type, that is, mysql. If the database needs to be migrated to oracle or other databases in the future, all the code at the application layer must be modified, this is not a good design.

To solve these problems, we must adopt other solutions. Currently, there are two main solutions:

1. Use other global cache locks such as memcached

2. Optimistic database-based locks using CAS, but do not need to rely on the Database Type

But sometimes, not all systems require tair or memcached caches. If you apply for a cache only for locking, the system is heavily dependent on an external environment, so it is not cost-effective, therefore, the following describes the second solution.

In fact, we can design a very simple table for each database. For example, the table name is lock:

Name	Version	Gmt_modified
Lock1	0	00:00:00

The table structure is very simple. There are only two attribute columns. One column represents the name of the lock, and the other column represents the current status of the lock.

If we want to obtain the lock, we only need to execute an update lock set version = 1 where name = 'lock1' and version = 0. If the lock is successfully obtained, otherwise, the lock fails to be obtained. Similarly, to release the lock, we only need to execute an update lock set version = 0 where name = 'lock1' and version = 1, if the lock is successfully released, the lock fails to be released.

When obtaining and Releasing locks, you do not need to be in the same transaction, or use the same connection, or obtain another lock. At the same time, the previous lock is automatically released, you can perform a large number of business logic operations in the process of obtaining and Releasing locks, without occupying the data connection for a long time or relying heavily on the database type, in the future, you do not need to change the code for database migration. Also, the lock won't have much data, and the time consumption is basically within milliseconds, and the performance is also guaranteed.

Of course, this solution may cause system release or other exceptions to lead to locks being acquired and locks being released in the middle, leading to the inability to release locks, but if we can make a good compensation mechanism, for example, if the number of failed attempts to obtain the lock exceeds a certain number, we will automatically release the lock; or if the lock takes longer than a certain period of time, we will automatically release the lock; or monitoring alarms, then, we can manually process them. These compensation schemes can also meet our expectations.