Error 3211 : The database engine could not lock table XXXX (AC2007)

[AOB]

Hi guys,

I have a DB with multiple import processes to take in information and populate various tables. This has been in place for some time and works with no issue.

The import process involves the creation of a local staging table, to which I import the received data, normalise it according to my own table structure, remove any duplication (i.e. same data imported repeatedly) and append the cleansed data to the main tables.

I've just added a validation step to check for data integrity. In other words, for duplicate data, rather than simply purge it as I was doing previously, I now check to see if the data has changed in any way and prompt the user if necessary (with the option to either ignore the change or update the existing record)

However, since I added this extra step, I am getting the following error message during my import process :

Error 3211 :
The database engine could not lock table 'tblStaging' because it is already in use by another person or process

It only appears a) when a data change is detected and b) when the option to update is chosen, and seems to occur after the update has taken place (i.e. I can see the records suitably updated)

But I can't replicate the error while debugging so it seems to be a run-time issue. It's definitely not another user as the staging table is deliberately local (specific to that user's front-end)

I suspect I can probably solve the problem by adding some kind of DoEvents call or brief pause somewhere but the problem is, I don't know where the problem is occuring, so I don't know where to add the line? Or if that would even solve the problem at all?

Is there an easy function I can call to check if the staging table is locked? Which I could then use to debug and identify exactly where the lock is causing a problem? Or even apply the DoEvents to release the lock? (It must eventually get released as if I step through the code, I don't get the error?)

Any and all suggestions appreciated!

Thanks

Al

 

[gemma-the-husky]

I presume it is what it says

the active user cannot undertake the process, because another user is doing something with the requested table


record locking is very hard to get right. Most of the time it is not really needed. Any locks should be retained for an absolute minimum time, and you need to guard against deadlock and similar issues.

I have to ask, though? why WOULD another user be using the staging table for a process? Is there in fact another user, or could it be yourself, with another process. That is what it sounds like it could be, actually, the way you describe the issue.

Are multiple users using a network copy of a database, or do they all have an individual copy.

 

[AOB]

Hi Dave,

That's precisely what's confusing me!

It is a split DB with multiple users each having their own local FE and a single network BE. The staging table is local; it is created within the front end at runtime and is only available to that particular user. They can't actually see it, it is in the background. It is subsequently dropped once the import process has completed.

So it's definitely not another user; it has to be a process within the same FE. It's the same staging table that I've always used; the only difference now is that I've added an extra step to check the integrity of the new data, before deleting any duplicate information. (Previously, I just deleted the duplicates in the staging table and assumed the information in those duplicates was the same as before)

But with all processes running sequentially, there should be no reason for the table to be locked when one piece finishes and the next starts. I'm guessing that there is a brief overlap from one to another which causes the problem. Hence my thought that adding a DoEvents 'somewhere' could prevent any such brief lock from causing the whole process to halt?

 

[AOB]

Solved, albeit in the loosest sense of the word...

I stuck in a DoEvents just after the dialog popup form advising the user of a data integrity issue returns to the module. To basically give the DB a chance to catch up on itself. The code now works at runtime without any error.

It must be imposing a lock on the temporary staging table and not quite relinquishing it before it attempts to delete the duplicates. Hence the problem only occurs at runtime and not during a debug.

Will mark as 'solved' but only because there's no option to mark it as 'damned if I know but I got it to stop'...

 

[gemma-the-husky]

I would remove the locks. I am sure you don't need them.

The problem might be that locking the table prevents your other process reading the data from the same table. In practice, you don't need to worry about "readers". All you need to worry about is "writers".

Having no locks does not mean the data is unsafe -access by default uses "optimistic locking" which is adequate for 99.9% of situations.

 

[AOB]

I don't impose the locks though?

I create linked tables (linked to locally saved text files) and use a SELECT INTO to create the staging table. I write the SQL in VBA and use a dbs.Execute to run the command. At no stage do I specify that the table should be locked?