This might be too simplistic. But when you select a record to edit, could you not update a column to note that it is locked, by whom, and when? Or possibly have another table that holds this information and update the actual data row with a foreign key that corresponds to the information in the lock table? This way, others can read the data but no one else can update it. When you update the record, you'd simply remove the foreign key to signify that the record is unlocked again. A side benefit is that you'd have a complete log of who updated what in the lock table.
Of course you'd have to write code (CFML, stored proc, etc.) to enforce all this... Just an idea. Regards, Brian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Barnes Sent: Wednesday, August 04, 2004 8:32 PM To: [EMAIL PROTECTED] Subject: [CFCDev] Data Locking is it too hard? Data Locking is it too hard? It's a subject not touched on much, but to me there seems to be a bit of a "research" void on the subject, in that how do you perform data locking on a coldfusion based application. The overall nature of coldfusion mx, is that it's an occasionally connected client. Its purpose is to ask a database for information, push it back to your view, the view then manipulates the data, and coldfusion mx sends the data back to the initial database. Through-out that workflow, you are effectively taking a snap shot of data, then once you have the data in view (via form etc) its now technically dirty, in that it *could* have been changed. So when you then go to write that data, you could be killing someone elses entire work by either a simple over-write or inputing "ghost" records in the given database. So how can we as CFMX developers ensure the data we read/write is down so the above doesn't happen? I sat in a meeting yesterday and theories were thrown around, in that "Lets lock the row" which in othe words means, put a lock on the row on the actual get routine, keep it locked until you write back to the DB and then unlock it. Fair enough, some client-top applications can get away with this, and it kind of does work as it allows you to hold that given row within a table but still allowing people to do dirty reads on that row. The problem with this in coldfusion MX is how do you then tell the database that the lock you just put on, is infact yours and that you now want to contribute new/updated information to that table and when finished unlock it? Also, how do you timeout the locks? In that if someone were to put a lock on the row, walks away or computer crashes it then will carry out an onExit() event of some kind? My initial thoughts are, dear god surely someone else has worked this stuff out for me why am I thinking of it? Am I the only one in history to do this kind of insurance with coldfusion mx? My team and I have a number of thoughts on how to achieve counter-acts to data locking within CFMX/FLASH even. Theory #1 Using a "Proxy" object in java, in that get a java developer in to write a database proxy between coldfusion mx and our database. Its purpose in life is to allow coldfusion mx to send it a key, and say "hey lock this row for me" it will then keep a perm connection to the db for that given key, with a lifespan of lets say 20mins? If after 20 mins coldfusion mx hasn't communicated to it, it automatically kills off the lock. As you can imagine, some negatives can be associated to this as its now an extra layer of latency in many ways between the DB&CFMX. Furthermore, it would have to have some very sweet amount of java code in place (ie written by a demi-god). It does however open up other possibilities in that a site administrator can kill locks off manually or users can over-rides someone elses lock. Theory #2 Using Flash & Flash Com (or XML Socket) we can inform users or update forms as they go, that data has changed the moment somone changes the db. In that, the nature of applications most of the time is a screen by screen solution, in which we go to a given form with a record id of some kind. In coldfusion mx, we can store that information in a shared scope, so that if someone else (Joe) goes to that screen/record then the originating user (Jane) can be informed that "hey, joe is now looking at the same record as you, do you want to allow him yes/no" or something along those lines. This will keep the backchannels open amongst users and gives you the flexabilty to inform users that information you are looking on screen can and will be changed, its up to them at that point to allow it - this theory highly depends on workflow procedures and is probably not suitable for "websites". There are negatives associated to this approach, in that communication channels could get very chatty, and on ScreenX there may be 10% of ROWJ editable while on ScreenY there is 90% of ROWJ editable? Who or what overlaps get informed and how. Theory #3. When you go to a given screen and initiate your get calls, you store the data & exact sql syntax used inside session scope. Present that information to the screen and allow them to manipulate information. When the user then clicks the save action, you then proceed to your 'set' calls, in that you open up firstly a CFTRANSACTION (aka row lock) (which so far, I don't entirely trust - need more exacts on this tag I think) execute the SQL syntax you have in session scope, compare the original data with live data field by field, if they match nicely allow the new data to be updated inside the database. If the data is not matched you can do one of the following: You can then either do a save on data that does match and present the user with data that doesn't (ie firstname has changed, but the rest of the persons address was saved). You could also do a conflict merge routine? In that a popup of "heres whats in the db, heres what your trying to save, what do you want to over-write) ..Dangerous though. You could simply throw an exception and say "sorry, data is dirty you need to redo your work" - aka pissing user right off). Also with this theory, we can kind of do part of Theory #2 but all it does is keep a list of people who is also seeing the same screen/record, allowing the users to view a list, pickup a phone etc and ask them to ping off if need be? To me all of the above have their own in-built flaws and to me, It seems like a hidden "how do I achieve this" within planning of most coldfusion mx applications. I have seen and heard people pin their entire hopes on CFTRANSACTION do this? But does it work? And furthermore what are the known issues with CFTRANSACTION (for one foreign CFC objects cannot be inserted within the transaction unless they are infact instatiated into the same CFC of which the method/cftransaction lives. Ok, not a bad thing, but can throw some peoples frameworks out a bit). I've also heard people mention Stored Procedures? Fair enough I'm not schooled up enough on these to comment, but to me I think its still in the same boat? In that how do you verify data about to be written isn't expired? Is it always a case of last in, last served? What happens with deletes and how does that affect the applications sequences? Lots of nasty questions, but yet un-answered in my view? And it would be great to address these types of issues officially somewhere atleast to help people like myself navigate around coldfusion mx and databases. I would love to hear peoples thoughts/comments on this as I think it's a hot topic? ---------------------------------------------------------- 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] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.734 / Virus Database: 488 - Release Date: 8/4/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.734 / Virus Database: 488 - Release Date: 8/4/2004 ---------------------------------------------------------- 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]
