-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, September 08, 2006 3:22 PM
To: CF-Talk
Subject: Re: Locking Theory
Mark A Kruger wrote:
>
> FIRST: User "A" does the following:
> SELECT * FROM users WHERE username = 'bob'
> WITH (updloc
Mark A Kruger wrote:
>
> FIRST: User "A" does the following:
> SELECT * FROM users WHERE username = 'bob'
> WITH (updlock)
>
> Presumably User "A" is now looking at Bob's information for editing
> on a web page
>
> SECOND: Meanwhile User "B" runs the same query
>SELE
>>It's not a database problem!
May be not, but if you don't deal with it, it WILL soon be a database
problem! ;-)
>>But your users are not connected to the database.
Right, but they are connected to a session in CF which is connected to
the database.
This is why it would be better if CF took
> Well, whether one deals with it in the SQL statement, whether one does
> it with the tools Matt Robertson designed, whether one does not deal with
> it at all. It's a question of preference. But since it's a database
> problem, and SQL is the unique way the programmer deals with the
> database,
Alrighty, thanks Jochem, i'll definitely look into those, my main
resource now is (apart from teh intarweb) books online for MS SQL
Server, but I'd like to gain a more generalized knowledge about SQL.
And and that manual, aren't you supposed to rtf it?
Mingo.
Jochem van Dieten wrote:
> And of
>>Are your users in your C program also, or are they using browsers to send
HTTP requests?
- Users use their browser to communicate with an application.
- In this application, which by the way uses sessions and session variables,
the programmer (not the user) communicates with the database thro
>>If we had to deal with the mechanics of that directly within every
SQL statement
we write, SQL would be much more difficult to write.
Well, whether one deals with it in the SQL statement, whether one does
it with the tools
Matt Robertson designed, whether one does not deal with it at all. It'
LLBACK
I'm having some trouble figuring out how this might be implemented... If
it's possible or worth it
-Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Friday, September 08, 2006 9:39 AM
To: CF-Talk
Subject: Re: L
As an aside, all of what I am saying is meant to apply exclusively to
the stateless web environment. When I do client/server stuff I use
locks and handle things entirely differently, but that environment is
not stateless so I have different tools available.
--
[EMAIL PROTECTED]
Janitor, MSB Web
On 9/8/06, Claude Schneegans <[EMAIL PROTECTED]> wrote:
> >>the updlock with SQL server doesn't prevent data reads occurring when
> lock is in
> place.
>
> Sure, and no one wants to prevent reading during that time.
> BUT it will certainly prevent another updlock on the same record, thus
> prevent
> > But the environment isn't connected.
>
> It sure is: when I am in MY C program, I can create an ODBC connection
> to a datasource, and keep that connection open as long as the program
> is running. And a CFX dll can be kept running in memory as long as the
> CF server itself is running. From
Ooops forgot about that ... :)
TK
- Original Message -
From: "Claude Schneegans" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Friday, September 08, 2006 12:03 PM
Subject: Re: Locking Theory
> >>the updlock with SQL server doesn't prevent data r
Mingo Hagen wrote:
> That's the kind of stuff more people need to know about. Do you have any
> tips on good advanced SQL books? (Or should I have seen this in the
> basic SQL books that are out there and did I just skip this bit.)
There are few advanced SQL books that do not focus on one implem
> I don't see your point here.
> - race condition is a concurrency problem involving the database,
> - Concurrency management is handled by the database engine,
> - SQL is simply a language that can be used to interact with
> the database,
> - then, why SQL shouldn't provide tools to help the
> p
>>But the environment isn't connected.
It sure is: when I am in MY C program, I can create an ODBC connection
to a datasource,
and keep that connection open as long as the program is running.
And a CFX dll can be kept running in memory as long as the CF server
itself is running.
From that conn
>>the updlock with SQL server doesn't prevent data reads occurring when
lock is in
place.
Sure, and no one wants to prevent reading during that time.
BUT it will certainly prevent another updlock on the same record, thus
prevent someone to read it for editing also.
--
_
ng to
that solution.
TK
- Original Message -
From: "Claude Schneegans" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Friday, September 08, 2006 11:34 AM
Subject: Re: Locking Theory
> >>So what happens when a user has made all his or her updates just to
>
> > How on earth would such a "tool" work, in a disconnected
> > environment?
>
> By keeping the environment connected.
> A CFX is a dll written in C that can stay resident, keep ODBC
> connections opened keep user handles in memory, and handle
> time limits.
> If the CFX locks a row in the dat
>>So what happens when a user has made all his or her updates just to
be told
"We are sorry but the record you are trying to edit is locked"
Actually, this is not the way it works.
When a user reads a record for editing, the record is locked until he
updates.
During that time, when another user
Claude Schneegans wrote:
>> You had to lock a table to index it? That is soo 80'ies
>
> Yeah, after 25 years, the only difference is that the lock is handled
> automatically by the database engine.
> Not a big deal ;-)
In a current database you can continue to insert, update and delete while you
September 08, 2006 10:56 AM
Subject: Re: Locking Theory
> >>A row-level lock isn't going to come even close to that.
>
> Why not ?
> With a row locked, it is not the update by another user which will be
> refused,
> but his own "lock for editing" before he
MS SQL locking is implemented as set of hints, for example:
Ah ok, so row locks are possible with MS SQL and Oracle.
That covers most of the market.
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send
>>A row-level lock isn't going to come even close to that.
Why not ?
With a row locked, it is not the update by another user which will be
refused,
but his own "lock for editing" before he can read the record.
The system will not tell him who has locked the row, but may be it is better
like this
>>How on earth would such a "tool" work, in a disconnected environment?
By keeping the environment connected.
A CFX is a dll written in C that can stay resident, keep ODBC
connections opened
keep user handles in memory, and handle time limits.
If the CFX locks a row in the database, CF will not
Claude Schneegans wrote:
>> SELECT * FROM table WHERE id = blah FOR UPDATE
>
> Ok, but I think this syntax is proper to Oracle.
It may have originated there, but it has been in the SQL standard since at
least 1992.
> - I don't see it documented in Access, SQL Server
MS calls it WITH updlock. Y
>>SQL is simply a language that can be
used to interact with the database at a high level of abstraction.
Concurrency management is handled by the database engine, and there are many
ways this can be done. It, like database-specific physical data storage, is
below the level of SQL.
I don't see yo
: "Claude Schneegans" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Friday, September 08, 2006 10:20 AM
Subject: Re: Locking Theory
> >>Then I have news for you: the feature is implemented in both ODBC and
> JDBC and is standard in SQL.
>
> Ok, so it should b
>>You had to lock a table to index it? That is soo 80'ies
Yeah, after 25 years, the only difference is that the lock is handled
automatically by the database engine.
Not a big deal ;-)
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/
>>Where'd you use row level locking? A real life example?
This example has already been described in this thread:
« 1. Bob opens a web page and goes to an edit tool and selects "jenny"
to edit.
2. Sally opens a web page and goet to an edit tool and also selects
"jenny" to edit.
3. Bob updates
>>Then I have news for you: the feature is implemented in both ODBC and
JDBC and is standard in SQL.
Ok, so it should be possible to make some CFX or CFC to handle locks.
>>SELECT * FROM table WHERE id = blah FOR UPDATE
Ok, but I think this syntax is proper to Oracle.
- I don't see it documen
Hey Jochem,
That's the kind of stuff more people need to know about. Do you have any
tips on good advanced SQL books? (Or should I have seen this in the
basic SQL books that are out there and did I just skip this bit.)
Thanks,
Mingo.
Jochem van Dieten wrote:
> SELECT * FROM table WHERE id =
>>Making a web app retain some idea of a cohesive session across HTTP
requests is a
work around of the inherent statelessness of the protocol.
Ok, but workaround or not, we are able to have sessions and keep track
of them.
>>All a session
is is knowing that two distinct requests within a give
Tom Kitta wrote:
> Just remember that CF only *suggests* isolation levels and the DB actually
> does the isolation. There are four isolation levels, with most DB using read
> committed as the default level.
If the database does not support the requested isolation level it is required
to upgrade
Claude Schneegans wrote:
>
> For my applications, I have no problem presently as few people are
> working in the same time,
> but in general, how would you lock a certain record in a table while
> some one is
> working on it ?
I wouldn't. I would refuse an update to an old row version and prese
Claude Schneegans wrote:
>> For example, many databases do have the kind of locking you are talking
>> about - for example in Oracle you can do select...for update, which
>> locks the selected record until the update is completed by the session
>> that initiated the select.
Actually, until the tra
Claude Schneegans wrote:
> This what so simple in 1980's, with dBase, clipper, Foxpro you name it,
> under DOS.
> All had Rlock() and Flock() functions.
> Rlock() was used when editing a record, Flock() locked the whole file,
> for instance
> while indexing a table.
You had to lock a table to in
How about solving it with AJAX or using Flash forms that use sockets to
check whatever the current user is still editing? You could even popup a
message like "You are taking a long time to edit" or "Someone else wants to
edit this data - please either release it or finish your work ASAP".
TK
> Please, I'm not that dumb, I know what an application is compared to a
> simple web page.
Never suggested you were dumb, just pointing out the root of the issue.
> All the same, we coldFusion developpers, are able to create complete
> multi-user
> true applications and work around THE HTTP "sta
Well, here's my results, which show that serializable is NOT the
default isolation level, at least for Oracle (10G), in CFMX6.1.
I ran two templates.
TEMPLATE 1
SELECT PKID,SOMEDATA
FROM TESTTRANSACTION
Before UPDATES:
PK: #PKID# DATA:
#QGetSomeData.SomeData#
UPDATE T
> If you have time, do the same and we can compare notes.
Unfortunately, I don't have CF where I am right now. Nor do I have a
database. In fact, I barely have a computer. But a better approach for
testing might be to create an SQL trace; I'm pretty sure that'll show you
your isolation level direc
I think the needs of a grownup application are well beyond simply
row-locking a table. You don't want the db to refuse an update. You
want the user to attempt to visit a record and be told "Bubba is using
this record already. You can look at it but if you want to do more
click here to yell at Bu
It's easy enough to test, which I'll do in a moment.
1) Write a page containing a transaction without an explicit level.
Between two queries in that transaction (maybe an update and a
select), make CF sleep for 10 seconds. Run the page
2) Run a second page that also does an update query, in the sl
> The default still depends on the DB and how it is set up.
>
> "If you do not specify a value for the isolation attribute,
> ColdFusion uses the default isolation level for the
> associated database."
>
> http://livedocs.macromedia.com/coldfusion/7/htmldocs/0346.htm
>
> For Oracle this is
The default still depends on the DB and how it is set up.
"If you do not specify a value for the isolation attribute, ColdFusion
uses the default isolation level for the associated database."
http://livedocs.macromedia.com/coldfusion/7/htmldocs/0346.htm
For Oracle this is often Read Committe
> Just remember that CF only *suggests* isolation levels and
> the DB actually does the isolation. There are four isolation
> levels, with most DB using read committed as the default level.
I don't think this is correct. When you specify an isolation level, that's
what the database uses. And, if
> Just what I thought, there is not tool available for this,
> neither in CF, nor ODBC, nor SQL. This is just unbelievable,
> now in 2006 with the database systems we have!
> This what so simple in 1980's, with dBase, clipper, Foxpro
> you name it, under DOS.
> All had Rlock() and Flock() functi
> > What are you saying here? That there is no way
> > (ncorrectly) to row lock in SQL?
>
> Not in the SQL standard as far as I know.
> Do you have an example ?
Locking, or more broadly, how concurrency is managed, is not described in
SQL. It has nothing to do with SQL - SQL is simply a language
> > ... in general, how would you lock a certain
> > record in a table while some one is working on it ?
>
> Cftransaction with the isolation attribute. Check the docs
> for the various options.
That's not going to get you very far. It will prevent two concurrent
requests from simultaneously man
On 9/7/06, Claude Schneegans <[EMAIL PROTECTED]> wrote:
> >>it's easier to treat every update as an insert, and just keep a
> history of who's done what when, and what was there before.
>
> Easier? This is just a patch to eventually fix problems by hand when
> they appear,
> PROVIDED someone finds
>>It is up to the vendor to supply locking, it will never be in a standard
Ok, it's up to vendors to supplies things to compensate for lacks in the
standard,
not the contrary.
I mean if vendors develop new options, it is because they are somehow
necessary,
then it's a reason good enough why it
On 9/7/06, Robertson-Ravo, Neil wrote:
> What are you saying here? That there is no way (ncorrectly) to row lock in
> SQL?
If he is I'd agree with him, insofar as the web/intranet environment
is concerned.
I wrote LockMonger to manage the locking of records at the application
level. Doesn't do d
-
From: Claude Schneegans
To: CF-Talk
Sent: Thu Sep 07 23:00:30 2006
Subject: Re: Locking Theory
>>What are you saying here? That there is no way (ncorrectly) to row
lock in
SQL?
Not in the SQL standard as far as I know.
Do you have an example ?
--
__
xhibitions."
Visit our website at http://www.reedexpo.com
-Original Message-
From: Claude Schneegans
To: CF-Talk
Sent: Thu Sep 07 23:00:30 2006
Subject: Re: Locking Theory
>>What are you saying here? That there is no way (ncorrectly) to row
lock in
SQL?
Not in the SQL standard
>>What are you saying here? That there is no way (ncorrectly) to row
lock in
SQL?
Not in the SQL standard as far as I know.
Do you have an example ?
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send
--Original Message-
From: Claude Schneegans
To: CF-Talk
Sent: Thu Sep 07 22:20:38 2006
Subject: Re: Locking Theory
>>The problem it seems is that you are expecting a web app to behave like
a classic client server app. Unfortunately, HTTP is a stateless
protocol, and simply doesn't behave
>>The problem it seems is that you are expecting a web app to behave like
a classic client server app. Unfortunately, HTTP is a stateless
protocol, and simply doesn't behave the same way.
Please, I'm not that dumb, I know what an application is compared to a
simple web page.
All the same, we col
g statelessness of HTTP.
> -Original Message-
> From: Claude Schneegans [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 07, 2006 3:29 PM
> To: CF-Talk
> Subject: Re: Locking Theory
>
> >>it's easier to treat every update as an insert, and just keep a
&g
>>it's easier to treat every update as an insert, and just keep a
history of who's done what when, and what was there before.
Easier? This is just a patch to eventually fix problems by hand when
they appear,
PROVIDED someone finds it, not really a method to PREVENT problems from
happening.
>>D
On 9/7/06, Claude Schneegans <[EMAIL PROTECTED]> wrote:
> >>cftransaction could lock for the duration of user edit ... it however,
> depending on the DB level of locking could prevent any other user from
> reading data in the table. I would consider implementing such locking a bug
> not a feature.
>>cftransaction could lock for the duration of user edit ... it however,
depending on the DB level of locking could prevent any other user from
reading data in the table. I would consider implementing such locking a bug
not a feature.
Of course, db level locking is abusive.
One should be able to
ROTECTED]>
To: "CF-Talk"
Sent: Thursday, September 07, 2006 10:13 AM
Subject: Re: Locking Theory
> Select with no lock should allow results to be returned - though it will
> effectively be a dirty read.
>
>
>
>
>
>
>
> "This e-mail is from Reed
edexpo.com
-Original Message-
From: Tom Kitta
To: CF-Talk
Sent: Thu Sep 07 17:03:20 2006
Subject: Re: Locking Theory
cftransaction could lock for the duration of user edit ... it however,
depending on the DB level of locking could prevent any other user from
reading data in the tabl
cftransaction could lock for the duration of user edit ... it however,
depending on the DB level of locking could prevent any other user from
reading data in the table. I would consider implementing such locking a bug
not a feature.
Try the following in SQL server query analyzer: Begin Tran [so
>>I assume that users want to look at the record, make changes and save
without having to worry about any other user doing the same thing to the
same record at the same time.
Depends what you call "user", I would rather say "developer" here.
For me, the user is the one who uses the applications I
>>I was pointing out locking of variables in scope rather than DB locking.
Exact, but locking variables is just a way of doing some db locking,
otherwise, it is not very useful. The real thing is db locking.
And what if you have two different applications using the same database?
--
___
>>But perhaps you are asking how to allow someone to edit data without
being overwritten by someone editing the same data.
Well, this is exactly what one would call a "race condition", isn't it ?
>>Sally's update has overwritten Bobs
Exact, this is actually what should be avoided in any datab
e - we are talking ms here not even seconds.
TK
- Original Message -
From: "Claude Schneegans" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Thursday, September 07, 2006 10:48 AM
Subject: Re: Locking Theory
> >>Cftransaction with the isolation attribute.
>
Talk"
Sent: Thursday, September 07, 2006 10:23 AM
Subject: RE: Locking Theory
> Cftransaction with the isolation attribute. Check the docs for the
> various options.
>
>> -Original Message-
>> From: Claude Schneegans [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, Sept
>>Cftransaction with the isolation attribute.
Ok, but the lock terminates with the closing http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAIL PROTECTED])
Thanks.
~|
Introducin
--
YOU Wrote:
but in general, how would you lock a certain record in a table while some
one is working on it ?
This is a bit different from the discussion so far - which has concentrated
on locking as it pertains to avoiding errors and race conditions. You can
lock a transa
I was pointing out locking of variables in scope rather than DB locking.
-Original Message-
From: Claude Schneegans [mailto:[EMAIL PROTECTED]
Sent: 07 September 2006 15:23
To: CF-Talk
Subject: Re: Locking Theory
>>Cflock, ensures that this never happens, as Ben will only e
Cftransaction with the isolation attribute. Check the docs for the
various options.
> -Original Message-
> From: Claude Schneegans [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 07, 2006 10:19 AM
> To: CF-Talk
> Subject: Re: Locking Theory
>
> Ok, the definit
>>Cflock, ensures that this never happens, as Ben will only ever be able to
chance value a to "Ben" after you have finished with it.
Yes, but what is locked is the template in which one can modify ANY
record in
the database, not just "Jenny", so no one should be able to modify
anything during t
Ok, the definitions says :
"A race condition occurs when multiple processes access and manipulate
the same data concurrently"
I remember, when I was working on applications in Clipper ages ago, we
had to lock records or files
in the database, and those locks were managed by DOS, not by the
appl
http://en.wikipedia.org/wiki/Race_condition
http://searchstorage.techtarget.com/sDefinition/0,,sid5_gci871100,00.html
http://www.tech-faq.com/race-condition.shtml
Mark
On 9/7/06, Jenny Gavin-Wear <[EMAIL PROTECTED]> wrote:
> Hi Ben,
>
> Could you explain the term "race condition" please?
>
> Jenn
Thanks Neil
-Original Message-
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED]
Sent: 07 September 2006 07:53
To: CF-Talk
Subject: Re: Locking Theory
A race condition is a condition is where a value may be not what you expect
when you get/set it. This is normally the case in
switchboard on +44 (0) 20 89107910. The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions."
Visit our website at http://www.reedexpo.com
-Original Message-
From: Jenny Gavin-Wear
To: CF-Talk
Sent: Thu Sep 07 07:43:30 2006
Subject
Hi Ben,
Could you explain the term "race condition" please?
Jenny
-Original Message-
From: Ben Nadel [mailto:[EMAIL PROTECTED]
Sent: 06 September 2006 23:30
To: CF-Talk
Subject: RE: Locking Theory
In my opinion, only use CFLock when you care if the race condition matters.
Ta
> Okay.. so, is cflock in or out these days?
If you're still working with CF 5 or earlier, locking is very important if
you want your server to continue working. If you're using a newer version,
locking is only needed when you may have a race condition. Race conditions
arise when you have concurre
In my opinion, only use CFLock when you care if the race condition matters.
Take setting SESSION values for instance. Let's say you have the following
code:
This would NOT require a lock. Yes, it's shared data. Yes you could have
conflicts. But the question is, does it matter? If a user has two
80 matches
Mail list logo
