> I just saw this code on this list and it made me think... Are we supposed > to be locking insert/update statements to get multi-user data integrity? I > have been thinking the database backend was doing this for us. SQL server > / PostgreSQL / Oracle etc.
> I guess it is important for writing a multi-user log files No? Yes and no. Locking ( and cftransaction ) are designed primarily for solving issues related to concurrency -- so for instance, a lot of people use this to get the last inserted record id from the db <cftransaction isolation="serializable"> <cfquery> insert into ...</cfquery> <cfquery> select top 1 * from mytable order by identitycolumn desc </cfquery> </cftransaction> You do need to perform some kind of locking, whether it's a transaction or a cflock or have some other means of securing against concurrency problems if you need to do something like this above, which is pretty common. That doesn't necessarily mean you have to use cflock or cftransaction, for instance, in MS SQL 2000 I use SCOPE_IDENTITY() in a stored procedure after performing an insert on a table with an identity column, which returns the id of the last inserted record, without the need for a serializable transaction. In MS SQL 7 I'm keeping the locking logic in the stored procedure, but I'm doing it by using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; insert into mytable... select top 1 * from mytable order by identitycolumn desc COMMIT TRANSACTION; This is because SCOPE_IDENTITY() was introduced in MS SQL 2000 and @@IDENTITY which was available in MS SQL 7 has the potential to return id values from different tables which receive inserts from an insert trigger. > <cflock name="CreateUserLock" timeout="20"> > <cftransaction> > <cfquery > > SQL INSERT Statement Here > </cfquery> > </cftransaction> > </cflock> This looks a bit odd ( is this a cut and paste or a paraphrased snippet? ) -- there doesn't appear to be a purpose for the <cftransaction> tag and I'm not sure what purpose the named lock has -- and out of context, I would have to guess that the <cflock> tag is a bit off as well, since there's nothing in the name to specify the application, etc. so if there's more than one application on the server using the name "CreateUserLock" their performances will all be connected ( and possibly degraded ) even if they use completely separate databases and have nothing to do with one another. S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com