This is a very point. I will add it to the manual. Massimo
On Jan 4, 6:53 pm, Michael Toomim <[email protected]> wrote: > I just figured out why transactions were confusing me and would like > to share. > > The default for postgresql makes every statement its own transaction > unless you issue "BEGIN TRANSACTION", in which case nothing commits > until you issue "COMMIT". This is called "autocommit" mode in > postgresql, because each statement is automatically committed unless > it is in a transaction. This goes AGAINST the sql92 standard, so you > can turn it off with "\set autocommit off". > > In web2py, "automatic commits" actually means the opposite. web2py > follows the SQL standard, where you never need to issue "BEGIN" (it is > implicit before every query) and nothing commits until you issue > "COMMIT". On top of that, web2py automatically issues "COMMIT" for > you after every controller function exits, unless it throws an error, > in which case it issues a ROLLBACK. > > So the confusing part for me is that postgresql has a completely > different type of "autocommit" mode, and that transactions work > opposite of the default in postgres. It might help future users to > add a line to the end of the "commit and rollback" section in the > manual that says something like "Note that web2py has no explicit > BEGIN TRANSACTION statement which PostgreSQL users may be accustomed > to. Web2Py uses the equivalent of "\set AUTOCOMMIT OFF" as described > athttp://www.postgresql.org/docs/8.4/interactive/app-psql.html."; > > Massimo, what would you think of adding this to the manual? > > On Jan 4, 12:56 pm, mdipierro <[email protected]> wrote: > > > On Jan 4, 1:54 pm, Thadeus Burgess <[email protected]> wrote: > > > > Can you disable auto_commit so that you have to explicitly declare > > > db.commit() ? > > > You can already expliticely db.commit() > > > > Also, say I have the following > > > > db.table.insert(myfields=...) > > > > and then later in the same request, would a > > > > db.table.select() pull in the newly inserted record, or does the > > > record need to be committed first? > > > It will show even if you do not commit > > > > If I insert data in a long running request, and then another request > > > comes in that is opened in another thread, does it have access to any > > > uncommitted data from the other running threads? > > > No unless you db.commit() after each insert (and you should if the > > process is long running or it may lock the database). > > > > -Thadeus > > > > On Sun, Dec 27, 2009 at 3:51 PM, mdipierro <[email protected]> > > > wrote: > > > > a request arrives web2py creates a new database connection object > > > > or pools an existing connection from a connection pool, then it > > > > creates a cursor object. All db IO in the request is done via the > > > > cursor object. This is thread-safe in the sense that the cursor is > > > > only used in one thread. As soon as the request completes, the > > > > transaction is committed or rolled back and the connection is closed > > > > or recycled. > > > > > You can pass the db object to a thread and use it in another thread > > > > BUT you must make sure that t > > -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
