Yes, there is a small gap between existence checking and the actual insert.
Unless you specifically commit the transaction early, all the statements would be in the same transaction. That is true with all the possible methods. Transactions are not the issue. Locking is, but it is only an issue on very busy servers. I use the EXISTS function all the time without fear, even though it is not inherently protected from concurrency issues. If two events happen at the same microsecond, you might see an error. If you are worried about the thread-safety issue, there are two approaches. 1. set the desired locking level to prevent concurrency problems. 2. handle the rare error event. If you set wider locks, you will punish every insert, plus the reads from that table, for what is a rare event. There are a couple methods of error handling, try/catch and inspecting @@error. If you want to specifically do something in case there is a error, I believe the T-SQL would be IF (@@ERROR = 2627). In your case you would probably be ignoring the error. I'm sure others would prefer the locking approach to the error handling approach. With SQL there is often a tradeoff between speed and safety. BEGIN TRY INSERT INTO table VALUES (@val) END TRY BEGIN CATCH IF (@@ERROR = 2627) print 'duplicate insert attempted.' ELSE print 'an unexpected error occurred' END CATCH I should mention that another assumption I am making is that you are not deleting items from that table very often. If there are a lot of deletes and updates, locking becomes more important. -Mike Chabot http://www.linkedin.com/in/chabot On Fri, Jan 28, 2011 at 10:58 AM, Michael Dinowitz < mdino...@houseoffusion.com> wrote: > > Yes. > > if exists(statement) > something > else > something else > > The question I have to look up is if the whole operation is one 'unit' or > does it have to have transaction control. Is there a 'gap' between the > exists() check and the actual sql operation? > > > > > On Fri, Jan 28, 2011 at 9:15 AM, Che Vilnonis <ch...@asitv.com> wrote: > > > > > Could this be combined with an "ELSE" statement in SQL? That is, insert > if > > new, update if existing? Thanks, Che > > > > -----Original Message----- > > This is what I'm using at the moment but I'm going to read what you have > as > > well. I want fast and safe. > > > > <cfquery datasource="#variables.dsn#"> > > if not exists( > > Select keywordid > > from keywords > > where keyword = <cfqueryparam value="#local.keyword#" > > cfsqltype="cf_sql_varchar">) > > Insert into keywords(keyword) > > values(<cfqueryparam value="#local.keyword#" > > cfsqltype="cf_sql_varchar">) > > </cfquery> > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341639 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
