The boss said I lost, now all dare to use MySQL to implement distributed lock, the more so I want to prove their own.
I used to participate in an inventory system, and because of its business complexity, I made many applications to support it. In this way, a single inventory data may have multiple applications to modify the inventory data. For example, several JAVA applications, such as the scheduled task domain xx.cron and the SystemA and SystemB domains, may modify the same inventory data at the same time. If you don’t coordinate, you get dirty data. For thread coordination across JAVA processes, an external environment such as DB or Redis is available.
Here’s how to use DB to implement distributed locking.
design
The interaction modes of distributed locks designed in this paper are as follows: 1. Generate transaction_ID according to the business field and create lock resources in a thread-safe manner. 2
Dynamically create lock resources
When using the synchronized keyword, you must specify a lock object.
synchronized(obj) {
}
Copy the codeThreads within a process can be synchronized based on OBJ. Obj can be understood here as a lock object. To enter a synchronized block, a thread must first hold a lock on an OBJ object. This lock is a built-in lock in JAVA and is created thread safe. So with DB, how do you ensure that lock creation is thread-safe? If a duplicate KEY is present, an exception will be thrown because the UNIQUE KEY is UNIQUE. In JAVA, is SQLIntegrityConstraintViolationException anomalies.
Create table distributed_lock (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'increment PRIMARY KEY ', Transaction_id varchar(128) NOT NULL DEFAULT COMMENT 'id', Last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL COMMENT 'Last UPDATE date ', Create_time TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL COMMENT 'create_time ', UNIQUE KEY `idx_transaction_id` (`transaction_id`) )Copy the codeTransaction_id is the transaction Id, for example, which can be used
Warehouse + bar code + sales model
To assemble a transaction_id that represents a bar code resource in a warehouse in a sales mode. Different bar codes, of course, have different transaction_id. If two applications have the same transaction_ID to create a lock resource, only one of them can be successfully created.
A distributed_lock record has been successfully inserted, indicating that a lock resource has been successfully created.
DB connection pool list design
In business systems with frequent write operations, repositories are usually divided to reduce the pressure of single database writes and improve the throughput of write operations. If a repository is used, the business data is naturally distributed to each database. Using DB distributed locks on such horizontally shard multiple databases, you can customize a DataSouce list. Expose a getConnection(String transactionId) method to find the Connection by transactionId.
The implementation code is as follows:
package dlock; import com.alibaba.druid.pool.DruidDataSource; import org.springframework.stereotype.Component; import javax.annotation.PostConstruct; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.util.ArrayList; import java.util.List; import java.util.Properties; @Component public class DataSourcePool { private List<DruidDataSource> dlockDataSources = new ArrayList<>(); @PostConstruct private void initDataSourceList() throws IOException { Properties properties = new Properties(); FileInputStream fis = new FileInputStream("db.properties"); properties.load(fis); Integer lockNum = Integer.valueOf(properties.getProperty("DLOCK_NUM")); for (int i = 0; i < lockNum; i++) { String user = properties.getProperty("DLOCK_USER_" + i); String password = properties.getProperty("DLOCK_PASS_" + i); Integer initSize = Integer.valueOf(properties.getProperty("DLOCK_INIT_SIZE_" + i)); Integer maxSize = Integer.valueOf(properties.getProperty("DLOCK_MAX_SIZE_" + i)); String url = properties.getProperty("DLOCK_URL_" + i); DruidDataSource dataSource = createDataSource(user,password,initSize,maxSize,url); dlockDataSources.add(dataSource); } } private DruidDataSource createDataSource(String user, String password, Integer initSize, Integer maxSize, String url) { DruidDataSource dataSource = new DruidDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUsername(user); dataSource.setPassword(password); dataSource.setUrl(url); dataSource.setInitialSize(initSize); dataSource.setMaxActive(maxSize); return dataSource; } public Connection getConnection(String transactionId) throws Exception { if (dlockDataSources.size() <= 0) { return null; } the if (transactionId = = null | | "" equals (transactionId)) {throw new RuntimeException (" transactionId is a must"); } int hascode = transactionId.hashCode(); if (hascode < 0) { hascode = - hascode; } return dlockDataSources.get(hascode % dlockDataSources.size()).getConnection(); }}Copy the codeStart by writing an initDataSourceList method and initializing a DataSource list using Spring’s PostConstruct annotation. The relevant DB configuration is read from db.properties.
DLOCK_NUM=2
DLOCK_USER_0="user1"
DLOCK_PASS_0="pass1"
DLOCK_INIT_SIZE_0=2
DLOCK_MAX_SIZE_0=10
DLOCK_URL_0="jdbc:mysql://localhost:3306/test1"
DLOCK_USER_1="user1"
DLOCK_PASS_1="pass1"
DLOCK_INIT_SIZE_1=2
DLOCK_MAX_SIZE_1=10
DLOCK_URL_1="jdbc:mysql://localhost:3306/test2"
Copy the codeDataSource uses ali’s DruidDataSource.
The next most important implementation is the getConnection(String transactionId) method. Get the HashCode of the transactionId and mod the length of the DataSource.
Once the connection pool list is designed, you can insert data into the distributed_LOCK table.
package dlock; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import java.sql.*; @Component public class DistributedLock { @Autowired private DataSourcePool dataSourcePool; /** * createLock resource according to transactionId */ public String createLock(String transactionId) throws Exception{if (transactionId == Null) {throw new RuntimeException("transactionId is required "); } Connection connection = null; Statement statement = null; try { connection = dataSourcePool.getConnection(transactionId); connection.setAutoCommit(false); statement = connection.createStatement(); statement.executeUpdate("INSERT INTO distributed_lock(transaction_id) VALUES ('" + transactionId + "')"); connection.commit(); return transactionId; } the catch (SQLIntegrityConstraintViolationException icv) {/ / that has been generated. if (connection ! = null) { connection.rollback(); } return transactionId; } catch (Exception e) { if (connection ! = null) { connection.rollback(); } throw e; } finally { if (statement ! = null) { statement.close(); } if (connection ! = null) { connection.close(); }}}}Copy the codeThe thread is locked by transactionId
Next use DB’s Select for Update feature to lock the thread. When multiple threads concurrently perform a select for update operation with the same transactionId, only one thread succeeds, and all the other threads block until the thread that succeeds commits. Only one of the threads in which the block resides can start work. We create a Lock method in the DistributedLock class above.
public boolean lock(String transactionId) throws Exception { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = dataSourcePool.getConnection(transactionId); preparedStatement = connection.prepareStatement("SELECT * FROM distributed_lock WHERE transaction_id = ? FOR UPDATE "); preparedStatement.setString(1,transactionId); resultSet = preparedStatement.executeQuery(); if (! resultSet.next()) { connection.rollback(); return false; } return true; } catch (Exception e) { if (connection ! = null) { connection.rollback(); } throw e; } finally { if (preparedStatement ! = null) { preparedStatement.close(); } if (resultSet ! = null) { resultSet.close(); } if (connection ! = null) { connection.close(); }}}Copy the codeUnlocking operations
After the thread completes the task, it must manually unlock the locked thread to continue working. In our implementation, we simply get the Connection of the thread that succeeded in the SELECT for update and commit.
So how do you get it? We can use ThreadLocal. First define it in the DistributedLock class
private ThreadLocal<Connection> threadLocalConn = new ThreadLocal<>();
Copy the codeEach time the lock method is called, the Connection is placed inside ThreadLocal. Let’s modify the lock method.
public boolean lock(String transactionId) throws Exception { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = dataSourcePool.getConnection(transactionId); threadLocalConn.set(connection); preparedStatement = connection.prepareStatement("SELECT * FROM distributed_lock WHERE transaction_id = ? FOR UPDATE "); preparedStatement.setString(1,transactionId); resultSet = preparedStatement.executeQuery(); if (! resultSet.next()) { connection.rollback(); threadLocalConn.remove(); return false; } return true; } catch (Exception e) { if (connection ! = null) { connection.rollback(); threadLocalConn.remove(); } throw e; } finally { if (preparedStatement ! = null) { preparedStatement.close(); } if (resultSet ! = null) { resultSet.close(); } if (connection ! = null) { connection.close(); }}}Copy the codeThis way, when you get the Connection, set it to ThreadLocal, and remove it from ThreadLocal if the lock method fails.
Now that we have these steps, we are ready to unlock. We add an unlock method to the DistributedLock.
public void unlock() throws Exception { Connection connection = null; try { connection = threadLocalConn.get(); if (! connection.isClosed()) { connection.commit(); connection.close(); threadLocalConn.remove(); } } catch (Exception e) { if (connection ! = null) { connection.rollback(); connection.close(); } threadLocalConn.remove(); throw e; }}Copy the codeAfter all, the disadvantage is to use DB to achieve distributed lock, or cause a certain amount of pressure on DB. At that time, an important reason to consider using DB to do distribution is that our application is a back-end application, and usually the flow is not large, but the key is to ensure the accuracy of inventory data. For operations like front-end inventory systems, such as adding shopping carts to occupy inventory, it is best not to use DB for distributed locking. What if you want to lock multiple copies of data? For example, an inventory operation that modifies both the physical inventory and the virtual inventory wants to lock both the physical inventory and the virtual inventory. Write a multiLock method that provides multiple transactionId inputs for the for loop. There will be time to catch up on this later.