struts-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shyam A <struts_new...@yahoo.com>
Subject [OT] Database locking and deadlock
Date Mon, 08 Mar 2004 21:31:39 GMT
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: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org


Mime
View raw message