> 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
