Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    File locks on Access .LDB files (Access 97)

    I must confess my total ignorance on this matter, but on NT 4 I have been looking at the number of file locks which are issued against shared Access database .LDB files on one of our file servers (the maximum found was 38!).

    My questions are:

    1) if there are a large number of locks issued for a particular file on behalf of a user, does this indicate poor Access programming?

    2) will a "large number of locks" cause any form of performance problem on an NT4 server?

    Thanks very much!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File locks on Access .LDB files (Access 97)

    What exactly do you mean by a "large number of locks"? There is an ldb file for the application database and another for the backend. Are you talking about the back end ldb file?
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File locks on Access .LDB files (Access 97)

    By "largenumber of locks" I'm referring to the output from NET FILES running on the server. Here's part of it:
    <pre>Path User name # Locks
    F:SYS...AGR_SVRSWorkflow_AG.ldb USERAA 23

    F:SYS...cherished plates.ldb USERBA 10

    F:SYS...ComplaintsV2.ldb USERCA 37
    F:SYS...ComplaintsV2.ldb USERCB 36

    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDA 32
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDB 34
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDC 32
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDD 34
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDE 37
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDF 35
    F:SYS...CUSTOM~1ComplaintsV2.ldb USERDG 34

    F:SYS...DEALER~1Dealer.ldb USEREA 11

    F:SYS...Early Terminations.ldb USERFA 13
    F:SYS...Early Terminations.ldb USERFB 12
    F:SYS...Early Terminations.ldb USERFC 12</pre>


    Obviously dummy usernames, and don't worry about the F:SYS -- it's NOT Novell! They didn't bother changing the directory names after a conversion to NT 4 a year or two back. I've only included a smattering (out of 4000-odd lines) of those with more than 10 locks, there are large numbers of .LDB files which show no locks whatever.

    I'm afraid I don't know what you mean by "an ldb file for the application database and another for the backend".
    The users run Access 97 on their workstations and are accessing (aargh, no pun intended!!) a number of common Access databases located on an NT4 file server, which I presume are each composed of two files, databasename.MDB and databasename.LDB. Does that elucidate?
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File locks on Access .LDB files (Access 97)

    A little explanation of what an ldb file is, and how it works.

    First of all, when a user logs into a database, there is a tiny portion of the .mdb file, that has a bit set, or not. That portion I believe is 512 bytes, 2 bytes each for the possible 255 users (and a set of header bytes). I may be a little rusty the specifics. The user finds the first 'available' spot. Then the same spot in the .ldb file is written to show the user is in the database (the ldb file is created if there were no previous users, and it is destroyed when the last person leaves). The ldb file is split into 64 byte chunks. The 64 byte chunks are split in half, one half is the Access User name used by the user, and the other half is the User's computer name. The information in the .ldb file is never deleted, it just gets destroyed with the .ldb file, when everyone logs out. What determines if someone is active is that small header bit of data within the .mdb itself.

    Now, here's where it gets fun. Whenever someone is doing something, that requires a lock on a portion of a database. (Reading, writing, deleting, etc.) A lock is NOT placed on the database .mdb itself. Instead, it is placed on a virtual portion of the .ldb file. If you do the math, 64 byts per user, times 255 possible users, the most an .ldb can actually get up to is 16,320 bytes, or 16k.

    There is an entire 'virtual' structure to the .ldb file. Let me back up a second. When you use read/write commands to a file system, you can lock a portion of the file for writing. You can actually lock a portion that doesn't exist yet. (Say you have a 16k file, you could lock a bit 500 megs out.) That lock isn't visible to anything other then the File System. It doesn't increase the size, it is just a lock stored within the File Systems cache. So, when the locks are created, to perform various database operations, they are actually placed onto the 'extended' virtual portions of the .ldb file. That allows other users to check the virtual locks on the .ldb before they perform a task.

    Make sense?

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File locks on Access .LDB files (Access 97)

    Drew, thanks for the long explanation, much of which makes sense!

    If I can paraphrase your answer, answering the first question I originally posed...

    the number of locks which a user has for a particular Access database .LDB file is proportional to the "amount of real data" which needs to be locked in the "real .MDB file", and no inference can be drawn just from the number of locks whether or not the Access programs have been written badly.

    However, you make no comment on whether a large (total) number of file locks has any performance implications on the file server where the database(s) are stored. Do you have a view on this?

    Thanks again for the help!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File locks on Access .LDB files (Access 97)

    Hmmmmm....good question. There are three basic aspects to good database design. 1-Table structure/Data Structure 2-Data communication, and 3-GUI. The number of locks cannot tell you much about #1. A good data structure is based on how well the relations are setup, thus the number of locks is almost useless to determine this. #2 is something that the number of locks can shed light on. With an Access database, you have to methods of grabbing/writing data. Bound, and unbound. Bound means that when a form is open, it is 'connected' to the data, thus locks are set. Unbound means that the data is only connected to when needed. A lot of persistant locks probably means that there are bound forms involved. An unbound connection would have locks that are persistant for milli-seconds, possibly full seconds if large data crunching is occurring. Whether the design is poor because it is bound, well that's a judgement call. I personally write most of my stuff to be unbound. It's sort of like souping up an engine. It allows for a lot more. However, bound is a much faster method of development, and it also is built to catch stuff automatically (where as Unbound requires the developer to cover all the aspects). #3 is obviously completely impossible to determine from the db locks.

    Does that answer your question? To answer in a more concise manner, the db locks can only hint as to whether the db is built as a bound application, or an ubound application, based upon how long the locks stay.

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File locks on Access .LDB files (Access 97)

    OK, I appreciate that the locks are a highly-transient phenomenon, and my NET FILES values were just a snapshot at a point in time.

    You (again!) made no comment on any performance implications for an NT server which (at any point in time) migt have hundreds (thousands?) of locks issued against .LDB files -- are there any, as far as you know, or am I just worrying unnecessarily?

    This probably isn't your area, but I'm also concerned about the total number of file handles which are open at once, and any performance implications for the NT server. TechNet is surprisingly vague about this matter...!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: File locks on Access .LDB files (Access 97)

    I don't know of any quantative method of analyzing a server in your situation, but I can share some anecdotal experience. We ran a relatively modest server (Dual Pentium Pros, 1GB RAM, software RAID 5) for two or three years where we had about 30 databases that were used regularly by some 80 users and got very acceptable performance. However there were maintenance issues from time to time when people managed to corrupt things. We migrated nearly all the databases to SQL Server (generally ODBC linked) which solved the maintenance issues, and made performance even better. I should add that the same server ran one large SQL Server back-end as well as storing Access MDB shared front-ends prior to upgrading to Access 2000 and migrating all back-ends to SQL Server.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File locks on Access .LDB files (Access 97)

    I can't give you a definite answer, just some experience I had that may relate
    - we had a multi-user database (1-4 users) on our Novell server, and the performance was an absolute dog
    - the developer couldn't see the problem on his machine, but that's because both front and back-end database were local
    - solution was IT had to really push up the number of locks available, didn't seem to hurt the server, and made the database a lot better to use

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File locks on Access .LDB files (Access 97)

    Server performance issues. I don't think the .ldb locks are really going to affect the server performance at all. It's not reading or writing any real data, it is just adding, checking, or removing byte sized locks. Any server should be able to handle thousands of those in it's sleep. Now, what is being read/written to on the actual .mdb file, that's a different story.

    The only issue I would really be concerned about is the allowable number of locks on your system. I know NT 4.0 has a 2048 limit. I think that is files per user though, not locks per file per user...which would be the total number of locks. I think Windows 2000 has a 64k limit. (We have an NT 4 domain here, so I have dealt with NT 4 quite a bit.)

  11. #11
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File locks on Access .LDB files (Access 97)

    Thanks to everyone for their replies - the general conclusion seems to be that I need not worry too muich...

    Gosh, this is a busy forum!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

Posting Permissions

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