mysql 锁不起作用_java,lock_ReentrantLock锁不起作用，java,lock,mysql - phpStudy

ReentrantLock锁不起作用

插入数据之前需要先检查数据库中没有了才插入，没加锁之前发现如果两次添加请求很接近会出现重复插入情况，所以添加了一个ReentrantLock锁，但是实际使用发现还是会出现重复插入。

锁添加在Spring 的 Controller 里：

@RestController

@RequestMapping("/user")

public class UserAction {

Lock lock = new ReentrantLock();

@RequestMapping(value = "add", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")

@ResponseBody

public ResponseEntity addUser(User user) {

lock.lock();

try {

//检查数据库是否已有用户没有的话才加入

//代码略

} finally {

lock.unlock();

}

问题补充:

根据回答又本地做了实验:

Lock lock = new ReentrantLock();

public Integer insertLock(String code) {

lock.lock();

try {

System.out.println("get lock: " + lock.hashCode());

//检查数据库是否已经有此code数据

if (lockCheck(code)) return 0;

QSCategory qsCategory = new QSCategory();

qsCategory.setCode(code);

qsCategory.setAdvice("1");

qsCategory.setCreationDate(DateUtil.nowDatetime());

qsCategory.setParentCode("1");

qsCategory.setCreationUser("1");

qsCategory.setStatus(0);

qsCategory.setTitle("1");

qsCategoryMapper.insert(qsCategory);

return qsCategory.getId();

} finally {

System.out.println("release lock: " + lock.hashCode());

lock.unlock();

}

输出日志：

get lock: 1031529608

16:18:00.585 [btpool0-4] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Preparing: select Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser from TB_QS_Category WHERE ( Code = ? )

16:18:00.603 [btpool0-4] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Parameters: cyh(String)

16:18:00.624 [btpool0-4] DEBUG c.b.j.c.d.m.Q.selectByExample - <== Total: 0

16:18:00.630 [btpool0-4] DEBUG c.b.j.c.d.m.QSCategoryMapper.insert - ==> Preparing: insert into TB_QS_Category (Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser, Advice) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

16:18:00.636 [btpool0-4] DEBUG c.b.j.c.d.m.QSCategoryMapper.insert - ==> Parameters: null, 1(String), cyh(String), 1(String), 0(Integer), 2015-09-02 16:18:00.0(Timestamp), 1(String), null, null, java.io.StringReader@1caa2881(StringReader)

16:18:00.637 [btpool0-4] DEBUG c.b.j.c.d.m.QSCategoryMapper.insert - <== Updates: 1

16:18:00.637 [btpool0-4] DEBUG c.b.j.c.d.m.Q.insert!selectKey - ==> Preparing: SELECT LAST_INSERT_ID();

16:18:00.637 [btpool0-4] DEBUG c.b.j.c.d.m.Q.insert!selectKey - ==> Parameters:

16:18:00.640 [btpool0-4] DEBUG c.b.j.c.d.m.Q.insert!selectKey - <== Total: 1

release lock: 1031529608

get lock: 1031529608

16:18:00.644 [btpool0-3] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Preparing: select Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser from TB_QS_Category WHERE ( Code = ? )

16:18:00.645 [btpool0-3] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Parameters: cyh(String)

16:18:00.647 [btpool0-3] DEBUG c.b.j.c.d.m.Q.selectByExample - <== Total: 0

16:18:00.647 [btpool0-3] DEBUG c.b.j.c.d.m.QSCategoryMapper.insert - ==> Preparing: insert into TB_QS_Category (Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser, Advice) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

16:18:00.648 [btpool0-3] DEBUG c.b.j.c.d.m.QSCategoryMapper.insert - ==> Parameters: null, 1(String), cyh(String), 1(String), 0(Integer), 2015-09-02 16:18:00.0(Timestamp), 1(String), null, null, java.io.StringReader@36386935(StringReader)

16:18:00.855 [btpool0-3] INFO o.s.b.f.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

16:18:00.895 [btpool0-3] INFO o.s.j.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

release lock: 1031529608

get lock: 1031529608

16:18:00.898 [btpool0-5] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Preparing: select Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser from TB_QS_Category WHERE ( Code = ? )

16:18:00.903 [btpool0-5] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Parameters: cyh(String)

16:18:00.906 [btpool0-5] DEBUG c.b.j.c.d.m.Q.selectByExample - <== Total: 1

release lock: 1031529608

get lock: 1031529608

16:18:00.907 [btpool0-0] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Preparing: select Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser from TB_QS_Category WHERE ( Code = ? )

16:18:00.909 [btpool0-0] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Parameters: cyh(String)

16:18:00.911 [btpool0-0] DEBUG c.b.j.c.d.m.Q.selectByExample - <== Total: 1

release lock: 1031529608

get lock: 1031529608

16:18:00.913 [btpool0-2] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Preparing: select Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser from TB_QS_Category WHERE ( Code = ? )

16:18:00.913 [btpool0-2] DEBUG c.b.j.c.d.m.Q.selectByExample - ==> Parameters: cyh(String)

16:18:00.916 [btpool0-2] DEBUG c.b.j.c.d.m.Q.selectByExample - <== Total: 1

release lock: 1031529608

16:18:00.991 [btpool0-3] INFO c.b.j.c.s.e.RestExceptionHandler -

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'cyh' for key 'Code'

### The error may involve com.bocean.jkyzx.common.dao.mapper.QSCategoryMapper.insert-Inline

### The error occurred while setting parameters

### SQL: insert into TB_QS_Category (Id, ParentCode, Code, Title, Status, CreationDate, CreationUser, ModificationDate, ModificaitonUser, Advice) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'cyh' for key 'Code'

; SQL []; Duplicate entry 'cyh' for key 'Code'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'cyh' for key 'Code'

org.springframework.dao.DuplicateKeyException:

同时5个http请求会产生5个线程，发现ReentrantLock其实是有效的，每个线程在加锁区域是同步的，但是第一个线程释放锁时候，数据还是没有完全插入进去，之后的线程查询不到就重复插入，出现这个的原因是什么？如何解决？