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
Re: Locking Theory
>>I was pointing out locking of variables in scope rather than DB locking. Exact, but locking variables is just a way of doing some db locking, otherwise, it is not very useful. The real thing is db locking. And what if you have two different applications using the same database? -- ___ 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:252361 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 perhaps you are asking how to allow someone to edit data without being overwritten by someone editing the same data. Well, this is exactly what one would call a "race condition", isn't it ? >>Sally's update has overwritten Bobs Exact, this is actually what should be avoided in any database application. Happily, it does not happen too often, unless the application has a large number of users. >>To solve this problem we need a table or session or application variable holding "checked out" records. The use of application.cfc is useful here because the "onsessionend( )" function can be used to "clean up" locked records that are orphaned by someone going out for pizza or pressing the red "reset" butto non their PC. How you do it depends on how complicated your DB schema is, and how granular you envision your locking scheme. 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. -- ___ 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:252360 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
You need for this application level locking that someone mentioned few posts back - essentially implement "source safe" type of locking with your records like files in source safe. 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. 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. I.e. transactions should take as little time as possible - we are talking ms here not even seconds. TK - Original Message - From: "Claude Schneegans" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Thursday, September 07, 2006 10:48 AM Subject: Re: Locking Theory > >>Cftransaction with the isolation attribute. > > Ok, but the lock terminates with the closing So how could one > 1. lock a record, > 2. read the record > 3. edit the record > 4. update the record > 5. unlock the record ? > > At least (1,2,3) and (4,5) could be in different templates. > > -- > ___ > 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:252358 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. TK - Original Message - From: "Doug Bezona" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Thursday, September 07, 2006 10:23 AM Subject: RE: Locking Theory > Cftransaction with the isolation attribute. Check the docs for the > various options. > >> -Original Message- >> From: Claude Schneegans [mailto:[EMAIL PROTECTED] >> Sent: Thursday, September 07, 2006 10:19 AM >> To: CF-Talk >> Subject: Re: Locking Theory >> >> Ok, the definitions says : >> "A race condition occurs when multiple processes access and manipulate >> the same data concurrently" >> >> I remember, when I was working on applications in Clipper ages ago, we >> had to lock records or files >> in the database, and those locks were managed by DOS, not by the >> application. >> >> In CF locks appear to be purely software locks, I mean only parts of > the >> program are locked, >> not records nor tables in the database. >> 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 ? >> >> -- >> ___ >> 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:252357 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 with the isolation attribute. Ok, but the lock terminates with the closing http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:252356 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
-- YOU Wrote: but in general, how would you lock a certain record in a table while some one is working on it ? This is a bit different from the discussion so far - which has concentrated on locking as it pertains to avoiding errors and race conditions. You can lock a transaction and the record is protected while it is being written to But perhaps you are asking how to allow someone to edit data without being overwritten by someone editing the same data. For example: Bob opens a web page and goes to an edit tool and selects "jenny" to edit. Sally opens a web page and goest to an edit tool and also selects "jenny" to edit. Bob updates "jenny" with new information... A minute later Sally updates "jenny" with new information Sally's update has overwritten Bobs - but sally never saw bob's information She only saw the original information To solve this problem we need a table or session or application variable holding "checked out" records. The use of application.cfc is useful here because the "onsessionend( )" function can be used to "clean up" locked records that are orphaned by someone going out for pizza or pressing the red "reset" butto non their PC. How you do it depends on how complicated your DB schema is, and how granular you envision your locking scheme. -mk ~| 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:252354 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 was pointing out locking of variables in scope rather than DB locking. -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: 07 September 2006 15:23 To: CF-Talk Subject: Re: Locking Theory >>Cflock, ensures that this never happens, as Ben will only ever be able to chance value a to "Ben" after you have finished with it. Yes, but what is locked is the template in which one can modify ANY record in the database, not just "Jenny", so no one should be able to modify anything during the lock. This is equivalent to locking the whole table. Can you give 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:252353 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 with the isolation attribute. Check the docs for the various options. > -Original Message- > From: Claude Schneegans [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 07, 2006 10:19 AM > To: CF-Talk > Subject: Re: Locking Theory > > Ok, the definitions says : > "A race condition occurs when multiple processes access and manipulate > the same data concurrently" > > I remember, when I was working on applications in Clipper ages ago, we > had to lock records or files > in the database, and those locks were managed by DOS, not by the > application. > > In CF locks appear to be purely software locks, I mean only parts of the > program are locked, > not records nor tables in the database. > 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 ? > > -- > ___ > 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:252350 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
>>Cflock, ensures that this never happens, as Ben will only ever be able to chance value a to "Ben" after you have finished with it. Yes, but what is locked is the template in which one can modify ANY record in the database, not just "Jenny", so no one should be able to modify anything during the lock. This is equivalent to locking the whole table. Can you give 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:252349 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
Ok, the definitions says : "A race condition occurs when multiple processes access and manipulate the same data concurrently" I remember, when I was working on applications in Clipper ages ago, we had to lock records or files in the database, and those locks were managed by DOS, not by the application. In CF locks appear to be purely software locks, I mean only parts of the program are locked, not records nor tables in the database. 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 ? -- ___ 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:252347 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
http://en.wikipedia.org/wiki/Race_condition http://searchstorage.techtarget.com/sDefinition/0,,sid5_gci871100,00.html http://www.tech-faq.com/race-condition.shtml Mark On 9/7/06, Jenny Gavin-Wear <[EMAIL PROTECTED]> wrote: > Hi Ben, > > Could you explain the term "race condition" please? > > Jenny -- E: [EMAIL PROTECTED] W: www.compoundtheory.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:252330 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
Thanks Neil -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 07 September 2006 07:53 To: CF-Talk Subject: Re: Locking Theory A race condition is a condition is where a value may be not what you expect when you get/set it. This is normally the case in say, application, server, and to a lesser degree session variables. So, you try and access value a, which you know to be "Jenny" - this is set at the server level. Problem is, Ben has went in at the same time to change this value to "Ben" just as you access it..this is a race condition. Cflock, ensures that this never happens, as Ben will only ever be able to chance value a to "Ben" after you have finished with it. Hth "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: Jenny Gavin-Wear To: CF-Talk Sent: Thu Sep 07 07:43:30 2006 Subject: RE: Locking Theory Hi Ben, Could you explain the term "race condition" please? Jenny -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: 06 September 2006 23:30 To: CF-Talk Subject: RE: Locking Theory In my opinion, only use CFLock when you care if the race condition matters. Take setting SESSION values for instance. Let's say you have the following code: This would NOT require a lock. Yes, it's shared data. Yes you could have conflicts. But the question is, does it matter? If a user has two pages that happen to run this code simultaneously, is there going to be a bad outcome if no locking? N. Both will set the appropriate value. Then take a session counter in the application: Again, you are updating shared memory... But again, does it matter? Can this ever fire in such a way where it will get hurt? No. No matter what, sessions are going to be added. So the questions you need to ask yourself in this order are: 1. Is there a race condition? 2. Does the race condition matter? .. Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.1/440 - Release Date: 06/09/2006 ~| 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:252328 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
A race condition is a condition is where a value may be not what you expect when you get/set it. This is normally the case in say, application, server, and to a lesser degree session variables. So, you try and access value a, which you know to be "Jenny" - this is set at the server level. Problem is, Ben has went in at the same time to change this value to "Ben" just as you access it..this is a race condition. Cflock, ensures that this never happens, as Ben will only ever be able to chance value a to "Ben" after you have finished with it. Hth "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: Jenny Gavin-Wear To: CF-Talk Sent: Thu Sep 07 07:43:30 2006 Subject: RE: Locking Theory Hi Ben, Could you explain the term "race condition" please? Jenny -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: 06 September 2006 23:30 To: CF-Talk Subject: RE: Locking Theory In my opinion, only use CFLock when you care if the race condition matters. Take setting SESSION values for instance. Let's say you have the following code: This would NOT require a lock. Yes, it's shared data. Yes you could have conflicts. But the question is, does it matter? If a user has two pages that happen to run this code simultaneously, is there going to be a bad outcome if no locking? N. Both will set the appropriate value. Then take a session counter in the application: Again, you are updating shared memory... But again, does it matter? Can this ever fire in such a way where it will get hurt? No. No matter what, sessions are going to be added. So the questions you need to ask yourself in this order are: 1. Is there a race condition? 2. Does the race condition matter? . Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.1/440 - Release Date: 06/09/2006 ~| 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:252327 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
Hi Ben, Could you explain the term "race condition" please? Jenny -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: 06 September 2006 23:30 To: CF-Talk Subject: RE: Locking Theory In my opinion, only use CFLock when you care if the race condition matters. Take setting SESSION values for instance. Let's say you have the following code: This would NOT require a lock. Yes, it's shared data. Yes you could have conflicts. But the question is, does it matter? If a user has two pages that happen to run this code simultaneously, is there going to be a bad outcome if no locking? N. Both will set the appropriate value. Then take a session counter in the application: Again, you are updating shared memory... But again, does it matter? Can this ever fire in such a way where it will get hurt? No. No matter what, sessions are going to be added. So the questions you need to ask yourself in this order are: 1. Is there a race condition? 2. Does the race condition matter? Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.1/440 - Release Date: 06/09/2006 ~| 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:252324 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
> Okay.. so, is cflock in or out these days? If you're still working with CF 5 or earlier, locking is very important if you want your server to continue working. If you're using a newer version, locking is only needed when you may have a race condition. Race conditions arise when you have concurrent requests that may access the same piece of data, and as a result may generate unintended results. If there's no potential for a race condition, or you don't care if there is one, you don't need to lock. > Should I be using cflock around session variable reads? writes? This all depends on the factors mentioned above. 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:252300 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
In my opinion, only use CFLock when you care if the race condition matters. Take setting SESSION values for instance. Let's say you have the following code: This would NOT require a lock. Yes, it's shared data. Yes you could have conflicts. But the question is, does it matter? If a user has two pages that happen to run this code simultaneously, is there going to be a bad outcome if no locking? N. Both will set the appropriate value. Then take a session counter in the application: Again, you are updating shared memory... But again, does it matter? Can this ever fire in such a way where it will get hurt? No. No matter what, sessions are going to be added. So the questions you need to ask yourself in this order are: 1. Is there a race condition? 2. Does the race condition matter? ... Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 6:20 PM To: CF-Talk Subject: Locking Theory Okay.. so, is cflock in or out these days? Should I be using cflock around session variable reads? writes? If so, why? Rick ~| 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:252297 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4