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 at http://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.
