info being lost when 2 users trying to write to db

[MtnGirl]

I'm trying to troubleshoot some code and I'm fairly new to this so I don't really know what to look for. I have a program that collects info from a user, inserts that info into a db table as a new record and then immediately sends an email to the user saying their info has been received.

I've noticed that sometimes when I have two users that are trying to use the system at approximately the same time, the first user ends up not getting an email (the second step in this process) and the second user's info is not written to the appropriate table (the first step in this process) and consequently they are also not receiving an email.

I assume there is a conflict between the email trying to go out and the info from the second person trying to be inserted into the database. What can I do to my code that will allow one user to get all the way through the process before the other user's info gets written to the database without generating an error that is seen by either user?

Can anyone point me in the right direction? I would greatly appreciate it!

thanks!

 

[CFDude]

What database are you using? Are you putting your cfquery inside a cftransaction tag?

 

[MtnGirl]

I'm using Access and no, I'm not putting the query inside a CFTransaction tag. Does that group everything together so that other users don't interrupt that particular transaction?

About 30-45 mins after I sent the first email above, some of the info that I thought had been lost actually appeared on my web pages. What would cause there to be a delay from the time the CF code processes until the time it actually gets written to the database? I checked the database almost immediately when I noticed a discrepancy in the info that was being displayed on my web page and there was a record missing. But then after the 30 min delay, that record was written to the database. Is that a server issue where my site is being hosted or is there something I can put in my code to help with that?

Thanks again!

 

[CFDude]

I don't play much with access, but I would definately wrap it in a cftransaction tag -- it couldn't help. I don't know why it took so long for the new data to show. Maybe the cftransaction will help there.

HTH,
Tim P.

 

[Guest_imported]

I'm not sure that the transaction tag does anything in Access. It's my understanding that in Access, "autocommit" is always on. That means that transactions are committed immediately and thus there is nothing to roll back. But it's worth a look.

The other thing, is that you should lock the database (preferably lock only the table or even only the record) when you do operations on it.

I think that the delay is not because the record was not written to the database. I think that you have some caching of web pages going on. Is there a proxy between your browser and the web server? Are you using IE? (It seems to cache pages when you least expect it.)

As to the email problem. Does you app use variables and/or files that are shared by every user? If so, the second user might be overwriting the value of an application variable (for example). Be sure to lock these variables. If using common files, be sure to use unique filenames, or if users must use the same file, you have to devise some locking scheme for them. Whenever you will have multiple users using an application at the same time, you must anticipate contention and devise methods to handle it.