-------上锁
set autocommit=0; #关闭自动提交
SELECT * from t_b_ru_task t where t.id='7373dc02597e4d15bfc9e823e238bf0d' FOR UPDATE;# 给id为1的数据行添加行锁
SELECT * from t_b_ru_task t where t.id='10c23242cc804ccbadbde0ca8bc2e5d7' FOR UPDATE;# 给id为1的数据行添加行锁
set autocommit=0; #关闭自动提交
SELECT * from t_b_ru_task t where t.id='10c23242cc804ccbadbde0ca8bc2e5d7' FOR UPDATE;# 给id为1的数据行添加行锁
SELECT * from t_b_ru_task t where t.id='7373dc02597e4d15bfc9e823e238bf0d' FOR UPDATE;# 给id为1的数据行添加行锁
------上锁结束
------查看锁
-- 查看进程
SHOW PROCESSLIST ;
-- 查看是否锁表
SHOW OPEN TABLES WHERE In_use > 0;
-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-----查看锁结束
------sql解锁
SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX where trx_state='LOCK WAIT';
kill trx_mysql_thread_id
------sql解锁结束
-------代码解锁
controller
}catch (Exception e) {
if (e.getMessage().indexOf("transaction")>-1) {
flowBusinessService.idLockDelete();
}
logger.error(e.getMessage()+":zhangyl:", "zhangyl:"+e);
e.printStackTrace();
return R.fail("操作失败!");
}
service
String idLockDelete();
serviceImpl
@Override
public String idLockDelete() {
//CommonUserDto userDto = ResourceUtil.getSessionUserName();
//查询待评审列表(已生效)
//QueryWrapper<String> query = new QueryWrapper<>();
//query.exists("select concat('kill ',id,';') from information_schema.processlist t where 1=1 and t.db = 'biddingcloud_workclowhn' and (t.State='statistics' or t.State='Waiting for table metadata lock')");
List<String> list = hiRuTaskMapper.idLockDelete();
list.stream().forEach(id ->{
hiRuTaskMapper.killId(id);
System.out.println(id);
});
return "成功";
}
mapper
@Select("SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX where trx_state='LOCK WAIT'")
List<String> idLockDelete();
@Select("kill #{id}")
void killId(@Param("id") String id);
-------代码解锁结束