Re: Inserts disappear after some time under high load

Just to add:
We also make sure that the connection,statements are closed properly
everytime.

Rajiv

> Hello,
> We run a high load site in which teachers create and assign Quizzes that
> students take. On an average we have about 150 to 200 students taking
> tests concurrently not to forget teachers using our reports feature to
> generate reports.
>
> We have explored all other possibilities and are exploring the possibility
> that the JDBC driver might be at fault for the problem we are facing. Any
> help/suggestions are welcome.
>
> The problem is with table inserts not visible after some time. We
> confirmed that the inserts went in earlier by logging the primary key that
> we got from the DB. After some time the record itself disappears. The
> problem occurs irrespective of whether we do things in a transaction or
> not. So we don't set autocommit false or do any rollback either. We also
> don't get any exceptions for the first insert not going through. Moreover,
> we have noticed that this happens ONLY under high load. Our setup is as
> follows.
> Postgresql 7.3.4
> pg73jdbc3 driver
> Redhat 9.0
> Apache/Tomcat 3.3.1
> We don't use any connection pooling.
>
> Problem description:
> There are 2 tables in the DB . One which stores quiz session information
> like ipaddress,date_taken etc (quiz_session) and the other which stores
> student responses (quiz_responses) .
>
> ============      ============
> quiz_session      quiz_responses
> ===========       ============
> session_id(PK)    quiz_responses_id(PK)
> date_taken        session_id(FK)
> etc..             responses ...etc
>
>
> When the student starts the quiz we create a entry in the quiz_session
> table by first selecting the nextval from the quiz_session_session_id
> sequence(session_id being the primary key). We store this session_id in a
> variable(temp_session_id) and do an insert into quiz_session for that
> student. This statement never throws an exception and the value returned
> by the execution of this query is never less than 1. We print out the
> session_id we received to a log file.
>
> We store the session_id we got in the first step in the Tomcat session in
> an object for later use when the students atart answering questions one by
> one.
>
> When the student answers a question we try to insert the same session_id
> to the quiz_responses table as a foreign key along with his responses. The
> constraints are already set up. This proceeds normally under normal load
> but throws a referential integrity exception under high load. We also log
> this insert into the other table to a file and found out that the
> session_id which we tried to insert is the same one that we got in the
> first step. Moreover when we go back and check the quiz_session table for
> the record having this session id, the record itself is not found and that
> can mean that the first insert didn't go through at all. But what baffles
> us is why we didn't get SQL exception at that point.
>
>
> We want to find out whether the semantics of the transaction are affected
> by high load and whether the DB driver can be a possible reason for this
> kind of behaviour..
>
> Any help is highly appreciated.
> Thanks,
> Rajiv
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>