Dominic Watson wrote: > Is that definately true with the select insert as I exampled? I imagine (but > that is all I do, quite willing to be wrong) that the single SQL statement > must complete before another is allowed to run..? I.e. I imagine that this > SQL will not run into the problem you describe: > > INSERT INTO myTable (id, title) > SELECT Max(id) + 1, 'Some title' > FROM myTable
I haven't tested your statements, but extrapolating from the basics of the concurrency control algorithms used by different databases I think that is not correct. With databases that use a locking approach to concurrency (DB2, MS SQL Server, Sybase) you will not have the problem because multiple occurences of this query will be serialized. With databases that use multiversioning (Oracle, PostgreSQL and MS SQL Server with snapshot isolation) this query can run concurrently and you can have conflicts. I think with MySQL/InnoDB your query will be serialized if you have a primary key (due to next key locking), and with MySQL/MyISAM you should be safe due to the overall restriction that only one data changing statement can write to a table. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295233 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
