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
