1

I have a program that multiple computers will be running that all need to read/write from the same SQLite database. Each program is performing an action on a file and it requests an "available" file name from a list stored in an SQLite table. At most, there will be 6-10 users running this. Pseudo code would be...

con = sqlite.connection(db,timeout=60)
filename = select file from tablename limit 1;
update tablename set status ="busy" where file = filename;
<..perform action.  takes 2-5 minutes..>
update tablename set status ="Finished" where file = filename;
repeat

So each transaction is very quick but unfortunately I'm still running into "db is locked" issues even when I set the connection timeout really high. I've read up on the asynchronous vfs in apsw but it sounds like the queue manager is only local to one machine. Any advice on how to proceed?

I should add I am under an IT restriction and can not set up a proper SQL server at my desk.

VLAZ
  • 26,331
  • 9
  • 49
  • 67
njfrazie
  • 91
  • 1
  • 14

1 Answers1

0

(Disclosure: I am the APSW author) The asynchronous VFS was deprecated quite a while ago by the SQLite team and hasn't been included with APSW for years. Far better integrated equivalent functionality is provided in the SQLite core using write ahead logging.

... local to one machine ...

That implies you are trying to use SQLite over the network. Do not do that. It will not work. It will initially appear to work, but eventually you will get corruption. You cannot test in advance to prove the corruption will not happen. More details here.

Community
  • 1
  • 1
Roger Binns
  • 3,203
  • 1
  • 24
  • 33