Thread: [SQLObject] ? Treadsafety, transactions, DB-API ?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Bud P. B. <bu...@si...> - 2003-06-11 17:39:38
|
Can someone help me calm my mental storm stemming from a confusion and lack of understanding on treadsafety, transactions, and DB-API? For SQLObject to be threadsafe, do threads need an isolation of their transactions? It seems so, since one thread will typically want to commit while another one is in the middle of its transaction... But then again, if multiple threads modify the same objects (in memory), does isolation of transactions really guarantee consistency? It seems that this could only be true if there were locks on application objects. For example, a multi-threaded app that transfers funds between instances of Account would probalby have to lock (or version) affected Account instances for the duration of a transaction such that no other thread can debit or accredit funds and mess up consistency. Does SQLObject do something of this sort or has my reasoning gone astray somewhere? Also, in my (probably faulty) understanding of the DB-API, transactions are properties of connections. Does that mean that different threads need to use different connections? Does SQLObject do that? many thanks for any clarifications --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Ian B. <ia...@co...> - 2003-06-11 19:33:47
|
On Wed, 2003-06-11 at 12:38, Bud P.Bruegger wrote: > Can someone help me calm my mental storm stemming from a confusion > and lack of understanding on treadsafety, transactions, and DB-API? > > For SQLObject to be threadsafe, do threads need an isolation of their > transactions? It seems so, since one thread will typically want to > commit while another one is in the middle of its transaction... Right now you do transactions something like: someConn = PostgresConnection(...) trans = someConn.transaction() myObj1 = MyObject(connection=trans) ... trans.commit() > But then again, if multiple threads modify the same > objects (in memory), does isolation of transactions really guarantee > consistency? It seems that this could only be true if there > were locks on application objects. For example, a multi-threaded app > that transfers funds between instances of Account would probalby have > to lock (or version) affected Account instances for the duration of a > transaction such that no other thread can debit or accredit funds and > mess up consistency. Does SQLObject do something of this sort or has > my reasoning gone astray somewhere? There's no locking implemented. We've talked about some ideas... I seem to remember you being the one that initially brought it up ;) If you *don't* use transactions, then you can be sure that each object is the only instance of that object (for that id) in the process. So you can put locks on the object itself. Like: class Account(SQLObject): def _init(self, id): SQLObject._init(self, id) self.lock = threading.Lock() acct = Account(10) acct.lock.acquire() ... acct.lock.release() Doesn't work if you have more than one process accessing the database at a time, and they all need to be locked. > Also, in my (probably faulty) understanding of the DB-API, > transactions are properties of connections. Does that mean that > different threads need to use different connections? Does SQLObject > do that? Yes, that's what .transaction() does. Ian |
From: Bud P. B. <bu...@si...> - 2003-06-13 14:13:27
|
On 11 Jun 2003 14:34:29 -0500 Ian Bicking <ia...@co...> wrote: > If you *don't* use transactions, then you can be sure that each object > is the only instance of that object (for that id) in the process. So > you can put locks on the object itself. Thanks, Ian, for the clarifications. Definitely a non-trivial problem... So handling single-threading, multi-processing with transactions seems manageable with some optimistic concurrancy control in the dbms, but multi-threading with transactions seems a real tough nut to crack... In the latter case, one probably needs a complete transaction engine in SQLObject (in addition to that in the dbms). Different transactions need to see "their" versions of objects..... or lock them for writing until the end of the transaction...... difficult and I'm wondering how easy it is to hide this complexity from application programmers... A maybe better approach would be that different threads have isolated instances of objects (at least conceptually, and then you could have a copy on write or similar for efficiency), their own cache (a single instance per object in any given thread). But that is a complicated version of multi-processing, what advantage would the threading bring (particularly in python where an interpreter can run only on a single processor--if I got that correctly). just thinking aload hoping it's of interest to others... -b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: xtian <xt...@to...> - 2003-06-16 02:38:18
|
When I use SQLBuilder to construct a query, the SQL clause generated is incorrect for anything that has a column name that deviates from Ian's standard. A little digging shows that eg, Organisation.q is a SmartTable instance, which tweaks any attribute access (like salespersonID) to replace camelCaseNames with corresponding underscore_names. This is the default attribute-to-column name mapping in SQLObject, but our database doesn't use this convention. I can simply tweak the module to stop Organisation.q from being a SmartTable (and be explicit about the column names) - but is there any simple way to have SQLBuilder get the dbName from the corresponding SQLObject column attribute? Thanks Christian |