I have an Web App hosted on Azure. It has a complicated signup form allowing users to rent lockers, add spouse memberships etc. I don't want to add records to the database until EVERYTHING on that page is completed and checks out. I am using a SQL Transaction so that I can add records to various tables and then roll them back if the user does not complete the entries properly, or simply exits the page. I don't want a bunch of orphaned records in my DB. All of the records that will eventually be added reference each other by the identity field on each table. So, if I don't add records to a table, I don't get an identity returned to reference in other tables.

At the start of the page, I open a SQL connection and associate it with a Transaction and I hold the transaction open until the end of the process. If all is well, I commit the transaction, send out emails etc.

I know best practice is to open and close a SQL connection as quickly as possible. I don't know of any other way to operate this page without opening a SQL connection and transaction and holding it open until the end of the process.

If I should not be doing it this way, how do others do it?

CodePudding user response：

I see two questions here, one about how would I do it, and the other about the limits of the DB. Starting with the second, the timeout of a transaction depends on your connection string timeout. So if the connection is still alive, you can complete the commit or do the rollback.

about how to do it, I'd not do it that way. Linking a database critical lock process to user interaction is a really bad approach. You put the performance in your user's hands and also, you're assuming goog intentional clients, but you'll also have bad guys.

I'd store it locally in the web browser the information and if the process is complete, then send the information to the DB to commit it. So the final "POST" would create all the items, which is going to also take some time.

Another option if you want to keep it server side, a Redis server to cache the information and then, "move it" into the DB when the process is finished.