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]
