web应用程序为提高性能查询时更改database isolation level

目前所做的项目中UAT阶段遇到一个BUG, BUG 描述为 Login takes up to 24 seconds on OPA when also importing allocation plan lines
fix solution is: Set trsanction isolation level to read uncommitted for the get allocation plan lines queries (including row count)


具体代码:

1. // Set the current session to have isolation read uncommitted.   
   
2. // Returns the previous isolation level before the read uncommitted was set.   
   
3. protected int setTransactionReadUncommited() {   
   
4. int currentTransactionLevel = -1;   
   
5. try {   
   
6. currentTransactionLevel = getSession().connection().getTransactionIsolation();   
   
7. getSession().connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);   
   
8. }   
   
9. catch (HibernateException e)   
   
10. { e.printStackTrace(); }   
    
11. catch (SQLException e)   
    
12. { e.printStackTrace();   
    
13. }   
    
14. return currentTransactionLevel;   
    
15. }   
    
16. 
    
17. // Set the current session to have isolation read uncommitted.
    
18. // Returns the previous isolation level before the read uncommitted was set.
    
19. protected int setTransactionReadUncommited() {
    
20. int currentTransactionLevel = -1;
    
21. try {
    
22. currentTransactionLevel = getSession().connection().getTransactionIsolation();
    
23. getSession().connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    
24. }
    
25. catch (HibernateException e)
    
26. { e.printStackTrace(); }
    
27. catch (SQLException e)
    
28. { e.printStackTrace();
    
29. }
    
30. return currentTransactionLevel;
    
31. }   

复制代码

调用部分:

1. public Object findByScheduleRequestID(Long requestID, Integer startIndex, Integer pageSize)   
   
2. {   
   
3. Criteria criteria = getSession().createCriteria(Delivery.class);   
   
4. Criteria srCriteria = criteria.createCriteria("scheduleRequest", Criteria.FULL_JOIN);   
   
5. srCriteria.add(Restrictions.eq("id", requestID));   
   
6. criteria.addOrder(Order.asc("id"));   
   
7. criteria.setFirstResult(startIndex.intValue());   
   
8. criteria.setMaxResults(pageSize.intValue());   
   
9. int previousTransactionLevel = setTransactionReadUncommited(); List result = criteria.list();   
   
10. setTransactionBack(previousTransactionLevel);   
    
11. return result;   
    
12. }  
    
13. 
    
14. public Object findByScheduleRequestID(Long requestID, Integer startIndex, Integer pageSize)
    
15. {
    
16. Criteria criteria = getSession().createCriteria(Delivery.class);
    
17. Criteria srCriteria = criteria.createCriteria("scheduleRequest", Criteria.FULL_JOIN);
    
18. srCriteria.add(Restrictions.eq("id", requestID));
    
19. criteria.addOrder(Order.asc("id"));
    
20. criteria.setFirstResult(startIndex.intValue());
    
21. criteria.setMaxResults(pageSize.intValue());
    
22. int previousTransactionLevel = setTransactionReadUncommited(); List result = criteria.list();
    
23. setTransactionBack(previousTransactionLevel);
    
24. return result;
    
25. }

复制代码

事务管理为编程型事务（JTA）方式用struts2的拦截器去拦截方式实现:
代码:

1. public class TransactionClosingInterceptor extends AbstractInterceptor   
   
2. {   
   
3. Logger log = Logger.getLogger(this.getClass());   
   
4. @Override public String intercept(ActionInvocation invocation) throws Exception {   
   
5. UserTransaction tx = null;   
   
6. try { String result ="";   
   
7. try{ result = invocation.invoke();   
   
8. }   
   
9. catch (Exception e)   
   
10. { e.printStackTrace(); throw e; }   
    
11. tx = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");   
    
12. tx.commit(); return result; }   
    
13. catch(Exception e) { e.printStackTrace(); if (tx !=null)   
    
14. { tx.rollback(); } invocation.getInvocationContext().getSession().clear();   
    
15. invocation.getInvocationContext().getSession().clear(); return "error_ns"; */ } } }   
    
16.   
    
17.   
    
18. public class TransactionOpeningInterceptor extends AbstractInterceptor {   
    
19. Logger log = Logger.getLogger(this.getClass());   
    
20. @Override public String intercept(ActionInvocation invocation) throws Exception { UserTransaction tx = null;   
    
21. try { tx = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");   
    
22. tx.setTransactionTimeout(300); tx.begin();   
    
23. return invocation.invoke(); } catch(Exception e)   
    
24. { if (tx !=null){ tx.rollback(); }   
    
25. return Action.ERROR; } } }  
    
26. 
    
27. public class TransactionClosingInterceptor extends AbstractInterceptor
    
28. {
    
29. Logger log = Logger.getLogger(this.getClass());
    
30. @Override public String intercept(ActionInvocation invocation) throws Exception {
    
31. UserTransaction tx = null;
    
32. try { String result ="";
    
33. try{ result = invocation.invoke();
    
34. }
    
35. catch (Exception e)
    
36. { e.printStackTrace(); throw e; }
    
37. tx = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");
    
38. tx.commit(); return result; }
    
39. catch(Exception e) { e.printStackTrace(); if (tx !=null)
    
40. { tx.rollback(); } invocation.getInvocationContext().getSession().clear();
    
41. invocation.getInvocationContext().getSession().clear(); return "error_ns"; */ } } }
    
42. 
    
43. 
    
44. public class TransactionOpeningInterceptor extends AbstractInterceptor {
    
45. Logger log = Logger.getLogger(this.getClass());
    
46. @Override public String intercept(ActionInvocation invocation) throws Exception { UserTransaction tx = null;
    
47. try { tx = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");
    
48. tx.setTransactionTimeout(300); tx.begin();
    
49. return invocation.invoke(); } catch(Exception e)
    
50. { if (tx !=null){ tx.rollback(); }
    
51. return Action.ERROR; } } }

复制代码

系统当中有六七个DAO用这种方式，担是如果用这种事务隔离级别（允许读取另外事务没有提交的数据）可能出现脏读的现像虽然查询完后有改回以前的事务隔离级别。这种处理方式真的没有不会带来业务
上的问题吗？欢迎各们同仁讨论！另外等我老大回来了我会和他讨论为什么采取这种方式，看和大家分析的是不是一致，（现在人家是vacation month）haha.

推荐推荐推荐推荐

我的我的 忘记了 呵呵

哈哈！！！！