Re: [Zope] SQL transaction uncommited?
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
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope] SQL transaction uncommited?
Hi,
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:
CREATE TABLE testtable
(
id serial, -- uses its own sequence
n int4
);
I use PostgreSQL and I have psql (an interactive SQL processor) opened
and I'm watching the testtable and its primary key sequence object.
I'm writing a product tool and I test the SQL connection with the
following code:
def manage_afterAdd(self, item, container):
if item is self:
self.db = getattr(self,MY_CONNECTION_ID)._v_database_connection
def qry(self, query):
results = self.db.query(query)
recs = {
'names': Results(results).names(),
'tuples': Results(results).tuples()
}
return recs
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.
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.
Thanks in advance!
--
\//\/\
(Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.)
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
Forwarded Message Date: 17. Dezember 2005 13:31:23 +0100 From: Vlada Macek [EMAIL PROTECTED] To: zope@zope.org Subject: [Zope] SQL transaction uncommited? Hi, 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: Dealing with transaction is the task of the database adapter and not the ZSQL methods. If you need to deal with transactions yourself you must subclass the transaction manager TM and implement the hooks for commit and abort (see TM.py). -aj pgpnBYS5rqhbu.pgp Description: PGP signature ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
[At 17.12.2005 13:49, Andreas Jung kindly sent the following quotation.] Dealing with transaction is the task of the database adapter and not the ZSQL methods. If you need to deal with transactions yourself you must subclass the transaction manager TM and implement the hooks for commit and abort (see TM.py). Yes, I looked at TM.py. I should have mentioned that I use ZPsycopgDA and ZPsycopgDA/db.py is subclassing and using TM already. So I was expecting the _finish() and _abort() are bound to PostgreSQL in the SQL connection I use. In the meanwhile I also discovered that when I put my 'insert into testtable...' to ZSQL method, the behavior is the same -- psql does not see new row, but the sequence is incremented. Now it's strange, isn't? With each ZSQL method call I now found out that a new connection to the database is keeping opened and RowExclusiveLock for testtable and AccessShareLock for its sequence is pending. In now have tens of connections and locks from my tryings. I may did some error in configuration, but I have no clue where and which. Python 2.3.5, Zope 2.7.5-final, psycopg2-2.0b6, PostgreSQL 8.1. -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] SQL transaction uncommited?
[At 17.12.2005 15:17, Tino Wildenhain kindly sent the following quotation.] In the meanwhile I also discovered that when I put my 'insert into testtable...' to ZSQL method, the behavior is the same -- psql does not see new row, but the sequence is incremented. Now it's strange, isn't? Actually not. sequences are incremented under any circumstances. Thats per definition. By strange I meant the behaviour of ZSQL method is the same as my product's. The seq incrementation is of course good thing. :-) With each ZSQL method call I now found out that a new connection to the database is keeping opened and RowExclusiveLock for testtable and AccessShareLock for its sequence is pending. In now have tens of connections and locks from my tryings. I may did some error in configuration, but I have no clue where and which. Python 2.3.5, Zope 2.7.5-final, psycopg2-2.0b6, PostgreSQL 8.1. If all goes well and no exception bubbles up to the zpublisher, it commits that transaction - both on zope and the database. In your case something seems to prevent that, this could be an error from one of your queries which you swallow in some try:/except: somewhere? Maybe you can lay out a simple test case where this happens. After a further investigation, the test case is fairly narrow now: I create a ZPsycopgDA (2.0b6) connection object (txn mode SERIALIZABLE). I create a ZSQL method bound to this connection and containing insert into testtable (n) values 1. When I use the Test tab to execute the method, command executes ok (This statement returned no results.). And the statement log of PostgreSQL shows: Dec 17 15:22:09 postgres[19407]: [2-1] LOG: connection received: host=127.0.0.1 port=36685 Dec 17 15:22:09 postgres[19407]: [3-1] LOG: connection authorized: user=tuttle database=his Dec 17 15:22:09 postgres[19407]: [4-1] LOG: statement: SET DATESTYLE TO 'ISO' Dec 17 15:22:09 postgres[19407]: [5-1] LOG: statement: SHOW client_encoding Dec 17 15:22:09 postgres[19407]: [6-1] LOG: statement: SHOW default_transaction_isolation Dec 17 15:22:09 postgres[19407]: [7-1] LOG: statement: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Dec 17 15:22:09 postgres[19407]: [8-1] LOG: statement: insert into testtable (n) values (1) Dec 17 15:22:09 postgres[19408]: [2-1] LOG: connection received: host=127.0.0.1 port=36686 Dec 17 15:22:09 postgres[19408]: [3-1] LOG: connection authorized: user=tuttle database=his Dec 17 15:22:09 postgres[19408]: [4-1] LOG: statement: SET DATESTYLE TO 'ISO' Dec 17 15:22:09 postgres[19408]: [5-1] LOG: statement: SHOW client_encoding Dec 17 15:22:09 postgres[19408]: [6-1] LOG: statement: SHOW default_transaction_isolation Dec 17 15:22:09 postgres[19409]: [2-1] LOG: connection received: host=127.0.0.1 port=36687 Dec 17 15:22:09 postgres[19409]: [3-1] LOG: connection authorized: user=tuttle database=his Dec 17 15:22:10 postgres[19409]: [4-1] LOG: statement: SET DATESTYLE TO 'ISO' Dec 17 15:22:10 postgres[19409]: [5-1] LOG: statement: SHOW client_encoding Dec 17 15:22:10 postgres[19409]: [6-1] LOG: statement: SHOW default_transaction_isolation So the DA opens three connections forcing postgres to spawn new processes. I don't know why. These connections are pending there. What is serious here is the first connection with my statement is not committed. I think this is a bug, probably in ZPsycopgDA. Is there some other DA for PostgreSQL which is known to be stable and with txns working properly? Should I try the psycopg1? Thanks. -- \//\/\ (Sometimes credited as 1494 F8DD 6379 4CD7 E7E3 1FC9 D750 4243 1F05 9424.) ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
