Professional Web Applications Themes

Transactions and Locking - Coldfusion Database Access

I could do with some help on implementing locking in a multiuser environment. The scenario is quite simple and I'm temporarily using MS Access to test the principle before moving on to MSSQL. Users can VIEW a customer record from a list, select EDIT (which generates another SQL Select to get latest information) and then select SAVE to update the record after an amendment (using SQL Update). In a client server environment I would put a transaction around the SELECT and UPDATE to LOCK the record from update by other users but still allow them to read the record. When ...

  1. #1

    Default Transactions and Locking

    I could do with some help on implementing locking in a multiuser environment.
    The scenario is quite simple and I'm temporarily using MS Access to test the
    principle before moving on to MSSQL.

    Users can VIEW a customer record from a list, select EDIT (which generates
    another SQL Select to get latest information) and then select SAVE to update
    the record after an amendment (using SQL Update). In a client server
    environment I would put a transaction around the SELECT and UPDATE to LOCK the
    record from update by other users but still allow them to read the record. When
    the user saves or cancel's EDIT, the transaction ends and the LOCK is released.

    The problem I have is I can't get my head around how to do this in a HTML/CFML
    environment. I know how to use CFTRANSACTION but you can't open it in the page
    that executes the SELECT and puts the data in the FORM, then terminate the
    CFTRANSACTION in the page called by the SUBMIT action where the UPDATE is run.
    If SELECT and UPDATE are not within the tranaction the customer record could
    have been changed by another user before the current user have saved their
    changes.

    I would appreciate any advise on how to do this.

    Cheers
    Graham

    gbrownuk Guest

  2. #2

    Default Re: Transactions and Locking

    Would using a timestamp be the answer? Select the record and note the value in
    a date/time field, make changes to the record then submit. Before the update is
    actioned the record is queried again and the record locked. If the timestamps
    match the record is updated, if they don't the operation is cancelled and the
    user informed the record had changed?

    Is the the right approach?

    Any help would be appreciated.

    gbrownuk Guest

  3. #3

    Default Re: Transactions and Locking

    If more than one person can update the same record, then they can overwrite
    previous updates irrespective of any code you write. So you might choose to
    not worry about it.

    If you do want to have a bit of control over things, you can add a field to
    the table that you set to 0/1, y/n whatever. The value ot the field determines
    whether the record is available for updating.

    Originally posted by: gbrownuk
    I could do with some help on implementing locking in a multiuser environment.
    The scenario is quite simple and I'm temporarily using MS Access to test the
    principle before moving on to MSSQL.

    Users can VIEW a customer record from a list, select EDIT (which generates
    another SQL Select to get latest information) and then select SAVE to update
    the record after an amendment (using SQL Update). In a client server
    environment I would put a transaction around the SELECT and UPDATE to LOCK the
    record from update by other users but still allow them to read the record. When
    the user saves or cancel's EDIT, the transaction ends and the LOCK is released.

    The problem I have is I can't get my head around how to do this in a HTML/CFML
    environment. I know how to use CFTRANSACTION but you can't open it in the page
    that executes the SELECT and puts the data in the FORM, then terminate the
    CFTRANSACTION in the page called by the SUBMIT action where the UPDATE is run.
    If SELECT and UPDATE are not within the tranaction the customer record could
    have been changed by another user before the current user have saved their
    changes.

    I would appreciate any advise on how to do this.

    Cheers
    Graham



    Dan Bracuk Guest

  4. #4

    Default Re: Transactions and Locking

    gbrownuk,

    You might want to have a look at the response by Kronin555 in this thread:

    [url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=2&thread[/url]
    id=1055297

    It describes the concept of versioning which sounds like what you're
    describing.



    mxstu Guest

Similar Threads

  1. Transactions
    By Eric in forum ASP Components
    Replies: 0
    Last Post: February 20th, 02:42 PM
  2. Transactions in Access
    By Astra in forum ASP Database
    Replies: 2
    Last Post: February 18th, 02:01 PM
  3. Transactions....HELP!
    By John Berry in forum Informix
    Replies: 0
    Last Post: October 16th, 06:54 PM
  4. Transactions - ADO/SQL or both?
    By London Developer in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 10:44 AM
  5. Problem with VB6 App & SQL 2000 (Locking & lost transactions)
    By Javier Villegas in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: March 29th, 03:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139