QLite and Concurrency across multiple computers with Python 2.7

Question

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.

This is precisely what SQLite shouldn't be used for. Use a proper database server such as PostgreSQL. — Alex Hall
– Alex Hall, Commented May 18, 2016 at 19:42

Community · Accepted Answer · 2017-05-23 11:58:46Z

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.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jun 6, 2016 at 2:40

Roger Binns

3,3582 gold badges30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

QLite and Concurrency across multiple computers with Python 2.7

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related