[Zope] SQL transaction uncommited?

[Gabriel Genellina]

At Saturday 17/12/2005 09:31, Vlada Macek wrote:

>in ZopeBook I read that Zope's transaction are tied to SQL backend's
>ones. I do not know whether this applies to ZSQL methods only, but I
>do not expect it. I got this table:


>     def manage_afterAdd(self, item, container):
>         if item is self:
>             self.db = getattr(self,MY_CONNECTION_ID)._v_database_connection

Keeping a _v_ attribute is *not* a good idea - it's suposed to be *volatile*.
It's not clear from this partial fragment, but if self inherits from 
Connection, use __call__ instead -i.e. place a () after the object- to 
obtain its database connection (using __call__ ensures that the connection 
is open).

>     def qry(self, query):
>         results = self.db.query(query)
>         recs = {
>             'names': Results(results).names(),
>             'tuples': Results(results).tuples()
>         }
>         return recs

Why create a Results object twice?
results = Results(self.db.query(query))...


>     def test0(self):
>         self.qry('insert into testtable (n) values (10)')
>
>     def test1(self):
>         self.qry('insert into testtable (n) values (10)')
>         self.db.commit()
>
>     def test2(self):
>         self.qry('insert into testtable (n) values (10)')
>         self.qry('commit')
>
>     def test3(self):
>         self.qry('insert into testtable (n) values (10)')
>         return self.qry('select * from testtable')
>
>     def test4(self):
>         self.qry('insert into testtable (n) values (10); commit')
>
>     def test5(self):
>         self.qry('insert into testtable (n) values (10); commit')
>         return self.qry('select * from testtable')
>
>When I call test0,1,2,3 -- the inserted row is not visible in psql,
>but the sequence is incremented! I'm afraid there is some pending
>transaction I cannot reach at that moment anymore.
>
>The test4,5 properly add the row to table (and increment the seq of
>course) as I can watch via psql immediatelly.
>
>In test3 the returned result does not contain the new row, in test5
>the new row is contained in select.

I don't know how the ProgressSQL adapter is implemented, but *if* every 
query() call created a different connection or started a different 
transaction -which does not complete until the Zope transaction commits 
finally- *then* you would see those results.
Updates made by the *same* transaction should always be available to that 
transaction, whatever the trans. isolation level in use.
If this was the case, that may indicate that the Progress adapter is not 
properly written.

>What am I doing wrong and how must I arrange it not need to care about
>transactions at all? I expected the SQL transactions are transparent
>in Zope and they are either commited when the webbrowser gets the
>no-exception response and rolled back otherwise.

I've only used the ZODBCDA adapter and it does not show these phenomena; 
its SQL transactions *are* coupled to the Zope transaction (I learned that 
the hard way, in a corner case...)


Gabriel Genellina
Softlab SRL