Re: Session based transaction!! - Mailing list pgsql-general

S Arvind <arvindwill@gmail.com> wrote:
>
> Web application have single DB only..

I'm unsure what you mean by that and how it relates to my answer.

> On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran <wmoran@potentialtech.com>wrote:
>
> > S Arvind <arvindwill@gmail.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
> >


--
Bill Moran
http://www.potentialtech.com