I guess if SQLite cannot handle many concurrent users while
writing/reading, should I move to postgres or mongoDB? There will be over
10k rows added every 10mins.
Thanks.
On Wednesday, October 14, 2015 at 10:03:30 PM UTC-4, kenny c wrote:
>
> Hi Niphlod,
>
> Thank you for this information.
>
> Could you tell me how you manage to copy new data into db from db2 after
> finishing the scheduler job?
>
> Thank you.
>
> On Friday, November 9, 2012 at 10:31:03 AM UTC-5, Niphlod wrote:
>>
>> if you can, use a separate db for the scheduler. SQLite doesn't handle
>> well concurrent writes (with default operational capabilities), so having
>> the scheduler operating on the same database of your "insertion" of
>> messages can lead to locks.
>>
>> Just do
>>
>> db = DAL('whatever.db')
>> db2 = DAL('whatever_scheduler.db')
>>
>> db.define_table('messages', .....)
>>
>> from gluon.scheduler import Scheduler
>> mysched = Scheduler(db2)
>>
>> and to queue tasks you can then use
>>
>> db2.scheduler_tasks.validate_and_insert(****)
>>
>> or, with the new API
>>
>> mysched.queue_task(***)
>>
>>
>>
>> On Friday, November 9, 2012 3:56:59 PM UTC+1, Mike Anson wrote:
>>>
>>> Greetings...
>>>
>>> I may have a problem with my database (sqlite) locking.
>>>
>>> Traceback (most recent call last):
>>> File "/home/web2py/src/web2py/gluon/scheduler.py", line 218, in
>>> executor
>>> result = dumps(_function(*args,**vars))
>>> File "applications/ircmessage/models/tasks.py", line 57, in
>>> send_unsent_messages
>>> for row in db(db.messages.status=='unsent').select(db.messages.id,
>>> db.messages.message, db.messages.uid):
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 8787, in select
>>> return adapter.select(self.query,fields,attributes)
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 2127, in select
>>> return super(SQLiteAdapter, self).select(query, fields, attributes)
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 1615, in select
>>> return self._select_aux(sql,fields,attributes)
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 1580, in _select_aux
>>> self.execute(sql)
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 1693, in execute
>>> return self.log_execute(*a, **b)
>>> File "/home/web2py/src/web2py/gluon/dal.py", line 1687, in log_execute
>>> ret = self.cursor.execute(*a, **b)
>>> OperationalError: database is locked
>>>
>>> I have a scheduler every minute that get's records (messages) out of the
>>> DB with a status of unsent. The messages then get posted to an IRC channel
>>> and updated to sent to they are not included the next time the scheduler
>>> runs the script.
>>>
>>> This seems to work just fine.
>>>
>>> It's when I inject a new message (which by default has a message status
>>> set to unsent) via a bash script. The message inserts fine but my script
>>> that posts it to the IRC channel doesn't not post anything but simply
>>> updates it's status to sent without actually sending it. By sending it I
>>> mean post a message over sockets.
>>>
>>> Here is my model for sending unsent messages:
>>> for row in db(db.messages.status=='unsent').select(
>>> db.messages.id, db.messages.message, db.messages.uid):
>>> message_id = row.id
>>> message_message = row.message
>>> message_uid = row.uid
>>>
>>> #socket connection already opened earlier in the script
>>> s.send("PRIVMSG %s :%s - %s\r\n" % (channel, message_uid,
>>> message_message))
>>> print "message %s has been sent" % message_id
>>>
>>> ## Only seems to print message when a delay is here.
>>> time.sleep(5)
>>>
>>> ## Set message record to sent and update modified field
>>> modified_stamp = strftime("%Y-%m-%d %H:%M:%S")
>>> db.messages[message_id] = dict(status='sent',
>>> modified=modified_stamp)
>>>
>>> db.commit()
>>>
>>> Inserting a message via jsonrpc (shown) and cURL (not shown):
>>>
>>> @service.jsonrpc
>>> def savemessage(message, uid):
>>> db.messages.insert(message=message, uid=uid)
>>> db.commit()
>>>
>>> message = {"status":"saved"}
>>>
>>> return message
>>>
>>>
>>> Should I simply switch to a postgresql or mysql database to prevent this
>>> locking?
>>>
>>> Thanks for any help/advice in advance
>>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
