Thread: Stop Access Table Locking

Is it possible to stop an instance of a program from locking another instance out of a database table?

For example, I have a networked Access Database and program on the server that everyone can run. The problem is that if two users attempt to run the program at the same time, they conflict because one of them has locked a table. To my understanding, table locking is to protect users from getting "dirty data" when the database is being updated. That is not a concern of mine since the database will not be getting updated while the user runs the program. Instead, the program is designed purely to retrieve data, so I see no real reason for table locking. Database updating is done by a separate program and is only done every 6 months or so.

Even some sort of queuing system will work, where the program will wait until a table is unlocked, if I have to do it that way. If so, how do I create such a thing?

Any help is greatly appreciated. This is a significant problem we are having.

I am not much experienced as you , but i feel you can simply achieve this by connection pooling.

If i am right then i can provide you code for that.

It is being caused by a really long transaction. And I can confirm that the tables are locking because I explicitatly got an error message that said it could not access the table because of user x locking it. Or something along those lines. It is really hard to reproduce the error since I need to make them clash into each other. Lastly, access doesn't let you use the Connection.setTransactionIsolation(int) method.

This seems a bit more like an issue with the DB you are using (which I'm not familiar enough with to give advice - perhaps try their help or a db specific forum). There might be alternatives on the client side: for example loading the data more 'lazily'...only let the client load data that you need for that moment (rather than pulling it all out in one shot). This will shorten the transactions and allow multi-client access (use things like LIMIT to select just what you need). Of course this will depend upon what you need and might not be an option. Another alternative would be to move to a more flexible and lower level db system where you can more readily control these parameters (postgres, mysql, oracle - most large data db's I've seen, used, and heard of use one of these big 3 - some database admin's I know would cringe at the thought of using MSAccess as it is built more for single person use rather than multi-user or enterprise use)