For some reason I thought ZSQL method calls were atomic, but they appear otherwise. Using an eGenix mxODBC Database Connection at /Database/PoPy_database_connection to a SQL Server 2000 back end, I have created a Python script to write SQL commands and feed them in one large string: UPDATE equip SET workstation=1 WHERE eq_id=39315; UPDATE node SET node='M28057' WHERE node_id=13451; UPDATE materiel SET mat_nm='mm-M28057-H-01', owner='mmaslak', datetime='2006/04/24 15:09:07.611 GMT-5' WHERE mat_id=39315; This all gets sent as one string to a ZSQL method where the first two commands execute, but the third fails because it doesn't like the datetime string, which indeed fails when I try it alone in the PoPy connection. Why, oh why doesn't the whole thing fail like I might expect? When I wrap the call in a 'begin transaction ... commit', it becomes atomic. Nothing happens if part of it fails. Great. But failure is not detectable by the Python script. I have used try/except and if/else blocks to ascertain failure. It thinks a non-commit is AOK, not an exception. Is this function of the eGenix ODBC? Of MS SQL Server 2K? Zope/ZSQL/Python? How can I detect failure and maintain atomicity? Michael Maslak, Jr. Associate Software Engineer Anteon Corporation 294 Thames Ave Bay St. Louis, MS 39520
--On 25. April 2006 10:38:30 -0400 "Maslak, Michael" <mmaslak@anteon.com> wrote:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise.
What do you mean by atomic? ZSQL are handled by the database adaper which is usually tied to the transaction manager of the ZODB. -aj -- ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany Web: www.zopyx.com - Email: info@zopyx.com - Phone +49 - 7071 - 793376 E-Publishing, Python, Zope & Plone development, Consulting
Hi Andreas, Andreas Jung wrote:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise.
What do you mean by atomic? ZSQL are handled by the database adaper which is usually tied to the transaction manager of the ZODB.
I'm too tired to find the words, so cut and pasted from answers.com: - "Indivisible. An atomic operation, or atomicity, implies an operation that must be performed entirely or not at all. For example, if machine failure prevents a transaction to be processed to completion, the system will be rolled back to the start of the transaction." Like Michael, I had understood that ZSQL database transactions were tied into Zope transactions, and would fail or succeed as a whole. Like him, I have found that - at least with this combination of DA & RDBMS - this is not so. I work around it, as I always do when a Microsoft product is involved. If I hit the same thing with PostgreSQL, I'll look into it deeper... -- Regards, PhilK Email: phil@xfr.co.uk PGP Public key: http://www.xfr.co.uk Voicemail & Facsimile: 07092 070518 “The creative impulse animates whatever instrument is placed at its disposal.” — Robert Fripp
Philip Kilner wrote:
Hi Andreas,
Andreas Jung wrote:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise. What do you mean by atomic? ZSQL are handled by the database adaper which is usually tied to the transaction manager of the ZODB.
I'm too tired to find the words, so cut and pasted from answers.com: -
"Indivisible. An atomic operation, or atomicity, implies an operation that must be performed entirely or not at all. For example, if machine failure prevents a transaction to be processed to completion, the system will be rolled back to the start of the transaction."
Like Michael, I had understood that ZSQL database transactions were tied into Zope transactions, and would fail or succeed as a whole. Like him, I have found that - at least with this combination of DA & RDBMS - this is not so. I work around it, as I always do when a Microsoft product is involved. If I hit the same thing with PostgreSQL, I'll look into it deeper...
I was under the impression that ZSQL methods were very much intended to be atomic: http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx#2-77 I know that it's been atomic to the point of frustration when working with Zope and MySQL. Perhaps the database adapter is at fault here? -- Floyd May Senior Systems Analyst CTLN - CareerTech Learning Network fmay@okcareertech.org
Hi Floyd, Floyd May wrote:
Like Michael, I had understood that ZSQL database transactions were tied into Zope transactions, and would fail or succeed as a whole. Like him, I have found that - at least with this combination of DA & RDBMS - this is not so. I work around it, as I always do when a Microsoft product is involved. If I hit the same thing with PostgreSQL, I'll look into it deeper...
I was under the impression that ZSQL methods were very much intended to be atomic: http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx#2-77
Yes, the docs seem pretty unambiguous.
I know that it's been atomic to the point of frustration when working with Zope and MySQL. Perhaps the database adapter is at fault here?
Surely there /is/ only one point to which it can be atomic? ;-) There are several layers to this - the eGenix DA, the eGenix ODBC stuff below that, MS's ODBC driver for SQL Server and SQL server itself. If I hit the issue with the eGenix DA and another (non-MS) database, or with another DA and another (non-MS) database entirely, I would spend time trying to diagnose it. Having wasted a big chunk of my life on troubleshooting this stuff I now have a rigid policy - if an MS product is involved I want to know how it /does/ work and develop against that, otherwise I want to know how it /should/ work and make sure it does. I'm not trying to denigrate MS (I earn a living on Windows, can live with the OS, can live without the applications), I'm just making an observation on not throwing precious time away on things that are unfathomable. -- Regards, PhilK Email: phil@xfr.co.uk PGP Public key: http://www.xfr.co.uk Voicemail & Facsimile: 07092 070518 “The creative impulse animates whatever instrument is placed at its disposal.” — Robert Fripp
Maslak, Michael wrote at 2006-4-25 10:38 -0400:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise.
Your database decides whether they are atomic or not. It is not a ZSQL feature.
... Using an eGenix mxODBC Database Connection at /Database/PoPy_database_connection to a SQL Server 2000 back end, I have created a Python script to write SQL commands and feed them in one large string:
Usually, the error should trigger an exception. Unless you catch the exception, it should cause a transaction abort/rollback and this should make your database system roll back the transaction effects. Up to you, to find out what in this chain does not happen as it should. -- Dieter
Hi Michael, Maslak, Michael wrote:
How can I detect failure and maintain atomicity?
You may get more responses to this on the Zope-DB list - as well as other RDBMS + Zope users, the eGenix guys are active over there (or as active as you can be on such a quiet list). -- Regards, PhilK Email: phil@xfr.co.uk PGP Public key: http://www.xfr.co.uk Voicemail & Facsimile: 07092 070518 “The creative impulse animates whatever instrument is placed at its disposal.” — Robert Fripp
I think that your problem is in the fact that you have fed them all in one long string (and may have autocommit on.) If they were separate ZSQL methods, they would, in my experience, roll back. I recommend separating them into separate methods, with one insert per method. jim penny zope-bounces@zope.org wrote on 04/25/2006 10:38:30 AM:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise.
Using an eGenix mxODBC Database Connection at /Database/PoPy_database_connection to a SQL Server 2000 back end, I have created a Python script to write SQL commands and feed them in one large string:
...
How can I detect failure and maintain atomicity?
Michael Maslak, Jr.
<a-wild-guess> if you separate them as 3 separate ZSQL methods (or class attributes if you're using zsql via a python product) and try again, do you get the same unatomic behaviour? </a-wild-guess> I'm always using Postgresql databases and I've been using PoPy for the DA but now I'm always using ZPsycopgDA. Atomicity has never been a problem. I'm not familiar with the eGenix mxODBC DA but perhaps you should contact it's author as well as the list. Best of luck Maslak, Michael wrote:
For some reason I thought ZSQL method calls were atomic, but they appear otherwise.
Using an eGenix mxODBC Database Connection at /Database/PoPy_database_connection to a SQL Server 2000 back end, I have created a Python script to write SQL commands and feed them in one large string:
UPDATE equip SET workstation=1 WHERE eq_id=39315;
UPDATE node SET node='M28057' WHERE node_id=13451;
UPDATE materiel SET mat_nm='mm-M28057-H-01', owner='mmaslak', datetime='2006/04/24 15:09:07.611 GMT-5' WHERE mat_id=39315;
This all gets sent as one string to a ZSQL method where the first two commands execute, but the third fails because it doesn't like the datetime string, which indeed fails when I try it alone in the PoPy connection. Why, oh why doesn't the whole thing fail like I might expect?
When I wrap the call in a 'begin transaction … commit', it becomes atomic. Nothing happens if part of it fails. Great.
But failure is not detectable by the Python script. I have used try/except and if/else blocks to ascertain failure. It thinks a non-commit is AOK, not an exception. Is this function of the eGenix ODBC? Of MS SQL Server 2K? Zope/ZSQL/Python?
How can I detect failure and maintain atomicity?
Michael Maslak, Jr.
Associate Software Engineer
Anteon Corporation
294 Thames Ave
Bay St. Louis, MS 39520
------------------------------------------------------------------------
_______________________________________________ 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 )
-- Peter Bengtsson, work www.fry-it.com home www.peterbe.com hobby www.issuetrackerproduct.com
participants (7)
-
Andreas Jung -
Dieter Maurer -
Floyd May -
jpenny＠ykksnap-america.com -
Maslak, Michael -
Peter Bengtsson -
Philip Kilner