hi all
while this Q is a bit general, it directly impacts on how we're building our data
access components (DAC). It's about having multiple CFQUERY's within a transaction,
data integrety, dirty reads and concurrent access
At present, any of our DML statements referring to multiple tables are being
explicitly locked either by a table lock (code below) or row lock (eg "SELECT fields
FROM table WHERE etc FOR UPDATE") (this is NOT a SQLServer db).
while most locks I've used are within SProcs (SQLServer), I have NEVER seen an example
of explicitly locking via the calling code - not in CF or ASP.NET. to be honest, I'm
too used to explicitly opening and closing connections (eg in ADO) and using Sprocs
for this sort of thing (not an option here)
is this idea (and the code below) either
1) prudent and worth the overhead in mission critical systems?
2) pointless and not worth the CPU cycles - OR -
3) dangerous and misguided
While CFTRANSACTION will place all it's queries on the same thread and is a "front"
to the db drivers transaction methods (my understanding of cftransaction), what
happens to other threads accessing the same tables at the same time? dirty reads are
OK but changed records behind the back of the transacted tables is a big no-no in this
case.
anyone got 2c of opinion they'd like to offer?
cheers
barry.b
<cftransaction action="BEGIN">
<cftry>
<!--- lock table --->
<cfquery name="qry" datasource="#dsn#">
lock table pstudaddr in share mode
</cfquery>
<cfquery name="qry" datasource="#dsn#">
select '' as found from pstudaddr
WHERE cmpy_code = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#arguments.CompanyCode#">
AND add_num = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#arguments.data.ADDR_CODE#">
</cfquery>
<cfif not qry.recordcount><!--- not found, OK to delete --->
<cfquery name="qry" datasource="#dsn#">
DELETE FROM psraddrname
WHERE cmpy_code = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#arguments.CompanyCode#">
AND ADDR_CODE = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#arguments.data.ADDR_CODE#">
</cfquery>
<cftransaction action="COMMIT" />
<cfset result = "true">
</cfif>
<cfcatch>
<cfset result = "false" />
<cftransaction action="ROLLBACK" />
<cfrethrow>
</cfcatch>
</cftry>
</cftransaction>
--
___________________________________________________________
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
