Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
> Acknowledged. My (admittedly conservative) approach is to prove that the app > _doesn't_ need locking rather than the reverse. Don't get me wrong, I'm all > in favour of > eliminating both the extra coding burden as well as the overhead. That said, > the docs > seem to lean towards locking when in doubt... that and the fact I've had the > whole > locking religion hammered home relentlessly over the years. It may take me a > while to > get with the new program. ;-) The docs were originally written in a time when failure to lock religiously often caused the server to crash. In my experience, locking is usually not needed with CF 6+. Going back to the questions I originally asked you about whether you care about race conditions - those are the questions you need to answer to implement locking optimally. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323070 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
@ Jason >Are there Ajax or iframe calls or something that could be making >updates to that session var for that user at the same time in a >situation where the order of execution could cause an unexpected value >to be read back out by the server? Yup... >To me, it just seems that you almost need to prove out that your >application is likely to allow a true race condition before locking >every call. Acknowledged. My (admittedly conservative) approach is to prove that the app _doesn't_ need locking rather than the reverse. Don't get me wrong, I'm all in favour of eliminating both the extra coding burden as well as the overhead. That said, the docs seem to lean towards locking when in doubt... that and the fact I've had the whole locking religion hammered home relentlessly over the years. It may take me a while to get with the new program. ;-) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323060 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
@Jay Yes, fair enough. Granted that CF doesn't guarantee the order of execution when processing threads in a request, does the application in this case really have the possibility of having multiple threads making sequential changes to that specific session variable (which is thread-safe to that user already) on different threads at the same time? Are there Ajax or iframe calls or something that could be making updates to that session var for that user at the same time in a situation where the order of execution could cause an unexpected value to be read back out by the server? That would be a very rare condition, but if it's possible, then, yes, you should lock. As for reading from the application scope, as in the OP example, that's at least going to be covered by the thread-safe nature of MX, as noted in Simon's entry. To me, it just seems that you almost need to prove out that your application is likely to allow a true race condition before locking every call. Even when I used to do some fairly complex apps that happened to be iframe-driven, I only let one frame ever *write* to persistent scope, the others were only reads, so for something like session or client scope, the move from 4.5 to MX was a great relief in that regard. But certainly to each his own :) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323058 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
>Then lock the application scope with a read-only lock, copy the value >into a local variable, then lock the session scope with an exclusive >lock, then copy the local variable into the session. Roger that... Thanks, Dave & all. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
On Mon, Jun 1, 2009 at 16:59, Jay Greer wrote: > > @Dave > >> What version of CF is this? > > CF-8.1 > >> Is there any likelihood of a race condition with Application.bar? > > It's possible, yes. > >> If there is the likelihood of a race condition with that variable, do >> I care? That is, could the race condition cause an improper value of >> any significance? > > Definitely... > >> Is there any likelihood of a race condition with Session.foo? > > It's possible, yes. > >> If there is, do I care? > > Definitely... Then lock the application scope with a read-only lock, copy the value into a local variable, then lock the session scope with an exclusive lock, then copy the local variable into the session. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323055 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
@Jason >Assuming you're on CFMX 7 or 8, you no longer need to LOCK on READ... Well that's debatable for sure... http://www.horwith.com/index.cfm/2008/4/28/cflock-explained http://www.horwith.com/index.cfm/2008/7/17/CFLOCK-further-explained ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323054 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
@Dave > What version of CF is this? CF-8.1 > Is there any likelihood of a race condition with Application.bar? It's possible, yes. > If there is the likelihood of a race condition with that variable, do > I care? That is, could the race condition cause an improper value of > any significance? Definitely... > Is there any likelihood of a race condition with Session.foo? It's possible, yes. > If there is, do I care? Definitely... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323052 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
> That would work, but it's unnecessary overhead unless you're still on CF 5. > Virtually all that locking has been handled in MX. Well, no, that's actually not correct. CF doesn't lock variables unless you use CFLOCK. It simply doesn't crash if you don't lock them, as it used to with CF 5 and earlier. But if you have the potential for a race condition, and you care about the outcome of said condition, you need to lock variables. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323041 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
That would work, but it's unnecessary overhead unless you're still on CF 5. Virtually all that locking has been handled in MX. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323037 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
> Is there any reason you can't simply lock the app scope, grab application variable and put it into a local variable, unlock, lock session, copy from local var to session var, unlock? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323033 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
Assuming you're on CFMX 7 or 8, you no longer need to LOCK on READ, so if you're concerned about race conditions on the WRITE, just do: (timeout can be whatever is appropriate) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323032 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Issue - Read value from APPLICATION scope & write it to SESSION scope
> Having a brain cramp on this one guys... > > How would one approach locking when reading a value from the APPLICATION > scope and subsequently writing it to the SESSION scope..? > > EXAMPLE > -- > > It depends. What version of CF is this? Is there any likelihood of a race condition with Application.bar? If there is the likelihood of a race condition with that variable, do I care? That is, could the race condition cause an improper value of any significance? Is there any likelihood of a race condition with Session.foo? If there is, do I care? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323031 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Shared Variables
You surround code that needs to be locked with cflock and /cflock statements. Also you are using a session lock with an application var. Don't take it wrong but the manual is probably a good place to go to get a handle on this. Using Persistent Data and Locking: http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/sharedVars.htm#1159066 -- -...@robertson-- Janitor, The Robertson Team mysecretbase.com ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317010 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Shared Variables
The ColdFusion manuals are really good and they are also free. I
recommend looking there for a good description of locking.
-Mike Chabot
On Fri, Dec 19, 2008 at 4:24 PM, Steve LaBadie wrote:
> I am using the Alagad Captcha Component and am not familiar with CFLOCK.
> Is this correct for locking shared variables on the application.cfm
> page? Do I need to add conditional expression also?
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Captcha").configure(expandPath("."), "XXX") />
>
>
>
>
>
> Steve LaBadie, Web Manager
>
> East Stroudsburg University
>
> 200 Prospect St.
>
> East Stroudsburg, Pa 18301
>
> 570-422-3999
>
> http://www.esu.edu
>
> slaba...@po-box.esu.edu
>
>
>
>
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317003
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking session variables in cf8
To ditto what has already been said, but also to clarify: It is not necessary to lock your shared scope access for the purpose of not obtaining corrupted reads. i.e. process 1 attempts to read large session variable while session 2 is writing to it. ColdFusion will take care of that for you and won't let you read a shared variable that is currently in the process of being written. What is necessary though, is using locks to eliminate race-conditions in code. An example would be where process 1 needs to read from a session variable, perform some computations and read/write that variable again and it is mandatory that the value of the variable did not change since it was first read. A second process could have modified the contents of the session variable after the first process read it but before the second process accessed it again. If you are trying to prevent something like that, then you would probably want to use a lock. Another common idiom where locking is required is when you want to check for the existence of a shared variable (session/application/server), and if it is not there create it where the creation is a costly operation. A lock would prevent multiple processes from trying to instantiate the shared variable all at the same time. ~Brad - Original Message - From: "j s" <[EMAIL PROTECTED]> To: "cf-talk" Sent: Sunday, December 07, 2008 3:12 PM Subject: locking session variables in cf8 > When creating session structures is it still necessary to cflock in cf8? I > think i read somewhere that it was no longer needed but I was probably > dreaming. > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316405 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking session variables in cf8
Lock to avoid problems with race conditions in the places where these might be an issue. Many Web applications have places where unhandled race conditions can be a problem. -Mike Chabot On Sun, Dec 7, 2008 at 4:36 PM, Matt Quackenbush <[EMAIL PROTECTED]> wrote: > In virtually every case, locking session vars has been completely > unnecessary since CFMX (6.0) came out. > > > On Sun, Dec 7, 2008 at 3:12 PM, j s <[EMAIL PROTECTED]> wrote: > >> When creating session structures is it still necessary to cflock in cf8? I >> think i read somewhere that it was no longer needed but I was probably >> dreaming. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316398 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: locking session variables in cf8
In virtually every case, locking session vars has been completely unnecessary since CFMX (6.0) came out. On Sun, Dec 7, 2008 at 3:12 PM, j s <[EMAIL PROTECTED]> wrote: > When creating session structures is it still necessary to cflock in cf8? I > think i read somewhere that it was no longer needed but I was probably > dreaming. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Teddy Payne wrote: > I typically don't make my UUIDs my primary keys are I prefer database to > perform binary searchings on numbers and 35 character strings. A UUID/GUID is a number. Its string representation may be a bit funky, but it is still just a 128 bit number. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255918 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Yes, that's the one. It's quite good, although it isn't a TOAD-killer (but there again what is?). On 10/8/06, Dave Watts <[EMAIL PROTECTED]> wrote: > > > Because Oracle has a Free tool now that does a lot of > > > the same work. > > > > What Free tool from Quest does the same thing as TOAD? > > I think James is referring to Oracle's new free SQL Developer: > > http://www.oracle.com/technology/software/products/sql/index.html > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more information! > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255911 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: locking, I think
> > Because Oracle has a Free tool now that does a lot of > > the same work. > > What Free tool from Quest does the same thing as TOAD? I think James is referring to Oracle's new free SQL Developer: http://www.oracle.com/technology/software/products/sql/index.html Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255892 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: locking, I think
James, What Free tool from Quest does the same thing as TOAD? I have been a TOAD user for many years and I didn't know that Quest had a free version like Toad. Dave -Original Message- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Saturday, October 07, 2006 2:32 AM To: CF-Talk Subject: Re: locking, I think That'll work fine. Out of interest, why can't you get TOAD to manage your Oracle DB? Too expensive or some other reason? Because Oracle has a Free tool now that does a lot of the same work. On 10/7/06, daniel kessler <[EMAIL PROTECTED]> wrote: > alright, I'm gonna try to get the number from DUAL and insert that. > > I'd like to try triggers, but the last time I tried to make them, I had problems. We aren't allowed to use a GUI (managing tool) to work with the DB. Admittedly, I was pretty much a complete novice at the time. So when I have time I'd like to try again. > > Thanks for the sequence information. I had once learned that it's its own table but I had forgotten completely. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255887 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
That'll work fine. Out of interest, why can't you get TOAD to manage your Oracle DB? Too expensive or some other reason? Because Oracle has a Free tool now that does a lot of the same work. On 10/7/06, daniel kessler <[EMAIL PROTECTED]> wrote: > alright, I'm gonna try to get the number from DUAL and insert that. > > I'd like to try triggers, but the last time I tried to make them, I had > problems. We aren't allowed to use a GUI (managing tool) to work with the > DB. Admittedly, I was pretty much a complete novice at the time. So when I > have time I'd like to try again. > > Thanks for the sequence information. I had once learned that it's its own > table but I had forgotten completely. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255876 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
alright, I'm gonna try to get the number from DUAL and insert that. I'd like to try triggers, but the last time I tried to make them, I had problems. We aren't allowed to use a GUI (managing tool) to work with the DB. Admittedly, I was pretty much a complete novice at the time. So when I have time I'd like to try again. Thanks for the sequence information. I had once learned that it's its own table but I had forgotten completely. >Yes, but then we are back to the original question of how to get that >last ID from the DB so the code can use it. > >To be completely safe, there are two alternatives: > >1) Use a trigger on the table to perform an "autoincrement" operation >with the sequence. Wrap the INSERT and the subsequent SELECT >MAX(someID) in a cftransaction, with an isolation level of >serializable. This last bit is important. > >2) Get the value from the sequence first and then do the insert, >remembering the value that was obtained from the sequence. This can be >done in a stored procedure or in CF code. > >On 10/6/06, Aaron Rouse <[EMAIL PROTECTED]> wrote: >> For people who are used to SQL server, the best thing to do would be to >> setup a trigger and that trigger in Oracle could grab the next sequence >> value and insert it when new data is being inserted into the table. > >-- >CFAJAX docs and other useful articles: >http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255814 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: locking, I think
> scope_identity and @@identity are one type of solution. Primary Keys that > are autoincremented are hard to follow soemtimes and are a bit tricky to > retrieve sometimes on high transactional systems. Which is exactly why I prefer Oracle's (and PostgreSQL as well, incidentally) method - you grab a value from the sequence first, and then apply it as your PK, FK's, in the subsequent insert(s). > An easier way would be > just create a GUID or UUID that would gurantee uniqueness that is not > incremented. A very good suggestion, particularly if database portability is the goal. Since virtually every DBMS uses a slightly different method for generating unique key values, this is really the only practical way to achieve real portability. > I typically don't make my UUIDs my primary keys are I prefer database to > perform binary searchings on numbers and 35 character strings. I do > advocate that you put a non-clustered index on the column for easier > retrieval as if to treat it like a foreign key. And that's sort of the rub - UUIDs have some drawbacks as you describe. Also, in a case where a PK value may end up as a URL query string, your URLs start getting rather long and unfriendly if you use UUID. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255793 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: locking, I think
I have never seen an indentity never beingretrieved on a high transaction system (well built that is) or not, I mean that is the whole point of them! I do like your solution as well though, both should work. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: Teddy Payne To: CF-Talk Sent: Fri Oct 06 14:29:37 2006 Subject: Re: locking, I think scope_identity and @@identity are one type of solution. Primary Keys that are autoincremented are hard to follow soemtimes and are a bit tricky to retrieve sometimes on high transactional systems. An easier way would be just create a GUID or UUID that would gurantee uniqueness that is not incremented. In CF this makes unique tables really simple. You create your UUID in the CF page and then insert the record into the DB. What this enables you to ahve a unique value for a given table that you do not have to call an aggregate function or requery the database to get the primary key. I typically don't make my UUIDs my primary keys are I prefer database to perform binary searchings on numbers and 35 character strings. I do advocate that you put a non-clustered index on the column for easier retrieval as if to treat it like a foreign key. Teddy On 10/6/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > I don't know that I see the wisdom in creating a trigger for every table > in a database just so someone doesn't have to learn how to use a > sequence. > > Practically speaking, sequences are rather easy to use, so it seems like > unnecessary effort to create and maintain a bunch of triggers. > > Philosophically, I am of the mind that developers should take the time > to know the environment they are working in, rather than just trying to > make it behave like something else they are more comfortable with. > > Oracle is different in many ways from SQL Server - you can't shield > yourself from all of it, and some Oracle specific features (connect > by...prior comes to mind) can be really valuable to know. The reverse, > of course, is also true. > > > -Original Message- > > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > > Sent: Thursday, October 05, 2006 4:46 PM > > To: CF-Talk > > Subject: Re: locking, I think > > > > For people who are used to SQL server, the best thing to do would be > to > > setup a trigger and that trigger in Oracle could grab the next > sequence > > value and insert it when new data is being inserted into the table. > > > > On 10/5/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > > > > > > > > You can access the sequence in a couple of ways. One is using > > > foo_sq.NextVal directly in the insert statement, as the original > poster > > > was doing. > > > > > > The other is to select it from DUAL, which is a sort of utility > table in > > > Oracle, like so: > > > > > > Select foo_sq.NextVal as id > > > From DUAL > > > > > > > > > > > > -- > > Aaron Rouse > > http://www.happyhacker.com/ > > > > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255788 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
scope_identity and @@identity are one type of solution. Primary Keys that are autoincremented are hard to follow soemtimes and are a bit tricky to retrieve sometimes on high transactional systems. An easier way would be just create a GUID or UUID that would gurantee uniqueness that is not incremented. In CF this makes unique tables really simple. You create your UUID in the CF page and then insert the record into the DB. What this enables you to ahve a unique value for a given table that you do not have to call an aggregate function or requery the database to get the primary key. I typically don't make my UUIDs my primary keys are I prefer database to perform binary searchings on numbers and 35 character strings. I do advocate that you put a non-clustered index on the column for easier retrieval as if to treat it like a foreign key. Teddy On 10/6/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > I don't know that I see the wisdom in creating a trigger for every table > in a database just so someone doesn't have to learn how to use a > sequence. > > Practically speaking, sequences are rather easy to use, so it seems like > unnecessary effort to create and maintain a bunch of triggers. > > Philosophically, I am of the mind that developers should take the time > to know the environment they are working in, rather than just trying to > make it behave like something else they are more comfortable with. > > Oracle is different in many ways from SQL Server - you can't shield > yourself from all of it, and some Oracle specific features (connect > by...prior comes to mind) can be really valuable to know. The reverse, > of course, is also true. > > > -Original Message- > > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > > Sent: Thursday, October 05, 2006 4:46 PM > > To: CF-Talk > > Subject: Re: locking, I think > > > > For people who are used to SQL server, the best thing to do would be > to > > setup a trigger and that trigger in Oracle could grab the next > sequence > > value and insert it when new data is being inserted into the table. > > > > On 10/5/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > > > > > > > > You can access the sequence in a couple of ways. One is using > > > foo_sq.NextVal directly in the insert statement, as the original > poster > > > was doing. > > > > > > The other is to select it from DUAL, which is a sort of utility > table in > > > Oracle, like so: > > > > > > Select foo_sq.NextVal as id > > > From DUAL > > > > > > > > > > > > -- > > Aaron Rouse > > http://www.happyhacker.com/ > > > > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255784 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: locking, I think
I don't know that I see the wisdom in creating a trigger for every table in a database just so someone doesn't have to learn how to use a sequence. Practically speaking, sequences are rather easy to use, so it seems like unnecessary effort to create and maintain a bunch of triggers. Philosophically, I am of the mind that developers should take the time to know the environment they are working in, rather than just trying to make it behave like something else they are more comfortable with. Oracle is different in many ways from SQL Server - you can't shield yourself from all of it, and some Oracle specific features (connect by...prior comes to mind) can be really valuable to know. The reverse, of course, is also true. > -Original Message- > From: Aaron Rouse [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 05, 2006 4:46 PM > To: CF-Talk > Subject: Re: locking, I think > > For people who are used to SQL server, the best thing to do would be to > setup a trigger and that trigger in Oracle could grab the next sequence > value and insert it when new data is being inserted into the table. > > On 10/5/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > > > > > You can access the sequence in a couple of ways. One is using > > foo_sq.NextVal directly in the insert statement, as the original poster > > was doing. > > > > The other is to select it from DUAL, which is a sort of utility table in > > Oracle, like so: > > > > Select foo_sq.NextVal as id > > From DUAL > > > > > > > -- > Aaron Rouse > http://www.happyhacker.com/ > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255774 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
On Friday 06 October 2006 06:23, James Holmes wrote: > 2) Get the value from the sequence first and then do the insert, > remembering the value that was obtained from the sequence. This can be > done in a stored procedure or in CF code. We did this in CF, and it worked fine. -- Tom Chiverton Helping to enthusiastically benchmark fine-grained users This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255768 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Lol, that will teach me to scroll down on my blackberry and not accept "more" :-) "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: James Holmes To: CF-Talk Sent: Fri Oct 06 10:34:33 2006 Subject: Re: locking, I think Because we are using Oracle. On 10/6/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > Not sure why you would need a trigger when SCOPE_IDENTITY would do the trick > (or @@identity depending on your needs) -- ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255767 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Because we are using Oracle. On 10/6/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > Not sure why you would need a trigger when SCOPE_IDENTITY would do the trick > (or @@identity depending on your needs) -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255765 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Not sure why you would need a trigger when SCOPE_IDENTITY would do the trick (or @@identity depending on your needs) "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: James Holmes To: CF-Talk Sent: Fri Oct 06 06:23:34 2006 Subject: Re: locking, I think Yes, but then we are back to the original question of how to get that last ID from the DB so the code can use it. To be completely safe, there are two alternatives: 1) Use a trigger on the table to perform an "autoincrement" operation with the sequence. Wrap the INSERT and the subsequent SELECT MAX(someID) in a cftransaction, with an isolation level of serializable. This last bit is important. 2) Get the value from the sequence first and then do the insert, remembering the value that was obtained from the sequence. This can be done in a stored procedure or in CF code. On 10/6/06, Aaron Rouse <[EMAIL PROTECTED]> wrote: > For people who are used to SQL server, the best thing to do would be to > setup a trigger and that trigger in Oracle could grab the next sequence > value and insert it when new data is being inserted into the table. -- ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255762 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Yes, but then we are back to the original question of how to get that last ID from the DB so the code can use it. To be completely safe, there are two alternatives: 1) Use a trigger on the table to perform an "autoincrement" operation with the sequence. Wrap the INSERT and the subsequent SELECT MAX(someID) in a cftransaction, with an isolation level of serializable. This last bit is important. 2) Get the value from the sequence first and then do the insert, remembering the value that was obtained from the sequence. This can be done in a stored procedure or in CF code. On 10/6/06, Aaron Rouse <[EMAIL PROTECTED]> wrote: > For people who are used to SQL server, the best thing to do would be to > setup a trigger and that trigger in Oracle could grab the next sequence > value and insert it when new data is being inserted into the table. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: locking, I think
For people who are used to SQL server, the best thing to do would be to setup a trigger and that trigger in Oracle could grab the next sequence value and insert it when new data is being inserted into the table. On 10/5/06, Doug Bezona <[EMAIL PROTECTED]> wrote: > > > You can access the sequence in a couple of ways. One is using > foo_sq.NextVal directly in the insert statement, as the original poster > was doing. > > The other is to select it from DUAL, which is a sort of utility table in > Oracle, like so: > > Select foo_sq.NextVal as id > From DUAL > > -- Aaron Rouse http://www.happyhacker.com/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255715 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: locking, I think
Oracle works completely differently from SQL Server in this regard.
There is no Identity type in Oracle. Instead, it has a mechanism called
a sequence, which is a separate object that provides a sequence of
single use values which are used to provide unique key values.
So, if you have a table "foo", you would create a sequence called, say,
"foo_sq". You can define various parameters for your sequence - start
value, increment value, etc.
Now whenever you create a record in foo, you grab a value from foo_sq
and use it as your PK id. Once that value is used, foo_sq will never
provide it again. It's basically the equivalent of having a table of
sequential numbers with a trigger that deletes a value from the table
when it's selected.
You can access the sequence in a couple of ways. One is using
foo_sq.NextVal directly in the insert statement, as the original poster
was doing.
The other is to select it from DUAL, which is a sort of utility table in
Oracle, like so:
Select foo_sq.NextVal as id
>From DUAL
So id is your sequence value, and you can now pass it to your insert
query to use as the PK id, as well as return it to the user, as the OP
was trying to do.
It's just one of those quirky Oracle things. Personally, I really like
sequences, but if you're used to SQL Server, it's an odd beast.
> -Original Message-
> From: Doug Brown [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 05, 2006 3:46 PM
> To: CF-Talk
> Subject: Re: locking, I think
>
> Here is a little snippet that may help, of course since you are using
> Oracle
> the syntax would differ.
>
> Using Identity Keys in SQL Server
> Most applications rely on primary keys in database tables. Many of
those
> same applications use the auto-increment capabilities of some DBMSes
like
> MSSQL Server. When inserting a record in a table that uses
auto-increment
> primary keys, you do not have to insert the value of the primary key
> yourself - the DB does it for you. But what if you want to know the
value
> of
> the row's primary key that you just inserted? Commonly, developers
will
> run
> two queries in a row, wrapped in . The first one
inserts
> the
> record and the second one selects the newest record. But using this
> syntax,
> SQL server can insert the record as well as return the value of the
new
> primary key all in one query.
>
>
>set nocount on
>insert into customer (firstName, lastName)
>values
>('#attributes.firstName#', '#attributes.LastName#')
>select @@identity as newID
>set nocount off
>
>
> I am the new primary key value:
> #insert.newID#
>
>
>
> - Original Message -
> From: "Daniel Kessler" <[EMAIL PROTECTED]>
> To: "CF-Talk"
> Sent: Thursday, October 05, 2006 12:01 PM
> Subject: locking, I think
>
>
> > I have an oracle database and I set the ID of a database entry
using:
> > unique_repair_ticket_s.NEXTVAL - which is basically a counter
> >
> > I then need to get that ID and send it to the user. Is the process
> > for this to then query the database for the last entry? If so, do I
> > surround it with a cftransaction or a cflock to inhibit others
> > writing to it until that query is complete so that the last number
is
> > actually the number that I need?
> >
> > thank you.
> >
> > --
> >
> > Daniel Kessler
> >
> > College of Health and Human Performance
> > University of Maryland
> > Suite 2387 Valley Drive
> > College Park, MD 20742-2611
> > Phone: 301-405-2545
> > http://hhp.umd.edu
> >
> >
> >
> >
> >
>
>
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255713
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: locking, I think
Here is a little snippet that may help, of course since you are using Oracle
the syntax would differ.
Using Identity Keys in SQL Server
Most applications rely on primary keys in database tables. Many of those
same applications use the auto-increment capabilities of some DBMSes like
MSSQL Server. When inserting a record in a table that uses auto-increment
primary keys, you do not have to insert the value of the primary key
yourself - the DB does it for you. But what if you want to know the value of
the row's primary key that you just inserted? Commonly, developers will run
two queries in a row, wrapped in . The first one inserts the
record and the second one selects the newest record. But using this syntax,
SQL server can insert the record as well as return the value of the new
primary key all in one query.
set nocount on
insert into customer (firstName, lastName)
values
('#attributes.firstName#', '#attributes.LastName#')
select @@identity as newID
set nocount off
I am the new primary key value:
#insert.newID#
- Original Message -
From: "Daniel Kessler" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Thursday, October 05, 2006 12:01 PM
Subject: locking, I think
> I have an oracle database and I set the ID of a database entry using:
> unique_repair_ticket_s.NEXTVAL - which is basically a counter
>
> I then need to get that ID and send it to the user. Is the process
> for this to then query the database for the last entry? If so, do I
> surround it with a cftransaction or a cflock to inhibit others
> writing to it until that query is complete so that the last number is
> actually the number that I need?
>
> thank you.
>
> --
>
> Daniel Kessler
>
> College of Health and Human Performance
> University of Maryland
> Suite 2387 Valley Drive
> College Park, MD 20742-2611
> Phone: 301-405-2545
> http://hhp.umd.edu
>
>
>
>
>
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255711
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: locking, I think
In cases where I need to use the ID immediately, I use a separate query to grab the sequence value first Select unique_repair_ticket_s.NEXTVAL as id >From dual (dual, if you don't already know, being a sort of "dummy" table that you use for this sort of thing) Then use the result of this query for your insert, e.g. Insert into table (ID,field1,field2) Values (#query.id#,etc) This way you can be sure you have the correct id value and don't have to worry about the issues you mentioned. > -Original Message- > From: Daniel Kessler [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 05, 2006 2:01 PM > To: CF-Talk > Subject: locking, I think > > I have an oracle database and I set the ID of a database entry using: > unique_repair_ticket_s.NEXTVAL - which is basically a counter > > I then need to get that ID and send it to the user. Is the process > for this to then query the database for the last entry? If so, do I > surround it with a cftransaction or a cflock to inhibit others > writing to it until that query is complete so that the last number is > actually the number that I need? > > thank you. > > -- > > Daniel Kessler > > College of Health and Human Performance > University of Maryland > Suite 2387 Valley Drive > College Park, MD 20742-2611 > Phone: 301-405-2545 > http://hhp.umd.edu > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255695 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
Jochem, Ah... Thanks for the info. As always - when it comes to SQL and DB stuff you are number 1 in my book :) This is pretty much what I suspected. It looks like a pretty hefty labor for all but the simplest of implementations. Thanks for the clarification. -Mark -Original Message- 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 (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 >SELECT * FROM users WHERE username = 'bob' > WITH (updlock) > > ... What happens here? Does the JDBC throw an error? It will wait until statement timeout. I don't know if that is recoverable in MS SQL Server (i.e., I don't know if User B can continue its transaction or has to rollback and try again). > How would we be able to determine (using SQL) that the record > is indeed locked for updating without would we have to trap a specific > error perhaps? In Oracle and PostgreSQL I would force the issue by using SELECT .. FOR UPDATE NOWAIT which will force an immediate error if some other transaction has the lock. Again, don't know about MS SQL Server. > THIRD: Finally, User "A" is done and decides she > 1) Wants to update the record ... So she runs > UPDATE users SET name = 'bob smith' > WHERE username = 'bob' > ... Is this update sufficient to release the lock? No, you need to commit the transaction. > ... Since JDBC will draw a connection from the connection pool - how > will the database know that this update is the same user that locked > the record previously? Wouldn't we have to use individual > usernames/permissions for the DB to make that work? You need to maintain the transaction between requests, i.e. you need the magic CFX that Claude can write for you. > 2) She decides that she doesn't want to do an update and cancels out >or closes the page. > ... How would we go about releasing this specific lock? Between the database and Claude's CFX it is just a rollback. I don't know how it is supposed to work betweeen the CFML page that talks to the CFX and the browser. I suppose some sort of timeout, and AJAX call or a gateway call in onSessionEnd. > I'm having some trouble figuring out how this might be implemented... > If it's possible or worth it It is possible, but I don't think it is worth it. Even when the magic CFX works there is always the issue of clustering: you are going to need a restartable, interweaveable XA datasource to make that work and the locking implications of that are downright scary. Like I said, I will just stick to optimistic record locking. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252646 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 >SELECT * FROM users WHERE username = 'bob' > WITH (updlock) > > ... What happens here? Does the JDBC throw an error? It will wait until statement timeout. I don't know if that is recoverable in MS SQL Server (i.e., I don't know if User B can continue its transaction or has to rollback and try again). > How would we be able to determine (using SQL) that the record is > indeed locked for updating without would we have to trap a specific error > perhaps? In Oracle and PostgreSQL I would force the issue by using SELECT .. FOR UPDATE NOWAIT which will force an immediate error if some other transaction has the lock. Again, don't know about MS SQL Server. > THIRD: Finally, User "A" is done and decides she > 1) Wants to update the record ... So she runs > UPDATE users SET name = 'bob smith' > WHERE username = 'bob' > ... Is this update sufficient to release the lock? No, you need to commit the transaction. > ... Since JDBC will draw a connection from the connection pool - how > will the database know that this update is the same user that locked the > record previously? Wouldn't we have to use individual usernames/permissions > for the DB to make that work? You need to maintain the transaction between requests, i.e. you need the magic CFX that Claude can write for you. > 2) She decides that she doesn't want to do an update and cancels out > or closes the page. > ... How would we go about releasing this specific lock? Between the database and Claude's CFX it is just a rollback. I don't know how it is supposed to work betweeen the CFML page that talks to the CFX and the browser. I suppose some sort of timeout, and AJAX call or a gateway call in onSessionEnd. > I'm having some trouble figuring out how this might be implemented... If > it's possible or worth it It is possible, but I don't think it is worth it. Even when the magic CFX works there is always the issue of clustering: you are going to need a restartable, interweaveable XA datasource to make that work and the locking implications of that are downright scary. Like I said, I will just stick to optimistic record locking. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252621 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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 care of the locks. It has always been my vision that CFLOCK should handle locks in the database, and not locks on scopes that it should be able to handle by itself (which is what it does now). -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252612 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> 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, I don't see why it couldn't be done through this mean. It's not a database problem! If all your users were, in fact, directly connected to the database, you could easily tell the database what kind of results you wanted, and leave it up to the database to figure out how to make that happen. But your users are not connected to the database. Instead, they periodically send messages to your application, which in turn sends responses to them. This makes it your application's problem, since your application is acting as a proxy for the actual users. Traditional approaches to locking will not get you very far, here. > By the way, apparently it is already implemented anyway, except with > Access datasources. I'm not sure what you mean by this. Row-level locking? How does that help you when you need multiple transactions to manage a single user interaction? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252609 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 course there is always the much overlooked manual. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252608 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 through CF and ODBC or JDBC, and could communicate with the same datasource using another tool developed in C or JAVA, a CFX tag, for some other tasks to be done on the same datasource. I do this every day with my ODBCinfo tag which returns all what I need to know about datasources, CF not even knowing about it, and even if CF has no datasource defined on the database. For each query, a connection is opened and closed, but it could be kept open as well, a handle returned to the programmer (not the user) until further requests. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252596 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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's a question of preference. But since it's a database problem, and SQL is the unique way the programmer deals with the database, I don't see why it couldn't be done through this mean. By the way, apparently it is already implemented anyway, except with Access datasources. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252595 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
Jochem,
Ok you threw me To summarize what you are saying:
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
SELECT * FROM users WHERE username = 'bob'
WITH (updlock)
... What happens here? Does the JDBC throw an error?
How would we be able to determine (using SQL) that the record is
indeed locked for updating without would we have to trap a specific error
perhaps?
THIRD: Finally, User "A" is done and decides she
1) Wants to update the record ... So she runs
UPDATE users SET name = 'bob smith'
WHERE username = 'bob'
... Is this update sufficient to release the lock?
... Since JDBC will draw a connection from the connection pool - how
will the database know that this update is the same user that locked the
record previously? Wouldn't we have to use individual usernames/permissions
for the DB to make that work?
... Should this query run an explicit commit and if so what's the
syntax?
...OR
2) She decides that she doesn't want to do an update and cancels out
or closes the page.
... How would we go about releasing this specific lock?
... You say "COMMIT" clears out all the locks .. So if I do
a...
COMMIT
...or..
ROLLBACK
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: Locking Theory
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252583
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 Systems mysecretbase.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252579 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 someone to read it for editing also. Which in turn means that your app design has to be built so that there is a 'view' screen' and a subsequent 'edit' screen, or the user has to in some other way positively activate an edit mode separate from a viewing mode. I've built apps like that in the past and I have yet to see a client happy with the fact that they have to do a 2-step process rather than a single step (i.e. the viewing screen is the edit screen). Its a situation where only the programmer likes the idea. s/he is making their problem the users' problem. Instead the app should be designed with the user experience in mind and the underlying mechanicals should conform accordingly. Handling this at the application level meets that usability goal. My own systems allow the user to see the data but there is no 'save' button onscreen, and typically a big red message informs them the record is in use by User X. Often they are granted the option of contacting User X, and User X's lock will expire after a set amount of idle time. further, an admin can break User X's lock manually in a pinch. While talk of db-level operations is fine as an intellectual exercise, these are not the sorts of things that make any sense to manage at the db level. You have to provide a level of user interaction in a fully featured app. Doing a row lock is MAYBE acceptable for the simplest apps but not in something enterprise level, or arguably any level. -- [EMAIL PROTECTED] Janitor, MSB Web Systems mysecretbase.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252578 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
> > 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 connection, I can control locks, > unlocks, who locked, etc. Are your users in your C program also, or are they using browsers to send HTTP requests? Because unless you want to define users out of your environment - as much as we'd all like to, sometimes - they will determine whether or not locking for the duration of a user's interaction is practical. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252577 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 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. > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252575 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 implementation or another. From the ones I read, I think the following might be interesting: Jim Melton, "SQL: 1999" and "Advanced SQL: 1999". Jim Melton is one of the editors of the SQL standard and this covers the 1999 version of the standard (including parts that are not implemented anywhere :). Dan Tow, "SQL Tuning". The first 5 chapters are about understanding query execution plans and provide a generalized mechanism for finding a fast one. After that it covers some implementations. Philip A. Bernstein, "Concurrency Control and Recovery in Database Systems". Not about SQL, but about database internals. Rather academic, but a must-read if you want to fully understand concurrency and serializability. And of course there is always the much overlooked manual. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252573 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> 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 > programmer tell the database > engine where Concurrency may happen and should be avoided? Every SQL statement can potentially run in a concurrent environment! 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. Instead, we tell the database what outcome we want, and we let the database figure out the implementation details. Likewise, SQL would be more difficult if we had to talk about leaves, pages, extents, etc. instead of tables and fields. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252571 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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 connection, I can control locks, unlocks, who locked, etc. But, since it appears that only Access datasources doesn't support row locks, I'm not sure this development would be worth anyway. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252570 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252569 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
What you are describing would need additional functionality since the updlock with SQL server doesn't prevent data reads occurring when lock is in place. There are about a dozen ways to implement what you suggest and definitely something like that had to be done. I would even add messaging 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 > 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 wants to open and lock the same > record, > for the same purpose, THEN he will get the message "sorry, this record > is locked", > BEFORE he edits the record, not after. > So for him, there is no time waisted, it's just the time for a coffee > break ;-) > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252565 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
> > 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 be able > to read that row, even if it does it from another connection. > I suppose it would be the same in Java with JDBC. But the environment isn't connected. You have no guarantee that the user will make a subsequent request, or when they'll make it, or even that the user hasn't been wiped off the face of the earth by a giant meteor. So, your connection logic will need to deal with that, and by doing this you're turning a fairly simple, reliable solution to concurrency into a complicated, unreliable solution that'll create bottlenecks. The simple, reliable solution to concurrency is to let your application track changes to records. For example, user A requests a record to edit. While user A is staring at his screen, trying to figure out what to change, user B requests the same record to edit. User B finishes quickly, and saves his changes. When user A attempts to save his changes, the application first checks the record to see if it's changed - for example, by comparing a timestamp field's value with the value it contained when user A first requested the record. If the values don't match, user A can't just overwrite user B's changes - how you specifically handle this is up to you. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252564 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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 wants to open and lock the same record, for the same purpose, THEN he will get the message "sorry, this record is locked", BEFORE he edits the record, not after. So for him, there is no time waisted, it's just the time for a coffee break ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252560 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 create an index. And when you can not afford to take the database offline for a few hours that is a big deal. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252558 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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" ... maybe there needs to be a bit more user friendly mechanism there, that would allow informing the user of someone else using the record etc. After all I would like to see the face of a user that worked for an hour just to find out that it was all a waste b/c of some Joe that is not releasing some record. TK - Original Message - From: "Claude Schneegans" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Friday, 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 can read the record. > The system will not tell him who has locked the row, but may be it is > better > like this ;-) > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252556 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252552 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 can read the record. The system will not tell him who has locked the row, but may be it is better like this ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252550 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 be able to read that row, even if it does it from another connection. I suppose it would be the same in Java with JDBC. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252547 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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. You would have to ask them why they don't just call it the same as the rest of the world. > - In MySql, there is only LOCK TABLES, http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html PostgreSQL, DB2, Mimer etc. all understand FOR UPDATE. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252548 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 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 programmer tell the database engine where Concurrency may happen and should be avoided? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252543 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
MS SQL locking is implemented as set of hints, for example: SELECT au_lname FROM authors WITH (NOLOCK) (dirty read - fast but dirty :) or for update as in SELECT au_lname FROM authors WITH (UPDLOCK) (data will not change till you do an update yourself) TK - Original Message - From: "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 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 documented in Access, SQL Server > - In MySql, there is only LOCK TABLES, > > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252542 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252540 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 "jenny" with new information... 4. A minute later Sally updates "jenny" with new information 5. Sally's update has overwritten Bobs - but sally never saw bob's information . She only saw the original information » This situation may happen every day in any application in which many users have access to editing facilities. Of course, if only one user can edit, you have no problem. (unless this one user horses around with two browsers in the same time ;-) What one should do normally is to lock the row for Jenny when selcting Jenny for editing, and unlock it either after a time limit, or after updating the record. This way, Sally would get a massage like "Sorry, but Jenny is already being edited by anothe user"... -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252539 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 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 documented in Access, SQL Server - In MySql, there is only LOCK TABLES, -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252535 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 = blah FOR UPDATE > > -- snip -- > COMMIT and ROLLBACK unlock everything you locked FOR UPDATE automatically. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252531 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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 given window of time are related And this is all what we need to develop full featured databases applications, and this is what we all are doing using ColdFusion. >>Here's the rub - let's say someone goes to an edit form on a site, and the data row is then locked. Then they decide they want to do it later instead, so they browse off to some other site. How do you release the lock? Like in any other application: with a time limit. whether the application is under DOS, Windows or CF, a lock must have a time limit. You had exactly the same kind of situation in Clipper when a user decided to take his coffee break after just having locked a record for editing. The fact that the user has access to the application through Internet makes no difference. >>Now, you could have a timeout on the lock, but it would have to be fairly short (minutes at most) to not be problematic. The more at risk the data is for a race condition, (i.e. the more likely a particular row is to have multiple editors) the more problematic a timeout mechanism is in this scenario. No difference with any other application >>In either case you could probably solve it using some sort of ping mechanism on the page to make sure they are still there, Gee, you are improving the mechanism! ;-) Make it Ajax! >but now you have a bunch of extra network chatter going on while someone sits and looks at the form. Peanuts! >>You are correct that there is no standard mechanism that handles this well. As others have pointed out, it's possible to handle in your code. My point is precisely that this is weird, because all these mechanisms were easily available back in the 80's, in dBase, the ancestor of all popular database systems. >>I would certainly be all for someone coming up with a better toolset to manage this sort of thing. As I say, when I'll have some time, I'll have a look to see if there is any solution through ODBC-JDBC and may develop some CFX to handle record locks. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252527 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 to a higher isolation level or throw an error. So as long as you are not using MySQL there is no risk of the events used to describe isolation levels suddenly occuring. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252528 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 present the error to the user. It is basically serialized writing and read-committed reading. Check out chapter 5 "Multiversion Concurrency Control" of "Concurrency Control and Recovery in Database Systems" http://research.microsoft.com/pubs/ccontrol/ for more details. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252521 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 transaction that locked the record committed or rolled back. >> However, in a web app, this doesn't work - >> there is no continuous session state for Oracle to track - the select >> and the subsequent update are entirely unconnected events. It's the >> nature of the beast. > > If this kind of feature was implemented in ODBC or JDBC, and was > standard in SQL, there could be a tool in CF. > CF is able to keep connections open, manage time limits. > IF ODBC or JDBC was able to manage locks, there would be no problem. Then I have news for you: the feature is implemented in both ODBC and JDBC and is standard in SQL. > I don't agree, it is a lack of facilities in SQL first. > suppose there was an SQL satement like > > LOCK FROM table > WHERE id = blah... SELECT * FROM table WHERE id = blah FOR UPDATE > and that this acted like a query returning a lock handle, > and suppose there was a twin statement like > > UNLOCK handle COMMIT and ROLLBACK unlock everything you locked FOR UPDATE automatically. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252522 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 index it? That is soo 80'ies :) Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252517 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 > Here's the rub - let's say someone goes to an edit form on a site, and > the data row is then locked. Then they decide they want to do it later > instead, so they browse off to some other site. How do you release the > lock? They didn't commit a change and they didn't send any additional > request to the server to close the session. > > Now, you could have a timeout on the lock, but it would have to be > fairly short (minutes at most) to not be problematic. The more at risk > the data is for a race condition, (i.e. the more likely a particular row > is to have multiple editors) the more problematic a timeout mechanism is > in this scenario. > > Now, let's say they didn't leave, but are simply taking a long time to > do the edit. The lock times out underneath them and you are back to > square one (no locking). > > In either case you could probably solve it using some sort of ping > mechanism on the page to make sure they are still there, but now you > have a bunch of extra network chatter going on while someone sits and > looks at the form. > > Neither solution is great. > >> I is just too bad we have no tool to really control race conditions in >> our databases, >> and I just wonder how many among us really care about it. > > You are correct that there is no standard mechanism that handles this > well. As others have pointed out, it's possible to handle in your code. > > I would certainly be all for someone coming up with a better toolset to > manage this sort of thing. > >> If this kind of feature was implemented in ODBC or JDBC, and was >> standard in SQL, there could be a tool in CF. >> CF is able to keep connections open, manage time limits. >> IF ODBC or JDBC was able to manage locks, there would be no problem. > > CF can manage database connections, but it's the lack of a persistent > connection between the client and the server that's the problem, which > is why the Oracle method doesn't work in a web environment - you simply > don't know if the client is intending to complete the transaction and > release the lock unless you "work around" the state issue. For all you > know the have browsed off to EBay while your lock is still in force. > >> As I said, the CF server itself is able to manage sessions, the > browser is >> not important here. If there were proper tools in ODBC, CF could do it >> easily. > > The browser is vitally important here. CF can hold database locks all > day long, it's knowing what the client (the browser) is doing when there > isn't an explicit request that is the issue. > > I don't disagree that there are situations where a race condition may > need to be handled in a way that goes beyond what the environment > natively supports - and with some creative coding, that can certainly be > done. > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252519 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> 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 "statelessness". The key phrase being "work around" - that's exactly what it is. 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. All a session is is knowing that two distinct requests within a given window of time are related Here's the rub - let's say someone goes to an edit form on a site, and the data row is then locked. Then they decide they want to do it later instead, so they browse off to some other site. How do you release the lock? They didn't commit a change and they didn't send any additional request to the server to close the session. Now, you could have a timeout on the lock, but it would have to be fairly short (minutes at most) to not be problematic. The more at risk the data is for a race condition, (i.e. the more likely a particular row is to have multiple editors) the more problematic a timeout mechanism is in this scenario. Now, let's say they didn't leave, but are simply taking a long time to do the edit. The lock times out underneath them and you are back to square one (no locking). In either case you could probably solve it using some sort of ping mechanism on the page to make sure they are still there, but now you have a bunch of extra network chatter going on while someone sits and looks at the form. Neither solution is great. > I is just too bad we have no tool to really control race conditions in > our databases, > and I just wonder how many among us really care about it. You are correct that there is no standard mechanism that handles this well. As others have pointed out, it's possible to handle in your code. I would certainly be all for someone coming up with a better toolset to manage this sort of thing. > If this kind of feature was implemented in ODBC or JDBC, and was > standard in SQL, there could be a tool in CF. > CF is able to keep connections open, manage time limits. > IF ODBC or JDBC was able to manage locks, there would be no problem. CF can manage database connections, but it's the lack of a persistent connection between the client and the server that's the problem, which is why the Oracle method doesn't work in a web environment - you simply don't know if the client is intending to complete the transaction and release the lock unless you "work around" the state issue. For all you know the have browsed off to EBay while your lock is still in force. > As I said, the CF server itself is able to manage sessions, the browser is > not important here. If there were proper tools in ODBC, CF could do it > easily. The browser is vitally important here. CF can hold database locks all day long, it's knowing what the client (the browser) is doing when there isn't an explicit request that is the issue. I don't disagree that there are situations where a race condition may need to be handled in a way that goes beyond what the environment natively supports - and with some creative coding, that can certainly be done. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252510 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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 TESTTRANSACTION SET SOMEDATA =2 WHERE PKID = 3 SELECT PKID,SOMEDATA FROM TESTTRANSACTION AFTER UPDATES PK: #PKID# DATA: #QGetSomeData.SomeData# TEMPLATE 2 UPDATE TESTTRANSACTION SET SOMEDATA =3 WHERE PKID = 4 RUN ONE: no level specified (i.e. I removed the serializable attribute from both templates): Before UPDATES: PK: 3 DATA: 0 PK: 4 DATA: 0 PK: 5 DATA: 0 PK: 6 DATA: 0 AFTER UPDATES PK: 3 DATA: 2 PK: 4 DATA: 3 PK: 5 DATA: 0 PK: 6 DATA: 0 RUN TWO Specifying seralizable as per the code above: Before UPDATES: PK: 3 DATA: 0 PK: 4 DATA: 0 PK: 5 DATA: 0 PK: 6 DATA: 0 AFTER UPDATES PK: 3 DATA: 2 PK: 4 DATA: 0 PK: 5 DATA: 0 PK: 6 DATA: 0 The data are different. The first run allows a non-blocking row to be written and displayed, as the second template was committed in between queries. The second run remains consistent during the first template's transaction, because the serializable level takes a transaction level snapshot instead of a statement level snapshot. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252490 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
> 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 directly. With SQL Server's SQL Profiler, for example, you'll see range locks if you have serializable transactions. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252489 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 Bubba, or dial Extension 123 to reach Bubba's secretary". A row-level lock isn't going to come even close to that. In my opinion this is not a decision a grownup application should make at the "can I write my changes" moment in an app's lifecycle. As a backup failsafe... Sure. Fine. Absolutely. But not as the first line of defense. -- [EMAIL PROTECTED] Janitor, MSB Web Systems mysecretbase.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252488 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 sleep time. 3) See what data you end up with in the first page's select. I'll let know how I go. If you have time, do the same and we can compare notes. On 9/8/06, Dave Watts <[EMAIL PROTECTED]> wrote: > > 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 Committed. > > For most databases, the default isolation level is read committed, but I > believe that the documentation may be in error on the point you quoted. This > came up a while back, according to my somewhat hazy memory, and I thought > the documentation was correct, but was told by someone (I forget who) that > this was no longer the case. Prior to CFMX, it was in fact the case that the > default isolation level of the database would be used. > > In any case, you should set your isolation level explicitly, I suppose! > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more information! > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252485 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> 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 Committed. For most databases, the default isolation level is read committed, but I believe that the documentation may be in error on the point you quoted. This came up a while back, according to my somewhat hazy memory, and I thought the documentation was correct, but was told by someone (I forget who) that this was no longer the case. Prior to CFMX, it was in fact the case that the default isolation level of the database would be used. In any case, you should set your isolation level explicitly, I suppose! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252481 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 Committed. On 9/8/06, Dave Watts <[EMAIL PROTECTED]> 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. > > I don't think this is correct. When you specify an isolation level, that's > what the database uses. And, if I recall correctly, serializable is the > default level used with the CFTRANSACTION tag in CFMX 6+. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Locking Theory
> 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 I recall correctly, serializable is the default level used with the CFTRANSACTION tag in CFMX 6+. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252476 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> 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() functions. > Rlock() was used when editing a record, Flock() locked the > whole file, for instance while indexing a table. How on earth would such a "tool" work, in a disconnected environment? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252474 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 ? 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 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. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252473 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Locking Theory
> > ... 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 manipulating the same data, but "working on ... a certain record" will involve more than one request, and you won't be able to have that series of requests from a single user contained within a single transaction. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252475 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
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, not really a method to PREVENT problems from > happening. Well, it prevents it from being a problem. You never have a race condition. At least not one that you can't look back on and KNOW who won-- Or even revise and tweak the results... I mean, HTTP is what it is, and sure CF and whatnot can make it seem pretty stateful... but I wouldn't want my heart surgeon to work on me remotely via it. But what are you thinking? Where'd you use row level locking? A r3al life example? Personally, I'd rather be able to do whatever I want to do, whenever I want to do it, and let some other process decide what should go where when. There's no reason for me to wait around... maybe a little "stan is working on this too" type message, but no lock. The connection isn't that cool for that-- and so you're looking at software-- so why not do it in CF anyways and be done with it, forever, nixing the weak link of the "sometimes there" web? DB's aren't web servers... but, then again. What are db's? There are "Object" databases now... tons of specialized ones... I guess part of the problem is having to connect from various sources-- some more programmatical, some more dialectical... eh, big mess of a problem that we're surely evolving towards a solution for. > > >>DB's are a lot faster than Clipper now, and can store alot more too. > > Hmmm Faster? DB are running on PC 1000 times faster than in the 80's, > this makes a big diffference. > About 7 years ago, I wrote an interpreter for a script language > ColdFusion like, > but based on the Clipper syntax instead of SQL, and believe it or not, > it was FASTER > than ColdFusion! ;-) And it was not written in C nor in Java, but 100% > in Clipper! I don't doubt speed in certain areas is vastly different than others, but I have to qualify that I've personally written applications that /screamed/ when I was the only one using them. LOL. Seriously, there is a *lot* going on besides raw data IO. Just look at the various DB providers... these things are geared for certain things, and there is only soo much bomber you can put in the fighter, or vice versa. > > >>Yech. Clipper. What a tank. > > Of course, it is completely medieval now, but in its own time it really > was a great product. > And at least it DID HAVE record and table lock facilities, which CF and > SQL do not have! ;-) dbase4 is swell too, but there is a reason why we grew out of it. And a lot of it has to do with connections --stateless or otherwise--, and lots of people working on the same stuff at once... or lots of data. Lots and lots of data. 8 terabytes in one record? *cough* Yeah. Maybe a patch idea, but space is cheaper now, and I can't help but think it's one of the "patch ideas" that might make a whole lot of stuff easier, and, with luck, might even qualify as a "feature". Anyone else using Subversion as a database? ;-) --ps throw in some AJAX, and you could have a live little message pop up "hey, carl wants to edit this same record. Wanna save what you're working on and let him?" or "How much longer you gonna have this locked?" type deal... wow... now /that's/ being connected. But locks are cool... I guess... =P ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252463 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 should be implemented in the standard. After all, JOINS were new in SQL 92. Now it's in the standard. So should be implemented locks, that were new,... well with DOS in 75, and with dBase, what? Some 25, 30 years ago? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252457 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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 db locks. Its an app structure that you can build in as you please. http://mysecretbase.com/lockmonger.cfm -- [EMAIL PROTECTED] Janitor, MSB Web Systems mysecretbase.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252453 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
when I meant in SQL, I meant in your vendors SQL implementation :-) "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: 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 ? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252450 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
It is up to the vendor to supply locking, it will never be in a standard I don't think. Vendors do provide row locking as you have seen earlier. "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: 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 ? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252449 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 ? -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252444 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
What are you saying here? That there is no way (ncorrectly) to row lock in SQL? "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: 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 same way. Please, I'm not that dumb, I know what an application is compared to a simple web page. All the same, we coldFusion developpers, are able to create complete multi-user true applications and work around THE HTTP "statelessness". I is just too bad we have no tool to really control race conditions in our databases, and I just wonder how many among us really care about it. >>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. However, in a web app, this doesn't work - there is no continuous session state for Oracle to track - the select and the subsequent update are entirely unconnected events. It's the nature of the beast. If this kind of feature was implemented in ODBC or JDBC, and was standard in SQL, there could be a tool in CF. CF is able to keep connections open, manage time limits. IF ODBC or JDBC was able to manage locks, there would be no problem. >>Web application servers work around statelessness to a degree with session management, but it's a bit of a kludge, and doesn't introduce true statefulness. It merely makes the application capable of knowing that two separate actions are part of one session, but since the app isn't connected to the browser in real time, there is no real knowledge of the "in between". As I said, the CF server itself is able to manage sessions, the browser is not important here. If there were proper tools in ODBC, CF could do it easily. Let me see if there is anything about lock in ODBC, and I might come up with a new version of CFX_ODBCInfo with a true record lock facility? >>but it's not a flaw in CF or SQL, but rather in the underlying statelessness of HTTP. I don't agree, it is a lack of facilities in SQL first. suppose there was an SQL satement like LOCK FROM table WHERE id = blah... and that this acted like a query returning a lock handle, and suppose there was a twin statement like UNLOCK handle Then, any one could use record locking in CF, provided they activate sessions. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252442 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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 same way. Please, I'm not that dumb, I know what an application is compared to a simple web page. All the same, we coldFusion developpers, are able to create complete multi-user true applications and work around THE HTTP "statelessness". I is just too bad we have no tool to really control race conditions in our databases, and I just wonder how many among us really care about it. >>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. However, in a web app, this doesn't work - there is no continuous session state for Oracle to track - the select and the subsequent update are entirely unconnected events. It's the nature of the beast. If this kind of feature was implemented in ODBC or JDBC, and was standard in SQL, there could be a tool in CF. CF is able to keep connections open, manage time limits. IF ODBC or JDBC was able to manage locks, there would be no problem. >>Web application servers work around statelessness to a degree with session management, but it's a bit of a kludge, and doesn't introduce true statefulness. It merely makes the application capable of knowing that two separate actions are part of one session, but since the app isn't connected to the browser in real time, there is no real knowledge of the "in between". As I said, the CF server itself is able to manage sessions, the browser is not important here. If there were proper tools in ODBC, CF could do it easily. Let me see if there is anything about lock in ODBC, and I might come up with a new version of CFX_ODBCInfo with a true record lock facility? >>but it's not a flaw in CF or SQL, but rather in the underlying statelessness of HTTP. I don't agree, it is a lack of facilities in SQL first. suppose there was an SQL satement like LOCK FROM table WHERE id = blah... and that this acted like a query returning a lock handle, and suppose there was a twin statement like UNLOCK handle Then, any one could use record locking in CF, provided they activate sessions. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252436 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 same way. 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. However, in a web app, this doesn't work - there is no continuous session state for Oracle to track - the select and the subsequent update are entirely unconnected events. It's the nature of the beast. Web application servers work around statelessness to a degree with session management, but it's a bit of a kludge, and doesn't introduce true statefulness. It merely makes the application capable of knowing that two separate actions are part of one session, but since the app isn't connected to the browser in real time, there is no real knowledge of the "in between". There have been a couple of good suggestions on how to potentially work around this, but it's not a flaw in CF or SQL, but rather in the underlying 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 > 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. > > >>DB's are a lot faster than Clipper now, and can store alot more too. > > Hmmm Faster? DB are running on PC 1000 times faster than in the 80's, > this makes a big diffference. > About 7 years ago, I wrote an interpreter for a script language > ColdFusion like, > but based on the Clipper syntax instead of SQL, and believe it or not, > it was FASTER > than ColdFusion! ;-) And it was not written in C nor in Java, but 100% > in Clipper! > > >>Yech. Clipper. What a tank. > > Of course, it is completely medieval now, but in its own time it really > was a great product. > And at least it DID HAVE record and table lock facilities, which CF and > SQL do not have! ;-) > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252419 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
>>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. >>DB's are a lot faster than Clipper now, and can store alot more too. Hmmm Faster? DB are running on PC 1000 times faster than in the 80's, this makes a big diffference. About 7 years ago, I wrote an interpreter for a script language ColdFusion like, but based on the Clipper syntax instead of SQL, and believe it or not, it was FASTER than ColdFusion! ;-) And it was not written in C nor in Java, but 100% in Clipper! >>Yech. Clipper. What a tank. Of course, it is completely medieval now, but in its own time it really was a great product. And at least it DID HAVE record and table lock facilities, which CF and SQL do not have! ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252408 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
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. [...] To quip in, usually in a situation where you're concerned about people overwriting other's work, or having to track who's working one what when, 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. Just my .02 US dollars. DB's are a lot faster than Clipper now, and can store alot more too. Yech. Clipper. What a tank. We've still got a couple of DBs in it... thank god for Java and JDBC, neh? Woot... back to your regularly scheduled talk... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252393 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 table. I would consider implementing such locking a bug not a feature. Of course, db level locking is abusive. One should be able to lock only one record being edited at a time. >>Try the following in SQL server The problem is doing it in a CF application, not directly on the SQL server. You cannot have a user edit a record INSIDE a cftransaction tag. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252371 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Locking Theory
You can always just use locking at the DB level. I am not sure about anything other than MSSQL but locking can be done there instead of CF if need be. http://www.sql-server-performance.com/at_sql_locking.asp - Original Message - From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> 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 Exhibitions (Oriel House, 26 The Quadrant, > Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, > Registered in England, Number 678540. It contains information which is > confidential and may also be privileged. It is for the exclusive use of the > intended recipient(s). If you are not the intended recipient(s) please note > that any form of distribution, copying or use of this communication or the > information in it is strictly prohibited and may be unlawful. If you have > received this communication in error please return it to the sender or call > our 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: 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 table. I would consider implementing such locking a bug > not a feature. > > Try the following in SQL server query analyzer: Begin Tran [some update of a > > table] > in new window try to select on the table and do some other ops. See what > happens. After you are done do 'commit' in transaction window or 'rollback'. > > Depending on locking you wil need to commit or rollback trans before you can > > see anything done by select > > TK > > > > > >>cftransaction locks that DB actually follows may lock the whole table > > and > > you should *never* place any user code or some prolonged operation inside > > transaction block. > > > > cftransaction is not a panacea either: it only locks for the time of a > > transaction, > > not for the time the user will edit a record. > > > > -- > > ___ > > REUSE CODE! Use custom tags; > > See http://www.contentbox.com/claude/customtags/tagstore.cfm > > (Please send any spam to this address: [EMAIL PROTECTED]) > > Thanks. > > > > > > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252369 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our 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: 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 table. I would consider implementing such locking a bug not a feature. Try the following in SQL server query analyzer: Begin Tran [some update of a table] in new window try to select on the table and do some other ops. See what happens. After you are done do 'commit' in transaction window or 'rollback'. Depending on locking you wil need to commit or rollback trans before you can see anything done by select TK > >>cftransaction locks that DB actually follows may lock the whole table > and > you should *never* place any user code or some prolonged operation inside > transaction block. > > cftransaction is not a panacea either: it only locks for the time of a > transaction, > not for the time the user will edit a record. > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252365 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 table. I would consider implementing such locking a bug not a feature. Try the following in SQL server query analyzer: Begin Tran [some update of a table] in new window try to select on the table and do some other ops. See what happens. After you are done do 'commit' in transaction window or 'rollback'. Depending on locking you wil need to commit or rollback trans before you can see anything done by select TK > >>cftransaction locks that DB actually follows may lock the whole table > and > you should *never* place any user code or some prolonged operation inside > transaction block. > > cftransaction is not a panacea either: it only locks for the time of a > transaction, > not for the time the user will edit a record. > > -- > ___ > REUSE CODE! Use custom tags; > See http://www.contentbox.com/claude/customtags/tagstore.cfm > (Please send any spam to this address: [EMAIL PROTECTED]) > Thanks. > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252364 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Locking Theory
>>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 develop. I'm the one who "wants to lock", the users have difficulties to make the difference between a computer and a toaster ;-) But yes, this is what I want, and this is only basic practice in all multiuser database applications. I've been always amazed by the futility of the CFLOCK tag. For me, it should lock records or tables, not just variable scopes which the CF server should be able to manage automatically. By the way, this is exactly what it has been doing since version 6. >>cftransaction locks that DB actually follows may lock the whole table and you should *never* place any user code or some prolonged operation inside transaction block. cftransaction is not a panacea either: it only locks for the time of a transaction, not for the time the user will edit a record. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252363 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
