Gustav Brock
Gustav at cactus.dk
Fri Aug 26 08:22:38 CDT 2011
Hi Jim I think the values very raised from NetWare 5. Prior to that, raising the values required running two command lines on the console without a reboot. Those were the days when you ran a server with 32 MB ram so every KB saved was of value. /gustav >>> jimdettman at verizon.net 26-08-2011 14:43 >>> << Tweaking the registry key to increase the locks did the trick, although I would question why the hell it is so darn low to start with?>> You would think by now they would have raised it. The reason for the limit originally was Novell Servers. That had a max locks setting of 10,000, which was there to prevent run away apps/processes from consuming all server resources. Novell refused to raise the limit, even though Access databases being hosted on a Novell servers was a common occurrence. So JET was set at 9500. Along the way, JET has been modified internally to breakup transactions when it can and changes in locking behavior reduced the number of locks being taken or held considerably. Externally, you can set the "Use transactions" property in queries to no. If you do, as soon as you hit the lock limit, the transaction is flushed. <<It is my limited understanding that this will create 50,000 "Locks".>> That depends. If your doing it all in a transaction, then yes, every single page (or record) will have a read lock placed on it. That includes index pages as well if their being used. <<Is there a way to avoid or minimize these locks? >> See the above in regards to queries. There are also a bunch of JET engine settings that will affect the locking behavior (basically if modifications are written to disk asynchronous or synchronously and when). Depends too on your method of access. DAO has not been updated in some time. The OLEDB provider however has quite a few options, some of which are not available that I'm aware of through DAO (ie. promote page locks to a table lock). I've also been told in the past that if a database is open in exclusive mode, locks are not placed at all. I have never verified that however. It seems though that with the changes made through the years that the 9500 is really not a problem for the most part. It's pretty rare that I see anyone say that had an issue with it and it's always when their dealing with hundreds of thousands of rows. Still, they could boost it I think. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Wednesday, August 24, 2011 07:02 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Basic Questions about Access Locking Hahaha, me neither. I got this error yesterday when I had a table of 200K+ rows that I wanted to add a primary key to (using autonumber - ok, not trying to restart the 'what is a real key' debate here - autonumber would do the task for what I needed just fine). I could only add 9500 in a single go, kinda pointless against 200,000+ entries. Anyway. Tweaking the registry key to increase the locks did the trick, although I would question why the hell it is so darn low to start with? Again, I could probably Google this for more blurb, but soon as I fixed the issue I lost considerable interest in further poking around - I was in a hurry yesterday. Cheers Darryl.