在代码中看到同事写的SQL代码中有FOR UPDATE,百度了一下,说是MYSQL行锁,自己写个demo验证一下。
参考博客:https://blog.csdn.net/u011957758/article/details/75212222
场景:锁住用户的信息,在这期间不允许其他线程修改该用户信息,
<select id="lockUserInfoById" parameterType="java.lang.Integer" resultType="com.test.model.User" >
SELECT * FROM user WHERE id=#{id} FOR UPDATE
</select>
private void lockUser(){
defaultTransactionDefinition = new DefaultTransactionDefinition();
defaultTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 事物隔离级别,开启新事务,这样会比较安全些。
status = dataSourceTransactionManager.getTransaction(defaultTransactionDefinition); // 获得事务状态
//锁住用户记录
logger.info("开始锁住id为9的用户信息");
userDao.lockUserInfoById(9);
}
尝试修改用户信息的方法:
private void editUser() {
//尝试修改id为9的用户信息
User user = new User();
user.setId(9);
user.setName("2");
logger.info("尝试修改用户id为9的数据");
int i = userDao.updateByPrimaryKeySelective(user);
logger.info("修改结果" + i);
}
完整的调用方法:
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserDaoTest {
private static final Logger logger = LoggerFactory.getLogger(UserDaoTest.class);
@Autowired
private UserDao userDao;
@Autowired
private DataSourceTransactionManager dataSourceTransactionManager;
private TransactionStatus status;
private DefaultTransactionDefinition defaultTransactionDefinition;
@Test
public void testSelectByPrimaryKey() {
System.out.println(123);
User user = userDao.selectByPrimaryKey(9);
logger.info(user.toString());
//System.out.println(user.getName());
}
@Test
public void testLock(){
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(5);
fixedThreadPool.execute(new Runnable() {
@Override
public void run() {
try {
lockUser();
Thread.sleep(5000);
logger.info("开始提交事务");
dataSourceTransactionManager.commit(status);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
});
fixedThreadPool.execute(new Runnable() {
@Override
public void run() {
try {
Thread.sleep(500);
logger.info("开始修改用户信息");
editUser();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
});
fixedThreadPool.shutdown();
while(true) {
if(fixedThreadPool.isTerminated()) {
System.out.println("run over");
break;
}
}
}
先锁住id为9的用户信息,当前线程睡眠,其他线程尝试修改id为9的用户信息,从控制台可以发现,修改用户的线程会阻塞,知道锁住用户的线程commit,才能修改id为9的用户的信息。