Error handler ignored on Error 3050 Locked DB, VB6
Anita
never catch the 3050.
In fact the Error 3050 crashes the entire 2 form app. The client app will
operate for 30 min, 1 hr, random periods, before this happens.
What's the secret for trapping and handling the Error 3050?
I have verified that users have read/write permissions to the database.
Is there some solution involving the .LDB file?
I thought setting the dbMaxLocksPerFile helped, but I'm not so sure now.
Anita
"Anita" <no_...@mail.com> wrote in message
news:8xQap.27112$5v7....@newsfe11.iad...
Anita
There is an .LDB file, when my client apps are active. Using this tool
http://www.mvps.org/access/general/gen0034.htm I can see the 2 users logged
in and listed in the .LDB.
ralph
I doubt you will find any reliable method to catch that error at the
Application layer in VB as it appears it is coming from the DAO
library itself. But never really tried as I feel "running out of
locks" more a symptom or warning of a bad design than something to be
managed on a frequent basis.
The default was set low since back when DAO was introduced because as
noted in the articles not all hardware supported 15,000 locks - it cut
down on phone calls. <g> The ability to over-ride the default is
useful as there are occasions at peak moments with multiple users you
can use those extra locks.
However, you are still running out of locks and therefore IMHO there
is something basically wrong with what you are trying to do. Think
about it - two users, modest tables, modest row counts - and 15,000
locks is not enough????
IIRC for your problem domain that is almost a lock for every two
rows???? (Actually locks are placed on blocks, 4k iirc. So you may
have more locked blocks than you have file. <g>) That sounds to me
like you are merely building a queue - a stack of pending operations
that are eventually never going to be completed. You could have 20,000
or 30,000 locks and you would eventually run out.
Do you really need to update a UI with every twitter and flutter of a
control? (Look back at what Helmet was doing with his PLCs.) Besides
with 15,000 pending locks - you ain't getting all the twitters anyway.
Hardware always out runs software - there is always some lag. (Windows
is not, nor are most O/Ss, real-time, they are only real-quick at
best.) Figure how much lag is acceptable and redesign your
applications within that business requirement.
You might be better off to write your own in-memory datasource. It
would take care of archieving itself (file to be used by reporting and
auditing applications),and the receiving and dispensing of data. That
would likely be a little quicker - but you would still have to define
an acceptable "lag" between your hardware and the software.
-ralph
Anita
"ralph" <nt_cons...@yahoo.net> wrote in message
news:qqunm6d9n2bvtps9a...@4ax.com...
I'd be lying if I claimed to understand data locks, and the implication of
using high values in dbMaxLocksPerFile, and how my app is creating high
numbers of these.
I'm researching this though, trying to learn how they are created, if they
have a TTL(time to live) or they time out/expire -- how to release, or reset
these locks.
If my app refreshed less often I'd hear from users that a patients call
lights are on a "long time before they display", and about that big delay in
our physical plant system alert... that the competitor's system doesn't
have. So making this display fresh frequently is important. I do have a
plan B. to prevent the continuous updates when there is nothing to display.
I have a variable in the master that contains the number of active events.
So while that value is zero I can stop the client apps from reading records,
then when an event comes on, I can Winsock each client app to start update
timer, etc.. I could even Winsock out the data to display on the client
app. We have done this sort of thing via serial for years without issue.
But not having to install an RS485 cable along side every existing ethernet
drop would be best.
I'll look into using an in-memory datasource, and what form that may take.
I've already tested pushing these active events to a text file, and read
that file from each client app, but I had lock errors with that too.
Do you know if LocksPerFile have a TTL(time to live) or they time out, or
expire -- how to release, or reset them? Would it help to have the master
app delete to .LDB file after activity has ceased, or at the nightly db
service window?(all clients are stopped via winsock for that) I understand
the .LDB stores users/clients, etc, does it store locks?. See LDB viewer
here: http://www.mvps.org/access/general/gen0034.htm. Maybe stopping the
the client updater between events and deleting the .LDB between events?
Your input is appreciated.
Anita
Another thing, I believe I've read that more of the record locking problems
began in versions after Access 97.
Is this true? If 97 was less strict about users & locking, I would switch
it back in a second. This app began in Access 97.
Helmut_Meukel
Anita,
I believe your problems are due to the continually opening and closing
of the Database.
With my storage tank program, the master app and the clients only open
the db when they start-up and close the db during shut-down.
I strongly suggest to at least open/close the db only every 15 minutes,
not every 7 seconds!
BTW, my db is still an Access 97 db, because they didn't upgrade the
client PC which is used for Database maintenance (e.g. changing tank
data like height, diameter, volume, ... in case of replacing one of the
tanks). My app however is using DAO360.
Helmut.
Anita
"Helmut_Meukel" <Helmut...@bn-hof.invalid> wrote in message
news:ikic3d$jg8$1...@news.eternal-september.org...
I have it running at 10 seconds now to test. I find that things are
sensitive about the DB & RS.Close & Set DB & RS = Nothing.
So with my current settings test things have been stable for 24 hours.
I'm going to code the master app to disable the clients' app update off when
no open events exist using winsock, I already turn it off for nightly DB
maintenance, etc.
And I'm changing the update interval to 15 seconds. During client-app off
time and nightly maint shutdown, I'm also going to (try to) delete the .LDB
file.
Maybe this will help clean up, and keep the locks count low(er).
ralph
File locks is a base File I/O service provided by the O/S for clients
to manage the serialization of requests in order to preserve data
integrity. File locks don't have a TTL. They exists as long as a
request exists - until the client removes the lock or until the
client itself no longer exists. In this case the client is Jet.
There are also different kinds of locks beyond the 'exclusive' and
'shared' of simple byte-range locking, and differences in rules for
complex scenarios and where those rules are managed. What happens for
example, if a process asks for an exclusive lock on a range of bytes
that lies within the midst of two over-lapping shared locks? (Jet, for
example, attempts to avoid this by having its data manager lock only
discrete blocks or records - which can lead to what may appear to be a
simple fetch query with a single lock request, to actually end up
requiring dozens of locks all pending on another lock.)
There is no logical or reasonable way to interfer with lock requests
by one client from another client. (Though not impossible, as what in
programming is ever impossible? It is all just Ones and Ohs sitting on
a register somewhere. <g>) And if you could, even if you could
identify a specific lock and knew exactly what it was for, you would
still be far more likely to screw something up as you would be to
"fix" it.
LDB files are used by Jet to identify other Users and their locations
(computer). They are called "locking" files but have nothing to do
with file locking themselves. They only provide high level information
for Jet - something on the order of "Hey, there is someone else in the
room" - tinkering with them will have either no effect or a disastrous
one. The only scenario I'm aware of where one has to step in, is in
the case of an improper client shutdown one might need to delete the
LDB in order to gain exclusive access to the database.
But the whole thing about locks is a bit of a "red herring" as far as
your problem is concerned. It is a symptom NOT the problem. Your app
simply has more demands for resources than your current configuration
can provide. You fix it by managing the frequency and the volume of
your requests, thus the solution lies in elswhere - not in trying to
fiddle with locks.
I'm at a lost to understand why you are having these problems with
such modest requirements. I've written dozens of applications for
chemical processing "bombs" within the same range of the number of
controls and response times without ever running out of "locks".
Go back and revisit how, where, and when you are using DAO (or ADO).
I'm positive that is where your problem lies and where a solution will
be found. Running special wires, stopping and starting services,
choking the wire with additional packets, etc. can only add complexity
and confusion. (When you go to work tomorrow check in your HP
calculator and pocket protector at the door, then put on your
programmer wizard hat and forget what all those clients represent and
look at their code.)
-ralph
Anita
Well, It is apparently not the DB connection at fault after all.
I have modified my client app's form in question to not open or read the db,
unless there is an active event.
Now after testing with the client app sitting idle for many hours, with no
db access, and no .LDB present, I'm still geting the '3050 Couldn't lock
file'.
I'm loking at a ReadINI sub I have to get an IP address from an INI file. I
suppose I'll hardcode these lookups in, one by one, to find the cause.
ralph
>
>Well, It is apparently not the DB connection at fault after all.
>
>I have modified my client app's form in question to not open or read the db,
>unless there is an active event.
>
>Now after testing with the client app sitting idle for many hours, with no
>db access, and no .LDB present, I'm still geting the '3050 Couldn't lock
>file'.
>
>I'm loking at a ReadINI sub I have to get an IP address from an INI file. I
>suppose I'll hardcode these lookups in, one by one, to find the cause.
>
Something new all the time. <g>
Download the utilities from SystemInternals
http://technet.microsoft.com/en-us/sysinternals
There are several that provide system-wide information as to who is
chewing on what at any one time. Might help.
-ralph
Anita
I really havn't been able to find an MS or msdn article explaing this error
and potential causes.
ralph
Once again I'm a tad confused - "other than MSAccess"???
Are you using MSAccess directly? Or do you mean accessing a
Jet-Formatted database from VB?
Does this error occur while reading an INI file? Or while also
accessing the .mdb file? Or perhaps both at the same time?
What is "ReadINI" using? The WinAPI INI access methods, such as
GetPrivateProfileString(), some inherent MSAccess routine, or
something of your own making? System INI file management is different
that normal File I/O.
If you download the tools from the SystemInternal site (I recommend
fetching them all while you are there) there are several utilities
that will tell you what files are being used and by whom.
AFAIK the "3050 Error" means exactly what it says - the system can no
longer service the requests for locks an application is requesting.
[Short Sidebar: With recent Windows platforms (anything Win2000 and
above - NTFS*) there are several layers between an application's
request for a FILE I/O service and a lock, and the management and
servicing of that request by the system and its lock handler. If a
wire is involved then additional actors get involved - eg. a Network
Redirector.
There are also various kinds of locks and different File I/O APIs. The
actual number of "locks" available will effected by each of the actors
involved. I suspect it is DAO/Jet since you were able to extend the
time to error by playing with its Max Number. Also consider that the
total number of locks available with a modern platform is something
like 300,000+, and theoretically limited only by the amount of memory
or whatever device I/O controller (both software and hardware) is
being used.
That 15,000 limit is likely a trottle DAO/Jet is using.
An application only makes *requests* for services, it has no control
over how, when, that request will be honored.]
I don't for one second presume I understand the internals of NTFS.
(There was a time I though I did, but I've grown up since then. <g>)
There may be other causes to why the system is running out of "locks".
For example, many types of "locks" require some kind of data caching -
perhaps the system is running out of disk space or even memory?
But in any case I'll go back to my original theme. Running out of
"file locks" is just another way of saying your application is
generating more "pending quests", asking for more resources, than the
system can honor. You have to redesign, re-structure, re-architect, or
re-something. You are not going to "fix it" anywhere except through
your code - through what you are doing and asking for.
Example: Say I had a piece of code like this ...
Dim n As Long
Dim s As String
Do Forever
s = "AFile" & CStr(n+1)
Open s
Write s, "A line of text"
Loop
And I came to you and said "The routine is failing. I need a bigger
'n'", or "I need to open more files at one time", ...
You'd likely tell me to get rid of that Forever Loop. Somewhere,
somehow you have a "forever loop" that is piling pending lock upon
pending lock.
Whiteboard it. I bet the answer will be staring you right in the face.
-ralph
[*NTFS - are you using it, or FAT?]