两个线程同时操作数据库的时候,期望一个线程操作数据库时,锁住当前访问行的信息,其它线程不能访问。这里涉及到Mysql的行锁,在网上看了相关代码,写了个demo验证了一下
参考博客:https://blog.csdn.net/BtWangZhi/article/details/84106840
设计如下使用场景
1.线程1执行数据库查询,查询的时候对改行数据加上排他锁。
2.线程2同时更新同一id的数据,需要等到线程1完成事务提交之后才能更新数据库
查询代码
<select id="selectById" resultType="com.self.learning.entity.Information">
SELECT job_name AS name, job_config AS config FROM pddr_pc_job
WHERE id = #{id} FOR UPDATE
</select>
锁住用户信息
private void lockUser(){
dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
defaultTransactionDefinition = new DefaultTransactionDefinition();
defaultTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
status = dataSourceTransactionManager.getTransaction(defaultTransactionDefinition);
log.info("锁住id为1的信息");
testMapper.selectById(2);
}
尝试更新用户信息
private void updateUser(){
PcJob pcJob = new PcJob();
pcJob.setId(2);
pcJob.setExecLog("更新id为2的信息");
int i = testMapper.updateByPrimaryKey(pcJob);
log.info("修改结果" + i);
}
完整代码如下
package com.self.learning;
import com.self.learning.entity.PcJob;
import com.self.learning.mapper.TestMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import javax.sql.DataSource;
import java.util.Date;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class LearningApplicationTests {
DefaultTransactionDefinition defaultTransactionDefinition;
DataSourceTransactionManager dataSourceTransactionManager;
TransactionStatus status;
@Autowired
DataSource dataSource;
@Autowired
TestMapper testMapper;
private void lockUser(){
dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
defaultTransactionDefinition = new DefaultTransactionDefinition();
defaultTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
status = dataSourceTransactionManager.getTransaction(defaultTransactionDefinition);
log.info("锁住id为1的信息");
testMapper.selectById(2);
}
private void updateUser(){
PcJob pcJob = new PcJob();
pcJob.setId(2);
pcJob.setExecLog("更新id为2的信息");
int i = testMapper.updateByPrimaryKey(pcJob);
log.info("修改结果" + i);
}
@Test
public void selectByPrimaryKey() {
updateUser();
log.info("更新用户信息");
}
@Test
public void testLock(){
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(5);
fixedThreadPool.execute(() ->{
try {
System.out.println("获取事务锁" + new Date());
lockUser();
Thread.sleep(10000);
System.out.println("开始提交事务" + new Date());
dataSourceTransactionManager.commit(status);
} catch (InterruptedException e) {
e.printStackTrace();
}
});
fixedThreadPool.execute(() ->{
try {
System.out.println("开始修改用户信息" + new Date());
updateUser();
System.out.println("修改用户信息完成" + new Date());
}catch (Exception e){
e.printStackTrace();
}
});
fixedThreadPool.shutdown();
while (true){
if(fixedThreadPool.isTerminated()){
log.info("run over");
break;
}
}
}
}
期望得到的结果,“修改结果” + i 应该在 “开始提交事务”之后打印。应该在线程1执行10S后才会打印出结果,表示线程2是在线程1释放了锁之后才能够执行数据库的更新操作
执行结果如下
我不知道为啥原博客中自动注入dataSourceTransactionManager这个Bean可以,我注入的时候一直报错找不到bean,所以我就自己new了一个,手动注入了一个datasource。