MaxLocksPerFile

[Guest]

I am trying to insert a random number field in a very large dataset (1.9 million records) so that I can break it in two without imposing a bias on the subsets. I plan to sort on the random number, then select the top half of the records

When I attempt to create the field I get an error "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. (Error 3052)". This message advises me to increase the number allowed, which is currently 9,500.

If the current number (9,500) represents the maximum number of records allowed for random number generation, I would have to increase it from 9,500 to over 1.9 million, a very scary large increase! I'd like to know what the concept MaxLocksPerFile means, what to change it to (if I should touch it at all), and whether there's any danger in changing it

 

[Tom Wickerath]

The MaxLocksPerFile setting determines the maximum number of locks Microsoft Jet places
against a file.
Microsoft has published several KB articles that involve MaxLocksPerFile:

http://tinyurl.com/3bhkd

They definitely indicate that you can increase this setting in the registry to a higher
number as long as you are not attempting to connect to a Jet Database on a Novell Server.
They also give other workarounds, such as using the SetOption Method to Temporarily Change
MaxLocksPerFile or Setting the UseTransaction Property in an Action Query to No.

Thank You Arvin Meyer for using a tinyurl in a recent post--I took a quick look at
tinyurl.com to learn more about how you created your tiny url.

Tom
___________________________________


I am trying to insert a random number field in a very large dataset (1.9 million records)
so that I can break it in two without imposing a bias on the subsets. I plan to sort on
the random number, then select the top half of the records.

When I attempt to create the field I get an error "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry. (Error 3052)". This message advises me to
increase the number allowed, which is currently 9,500.

If the current number (9,500) represents the maximum number of records allowed for random
number generation, I would have to increase it from 9,500 to over 1.9 million, a very
scary large increase! I'd like to know what the concept MaxLocksPerFile means, what to
change it to (if I should touch it at all), and whether there's any danger in changing it.

 

[Guest]

Thanks for that note. I'm still uncertain about what this concept means and about what to do. I don't feel comfortable messing with the registry, so I want to be very sure I know what I'm doing. I might add, if someone has a simpler solution to the problem of breaking up the 1.9 million records into 2 roughly equal subset that are randomly divided, that would also solve my problem
a)I understand that it is the maximum number of locks that Microsoft Jet places against a file. What I don't understand is exactly what a "lock" is and why there needs to be a maximum, which leads to my second question
b)Should I increase the number to 1.9 million to correspond to the number of records I have in the database? Or is it wrong to think of the lock limit as being related to the number of records in the database? If that is wrong, what is the right way to figure out how much to increase the value by for my 1.9 million records, to get rid of the error message and get my random numbers generated?

 

[Tom Wickerath]

I don't know the answers to your questions. Why not just try setting the UseTransaction
property of your update query from Yes to No? That may very well solve your problem,
without having to monkeydick around with a MaxLocksPerFile setting....

Tom
___________________________________


Thanks for that note. I'm still uncertain about what this concept means and about what to
do. I don't feel comfortable messing with the registry, so I want to be very sure I know
what I'm doing. I might add, if someone has a simpler solution to the problem of breaking
up the 1.9 million records into 2 roughly equal subset that are randomly divided, that
would also solve my problem.

a) I understand that it is the maximum number of locks that Microsoft Jet places against a
file. What I don't understand is exactly what a "lock" is and why there needs to be a
maximum, which leads to my second question.

b) Should I increase the number to 1.9 million to correspond to the number of records I
have in the database? Or is it wrong to think of the lock limit as being related to the
number of records in the database? If that is wrong, what is the right way to figure out
how much to increase the value by for my 1.9 million records, to get rid of the error
message and get my random numbers generated?

 

[Guest]

Thanks Tom. I certainly would prefer a workaround. I'll give that a try.

A thought:
MaxLocksPerFile sounds like a control on a device that locks out records that are being edited in a shared environment. If that's what it is, I should be able to tell Access that I'm not sharing the database. If I'm right and Access is smart, it would stop giving the error message. Not sure how to proceed with that, but it might be true.

 

[Tom Wickerath]

MaxLocksPerFile sounds like a control on a device that locks out records that are being
edited in a shared environment.

I don't know about that. I think--although I'm certainly not positive--that the
MaxLocksPerFile setting determines how much RAM memory can be allocated to handle file
locks.

Tom
________________________________________________


Thanks Tom. I certainly would prefer a workaround. I'll give that a try.

A thought:
MaxLocksPerFile sounds like a control on a device that locks out records that are being
edited in a shared environment. If that's what it is, I should be able to tell Access that
I'm not sharing the database. If I'm right and Access is smart, it would stop giving the
error message. Not sure how to proceed with that, but it might be true.