(一)
@Transactional
public void insertForDirtyReadAndIllusion () {
jdbcTemplate.execute("insert into tao values (1,'d')");
try {
Thread.sleep(00000);
} catch (InterruptedException e) {
e.printStackTrace();
}
// int a = 1/0;
}
@Transactional(isolation = Isolation.READ_COMMITTED)
public Object listForIllusionRead() {
List<Map<String,Object>> map = jdbcTemplate.queryForList("select * from tao");
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
List<Map<String,Object>> map2 = jdbcTemplate.queryForList("select * from tao");
Map<String,Object> res = new HashMap<String, Object>();
res.put("before", map);
res.put("after", map2);
return res;
}
注意:
(1)查询的隔离级别定为read_committed,理论上无法防幻读
(2)insert函数调整一下,不让其出异常,且不sleep,也可以去除transaction注解,仅仅确保一次有效插入会话即可
会话1执行listForIllusionRead,第一次读
会话1sleep阻塞
会话2执行insert有效会话操作
会话1第二次读,
会话1返回
{ "before":[],
"after":[
{ "col1":1,
"col2":"d"
} ]}
结论:与理论相符,read_committed级别无法防止幻读
(二)
@Transactional
public void insertForDirtyReadAndIllusion () {
jdbcTemplate.execute("insert into tao values (1,'d')");
try {
Thread.sleep(00000);
} catch (InterruptedException e) {
e.printStackTrace();
}
// int a = 1/0;
}
@Transactional(isolation = Isolation.REPEATABLE_READ)
public Object listForIllusionRead() {
List<Map<String,Object>> map = jdbcTemplate.queryForList("select * from tao");
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
List<Map<String,Object>> map2 = jdbcTemplate.queryForList("select * from tao");
Map<String,Object> res = new HashMap<String, Object>();
res.put("before", map);
res.put("after", map2);
return res;
}
注意:
(1)list函数spring隔离级别定义为repeatable_read
会话1执行listForIllusionRead,第一次读
会话1sleep阻塞
会话2执行insert有效会话操作
会话1第二次读,
会话1返回
{ "before":[ { "col1":1, "col2":"d" } ], "after":[ { "col1":1, "col2":"d" } ]}结论:与理论相符,repeatable_read级别可防止幻读
修改方 查询方
thread A B
transcation off on(为了定义隔离级别,也为了定义为一次会话)
isolation not care read-commited
有幻读
修改方 查询方
thread A B
transcation off on(为了定义隔离级别,也为了定义为一次会话)
isolation not care repeatable_read
无幻读