RE: [CFCDev] Repost: SQL Concurrency

[Andrew Scott]

Barry,   Originally this question is based on a c# project that I am also working on, but the coldfusion application is the same. When looking at how C# and VB do it I keep coming across these 3 examples, our current coldfusion application is last in method and I am sure that there must be a better way. Ye it seems to fit our clients needs at the moment, with no complaints to date.   I have read that it is dependant on the business needs, but wanted to know how others also coped with this as well.   The timestamp/checksum was another method that the new ado.net has adopted with datasets and was curious on this further of implementing what others seem to be the need of their applications.   Anyway thanks for the comments but still would like to hear more from others as well.     Regards Andrew Scott Technical Consultant NuSphere Pty Ltd Level 2/33 Bank Street South Melbourne, Victoria, 3205 Phone: 03 9686 0485  -  Fax: 03 9699 7976 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Friday, 27 May 2005 3:29 PM To: CFCDev@cfczone.org Subject: RE: [CFCDev] Repost: SQL Concurrency   Andrew, you probably already realise this but these three "types" really refer how the database itself does it's updates, and maps to such things as row and table locking.   this tallies up to the old ADODB  adLockReadOnly , adLockOptimistic ,  adLockBatchOptimistic , et al. The point is that this is per connection. Perfectly suited for a client/server app where the application can happily hold a database connection open for as long as it takes to update that record. but when the app moves to the web, the disconnected client woes kick in so that connection cannot be held open and concurrent across multiple requests/responses. It'd be like having a "<cftransaction..." when the initial select is done before (displaying to the user) and a "</cftransaction>" just after the update query when the form is posted back - it can't be done.   that's why people use various methods to work out whether the origional data has changed since the user first got that record.   adding a timestamp field to every table and selecting that with the rest of the record to compare (just before the update) is one method. Selecting the whole record and carrying that back with the changed data is another. A version of that is a hash of the record and compare hashes. In all cases it's a case of compare and throw if something has changed.   To be honest, I have yet to see (in CF or ASP land) anything that's radically different to these basic methods.   If there is, I'd like to hear about it (without going into things like FlashCommunicationServer to see if the client is still connected...)     HTH barry.b   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Andrew Scott Sent: Friday, 27 May 2005 12:07 PM To: CFCDev@cfczone.org Subject: RE: [CFCDev] Repost: SQL Concurrency The 3 types that I have read are:   Pessimistic concurrency control - a row is unavailable to users from the time the record is fetched until it is updated in the database.   Optimistic concurrency control - a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.   Last in wins - a row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.   With this in mind I was asking what others are doing, and what would be the best approach to this.     Regards Andrew Scott Technical Consultant NuSphere Pty Ltd Level 2/33 Bank Street South Melbourne, Victoria, 3205 Phone: 03 9686 0485  -  Fax: 03 9699 7976 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Barry Beattie Sent: Friday, 27 May 2005 11:19 AM To: CFCDev@cfczone.org Subject: RE: [CFCDev] Repost: SQL Concurrency   >>  a few articles on MSDN about the 3 types of record locking available   can you elaborate? what are the 3 ways mentioned?   there's many ways to skin this (disconnected client) cat...     -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Andrew Scott Sent: Friday, 27 May 2005 11:17 AM To: CFCDev@cfczone.org Subject: RE: [CFCDev] Repost: SQL Concurrency Ok I am working on an application that is a multi user intranet application, the current system works on the basis that last in wins on the storage of the data placed back to the database.   I was wondering what other people do to prevent this, like record locking. I was reading a few articles on MSDN about the 3 types of record locking available, but wanted to know how others approached or dealt with the subject as well.   Any help or further discussion would be great.     Regards Andrew Scott Technical Consultant NuSphere Pty Ltd Level 2/33 Bank Street South Melbourne, Victoria, 3205 Phone: 03 9686 0485  -  Fax: 03 9699 7976 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Roland Collins Sent: Friday, 27 May 2005 10:46 AM To: CFCDev@cfczone.org Subject: RE: [CFCDev] Repost: SQL Concurrency   What particular aspects?  SQL concurrency is a pretty large topic, depending on what aspects you’re referring to. ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to cfcdev@cfczone.org with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/cfcdev@cfczone.org