JDBC事务难题

大牛们 ，，，有一段加了JDBC事务的方法

public boolean updateStuAcc(int stuID, List accL, int schID) {


        boolean b = true;


        Connection conn = null;


        PreparedStatement ps = null;


        ResultSet rs = null;


        String sql1 = "select * from stuAccounts where stuID!=? and Account=?";


        String sql2 = "update stuAccounts set Account=? where pactID=?";


        String sql3 = "update [" + schID


                + "_StuRefundInfo] set account=? where pactID=? ";


        try {


            conn = DBCon.getCon();


            conn.setAutoCommit(false);


            for (int i = 0; i < accL.size(); i++) {


                StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);


                ps = conn.prepareStatement(sql1);


                ps.setInt(1, stuID);


                ps.setString(2, suaDto.getAccount());


                rs = ps.executeQuery();


                if (rs.next()) {


                    b = false;


                    break;


                } else {


                    ps = conn.prepareStatement(sql2);


                    ps.setString(1, suaDto.getAccount());


                    ps.setString(2, suaDto.getPactID());


                    ps.executeUpdate();


                    ps = conn.prepareStatement(sql3);


                    ps.setString(1, suaDto.getAccount());


                    ps.setString(2, suaDto.getPactID());


                    ps.execute();


                }


            }


            conn.commit();


        } catch (SQLException e) {


            b = false;


            try {


                conn.rollback();


            } catch (SQLException e1) {


                e1.printStackTrace();


            }


            e.printStackTrace();


        } finally {


            try {


                if (rs != null) {


                    rs.close();


                }


                if (ps != null) {


                    ps.close();


                }


                if (conn != null) {


                    conn.close();


                }


            } catch (SQLException e) {


                e.printStackTrace();


            }


        }


        return b;


    }  

public boolean updateStuAcc(int stuID, List accL, int schID) {

        boolean b = true;

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        String sql1 = "select * from stuAccounts where stuID!=? and Account=?";

        String sql2 = "update stuAccounts set Account=? where pactID=?";

        String sql3 = "update [" + schID

                + "_StuRefundInfo] set account=? where pactID=? ";

        try {

            conn = DBCon.getCon();

            conn.setAutoCommit(false);

            for (int i = 0; i < accL.size(); i++) {

                StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);

                ps = conn.prepareStatement(sql1);

                ps.setInt(1, stuID);

                ps.setString(2, suaDto.getAccount());

                rs = ps.executeQuery();

                if (rs.next()) {

                    b = false;

                    break;

                } else {

                    ps = conn.prepareStatement(sql2);

                    ps.setString(1, suaDto.getAccount());

                    ps.setString(2, suaDto.getPactID());

                    ps.executeUpdate();

                    ps = conn.prepareStatement(sql3);

                    ps.setString(1, suaDto.getAccount());

                    ps.setString(2, suaDto.getPactID());

                    ps.execute();

                }

            }

            conn.commit();

        } catch (SQLException e) {

            b = false;

            try {

                conn.rollback();

            } catch (SQLException e1) {

                e1.printStackTrace();

            }

            e.printStackTrace();

        } finally {

            try {

                if (rs != null) {

                    rs.close();

                }

                if (ps != null) {

                    ps.close();

                }

                if (conn != null) {

                    conn.close();

                }

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        return b;

    }

该方法有时候会抛出这样的异常







at java.lang.Thread.run(Thread.java:595)

com.microsoft.sqlserver.jdbc.SQLServerException: 事务(进程 ID 54)与另一个进程被

死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError

(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown

Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep

aredStatement(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecC

md.doExecute(Unknown Source)

        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unkno

wn Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknow

n Source)

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unkn

own Source)

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(

Unknown Source)

        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(

DelegatingPreparedStatement.java:93)

        at dao.StuAllInfoUDao.updateStuAcc(StuAllInfoUDao.java:33)

        at workerServlet.SchUpdateStuInfoServlet.doPost(SchUpdateStuInfoServlet.

java:59)

你可以尝试着这样做一下：

...........
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;

...............

try {

conn = DBCon.getCon();

conn.setAutoCommit(false);

for (int i = 0; i < accL.size(); i++) {

StuUpdateAccDto suaDto = (StuUpdateAccDto) accL.get(i);

ps1 = conn.prepareStatement(sql1);

ps1.setInt(1, stuID);

ps1.setString(2, suaDto.getAccount());

rs = ps1.executeQuery();

if (rs.next()) {

b = false;

break;

} else {

ps2 = conn.prepareStatement(sql2);

ps2.setString(1, suaDto.getAccount());

ps2.setString(2, suaDto.getPactID());

ps2.executeUpdate();

ps3 = conn.prepareStatement(sql3);

ps3.setString(1, suaDto.getAccount());

ps3.setString(2, suaDto.getPactID());

ps3.execute();

}

}

conn.commit();

} catch (SQLException e) {

b = false;

try {

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

} finally {

try {

if (rs != null) {

rs.close();

}

if (ps1 != null) {

ps1.close();

}
if (ps2 != null) {

ps2.close();

}

            if (conn != null) {      
                conn.close();      
            }      
        } catch (SQLException e) {      
            e.printStackTrace();      
        }      
    }   

at dao.StuAllInfoUDao.updateStuAcc(StuAllInfoUDao.java:33)
看看他在执行什么