Re: Session based transaction!!

Web application have single DB only..

On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran <wmoran(at)potentialtech(dot)com>wrote:

> S Arvind <arvindwill(at)gmail(dot)com> wrote:
> >
> > Hi Everyone,
> >
> > In a web application running in Tomcat and Postgres 8.3 as DB, i need to
> > know whether my given task is feasible or not.
> > All the db operation which is done by that web application
> must
> > be rollback at the end(session dies) and the DB data must be same as the
> > starting stage(of session). Its like virtually placing the data for that
> > session alone and rollbacking the db to the template which is originally
> > been. So whenever users get in the webapplication, the initial data must
> be
> > the template data only and he can perform any operation for which data is
> > visible for that user alone and when the users leaves(session destroyed)
> all
> > the data changed in that time must be rollbacked.
> >
> > I thought this by, When the session created in the application a
> transaction
> > must be started and all the activites must be done on the DB, but the
> > transaction will not be commited or rollback across request but it must
> be
> > done across the session. By not destroying the connection and using it
> for
> > all the work done by that session. when session destroy we can rollback
> the
> > entire transaction
> >
> > Is there any other way to achieve the requirements in postgres.
> > Thanks in advance..
>
> Would be easy except for one factor that I don't know about in Tomcat.
>
> In most web applications, the database connection is not maintained between
> page loads. Each new page view may (and usually does) get a different DB
> connection than the previous one. If Tomcat maintains a single DB
> connection for a session across all page view, then you should be able
> to implement this. However, if Tomcat runs like most of the other web
> systems I've seen, you'll have no way to ensure that a particular page
> view will have the same DB connection as a previous page view. It will
> require some sort of middleware that keeps the DB connections open and
> associates HTTP sessions with DB connections.
>
> Past that, however, I expect it will be a maintenance nightmare. Each
> rolled back DB session is going to generate a lot of dead rows that
> vacuum will have to reclaim. Whether or not this is feasible overall
> depends on a lot of questions that I don't know the answers to. Partly,
> it's going to depend on the amount of change and amount of concurrency
> that occurs.
>
> Personally, I would recommend coming up with a different approach, but
> I might be wrong.
>
> --
> Bill Moran
> http://www.potentialtech.com
>