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]
