How to handle multiple connection objects with sqlite

This is the place for queries that don't fit in any of the other categories.

How to handle multiple connection objects with sqlite

Postby steadyonabix » Tue Dec 31, 2013 1:31 pm

Hello

I am very new to python (2 days study) although not a novice developer.

I want to create three processes that access the same table in a sqlite database; two will read and write, the other only write.

I note that the sqlite connection object will raise an exception if multiple threads try to access it at the same time. I want multiple processes to use three different connectors to access the same table...

Can anyone suggest how I can manage this?

I presume I will need to implement some form of locking and waiting mechanism but I don't know how to implement it and I don't seem to be able to find any examples online.

The table will be a messages table and the processes -

[*]Listener (Will write incoming messages to the table)
[*]State machine (Will read and write messages)
[*]Sender (Will read messages from the table for transmission and update the status column)

I understand how to create the three separate processes and instantiate connections from each. I am just looking for advice on how to handle getting an exclusive lock for each on the database. i.e. How do I detrmine if another process has made a connection at any given instant so that I can implement a wait for lock?

Thanks for any help in advance.

Brad
Last edited by stranac on Tue Dec 31, 2013 2:32 pm, edited 1 time in total.
Reason: First post lock.
steadyonabix
 
Posts: 6
Joined: Tue Dec 31, 2013 1:11 pm

Re: How to handle multiple connection objects with sqlite

Postby Yoriz » Tue Dec 31, 2013 2:36 pm

Can multiple applications or multiple instances of the same application access a single database file at the same time?
Its probably easier to just move to a database that takes care of this for you because sqlite is more for single access.
Sqlite FAQ Q5 wrote:However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 570
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: How to handle multiple connection objects with sqlite

Postby steadyonabix » Tue Dec 31, 2013 4:15 pm

Hi Yoriz

Thanks for the reply.

I'm not sure what the concurrency would be. I could have several hundred listeners all writing messages to the database but the individual TPS per node would be quite low... In fact it wouldn't even be transactions per second for each node in question.

I have read further today and the documentation for the python sqlite connector suggests that multiple connections are supported but that if any connection performs an update then the database will be locked while it is in progress. The connector would then fall back on its wait timeout setting.

What I don't want to do though is commit to a design that runs into issues after I have coded it....

Perhaps it would be best to implement my own file based message table and not use a database for it at all.
steadyonabix
 
Posts: 6
Joined: Tue Dec 31, 2013 1:11 pm


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 2 guests