Making a ZSQL.DA fully multi-threaded?
I am using ZsapdbDA with SAPDB (now MaxDB) and have run into problems when Zope has more than one thread. The folks at SAP suggest that there should be one database connection per thread, rather than sharing one connection across threads. I haven't yet looked at the Zsapdb source, but I think it has only one connection for all threads. Is there a document somewhere that explains how to make variables "thread private" in Zope? I vaguely recall something about one cache per thread, or that variables are read- only shared unless updated by a thread, and then they're copied.. This is probably quite wrong, but obviously I just don't understand how data and threading works in Zope. So, I'm looking for a document that explains this clearly. How to have data that is not shared between threads (perhaps a kind of connection pool thingy). Also, I have other projects where I really want to have just one object of it's kind no matter how many threads. For example, gvibDA uses Thunked_TM for this, but that doesn't really work with ZSQLDA because I've seen that multiple connect calls are made, even with the THUNKED_TM. Is there a "Zen Of" document about this somewhere? Thanks -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Brad Clements wrote at 2004-7-9 09:35 -0400:
I am using ZsapdbDA with SAPDB (now MaxDB) and have run into problems when Zope has more than one thread.
The folks at SAP suggest that there should be one database connection per thread, rather than sharing one connection across threads.
This is the default for DAs as it is much easier to implement one connection per thread than one connection per DA instance...
I haven't yet looked at the Zsapdb source, but I think it has only one connection for all threads.
Then do look at the source. I would be astonished when you were right... Check whether the connection is maintained in a "_v_" attribute (in this case, it is per thread). Otherwise, the connection would need to be maintained in a module global registry indexed with a DA id (e.g. its ZODB path). -- Dieter
On 9 Jul 2004 at 21:17, Dieter Maurer wrote:
Check whether the connection is maintained in a "_v_" attribute (in this case, it is per thread). Otherwise, the connection would need to be maintained in a module global registry indexed with a DA id (e.g. its ZODB path).
This is the deep magic of which my previous note speaks. Where are these details documented (in general terms, not just about DA's), so that I can figure this out without bugging the list too much? -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
* Brad Clements <bkc@murkworks.com> [2004-07-09 22:36]:
On 9 Jul 2004 at 21:17, Dieter Maurer wrote:
Check whether the connection is maintained in a "_v_" attribute (in this case, it is per thread). Otherwise, the connection would need to be maintained in a module global registry indexed with a DA id (e.g. its ZODB path).
This is the deep magic of which my previous note speaks.
Where are these details documented (in general terms, not just about DA's), so that I can figure this out without bugging the list too much?
I don't think there are any comprehensive documentation about writing DA's but '_v_' attributes are *volatile* attributes and they are not persisted, see http://zope.org/Wikis/ZODB/VolatileAttributes. -- Roché Compaan Upfront Systems http://www.upfrontsystems.co.za
Brad Clements wrote:
On 9 Jul 2004 at 21:17, Dieter Maurer wrote:
Check whether the connection is maintained in a "_v_" attribute (in this case, it is per thread). Otherwise, the connection would need to be maintained in a module global registry indexed with a DA id (e.g. its ZODB path).
This is the deep magic of which my previous note speaks.
Where are these details documented (in general terms, not just about DA's), so that I can figure this out without bugging the list too much?
Just checking: you do know that each thread in ZODB gets what appears to it to be it's own copy of the ZODB right? So, even though there's only one connection stored in the class of the DA, that'll mean there's one DA connection opened up per Zope thread. This often means that many more database connections are opened than are actually necessary. And if you start timing out inactive database connectiosn on the database side, you're in for a world of pain and suffering. I branched ZOracleDA some time ago to move away from this model for that very reason... What's the actual problem you're experiencing? Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
On 16 Jul 2004 at 8:57, Chris Withers wrote:
This often means that many more database connections are opened than are actually necessary. And if you start timing out inactive database connectiosn on the database side, you're in for a world of pain and suffering.
I branched ZOracleDA some time ago to move away from this model for that very reason...
What's the actual problem you're experiencing?
I have set the SAPDB timeout to 32400 seconds. However when running with more than 1 thread I still get "mysterious segfaults" in the sapdbapi component. The SAP folks say their adapter is "multi-thread capable", but I've come to believe that only means that it's ok to open a connection in one thread and use it in another, but NOT to have 2 threads make requests on the same connection at the same time. Also, two threads can' t open a connection "at the same time" because their connection table management isn't "thread safe". What I need, I suppose, is a DB connection pool mechanism of some kind. I also have Python extensions that need a connection as well. Getting the actual database connection from a DA stinks (ie, it's not really possible). I'd like to see an API extension to ZopeDA's to allow Pythonscripts, Products and external methods get an actual db connection. But that I suppose is a problem to solve later. I think I will take a look at ZOracleDA to see what you did. -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
On Fri, 16 Jul 2004 10:36:27 -0400, Brad Clements <bkc@murkworks.com> wrote:
The SAP folks say their adapter is "multi-thread capable", but I've come to believe that only means that it's ok to open a connection in one thread and use it in another, but NOT to have 2 threads make requests on the same connection at the same time.
I would be surprised to find any database API that allowed that. Generally "thread-safe" means that there are no global state variables, i.e. all connection state is encapsulated in the connection object/struct. Generally I would not try to share connections between threads at all. To do it, you'd need to ensure that a thread performs a commit or rollback on the connection before another thread can use it, i.e. performs a complete transaction or none at all. Otherwise you will have all sorts of interesting problems.
Also, two threads can' t open a connection "at the same time" because their connection table management isn't "thread safe".
That does suck, but it doesn't seem like an insurmountable problem for a DA. You just need a per-DA instance mutex which you acquire before opening a connection release afterwards.
On 16 Jul 2004 at 11:38, Andy Dustman wrote:
Also, two threads can' t open a connection "at the same time" because their connection table management isn't "thread safe".
That does suck, but it doesn't seem like an insurmountable problem for a DA. You just need a per-DA instance mutex which you acquire before opening a connection release afterwards.
Someone else said that every thread has it's own copy of ZODB, hence their own copies of each DA. So putting the mutex on the DA won't help this, will it? -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Brad Clements wrote:
That does suck, but it doesn't seem like an insurmountable problem for a DA. You just need a per-DA instance mutex which you acquire before opening a connection release afterwards.
Someone else said that every thread has it's own copy of ZODB, hence their own copies of each DA. So putting the mutex on the DA won't help this, will it?
That someone else was Jim, I'm just quoting him ;-) Have you looked at my ZOracleDA branch yet? I just use a module level Lock... Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
sqlrelay seems to hold some promise for connection pooling although I am not sure if theres support for sapdb Brad Clements wrote:
On 16 Jul 2004 at 8:57, Chris Withers wrote:
This often means that many more database connections are opened than are actually necessary. And if you start timing out inactive database connectiosn on the database side, you're in for a world of pain and suffering.
I branched ZOracleDA some time ago to move away from this model for that very reason...
What's the actual problem you're experiencing?
I have set the SAPDB timeout to 32400 seconds. However when running with more than 1 thread I still get "mysterious segfaults" in the sapdbapi component.
The SAP folks say their adapter is "multi-thread capable", but I've come to believe that only means that it's ok to open a connection in one thread and use it in another, but NOT to have 2 threads make requests on the same connection at the same time.
Also, two threads can' t open a connection "at the same time" because their connection table management isn't "thread safe".
What I need, I suppose, is a DB connection pool mechanism of some kind. I also have Python extensions that need a connection as well. Getting the actual database connection from a DA stinks (ie, it's not really possible). I'd like to see an API extension to ZopeDA's to allow Pythonscripts, Products and external methods get an actual db connection. But that I suppose is a problem to solve later.
I think I will take a look at ZOracleDA to see what you did.
Hi, On Fri, 2004-07-16 at 09:36, Brad Clements wrote:
On 16 Jul 2004 at 8:57, Chris Withers wrote:
This often means that many more database connections are opened than are actually necessary. And if you start timing out inactive database connectiosn on the database side, you're in for a world of pain and suffering.
I branched ZOracleDA some time ago to move away from this model for that very reason...
What's the actual problem you're experiencing?
I have set the SAPDB timeout to 32400 seconds. However when running with more than 1 thread I still get "mysterious segfaults" in the sapdbapi component.
The SAP folks say their adapter is "multi-thread capable", but I've come to believe that only means that it's ok to open a connection in one thread and use it in another, but NOT to have 2 threads make requests on the same connection at the same time. Yes. It is defined in DBAPI and breaking it is not a good idea as it will cause problems like when one thread writes data, and another thread on the same connection wants to read it before the first can commit.
Also, two threads can' t open a connection "at the same time" because their connection table management isn't "thread safe".
What I need, I suppose, is a DB connection pool mechanism of some kind. I also have Python extensions that need a connection as well. Getting the actual database connection from a DA stinks (ie, it's not really possible). I'd like to see an API extension to ZopeDA's to allow Pythonscripts, Products and external methods get an actual db connection. But that I suppose is a problem to solve later.
You have to change ZopeDA to do that. But I doubt if it will be beneficial. We were experimenting with Pscopg and its Zope adapter. Psycopg has an option of opening many physical connections (one physical connection per cursor) to the DB using a non-serialize mode(yes that part breaks compatibility from DBAPI and suffers from the problem mentioned above) When we bench marked using AB, it was found that non serialized mode is very slow even though the threads were accessing the database in parallel because of the overhead of setting up new connections. To implement connection pooling, I think it is best to do it using an externel product like SQLRelay than at the driver level. thanks, ganesh Zeomega LLC Open minds' Open Solutions Plano, Texas, USA Bangalore, India phone: 972-731-6750 http://www.zeomega.com
I think I will take a look at ZOracleDA to see what you did.
Ganesha Shanmuganathan wrote:
parallel because of the overhead of setting up new connections. To implement connection pooling, I think it is best to do it using an externel product like SQLRelay than at the driver level.
You may find connecting to SQLRelay suffers from the same problems... It's also another seperate system that needs to be maintained, understood, etc... Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
Brad Clements wrote:
What's the actual problem you're experiencing?
I have set the SAPDB timeout to 32400 seconds.
Unless you have an extremely heavily loaded Zope, that's too low, and may be causing you problems... Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
On 19 Jul 2004 at 7:44, Chris Withers wrote:
Brad Clements wrote:
What's the actual problem you're experiencing?
I have set the SAPDB timeout to 32400 seconds.
Unless you have an extremely heavily loaded Zope, that's too low, and may be causing you problems...
That is the maximum allowed value in dbmgui. Anyway, after setting Zope to be single threaded, I have not had any segfaults. Perhaps I'm wrong on the 'exact' value. I set it to the maximum allowed, perhaps its 324000 seconds. I know it's more than a day so the first number above must be a typo. -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Brad Clements wrote:
I have set the SAPDB timeout to 32400 seconds.
Unless you have an extremely heavily loaded Zope, that's too low, and may be causing you problems...
That is the maximum allowed value in dbmgui.
My point is that unless you implement a connection pool model independent of Zope's threading, you WILL run into problems if you have ANY kind of timeout where the DA isn't smnart enough to reconnect connections that have closed...
Anyway, after setting Zope to be single threaded, I have not had any segfaults.
Tada: You made sure that one connection will get used and you won't get stale ones being timed out and then randomly selected for use at some later point ;-) Get rid of the connection timeouts, and you should be fine for multi-threaded Zope. Either that or restructure the DA like I did for ZOracleDA ;-) Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
On 21 Jul 2004 at 16:44, Chris Withers wrote:
My point is that unless you implement a connection pool model independent of Zope's threading, you WILL run into problems if you have ANY kind of timeout where the DA isn't smnart enough to reconnect connections that have closed...
I understand. sapdbapi is *supposed* to reconnect on timeout.
Tada: You made sure that one connection will get used and you won't get stale ones being timed out and then randomly selected for use at some later point ;-)
I disagree that this was the cause of the segfaults, since I had segfaults within minutes of starting zope with more than 1 thread, way less than the timeout time.
Either that or restructure the DA like I did for ZOracleDA ;-)
I want to do that. Hope my client will pay for it ;-) -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Chris Withers wrote at 2004-7-21 16:44 +0100:
Brad Clements wrote:
I have set the SAPDB timeout to 32400 seconds.
Unless you have an extremely heavily loaded Zope, that's too low, and may be causing you problems...
That is the maximum allowed value in dbmgui.
My point is that unless you implement a connection pool model independent of Zope's threading, you WILL run into problems if you have ANY kind of timeout where the DA isn't smnart enough to reconnect connections that have closed...
All DA's I saw up to now, do a reconnect. But this is *WRONG" -- as part of a transaction may have been lost. After "reconnecting", they should raise an exception derived from "ConflictError" and let the complete request retry. -- Dieter
Dieter Maurer wrote:
All DA's I saw up to now, do a reconnect.
ZOracleDA didn't...
But this is *WRONG" -- as part of a transaction may have been lost. After "reconnecting", they should raise an exception derived from "ConflictError" and let the complete request retry.
...but it now does this :-) Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
On 23 Jul 2004 at 10:58, Chris Withers wrote:
Dieter Maurer wrote:
All DA's I saw up to now, do a reconnect.
ZOracleDA didn't...
But this is *WRONG" -- as part of a transaction may have been lost. After "reconnecting", they should raise an exception derived from "ConflictError" and let the complete request retry.
Perhaps I don't understand, but how could their be a missing transaction? Zope starts, connects to database Zero or more transactions occur Zope is idle for some period of time Z new transaction arrives, some transactions occur in ZODB. A transaction is attempted on a database connection. The DA gets a 'your connection has timed out' error from the db connection. It reconnects, it retries the transaction, no error occurs, so DA returns success. This is the way gvibDA works. It reconnects and retries. Where are transactions lost in this scenario? Remember, I'm talking about the DA catching "connectioned timed out" due to idle activity, and no other DB exceptions. -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Brad Clements wrote at 2004-7-23 09:53 -0400:
On 23 Jul 2004 at 10:58, Chris Withers wrote:
Dieter Maurer wrote:
All DA's I saw up to now, do a reconnect.
ZOracleDA didn't...
But this is *WRONG" -- as part of a transaction may have been lost. After "reconnecting", they should raise an exception derived from "ConflictError" and let the complete request retry.
Perhaps I don't understand, but how could their be a missing transaction?
Zope starts, connects to database
Zero or more transactions occur
Zope is idle for some period of time
The bad sequence can look as follows: * Zope starts a request (and thereby a transaction) * The request sends a modifying request to a relational database * The connection is lost; the former modification is discarded as the database performs an automatic abort on connection close * The request sends another modifying requst to the database The DA detects the lost connection, reconnects and sends the SQL * The request ends and commits the transaction In this case, you get only one of two changes in the database while you should have got either none or both -- an inconsistency. Raising an exception derived from "ConflictError" will let "ZPublisher" abort the transaction and then restart the complete request. You have a chance to get both changes... -- Dieter
On 23 Jul 2004 at 20:08, Dieter Maurer wrote:
The bad sequence can look as follows:
* Zope starts a request (and thereby a transaction)
* The request sends a modifying request to a relational database
* The connection is lost; the former modification is discarded as the database performs an automatic abort on connection close
The former modification cannot be lost because it was commited by the DA as part of the previous transaction. At least, gvibDA performs a database commit as part of the overall transaction machinery. So again, I don't see the loss.. -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
I dont see a data loss problem either (unless there are hardware failures) If a connection is dropped due to inactivity it should not affect any transactions going to occur in the future as a reconnect is issued before submitted new transactions. However connections getting dropped due to transport layer problems is a different matter. if a connection gets dropped by either side after making the request then it should be detected at either end and the transaction aborted (assuming a well behaved transport layer). There cannot be any loss in data in this scenario. This is expected behaviour with any RDBMS connection infrastructure, i.e If the transport layer guarantees delivery and employs the customary process of error detection and informs the application there should not be a data inconsistency problem ( unless there is a hardware failure ) I beleive Whether or NOT the transaction is resubmitted should be a decision on part of the application and not the DA or Zpublisher. The application can make a decision based on the circumstance and then if it chooses to do so issue the conflict error to invoke the Zpublisher machinery. Brad Clements wrote:
On 23 Jul 2004 at 20:08, Dieter Maurer wrote:
The bad sequence can look as follows:
* Zope starts a request (and thereby a transaction)
* The request sends a modifying request to a relational database
* The connection is lost; the former modification is discarded as the database performs an automatic abort on connection close
The former modification cannot be lost because it was commited by the DA as part of the previous transaction.
At least, gvibDA performs a database commit as part of the overall transaction machinery.
So again, I don't see the loss..
sathya wrote at 2004-7-23 18:00 -0500:
I dont see a data loss problem either (unless there are hardware failures)
If a connection is dropped due to inactivity it should not affect any transactions going to occur in the future as a reconnect is issued before submitted new transactions.
However, connections can be lost for other reasons than inactivity...
However connections getting dropped due to transport layer problems is a different matter. if a connection gets dropped by either side after making the request then it should be detected at either end and the transaction aborted (assuming a well behaved transport layer).
The problem occurs when the DA silently reopens the connection without telling the appliciation that the connection was lost (and thereby the transaction aborted by the database).
... I beleive Whether or NOT the transaction is resubmitted should be a decision on part of the application and not the DA or Zpublisher.
However, this means that the DA must report the lost connection via some kind of exception and must not silently reconnect. -- Dieter
On 25 Jul 2004 at 23:22, Dieter Maurer wrote:
If a connection is dropped due to inactivity it should not affect any transactions going to occur in the future as a reconnect is issued before submitted new transactions.
However, connections can be lost for other reasons than inactivity...
I think this is why the thread has gone on so long. I'm ONLY talking about idle timeout drops, and nothing else. gvibDA and sapdbi make a distinction between idle timeout disconnects and others, since they know when the server has dropped them due to inactivity. -- Brad Clements, bkc@murkworks.com (315)268-1000 http://www.murkworks.com (315)268-9812 Fax http://www.wecanstopspam.org/ AOL-IM: BKClements
Brad Clements wrote at 2004-7-23 16:12 -0400:
On 23 Jul 2004 at 20:08, Dieter Maurer wrote:
The bad sequence can look as follows:
* Zope starts a request (and thereby a transaction)
* The request sends a modifying request to a relational database
* The connection is lost; the former modification is discarded as the database performs an automatic abort on connection close
The former modification cannot be lost because it was commited by the DA as part of the previous transaction.
We are in one request and therefore in one transaction... The modification between the "starts a request" and "connection is lost" is lost... -- Dieter
Chris Withers wrote at 2004-7-23 10:58 +0100:
Dieter Maurer wrote:
All DA's I saw up to now, do a reconnect.
ZOracleDA didn't...
Really? I saw it reconnecting...
But this is *WRONG" -- as part of a transaction may have been lost. After "reconnecting", they should raise an exception derived from "ConflictError" and let the complete request retry.
...but it now does this :-)
Raising an exception derived from "ConflictError" -- that's fine! -- Dieter
Dieter Maurer wrote:
Chris Withers wrote at 2004-7-23 10:58 +0100:
Dieter Maurer wrote:
All DA's I saw up to now, do a reconnect.
ZOracleDA didn't...
Really? I saw it reconnecting...
It had some pretty whacky heuristic crap for when to reconnect, you must have been very lucky :-)
Raising an exception derived from "ConflictError" -- that's fine!
IIRC, my branch uses the REQUEST.retry feature of Zope.. Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
participants (7)
-
Andy Dustman -
Brad Clements -
Chris Withers -
Dieter Maurer -
Ganesha Shanmuganathan -
Roché Compaan -
sathya