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. 2014-04-25 19:33 GMT+02:00 Niphlod <niph...@gmail.com>: > 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 > > > On Friday, April 25, 2014 12:13:28 PM UTC+2, Michele Comitini wrote: >> >> 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. >> >> 2014-04-24 23:03 GMT+02:00 Manuele Pesenti <manuele...@gmail.com>: >> > 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. >> > >> > -- >> > 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. > > -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
