Is Auto Number PK more likey to produce Locking errors in a multiuser db

[KeithG]

I have a db split into an FE/BE with about 15 users. Original I desinged the db with an AutoNumber PK's because I am a believe that a PK should have no meaning. When we started using the db on a daily basis multiple users were recieving errors that the record was locked but I know in fact this is not true because the users are stritly doing data entry. As a result I changed by PK to AccountNumber instead of an Autonumber PK and it seems to have fixed the problem. My question is, was it the AutoNumber PK causing the locking?

 

[The_Doc_Man]

Databases lock up for many reasons. The one that is hardest to call and hardest to prove is when it is a buffer lock.

See, Access wants to store everything in a buffer in order to write it back to the MDB file. BUT... if that buffer contains more than just the thing you are updating, everything in that buffer has to be locked. This is because internally, MDB files are simply virtual address spaces where Access puts things. The same address cannot be active in two places at once with any update possibility, so Access locks virtual address space ranges corresponding to its active buffers.

As to why the change you described would also change locking? Perhaps the tabledef (which holds the last viable autonumber value) was getting locked. When you started generating the PK another way, the tabledef didn't get locked as often. Maybe. But it's all guesswork with Access internals anyway.

 

[KeithG]

Doc Man first off I would like to thank you for your post and let you know that you are by far my favorite "poster" at this forum. There are many Access Guru's posting here but I always enjoy reading your posts the most. I have tried to IM before but you do not except them (which I totaly understand) to give you your props! You Are The Man!

My thought on the AutoNumber was that since it is a primary key it is index and access stores the AutoNum ID in Ascending order. So for instance lets user1 creates a new record but is still entering in the info on the form (so the AutoNum has already been assinged) then 2 seconds later user two tries to create a new record but the records are going to be stored in the same page in Access and user1 already has that page locked. Would you agree?

 

[KKilfoil]

Locking

Jet allowed multiple users to access the database concurrently. To prevent that data from being corrupted or invalidated when multiple users tried to write to the database, Jet employed a data write locking policy. Any single user could only modify those database records (that is, items in the database) to which they had applied a lock that gave them exclusive access to the record until the lock was released. Up to Jet 4, a page locking model was used, and in Jet 4 a record locking model was employed. Microsoft databases are organised into data "pages", which are fixed length (2 kB before Jet 4, 4 kB in Jet 4) data structures that divide up the database. Data is stored in "records", but these are of variable length and so may take up less or more than one page. The page locking model worked by locking the pages, instead of individual records, which though less resource intensive also meant that more than one record might be locked at any one time. Record locking was introduced in Jet 4.

There were two mechanisms that Microsoft used for locking: pessimistic locking, and optimistic locking. With pessimistic locking, the record or page is locked immediately when the lock is requested, while with optimistic locking, the synchronization is delayed for transactions until the operations are actually performed. Conflicts are less likely to occur with optimistic locking; since the record is locked for a shorter duration of time, there is a lesser chance of someone needing to access it while it is locked. However, it cannot be certain that the update will succeed because another user could potentially update the record first. With pessimistic locking, the update is guaranteed to succeed once the lock is obtained, but other users are unable to make their own changes until the lock is released. Lock conflicts, which either require the user to wait, or cause the request to fail (usually after a timeout) are more common with this policy.

from
http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

 

[The_Doc_Man]

Ah, Access has changed buffer sizes (again).

In any case, KKilFoil's post correctly points out that optimistic and pessimistic locking are contributors. You need to check the database settings for the type of locking involved, and also need to check the lock settings for the forms involved to see if they have too wide a scope on the locks they set. You CAN (but should not) set a form to NOLOCKS. There are RECORD and TABLE lock to consider as well.

KKilFoil reminded me of the other factors. A change to key style (from autonum to something else) shouldn't interact strongly with these settings unless TABLE is selected for lock scope.

The Help files have some pretty good articles on setting locks in forms and queries, and either could be the source of the problem. You could ALSO be accidentally setting a table lock on a read-only lookup table, which you should check as a possible explanation, but again, a change in key style should not interact with such a setting.

Unfortunately, there is a wild-card in the deck. WINDOWS communicates the updates to the LDB file which is where lock info is kept. If you do something that is relatively slow or requires access to a single item (the TableDef, my original point of focus), you are updating a lock structure in the LDB. The slower this operation (the more disk I/O is involved), the longer the lock is engaged. And the longer the lock is engaged, the higher the odds that some other person on the same mission will encounter the lock.

Changing the key structure to something that somehow is either faster or doesn't have to open the TableDef in update mode SHORTENS the time that the lock is engaged in the LDB file, which lowers the odds of encountering a locking conflict.

Therefore, my idea of what is going on remains the same, but KKilFoils article prompted me to point out other factors (lock size, lock type) that affect the odds of locking encounters. In the final analysis, it is your problem. All we can do is offer guidelines and idea. I hope I have given you a good one.

Thanks for your kind words. My normal place of work is a Dept. of Defense office that blocks IM at the perimeter firewall. I can turn on IM or not, it won't matter. (Actually, it WILL matter. I'll get lectured.)

I don't turn on IM on my home machine because I work as a security manager for a large applications/database machine and am too intimately aware of what happens when IM is turned on. So even though you have absolutely good intentions, my home firewall is still going to block IM too.

 

[boblarson]

My normal place of work is a Dept. of Defense office that blocks IM at the perimeter firewall. I can turn on IM or not, it won't matter. (Actually, it WILL matter. I'll get lectured.)

I don't turn on IM on my home machine because I work as a security manager for a large applications/database machine and am too intimately aware of what happens when IM is turned on. So even though you have absolutely good intentions, my home firewall is still going to block IM too.

Actually, I think Keith may have been attempting to say he has tried to PM you (private message through this site), not IM you. Correct me if I'm wrong Keith.

I too enjoy seeing The_Doc_Man's posts as I learn a lot and get to see I still have a LONG way to go before I consider myself highly knowledgeable.

 

[KeithG]

Actually, I think Keith may have been attempting to say he has tried to PM you (private message through this site), not IM you. Correct me if I'm wrong Keith.

You are correct Bob

 

[The_Doc_Man]

The forum has a PM?