This is the first large-scale asp project that I'm working on, and I have 

a question about how to approach a database transaction that could 

potentially span many pages. I know that it is recommended to save the 

data (possibly in Session variables) as you go along, and limit the actual 

transaction to one page, at the end. This makes sense to me, but I've also 

read a great deal about how you should not save a ton of data (especially 

objects) in Session variables because of performance issues. Potentially, 

we could be talking about a transaction that involves saving multiple 

files (possibly very large ones, such as images), as well as other form 

data. Can anyone recommend an approach to this? 



Thanks!

Rachel

Hi Rachel!



There are three approaches I've used to deal with this issue.



1) Add a flag field to the tables involved indicating that they are

uncommitted, and write an sp to remove all uncommitted records for a

prticular session ID. I usually create a field that is null or empty except

when uncommitted, then it contains the sesion ID of the ASP session.



2) Create a log file with fields for the tablename and record key of

uncommited records. Write an SP that "rolls back" records in this log based

on a transaction ID.



3) Store the recordsets as session vars, and leave them open until you get

to the point where you eithre rollback or commit the changes. For example,

it the multi pages you refer to are serial (i.e. start page, second page,

third page, final page) You might open all of the record sets when saving

the first page, make updates on subsequent pages, and finally close the

transaction when saving the last page.



You should carefully consider the problems introduced and inherent in web

delivery/collection of data. The user might loose the connection before

reaching the last page, leaving a transaction open, they might decide to

just leave or hit the back button. Rolling your own transaction using #1 or

#2 above prevents SQL server from dealing with many open transactions that

are abandoned.  I'd suggest using #2 based on what you said in your post,

but only if you are strictly adding new records, and not if you are updating

existing records.



Sloan



"rachel" <rgreen@e...> wrote in message news:138288@a..._databases...

>

> This is the first large-scale asp project that I'm working on, and I have

> a question about how to approach a database transaction that could

> potentially span many pages. I know that it is recommended to save the

> data (possibly in Session variables) as you go along, and limit the actual

> transaction to one page, at the end. This makes sense to me, but I've also

> read a great deal about how you should not save a ton of data (especially

> objects) in Session variables because of performance issues. Potentially,

> we could be talking about a transaction that involves saving multiple

> files (possibly very large ones, such as images), as well as other form

> data. Can anyone recommend an approach to this?

>

> Thanks!

> Rachel

>

>