Random Talk about database lock

Objective

There were a lot of problems with the Get_lock() function to get the lock on the line and project in the previous period , there are two main types of problems:

1, one connection can not acquire two locks at the same time, because the next one will automatically release the previous lock, in addition, if the lock or release lock to use the connection is not the same, If the connection of the lock is recycled by the connection pool, it can also cause the first lock to be automatically released, eventually causing your business to not finish, and others to deal with the same business at the same time, resulting in inconsistent business.

2 , in order to solve the first problem, many students put the lock and release lock in a transaction, in the acquisition of locks and release locks in the middle of a lot of time-consuming business logic (even these business logic is not involved in database operations), such as the call of several external systems of the TR service, or batch processing function, causing a connection to take a long time, if the concurrency of a large, not only will lead to the performance of the interface degradation, but also cause the database connection is not enough, directly caused the system large area paralysis, very dangerous.

The following is a detailed description of the get_lock () function in the official MySQL document , and if you have studied this function carefully, I am sure you will not be able to use the Get_lock () scenario easily .

Locking: "Selectget_lock (' {$key} ', {$timeout}) as Get_lock";
Unlock: "Selectrelease_lock (' {$key} ') as Release_lock";

• Get_lock (str,timeout)

try to use string str Given the name to get a lock, timeout to timeout seconds. If the lock is successfully obtained, 1 is returned, and if the operation times out it returns 0 (for example, because another client has blocked the name earlier), or null if an error occurs (such as a lack of memory or thread Mysqladmin kill is disconnected). If you have a lock that you get with Get_lock (), the lock will be lifted when you perform release_lock () or your connection is disconnected (normal or abnormal).

This function can be used to perform an application lock or an impersonation record lock. The name is locked within the server scope. If a name has been blocked by a client, Get_lock () will block any request from another client for blocking the same name. This allows the client to agree on a blockade name to use this name to work with the proposed lock. Be aware, however, that it also allows a client that is not in a group of cooperating clients to block the name, either intentionally or unintentionally, so that any client in cooperation is blocked from blocking the name. One way to reduce this situation is to use database-specific or application-specific block names. For example, use a block name in the form of db_name.str or app_name.str .

Mysql> SELECT get_lock (' Lock1 ', ten);

1

Mysql> SELECT is_free_lock (' Lock2 ');

1

Mysql> SELECT get_lock (' Lock2 ', ten);

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 untied by a second get_lock () call.

Use a different approach to solve and avoid the above problems

the mysql get_lock () function has the following problems in addition to the above problem:

• The system relies heavily on the database type, which is heavily dependent on MySQL, and if the database needs to be migrated to Oracle or other databases later, the entire application layer code needs to be modified, which is not a good design.

So in order to solve these problems we must adopt other solutions, there are two main solutions currently:

1 , with other global cache locks like memcached

2 , database-based optimistic locking with CAs , but no dependent database type

But sometimes, not all systems need tair or memcached cache, if only for the lock to request caching, which causes the system to rely heavily on an external environment, so it is not cost-effective, so the following main focus on the second scenario.

In fact, we can design a very simple table for each library, for example, the table name is called Lock:

Name	Version	Gmt_modified
Lock1	0	2015-04-19 00:00:00

The structure of the table is simple, with only two columns of properties, one column representing the name of the lock, and one column representing the current state of the lock.

If we want to get the lock, we just need to execute an update lock set version=1 where Name= ' Lock1 ' and version=0, if the execution succeeds in obtaining the lock successfully, otherwise it means getting The lock fails; the same we want to release the lock, we only need to execute an update lock set version=0 where Name= ' lock1 ' and version=1, if the execution succeeds in indicating that the release lock succeeds, otherwise the release lock fails.

There is no need to ask for a lock and release lock in the same transaction, do not have to be the same connection, do not appear to get another lock and automatically release the previous lock, you can get the lock and release lock in the middle of doing a lot of business logic operations, does not cause the data connection to take too long, and is not strongly dependent on the database type, do not change the database after the code, and lock does not have too many data, time-consuming basically in the millisecond, performance is also guaranteed.

Of course, this scenario may result in a system release or other abnormal conditions resulting in the middle disconnection of the lock and release lock, resulting in a failure to release the lock, but if we can do a compensation mechanism, such as the number of times the lock is not successful more than a certain amount, we automatically release the lock , or the lock takes longer than a certain amount of time, we automatically release the lock, or monitor the alarm, and then manual processing, and so on; these compensation schemes can also meet our expectations.

Random Talk about database lock