parallel shell cause portal block

[Manuele Pesenti]

Hi!
I want to notify a strange behaviour. If I run any kind of parallel
command, such as calling web2py.py with -K option to run my scheduled
task or with -S <app> -M to test manually some function in my
environment it cause a portal block. The portal will reply again only
from the moment I kill the parallel process. Any idea??

Thank you very mutch
Cheers

Manuele

[Marin Pranjić]

It might be that database is blocking because you never end a transaction.

Do you have db.commit() in your task?

Marin

    Manuele

--
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 web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Manuele Pesenti]

Il 24/04/14 21:03, Marin Pranjić ha scritto:

> It might be that database is blocking because you never end a transaction.
> Do you have db.commit() in your task?
>
> Marin

Hi Marin,
thanks for you replay. The only commit explicitly called is inside a
scheduler function where afaik is necessary because in scheduler run I
don't have submit transitions... right?

Cheers

M.

[Michele Comitini]

you *shall* commit or rollback as frequently as possibile in your
parallel task. Otherwise you keep an open transaction that is likely
to lock the whole database.

[Niphlod]

let's clear things up.....

a) as a general rule of thumb, one should commit() as soon as the modifications issued to the backend are atomic and leave the state of the database as consistent with the data model and the app code
b) the web2py "web" part commit()s at every end of a successful request automatically. It also rollback()s on errors
c) BOTH web2py "shell" and "scheduler" modes NEED to be told to commit() or rollback() whenever deemed useful. If you're not concerned about open transactions, at the very least issue a commit() (or a rollback()) before the end of the task (or before leaving the shell)

Now, back to "concurrency issues". If you're using SQLite, you should know that a single write operation on any table of the database blocks ANY read operation on any table until you commit() (or rollback()). It's also true that any read operation blocks any write, but that's a minor issue since read(s) are usually fast and writes are blocked only for that particular table (and read operations don't need any commit() or rollback()). If you activate WAL, things are better, but SQLite still suffers a lot on the concurrency side.

If instead you're using any "serious" db backend (mssql, oracle, postgresql, mysql) concurrency SHOULDN'T be an issue. Unless misconfigured any of those handles without hiccups concurrent readers and writers.
This doesn't mean that you should forget commit()ing (or rollback()ing) .... an open transaction that updates 1M rows is going to take a toll on the subsystem anyway .....but alas, no blocking should be observed.
That's why there are a ton of books on the matter of mvcc, transaction conflicts merging, "multiple version of truth", write-ahead logs, etc. .............. that's one of the many basic reasons we use databases and not csv files to process data! :-P

[Michele Comitini]

I suggest not to keep open transactions for days in any case. Keeping
the state of the application in an uncommitted transaction is usually
sign of a bug easy to fix. Some ORM users do it without understanding
the implications. (I have experience of such bad designs in Hibernate
applications I had to fix).

Keeping a long backlog becomes very expensive in terms of resorces,
and things tend to slow down. I know that PostgreSQL had some locking
issues before 9.2 because serialiazed isolation level was locking
rows.