single jdbc connection spanning multiple http requests

[prakash pannala]

hi,
i would like to know that whether a jdbc connection can span multiple http requests. say like i create a connection and start a transaction in the first http request and add statements to my transaction through subsequent http requests and and say in the 5th http request,i commit the transaction and close the connection.
and one more question is whether this connection is unique for each session?
thanks a bunch
pannala

[Peter den Haan]

Yes you can, but it is not normally done, because the transaction can be very long (in fact the user may abandon it halfway, in which case you'll probably have to wait for session timeout). You will be holding on to record locks and other database resources which doesn't bode well for scalability.
The usual fix is either to gather all information in the HttpSession and then commit it in one go, or to engineer the operations in such a way that they can be done in multiple small transactions. If you need to lock records during the sequence of requests, this can be done using optimistic locks, often implemented by adding a versioning or timestamp field to the database table(s). If another transaction changed the version behind your back, you have to back out of your operation.
- Peter
[ January 24, 2003: Message edited by: Peter den Haan ]