File locks on Access .LDB files (Access 97)

Topic

New Reply

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!

Reply | Quote

Replies

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?

Reply | Quote

By “largenumber of locks” I’m referring to the output from NET FILES running on the server. Here’s part of it:

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

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?

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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…!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote

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!

Reply | Quote