Re: CF Database Question add.
Isn't it though? In an ideal world, I would have all DB access via Stored Procs, to begin with. I'm just working with what I got. > Having to do workarounds like this is kinda scary, eh? > > Laterz! > > J > > On 4/20/05, Ali Awan <[EMAIL PROTECTED]> wrote: > > Thanks to everyone who responded to this thread. > > Especially Jared and Jochem, I will try out the with > a serializable isolation level. I think that will solve the problem. > > > > Thanks again, > > Ali > > -- > --- > - > Buy SQLSurveyor! > http://www.web-relevant.com/sqlsurveyor > Never make your developers open Enterprise Manager again. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:204752 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
No worries, Ali... that's why some of us join these lists. I'm interested to know, though, why you don't have your system either use an internally regulated ID number (like an auto-increment constraint on the column) or use something like a UUID that's generated outside the database... Having to do workarounds like this is kinda scary, eh? Laterz! J On 4/20/05, Ali Awan <[EMAIL PROTECTED]> wrote: > Thanks to everyone who responded to this thread. > Especially Jared and Jochem, I will try out the with a > serializable isolation level. I think that will solve the problem. > > Thanks again, > Ali -- --- - Buy SQLSurveyor! http://www.web-relevant.com/sqlsurveyor Never make your developers open Enterprise Manager again. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203795 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question
Ali, Wow... ummm... that's a shocker. As far as isolation levels... umm... which is worse: A performance Drop Perpetually Corrupted Data I have to say that to have the DB set up so that this could happen at all is a matter of concern for me. It doens't sound like a sound design. I hope that wasn't too blunt, but this should have happened to begin with. You've got a limited number of choices, and CFTransaction won't help unless you set the isolation level high enough that the next query can't write until the first query's data has been comitted. Read_comitted should be close enough, and give you less of a performance hit than serializable... but without reworking some things I'm afraid there's not a lot that can be done otherwise. You could set up an On Insert trigger to generate a UUID You could add an auto-incremented field You could add a date-time field with a default value of getdate() you could add an inserted-by field that carries the application name and cftoken values There's many different ways you could structure this to keep the data unique even IF the records end up with a confict such as you've encountered. I would HIGHLY recommend you add a UNIQUE constraint to the column in question as well... to prevent such duplicates in the future. You could code your create routine, then to try to insert, and in CFCATCH type="database" check for whatever you need to see if a duplicate entry was attempted to be inserted in a column with a unique constraint and re-fire your create routine. These are just some thoughts... the eventual implementation is entirely up to you. Laterz! J On 4/20/05, Ali Awan <[EMAIL PROTECTED]> wrote: > Jared, > > LOL, yeah it sounds like an Access issue. > Actually we're using SQL 2k. > > Another question though, by using a high isolation level will that slow the > app down significantly? I read in the docs that there's a lot of overhead > associated with it? > > Thanks, > Ali > > >Ali, > > > >You'd be best off to use a CFTRANSACTION with a high isolation level, > >like read_committed or even serializable. > > > >I'm gonna take a stab at this and guess you're using Access? > > > >Keep us posted! > > > >J > > > >On 4/19/05, Ali Awan <[EMAIL PROTECTED]> wrote: > > > > > >-- > >--- > >- > >Buy SQLSurveyor! > >http://www.web-relevant.com/sqlsurveyor > >Never make your developers open Enterprise Manager again. > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203741 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
Thanks to everyone who responded to this thread. Especially Jared and Jochem, I will try out the with a serializable isolation level. I think that will solve the problem. Thanks again, Ali >Ali Awan wrote: > >If you do all this in a serializable transaction, you should be >fine in MS SQL Server. > >Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203699 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
Ali Awan wrote: > > > Select Max(ID) as myNewID > From TableA > > > > > > Insert Into TableA (tableAID) Values (#myNewID#) > > > > > Insert Into TableB (TableBID, columns...) VALUES (#myNewID#, columnValues...) > If you do all this in a serializable transaction, you should be fine in MS SQL Server. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203670 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CF Database Question add.
Cool, that's good to know. However, if 2 apps are on 2 different servers, then locking them won't serve me any purpose. The 2 apps can still end up modifying the same data in the database at the same time, I believe. Thanks, Ali >Well, you can, there is nothing stopping you ...just not very good practice. > > >-Original Message- >From: Ali Awan [mailto:[EMAIL PROTECTED] >Sent: 20 April 2005 16:25 >To: CF-Talk >Subject: Re: CF Database Question add. > >I'm not sure but I believe that you can't 's. > >Ali ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203663 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
>How are you generating the ID? > Select Max(ID) as myNewID >From TableA Insert Into TableA (tableAID) Values (#myNewID#) Insert Into TableB (TableBID, columns...) VALUES (#myNewID#, columnValues...) Ali >>I forgot to add that App1 and App2 are inserting to the same table. >> >>I ended up with 2 records with the same ID. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203662 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CF Database Question add.
Well, you can, there is nothing stopping you ...just not very good practice. -Original Message- From: Ali Awan [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 16:25 To: CF-Talk Subject: Re: CF Database Question add. I'm not sure but I believe that you can't 's. Ali >At 07:45 PM 19/04/2005, Ali Awan wrote: >>I forgot to add that App1 and App2 are inserting to the same table. >> >>I ended up with 2 records with the same ID. > >Couldn't you use cflock to lock the table until the first app is done? > >T > >--- >[This E-mail scanned for viruses by Declude Anti-Virus] ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203662 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
I'm not sure but I believe that you can't 's. Ali >At 07:45 PM 19/04/2005, Ali Awan wrote: >>I forgot to add that App1 and App2 are inserting to the same table. >> >>I ended up with 2 records with the same ID. > >Couldn't you use cflock to lock the table until the first app is done? > >T > >--- >[This E-mail scanned for viruses by Declude Anti-Virus] ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203658 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question
Jared, LOL, yeah it sounds like an Access issue. Actually we're using SQL 2k. Another question though, by using a high isolation level will that slow the app down significantly? I read in the docs that there's a lot of overhead associated with it? Thanks, Ali >Ali, > >You'd be best off to use a CFTRANSACTION with a high isolation level, >like read_committed or even serializable. > >I'm gonna take a stab at this and guess you're using Access? > >Keep us posted! > >J > >On 4/19/05, Ali Awan <[EMAIL PROTECTED]> wrote: > > >-- >--- >- >Buy SQLSurveyor! >http://www.web-relevant.com/sqlsurveyor >Never make your developers open Enterprise Manager again. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203656 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CF Database Question
What is the database? SQL 7, Oracle, Access, ? -Original Message- From: Ali Awan I have a question regarding ColdFusion and database requests. I have 2 applications on different servers, Server A and Server B and they both access a database on Server C. The problem is that recently that App1 on Server A and App2 on Server B ended up hitting the database at the same time. They both have identical code to insert a new record and whose ID is created by selecting a max id and incrementing it. They both ended up with the same ID. What is the best way to avoid this? I know that it is not possible to lock queries. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203609 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question
Ali, You'd be best off to use a CFTRANSACTION with a high isolation level, like read_committed or even serializable. I'm gonna take a stab at this and guess you're using Access? Keep us posted! J On 4/19/05, Ali Awan <[EMAIL PROTECTED]> wrote: > I have a question regarding ColdFusion and database requests. > > I have 2 applications on different servers, Server A and Server B and they > both access a database on Server C. > > The problem is that recently that App1 on Server A and App2 on Server B > ended up hitting the database at the same time. > > They both have identical code to insert a new record and whose ID is created > by selecting a max id and incrementing it. > > They both ended up with the same ID. > > What is the best way to avoid this? > > I know that it is not possible to lock queries. > > If I wrap the queries in a CFTRANSACTION would that prevent this from > re-occuring? > > Any help is greatly appreciated. > > Thanks, > > Ali -- --- - Buy SQLSurveyor! http://www.web-relevant.com/sqlsurveyor Never make your developers open Enterprise Manager again. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203602 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
How are you generating the ID? >I forgot to add that App1 and App2 are inserting to the same table. > >I ended up with 2 records with the same ID. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203595 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CF Database Question add.
At 07:45 PM 19/04/2005, Ali Awan wrote: >I forgot to add that App1 and App2 are inserting to the same table. > >I ended up with 2 records with the same ID. Couldn't you use cflock to lock the table until the first app is done? T --- [This E-mail scanned for viruses by Declude Anti-Virus] ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203594 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
