Professional Web Applications Themes

Access + Asp + multiuser + table lock - ASP Database

Hi all. In my asp site, i have a "Search and Sort" button that creates a lot of activity in my access database. Clciking on the button does that : 1. Begin a transaction 2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable FROM ModelTable". Of course DuplicateTable is a unique name. 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update in the duplicate tables 4. Destroy duplicate tables 5. Close transaction Plz don't ask why i create duplicate tables as i've tried many different solutions : this one is the closest to what i want. (if ...

  1. #1

    Default Access + Asp + multiuser + table lock

    Hi all.

    In my asp site, i have a "Search and Sort" button that creates a lot of
    activity in my access database.

    Clciking on the button does that :
    1. Begin a transaction
    2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
    FROM ModelTable". Of course DuplicateTable is a unique name.
    3. Use lot of "conn.execute sqlstring" instructions to add/delete/update in
    the duplicate tables
    4. Destroy duplicate tables
    5. Close transaction

    Plz don't ask why i create duplicate tables as i've tried many different
    solutions : this one is the closest to what i want.
    (if you want precisions on the other solutions i tried, i wrote a post in
    microsoft.public.access.multiuser called Asp Multiuser and table lock)

    It works fine until two users click the "Search and Sort" button at the very
    same time
    In this case, the second user get an error message when creating duplicate
    tables (Step 2).
    It says something about a lock that i don't know how to translate. Error
    number is 80004005.

    Why is there a lock here ? I don't open any recordset that involves the
    tables concerned by Step 2 !
    Any advice welcome


    Patrick Guest

  2. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Jeff Cochran Guest
    Moderated Post

  3. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Patrick Guest
    Moderated Post

  4. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Jeff Cochran Guest
    Moderated Post

  5. #5

    Default Re: Access + Asp + multiuser + table lock

    I don't understand anything to these locks.
    Why do i get a lock for a table that has no recordsets and no SQL query that
    add/delete/update its rows ?

    I tried some prvider specific settings, but nothing works :
    Jet OLEDB:Database Locking Mode
    Jet OLEDB:Lock Delay
    Jet OLEDB:Lock Retry
    Jet OLEDB:Max Locks Per File
    Jet OLEDB:Locking Granularity

    I hate not knowing the cause of a problem.
    I'll do error handling for this, but error handling in ADO s and in my
    cases makes both users wait a long time before getting the results on the
    screen.

    Next time I swear i'll use MSDE :-)


    Patrick Guest

  6. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Larry Linson Guest
    Moderated Post

  7. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Patrick Guest
    Moderated Post

  8. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Jeff Cochran Guest
    Moderated Post

  9. #9

    Default Re: Access + Asp + multiuser + table lock

    > Except on an Access, err, Jet, database, that can lock the MDB file
    > itself. Part of your problem may very well be the single user locking
    > of the database as Larry suggested. On the other hand, even with
    > multiple users Access, um, I mean Jet, databases have some locking
    > issues. You may see it in an ADO environment where you may not see it
    > elsewhere too.
    >
    > Larry can probably go into better detail with the actual locking
    > levels, but you may need to redesign your database or move to a MSDE
    > or SQL Server database to keep from having these locking issues.
    Thanks to take the time to answer me Jeff !
    I hope i won't have to redesign my app or change my database as i'm nearly
    finished with it.
    As I'm running out of time, i prefer put a friendly error message asking the
    user to wait rather than redesigning the entire app !

    But i don't like not knowing why things do not work !


    Patrick Guest

  10. Moderated Post

    Default Re: Access + Asp + multiuser + table lock

    Removed by Administrator
    Larry Linson Guest
    Moderated Post

  11. #11

    Default Re: Access + Asp + multiuser + table lock

    "Patrick" wrote
    > 1. Begin a transaction
    > 2. Create duplicates of empty model tables : "SELECT * INTO DuplicateTable
    > FROM ModelTable". Of course DuplicateTable is a unique name.
    > 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update
    in
    > the duplicate tables
    > 4. Destroy duplicate tables
    > 5. Close transaction
    Does 2, 1, 3, 5, 4 work?

    Since DuplicateTable is unique, do you need the transaction?


    --
    roger


    roger Guest

  12. #12

    Default Re: Access + Asp + multiuser + table lock

    > If you
    > do so in Access itself, a well-regarded approach is to create a temporary
    > database, and then create the temporary table in the temporary database.
    > When it is no longer needed, you use the KILL statement to delete the
    whole
    > temporary database. You'd still need to make sure
    > that multiple users aren't trying to create/use the same temp table.


    OK. Thanks for the advice



    > I don't know that you'll have to redesign the whole app, but I don't know
    > the equivalent of "just a squirt of oil right here and she'll run fine"
    for
    > this.


    I could try...

    Thanks for all Larry


    Patrick Guest

  13. #13

    Default Re: Access + Asp + multiuser + table lock

    > Does 2, 1, 3, 5, 4 work?

    I'm not sure to understand what you mean.
    Do you think that using transactions can cause the error ?
    > Since DuplicateTable is unique, do you need the transaction?
    In case of trouble (server hangs), transaction will not commit and my
    database will not be polluted by temporary tables that should have been
    destroyed.


    Patrick Guest

  14. #14

    Default Re: Access + Asp + multiuser + table lock

    > > 1. Begin a transaction
    > > 2. Create duplicates of empty model tables : "SELECT * INTO
    DuplicateTable
    > > FROM ModelTable". Of course DuplicateTable is a unique name.
    > > 3. Use lot of "conn.execute sqlstring" instructions to add/delete/update
    > in
    > > the duplicate tables
    > > 4. Destroy duplicate tables
    > > 5. Close transaction
    >
    > Does 2, 1, 3, 5, 4 work?
    It works ! Why ?


    Patrick Guest

  15. #15

    Default Re: Access + Asp + multiuser + table lock

    "Patrick" wrote
    > > > 1. Begin a transaction
    > > > 2. Create duplicates of empty model tables : "SELECT * INTO
    > DuplicateTable
    > > > FROM ModelTable". Of course DuplicateTable is a unique name.
    > > > 3. Use lot of "conn.execute sqlstring" instructions to
    add/delete/update
    > > in
    > > > the duplicate tables
    > > > 4. Destroy duplicate tables
    > > > 5. Close transaction
    > >
    > > Does 2, 1, 3, 5, 4 work?
    >
    > It works ! Why ?
    I don't know, sorry.

    But since "Select into" caused the problem,
    moving it out of the transaction seemed sensible.

    Does the error message give you any clue about
    which table is locked - DuplicateTable or ModelTable?

    --
    roger


    roger Guest

  16. #16

    Default Re: Access + Asp + multiuser + table lock

    Damned !
    This caused me so much trouble and the solution was so easy !

    I'm not forced to use duplicates of empty tables anymore because it seems
    that the problem didn't come from queries or recordsets but from the fact of
    using a transaction.
    Thanks for the advice roger as it helped me a lot.

    But if someone has an idea why transactions causes table locks, don't
    hesitate to explain !


    "roger" <mothlandbtopenworld.com> a écrit dans le message de
    news:c5r9if$4kf$1sparta.btinternet.com...
    > "Patrick" wrote
    >
    > > > > 1. Begin a transaction
    > > > > 2. Create duplicates of empty model tables : "SELECT * INTO
    > > DuplicateTable
    > > > > FROM ModelTable". Of course DuplicateTable is a unique name.
    > > > > 3. Use lot of "conn.execute sqlstring" instructions to
    > add/delete/update
    > > > in
    > > > > the duplicate tables
    > > > > 4. Destroy duplicate tables
    > > > > 5. Close transaction
    > > >
    > > > Does 2, 1, 3, 5, 4 work?
    > >
    > > It works ! Why ?
    >
    > I don't know, sorry.
    >
    > But since "Select into" caused the problem,
    > moving it out of the transaction seemed sensible.
    >
    > Does the error message give you any clue about
    > which table is locked - DuplicateTable or ModelTable?
    >
    > --
    > roger
    >
    >

    Patrick Guest

  17. #17

    Default Re: Access + Asp + multiuser + table lock

    Larry Linson wrote:
    > Patrick, unfortunately, I don't do .asp, and do little ADO, so I am
    > not a particularly good source of details. In my experience, however,
    > it is rarely necessary to create temporary tables, and it causes
    > enough people enough trouble that it's a good idea to avoid it if
    > there is any other way. If you do so in Access itself, a
    > well-regarded approach is to create a temporary database, and then
    > create the temporary table in the temporary database. When it is no
    > longer needed, you use the KILL statement to delete the whole
    > temporary database. But, I am not at all sure what the ADO / ADOX
    > code would be to accomplish this -- it's easy using DAO. You'd still
    > need to make sure that multiple users aren't trying to create/use the
    > same temp table.
    >
    Patrick, if you don't have Visual Studio, the ADOX online help can be found
    here:
    [url]http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp[/url]

    Another article that I have found useful is:
    [url]http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate.asp[/url]

    You will find the procedure to create a database in there, although you will
    have to rewrite the code examples which were written for VB to vbscript
    (mostly a matter of getting rid of the type declarations from the dim
    statements and replacing Set ... = New ... with Server.CreateObject)

    You can use ADOX to create a database. I think you will need to use the
    FileSystemObject to delete the database file once you are done with it. You
    can find help with FSO at [url]www.aspfaq.com[/url].

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  18. #18

    Default Re: Access + Asp + multiuser + table lock

    thanks Bob.

    It looks that my problem comes from the use of transactions.
    Check the same topic in "
    microsoft.public.inetserver.asp.db" if you're interested


    Patrick Guest

  19. #19

    Default Re: Access + Asp + multiuser + table lock

    "Patrick" wrote
    > But if someone has an idea why transactions causes table locks, don't
    > hesitate to explain !
    Well that's their purpose when you are changing data,
    to stop other users seeing or changing data that may
    no longer be current, or even exist.

    It's not so obvious when you are reading data
    e.g. from ModelTable.

    But if that's locked too, I'm not really *that* surprised


    --
    roger


    roger Guest

Similar Threads

  1. Access multiuser / transactions / asp
    By Patrick in forum ASP Database
    Replies: 13
    Last Post: April 8th, 10:03 PM
  2. Replies: 3
    Last Post: August 30th, 02:42 PM
  3. lock table overflow
    By Bill Hamilton in forum Informix
    Replies: 12
    Last Post: August 21st, 09:00 PM
  4. Transactions and Table Lock
    By Andreas Bretl in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:02 PM
  5. Lock a table
    By Panos Stavroulis in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 01:47 PM

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