Re: Inserts disappear after some time under high load

Is your code always using the commit() method on Connection objects instead
of a Statement execution for "commit"? The latter technique is often applied
by people who aren't experienced with JDBC, but it will generate
inconsistent update behavior.

Just checking...

rajiv(at)altec(dot)org wrote:

>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
>
>
>