Locking tables in Access database using Perl DBI

relax99 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
I ran into a concurrency problem with my Perl / CGI application. It uses a Microsoft Access database for data storage. I noticed that if I run a DELETE query and then a SELECT right after it, I get results that include the deleted records. If I insert sleep(1) command between the two database calls, I get the correct results. It seems to me that this behavior results from the way Access processes/caches query requests.

I am at the point where I think I need to lock the table to which I am doing the updates, but I am not sure how to do it using DBI module. I searched in groups.google.com, DBI documention and here, but couldn't find an answer. I know that Access is probably not a good choice for a multiuser application, but, if it all possible, I want to use Access initially and then switch over to something else (SQL Server or Oracle).

There's a way to lock records using ADO provider for Access, but I want to stick with DBI / ODBC for now. Could anyone give me any hints, comments, or pointers on this?

Thank you,
Alex

Comment on Locking tables in Access database using Perl DBI

Replies are listed 'Best First'.
(jeffa) Re: Locking tables in Access database using Perl DBI by jeffa (Bishop) on Dec 03, 2002 at 16:29 UTC
If there is no way to directly lock an Access file, then you could use a 'hack' and flock some temp file instead: User A comes along and attempts to delete some records, your code first checks to see if the temp file has been locked. If not, lock it - delete - unlock. If user B tries to access the database while user A's request for deletion is still being processed, user B will have to wait until the lock is freed. Of course, this still might not address the needed delay between deleting and selecting. I presume that you have to use Access? If you really don't, you might want to look at DBD::SQLite as a better replacement. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply]
Re: Re: Locking tables in Access database using Perl DBI by relax99 (Monk) on Dec 03, 2002 at 20:33 UTC
I also think this would not address the delay between deleting and selecting. I wonder how one does table/record locking with industrial strength databases like SQL Server or Oracle using Perl and DBI. I really didn't find anything in the DBI documentation that addresses locking issues.	[reply]