e.g Do a "select for update nowait" before you issue the update to grab the lock in advance. That way you'll get an oracle error (ora -54) if another user has the row and you can handle it appropriately
Duncan
Shyam A wrote:
Hi,
I know the subject of my mail is off-topic but I hope somebody will be able to help me out with my problem.
I experienced a wierd problem today when a deadlock
occured in my database - Oracle 9i, and it locked up
all connections in the connection pool on my server -
OC4J (Oracle 9iAS). I have a connection pool, which is
configured on my server- OC4J. It is intialized on
server startup, using a Servlet.
I would like to verify if the code I use in my DAO may cause any deadlock condition. In my DAO, I use "Update" queries as part of a transaction. i.e, I turn off auto-commit, do my "Update" queries, and commit or rollback depending on the outcome of the queries. In one of the queries, I may need to update multiple rows of a table, and hence do the update operation in a loop (over a collection).
Given below is the relevant portion of the code:
<snip> //method that updates votes for candidates in an election public int updateVotes(BallotList ballotList) { int result=0; //result of insert operation String query="";
Ballot ballot=null; CandidateList candList=null; Candidate candidate=null; int currVote=0;
int electionNo = ballotList.getElectionNo();
try
{
for(int i=0;i<ballotList.getSize();i++)
{
ballot = ballotList.getBallot(i);
candList = ballot.getCandidateList();
for(int j=0;j<candList.getSize();j++) { candidate = candList.getCandidate(j); //update votes for candidate if(candidate.getVote() > 0) { //get current vote count and update currVote = getCurrentVote(electionNo,candidate.getPostNo(),candidate.getPidm());
currVote++;
query = "Update DZRCAND set DZRCAND_NUM_VOTE =
"+currVote+" " + "where DZRCAND_ELCTN_NO = "+electionNo+" " +
"and DZRCAND_POST_NO =
"+candidate.getPostNo()+" " +
"and DZRCAND_CAND_PIDM =
"+candidate.getPidm()+"";
result += dbConnection.doUpdate(query);
}
} //end of inner for loop
} //end of outer for loop }
catch(Exception e)
{
System.out.println("Exception in DZRCAND
update for votes: " + e.getMessage());
System.out.println("query:" + query);
}
return result;
} </snip>
Is there a possibilty of a deadlock occuring in the above code when there are mutliple users concurrently accessing the system.i.e, multiple users update multiple rows of the table at the same time
Could somebody throw some light on this?
My understanding is that deadlocks are automatically handled by the DBMS. Anyway, is there a way to prevent the possiblity of deadlocks in my program ?
One more thing - in the code above and in other "Select" queries, I open and close the Connection from the connection pool. However, I do not close the Statement or ResultSet objects. Will this lead to problems ? My understanding is that closing the Connection object will free up all the associated resources.
Any help/pointers/suggestions will be greatly appreciated.
Thanks, Shyam
__________________________________ Do you Yahoo!? Yahoo! Search - Find what you’re looking for faster http://search.yahoo.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
