Ryan, I raise these points because we're traveling down this path too and it
might be worthwhile clarifying some points/ideas. We're using an Informix db
which uses simular
SELECT * FROM child WHERE id = 101 FOR UPDATE;
are you *sure* you are after the db engine's row level locking?
this will lock the record according to . fine. How do you unlock it?
complete the update? sure, but don't you need to be within the same, if not db
connection then at least the same session, yes?
I'm no MySQL guru but is this what you're talking about?
<cfquery>
SELECT * FROM child WHERE id = 101 FOR UPDATE;
-- other queries go here
UPDATE child SET fName = "#args.fName#" WHERE id = 101;
</cfquery>
the select does the lock, the update does the work and releases it.
isn't that _functionally_ the same as
<cftransaction isolation="serializable">
<cfquery>
-- other queries go here
</cfquery>
<cfquery>
UPDATE child SET fName = "#args.fName#" WHERE id = 101;
</cfquery>
</cftransaction>
? and because of the isolation="serializable", dirty reads are prevented?
what this thread has touched on is strategies in overcomming
first request/response (get data, load form):
SELECT * FROM child WHERE id = 101 FOR UPDATE;
second request/response (get and save submitted data from form,
complete/release the lock):
UPDATE child SET fName = "#args.fName#" WHERE id = 101;
especially when that second request/response is not guarenteed...
your thoughts?
cheers
barry.b
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Behalf Of Ryan Guill
> Sent: Thursday, 16 June 2005 3:54 AM
> To: [email protected]
> Subject: Re: [CFCDev] Repost: SQL Concurrency
>
>
> Has anyone ever used the row level locking in mysql?
>
> On 5/30/05, Barry Beattie <[EMAIL PROTECTED]> wrote:
> >
> > here's the reasoning behind the Qof Q idea but I've got a couple of
> > questions myself (below)
> >
> > anyhoo... QofQ makes it easy to get rid of the old locks,
> both expired and
> > specific. This is done by re-querying the "artificial"
> query to create a new
> > one without the old ones and then that becomes the query store
> >
> > purging stale locks
> >
> > <cfquery dbtype="query" name="qryPurge">
> > select entityType, alphaID, sessID, userID, dtExpires
> > from _qryLockStore
> > where dtExpires >= <cfqueryparam
> cfsqltype="CF_SQL_DATE" value="#now()#"
> > />
> > </cfquery>
> > <!--- the lock is now the remaining query rows --->
> > <cfset _qryLockStore = qryPurge />
> >
> >
> > check to see if it exists
> >
> > <cfquery dbtype="query" name="qryExists">
> > select entityType, alphaID, sessID, userID, dtExpires
> > from _qryLockStore
> > where entityType = <cfqueryparam cfsqltype="CF_SQL_CHAR"
> > value="#arguments.entityType#" />
> > and alphaID = <cfqueryparam cfsqltype="CF_SQL_CHAR"
> > value="#arguments.alphaID#" />
> > </cfquery>
> >
> > destroying a specific lock
> >
> > <cfquery dbtype="query" name="qryPurge">
> > select entityType, alphaID, sessID, userID, dtExpires
> > from _qryLockStore
> > where entityType != <cfqueryparam cfsqltype="CF_SQL_CHAR"
> > value="#arguments.entityType#" />
> > and alphaID != <cfqueryparam cfsqltype="CF_SQL_CHAR"
> > value="#arguments.alphaID#" />
> > </cfquery>
> > <!--- the lock is now the remaining query rows --->
> > <cfset _qryLockStore = qryPurge /><!--- copy or
> reference? hmmm... --->
> >
> > ===================================================
> >
> > so now on to my question:
> > ---------------------------------------
> >
> > we have a simple MVC/MVP hybrid* (think benorama, not fusebox)
> >
> >
> > *persist -> service - > singleton controller (cfc) -> page
> presenters (cfm)
> > -> pagelets (custom tags)
> >
> > because the getting of the data comes down to a request
> from the view, and
> > we want to trap the user ID (from their session vars) we
> can either do the
> > locking in the singleton controller or in the main page
> presenters (the
> > presenters request data and send to the controller)
> >
> > so where to put the locking? as a function of the
> controller (CFC) or in
> > each presenter page (CFM)?
> >
> > thanx
> > barry.b
> >
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of
> > Bill Rawlinson
> > Sent: Tuesday, 31 May 2005 6:48 AM
> > To: [email protected]
> > Subject: Re: [CFCDev] Repost: SQL Concurrency
> >
> > I missed the query of query reference in the earlier
> discussion, can you
> > refresh my memory?
> >
> >
> > On 5/30/05, Jared Rypka-Hauer - CMG, LLC <
> [EMAIL PROTECTED]> wrote:
> > > Alrighty then!
> > >
> > > My guess would have been that there was some sort of CF
> instance managing
> > which records were locked... what freaked me out is that I
> may have missed
> > something in the last 6 years of CFing that would have made
> my life a lot
> > easier. :)
> > >
> > > I've never had a situation where these long-lived locks
> were important, so
> > I'm not in need of it at this point. Yay me, eh?
> > >
> > > Anyway, thanks for the deeper explanation (and you too,
> Bill)... if for no
> > other reason than that when I DO need it I'll know where to
> start without
> > having to dig around for the answers.
> > >
> > > Quick question, though... why use a QofQ when you could
> use something like
> > a struct or even an in-memory XML doc to manage tables,
> rows, and so on, to
> > handle the locking? It sounds like a lot more work when you
> could just have
> > a "locked entity" structure for each entity type and store
> the locks in
> > something other than a query object...
> > >
> > > Laterz!
> > >
> > > J
> > >
> > >
> > > On 5/29/05, Barry Beattie < [EMAIL PROTECTED]> wrote:
> > > >
> > > > >> is there some sort of DB locking available to us
> that I haven't seen
> > in CF?
> > > >
> > > > oh, I wish there was! the hoops one has to go thru to
> get table and (esp
> > in this case) row level locking! (another feature request
> for Tim Buntel and
> > CF8, perhaps?)
> > > >
> > > > Jared, it's a singleton CFC in server scope (for us -
> Application
> > otherwise) that uses a QofQ (believe it or not) as the
> "guts"...so a cflock
> > will be needed to serialise the access to this CFC (to stop
> two locks
> > hitting at the same time)
> > > >
> > > > (to clarify: "cflock" != "data (application) lock" !=
> "database table
> > lock")
> > > >
> > > > where it get's complicated is when you're dealing with
> "entities" and
> > you need to lock more than one (db) record that makes up that
> > entity...("sorry you cannot edit this student - the
> students' parent's
> > address is locked for editing")
> > > >
> > > > ...and then bring in editable datagrids which deals with many
> > entities...
> > > >
> > > > >> I'm interested...
> > > >
> > > > interested enough to go down this route? I hope your
> business rules are
> > easier...
> > > >
> > > > Dave Watts made some points on this that might be worth
> considering...
> > > >
> > http://www.mail-archive.com/[email protected]/msg06061.html
> > > >
> > > >
> > > > has anyone else done anything like this?
> > > >
> > > > thanx
> > > > barry.b
> > >
> > >
> > >
> > > --
> > >
> > > ---------------
> > > -------------------------------------
> > > Buy SQLSurveyor!
> > > http://www.web-relevant.com/sqlsurveyor
> > > Never make your developers open Enterprise Manager again.
> > ----------------------------------------------------------
> > > You are subscribed to cfcdev. To unsubscribe, send an email to
> > [email protected] with the words 'unsubscribe cfcdev' as
> the subject of the
> > email.
> > >
> > > CFCDev is run by CFCZone ( www.cfczone.org) and supported
> by CFXHosting
> > (www.cfxhosting.com).
> > >
> > > CFCDev is supported by New Atlanta, makers of BlueDragon
> > > http://www.newatlanta.com/products/bluedragon/index.cfm
> > >
> > > An archive of the CFCDev list is available at
> > www.mail-archive.com/[email protected]
> >
> >
> >
> > --
> > [EMAIL PROTECTED]
> > http://blog.rawlinson.us
> >
> > If you want Gmail - just ask.
> > ----------------------------------------------------------
> > You are subscribed to cfcdev. To unsubscribe, send an email to
> > [email protected] with the words 'unsubscribe cfcdev' as
> the subject of the
> > email.
> >
> > CFCDev is run by CFCZone (www.cfczone.org) and supported by
> CFXHosting
> > (www.cfxhosting.com).
> >
> > CFCDev is supported by New Atlanta, makers of BlueDragon
> > http://www.newatlanta.com/products/bluedragon/index.cfm
> >
> > An archive of the CFCDev list is available at
> > www.mail-archive.com/[email protected]
> > ----------------------------------------------------------
> > You are subscribed to cfcdev. To unsubscribe, send an email to
> > [email protected] with the words 'unsubscribe cfcdev' as
> the subject of the
> > email.
> >
> > CFCDev is run by CFCZone (www.cfczone.org) and supported by
> CFXHosting
> > (www.cfxhosting.com).
> >
> > CFCDev is supported by New Atlanta, makers of BlueDragon
> > http://www.newatlanta.com/products/bluedragon/index.cfm
> >
> > An archive of the CFCDev list is available at
> > www.mail-archive.com/[email protected]
>
>
> --
> Ryan Guill
> BlueEyesDevelopment
> [EMAIL PROTECTED]
> www.ryanguill.com
> (270) 217.2399
> Want gmail? Get it here while its hot.
>
> www.ryanguill.com/blog/
>
>
> ----------------------------------------------------------
> You are subscribed to cfcdev. To unsubscribe, send an email
> to [email protected] with the words 'unsubscribe cfcdev' as
> the subject of the email.
>
> CFCDev is run by CFCZone (www.cfczone.org) and supported by
> CFXHosting (www.cfxhosting.com).
>
> CFCDev is supported by New Atlanta, makers of BlueDragon
> http://www.newatlanta.com/products/bluedragon/index.cfm
>
> An archive of the CFCDev list is available at
> www.mail-archive.com/[email protected]
>
>
>
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
CFCDev is supported by New Atlanta, makers of BlueDragon
http://www.newatlanta.com/products/bluedragon/index.cfm
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
