Thread: [SQLObject] should Transaction.commit makeObsolete?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Kevin D. <da...@gm...> - 2005-06-21 16:45:11
|
In dbconnection.py, the error messages seem to imply that after you've committed (or rolled back) a transaction, you need to either get a new transaction object or call begin(). Looking at the code, though, rollback calls _makeObsolete, whereas commit does not. This is causing some interesting behavior in my test code, because I made some database updates that I hadn't committed and they were getting rolled back when the previous, already committed transaction was garbage collected. I can certainly add an explicit rollback in my own code, but I wanted to see if there really should be a makeObsolete call in there. Kevin |
From: Oleg B. <ph...@ma...> - 2005-06-21 18:22:28
|
On Tue, Jun 21, 2005 at 09:23:05AM -0400, Kevin Dangoor wrote: > This is causing some interesting behavior in my test code, because I > made some database updates that I hadn't committed and they were > getting rolled back when the previous, already committed transaction > was garbage collected. Looks like a bug. > I can certainly add an explicit rollback in my own code, but I wanted > to see if there really should be a makeObsolete call in there. I don't think it should. Normal patter IMHO is ...do some work ... transaction.commit() ...do some more work ... transaction.commit() ...do even more work ... transaction.rollback() transaction.begin() That is, one only needs to .begin() a transaction after .rollback(), but not after .commit(). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-21 18:44:20
|
On 6/21/05, Oleg Broytmann <ph...@ma...> wrote: > I don't think it should. Normal patter IMHO is >=20 > ...do some work ... > transaction.commit() > ...do some more work ... > transaction.commit() > ...do even more work ... > transaction.rollback() > transaction.begin() >=20 > That is, one only needs to .begin() a transaction after .rollback(), > but not after .commit(). That seems fine to me. So maybe all that needs to change are the messages which imply that you need a new transaction after each commit. Kevin |
From: Oleg B. <ph...@ph...> - 2005-06-29 05:05:15
|
On Tue, Jun 21, 2005 at 02:43:45PM -0400, Kevin Dangoor wrote: > . So maybe all that needs to change are the > messages which imply that you need a new transaction after each > commit. Committed at revision 822. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-21 19:22:36
|
Err, I just realized that I hadn't fully responded with my last message about this. I agree with what you say about a transaction not becoming obsolete after a commit. But, I think we're both in agreement that issuing a rollback in __del__ even though the transaction was successfully committed seems like bad news. Perhaps Transaction needs another flag to denote whether data modification statements have been issued since the last commit? Then __del__ can check that flag as well as obsolete to decide whether it should do a rollback. Kevin On 6/21/05, Oleg Broytmann <ph...@ma...> wrote: > On Tue, Jun 21, 2005 at 09:23:05AM -0400, Kevin Dangoor wrote: > > This is causing some interesting behavior in my test code, because I > > made some database updates that I hadn't committed and they were > > getting rolled back when the previous, already committed transaction > > was garbage collected. >=20 > Looks like a bug. >=20 > > I can certainly add an explicit rollback in my own code, but I wanted > > to see if there really should be a makeObsolete call in there. >=20 > I don't think it should. Normal patter IMHO is >=20 > ...do some work ... > transaction.commit() > ...do some more work ... > transaction.commit() > ...do even more work ... > transaction.rollback() > transaction.begin() >=20 > That is, one only needs to .begin() a transaction after .rollback(), > but not after .commit(). >=20 > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > |
From: Oleg B. <ph...@ph...> - 2005-06-21 19:30:19
|
On Tue, Jun 21, 2005 at 03:22:33PM -0400, Kevin Dangoor wrote: > Perhaps Transaction needs another flag to denote whether data > modification statements have been issued since the last commit? Then > __del__ can check that flag as well as obsolete to decide whether it > should do a rollback. How can the transaction (which is a connection) understand this? Should it parse SQL queries? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-21 20:30:51
|
On 6/21/05, Oleg Broytmann <ph...@ph...> wrote: > On Tue, Jun 21, 2005 at 03:22:33PM -0400, Kevin Dangoor wrote: > > Perhaps Transaction needs another flag to denote whether data > > modification statements have been issued since the last commit? Then > > __del__ can check that flag as well as obsolete to decide whether it > > should do a rollback. >=20 > How can the transaction (which is a connection) understand this? > Should it parse SQL queries? I'll grant that that's ugly. A simple solution would be to just look at whether any queries have run through the transaction. That would certainly do the trick in my case... (I'm using the do_in_transaction idiom that Ian posted a few weeks back... a transaction is created and then either committed or rolled back with nothing else following on that transaction.) Kevin |
From: Oleg B. <ph...@ph...> - 2005-06-22 13:15:38
|
On Tue, Jun 21, 2005 at 04:30:47PM -0400, Kevin Dangoor wrote: > On 6/21/05, Oleg Broytmann <ph...@ph...> wrote: > > How can the transaction (which is a connection) understand this? > > Should it parse SQL queries? > > I'll grant that that's ugly. A simple solution would be to just look > at whether any queries have run through the transaction. Any query including SELECT?! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-22 13:18:56
|
On 6/22/05, Oleg Broytmann <ph...@ph...> wrote: > On Tue, Jun 21, 2005 at 04:30:47PM -0400, Kevin Dangoor wrote: > > On 6/21/05, Oleg Broytmann <ph...@ph...> wrote: > > > How can the transaction (which is a connection) understand this? > > > Should it parse SQL queries? > > > > I'll grant that that's ugly. A simple solution would be to just look > > at whether any queries have run through the transaction. >=20 > Any query including SELECT?! If you want to get around parsing the SQL, yes. That's still better than the current state which is to issue a rollback on __del__ even when the transaction has been committed and is done. Kevin |
From: Oleg B. <ph...@ph...> - 2005-06-23 19:22:28
|
On Wed, Jun 22, 2005 at 09:18:54AM -0400, Kevin Dangoor wrote: > > > I'll grant that that's ugly. A simple solution would be to just look > > > at whether any queries have run through the transaction. > > > > Any query including SELECT?! > > If you want to get around parsing the SQL, yes. I'd like to avoid parsing because of this: tsx.queryAll("INSERT...; UPDATE...; DELETE...; SELECT * FROM atable"); PS. Sorry, I am very busy these days, and will be busy until at least Monday... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-23 21:58:59
|
On 6/23/05, Oleg Broytmann <ph...@ph...> wrote: > On Wed, Jun 22, 2005 at 09:18:54AM -0400, Kevin Dangoor wrote: > > > > I'll grant that that's ugly. A simple solution would be to just loo= k > > > > at whether any queries have run through the transaction. > > > > > > Any query including SELECT?! > > > > If you want to get around parsing the SQL, yes. >=20 > I'd like to avoid parsing because of this: >=20 > tsx.queryAll("INSERT...; UPDATE...; DELETE...; SELECT * FROM atable"); Agreed. Parsing is a slippery slope. That's why I suggested just watching for any query. It's not perfect, but it's better than the present... > PS. Sorry, I am very busy these days, and will be busy until at least > Monday... Or to rephrase this, "patches accepted" :) You're giving great support for a free project. Kevin |
From: Kevin D. <da...@gm...> - 2005-06-23 22:00:28
|
On 6/23/05, Kevin Dangoor <da...@gm...> wrote: > > PS. Sorry, I am very busy these days, and will be busy until at least > > Monday... >=20 > Or to rephrase this, "patches accepted" :) >=20 > You're giving great support for a free project. Err, I just realized that those two sentences together almost implied a sarcastic "great support". That's not at all what I meant... You are truly giving excellent support for SQLobject. I've seen other projects where you don't get any kind of thoughtful resposne to queries... Kevin |
From: Oleg B. <ph...@ph...> - 2005-06-24 11:08:27
|
On Thu, Jun 23, 2005 at 06:00:24PM -0400, Kevin Dangoor wrote: > Err, I just realized that those two sentences together almost implied > a sarcastic "great support". That's not at all what I meant... No problem! :) > You are truly giving excellent support for SQLobject. I've seen other > projects where you don't get any kind of thoughtful resposne to > queries... Thank you! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Stuart B. <st...@st...> - 2005-06-29 03:25:42
Attachments:
signature.asc
|
>>>> How can the transaction (which is a connection) understand this? >>>>Should it parse SQL queries? >>> >>>I'll grant that that's ugly. A simple solution would be to just look >>>at whether any queries have run through the transaction. >> >> Any query including SELECT?! SELECT statements can modify data if they are selecting a stored procedure (which might even look like a standard table select if the stored procedure is hidden behind a view). -- Stuart Bishop <st...@st...> http://www.stuartbishop.net/ |
From: Oleg B. <ph...@ph...> - 2005-06-29 05:26:30
|
Hello! On Tue, Jun 21, 2005 at 09:23:05AM -0400, Kevin Dangoor wrote: > This is causing some interesting behavior in my test code, because I > made some database updates that I hadn't committed and they were > getting rolled back when the previous, already committed transaction > was garbage collected. Now when I got a spare minute to look at it... I do not understand what is going on. Are you really saying when a transaction is being rolled back objects in another different transaction are rolled back? Something completely wierd. I suspect this is a problem in DBMS, not in SQLObject. What database do you use? What DB API driver? MySQL or SQLite, I suppose. SQLite, most probably. Certainly not Postgres - Postgres correctly handles transactions in different connections, where SQLite has one global transaction, as far as I understand. Can you make a test program that demonstrates the problem? I'll run it in Postgres and SQLite and compare the results. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-29 13:20:34
|
On 6/29/05, Oleg Broytmann <ph...@ph...> wrote: > Hello! >=20 > On Tue, Jun 21, 2005 at 09:23:05AM -0400, Kevin Dangoor wrote: > > This is causing some interesting behavior in my test code, because I > > made some database updates that I hadn't committed and they were > > getting rolled back when the previous, already committed transaction > > was garbage collected. >=20 > Now when I got a spare minute to look at it... I do not understand wha= t > is going on. > Are you really saying when a transaction is being rolled back objects = in > another different transaction are rolled back? Something completely wierd= . > I suspect this is a problem in DBMS, not in SQLObject. > What database do you use? What DB API driver? MySQL or SQLite, I > suppose. SQLite, most probably. Certainly not Postgres - Postgres correct= ly > handles transactions in different connections, where SQLite has one globa= l > transaction, as far as I understand. > Can you make a test program that demonstrates the problem? I'll run it > in Postgres and SQLite and compare the results. This might be a little tricky to write a consistent test for, because it relies on the garbage collector. It could probably be simulated by calling __del__ directly, but that seems dicey. I can describe the steps that happen easily enough: Ian posted a do_in_transaction method a few weeks back. It basically looked like this: - change connection in the hub to a transaction - run your callable - commit if no errors - set the connection in the hub back 1) open a connection 2) start using a transaction 3) make changes 4) commit them 5) get a new transaction from the connection 6) make changes 7) garbage collector runs the first transaction's __del__ method. this causes a ROLLBACK on the connection, thus eliminating the changes made in step 6 do_in_transaction could set the obsolete flag on the transaction, but doesn't feel like the right place to implement a fix for this. Kevin |
From: Oleg B. <ph...@ph...> - 2005-06-29 15:31:37
|
On Wed, Jun 29, 2005 at 09:20:31AM -0400, Kevin Dangoor wrote: > 1) open a connection > 2) start using a transaction > 3) make changes > 4) commit them > 5) get a new transaction from the connection A new, different transaction! > 6) make changes > 7) garbage collector runs the first transaction's __del__ method. this > causes a ROLLBACK on the connection, thus eliminating the changes made > in step 6 I do not unerstand this. How can a ROLLBACK in one connection cause problems in another?! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Kevin D. <da...@gm...> - 2005-06-29 18:17:47
|
On 6/29/05, Oleg Broytmann <ph...@ph...> wrote: > On Wed, Jun 29, 2005 at 09:20:31AM -0400, Kevin Dangoor wrote: > > 1) open a connection > > 2) start using a transaction > > 3) make changes > > 4) commit them > > 5) get a new transaction from the connection >=20 > A new, different transaction! Yes. That's what happens if you use something like do_in_transaction, which basically uses the transaction for just one commit or rollback and then discards it. >=20 > > 6) make changes > > 7) garbage collector runs the first transaction's __del__ method. this > > causes a ROLLBACK on the connection, thus eliminating the changes made > > in step 6 >=20 > I do not unerstand this. How can a ROLLBACK in one connection cause > problems in another?! It's the same connection. Here's Ian's code to make it clearer: def do_in_transaction(func, *args, **kw): old_conn =3D sqlhub.getConnection() conn =3D old_conn.transaction() sqlhub.threadConnection =3D conn try: try: value =3D func(*args, **kw) except: conn.rollback() raise else: conn.commit() return value finally: sqlhub.threadConnection =3D old_conn So, your connection is getting reused every time, but the transaction is being discarded. Since the connection is still open, if you start running more statements, those updates will get clobbered by the old transaction's __del__ doing a rollback. Kevin |
From: Oleg B. <ph...@ph...> - 2005-07-12 17:18:21
|
On Wed, Jun 29, 2005 at 02:17:44PM -0400, Kevin Dangoor wrote: > It's the same connection. Here's Ian's code to make it clearer: > > def do_in_transaction(func, *args, **kw): > old_conn = sqlhub.getConnection() > conn = old_conn.transaction() > sqlhub.threadConnection = conn > try: > try: > value = func(*args, **kw) > except: > conn.rollback() > raise > else: > conn.commit() > return value > finally: > sqlhub.threadConnection = old_conn > > So, your connection is getting reused every time, but the transaction > is being discarded. Since the connection is still open, if you start > running more statements, those updates will get clobbered by the old > transaction's __del__ doing a rollback. Now I think it is the hub's job to obsoletes the connection. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Daishi H. <da...@eg...> - 2005-06-23 20:19:53
|
Hi, I'm not sure this is entirely related, but I'm seeing: Exception pysqlite2.dbapi2.ProgrammingError: 'Cannot operate on a=20 closed database.' in <bound method Transaction.__del__ of=20 <sqlobject.dbconnection.Transaction object at 0xb6f9ca2c>> ignored on the attached test code, which goes away if I add a _makeObsolete call in Transaction.commit(). (I'm using the current pysqlite2 and sqlobject). I'm wondering if I'm just not doing the right thing in the test code, or if I should ignore the message, or if there's something actually wrong elsewhere. In particular, is there a better way to obtain the transaction then accessing ._connection as I do? On a separate note, I see that DBConnection has a queryOne() method; is there a corresponding "get_one" method at the SQLObject level that can replace the iter(select()).next() hack in query() below? tia,d --- import sqlobject URI =3D 'sqlite:/:memory:' N =3D 10 class Test(sqlobject.SQLObject): class sqlmeta: cacheValues =3D False _connection =3D sqlobject.connectionForURI(URI) foo =3D sqlobject.StringCol() def create(): Test.dropTable(ifExists=3DTrue) Test.createTable() def load(): transaction =3D Test._connection.transaction() for i in xrange(N): Test(foo=3Dstr(i), connection=3Dtransaction) transaction.commit() def query(): for i in xrange(N): x =3D iter(Test.selectBy(foo=3Dstr(i))).next() #print x def main(): #Test._connection.debug =3D True create() load() query() if __name__ =3D=3D '__main__': main() On Jun 21, 2005, at 6:23 AM, Kevin Dangoor wrote: > In dbconnection.py, the error messages seem to imply that after you've > committed (or rolled back) a transaction, you need to either get a new > transaction object or call begin(). Looking at the code, though, > rollback calls _makeObsolete, whereas commit does not. > > This is causing some interesting behavior in my test code, because I > made some database updates that I hadn't committed and they were > getting rolled back when the previous, already committed transaction > was garbage collected. > > I can certainly add an explicit rollback in my own code, but I wanted > to see if there really should be a makeObsolete call in there. > > Kevin > > > ------------------------------------------------------- > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies > from IBM. Find simple to follow Roadmaps, straightforward articles, > informative Webcasts and more! Get everything you need to get up to > speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&op=3Dclick > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Kevin D. <da...@gm...> - 2005-06-23 22:04:35
|
That's funny. I've seen that in my own test cases and never paid it too much attention. This is definitely the same problem... The database is closed, but the Transaction's __del__ method is trying to perform a rollback on the database. Kevin On 6/23/05, Daishi Harada <da...@eg...> wrote: > Hi, >=20 > I'm not sure this is entirely related, but I'm seeing: >=20 > Exception pysqlite2.dbapi2.ProgrammingError: 'Cannot operate on a > closed database.' in <bound method Transaction.__del__ of > <sqlobject.dbconnection.Transaction object at 0xb6f9ca2c>> ignored >=20 > on the attached test code, which goes away if I > add a _makeObsolete call in Transaction.commit(). > (I'm using the current pysqlite2 and sqlobject). >=20 > I'm wondering if I'm just not doing the right thing > in the test code, or if I should ignore the message, > or if there's something actually wrong elsewhere. >=20 > In particular, is there a better way to obtain the > transaction then accessing ._connection as I do? >=20 > On a separate note, I see that DBConnection has > a queryOne() method; is there a corresponding > "get_one" method at the SQLObject level that > can replace the iter(select()).next() hack in query() > below? >=20 > tia,d >=20 > --- >=20 > import sqlobject >=20 > URI =3D 'sqlite:/:memory:' > N =3D 10 >=20 > class Test(sqlobject.SQLObject): > class sqlmeta: > cacheValues =3D False > _connection =3D sqlobject.connectionForURI(URI) > foo =3D sqlobject.StringCol() >=20 > def create(): > Test.dropTable(ifExists=3DTrue) > Test.createTable() >=20 > def load(): > transaction =3D Test._connection.transaction() > for i in xrange(N): > Test(foo=3Dstr(i), connection=3Dtransaction) > transaction.commit() >=20 > def query(): > for i in xrange(N): > x =3D iter(Test.selectBy(foo=3Dstr(i))).next() > #print x >=20 > def main(): > #Test._connection.debug =3D True > create() > load() > query() >=20 > if __name__ =3D=3D '__main__': > main() >=20 >=20 >=20 > On Jun 21, 2005, at 6:23 AM, Kevin Dangoor wrote: >=20 > > In dbconnection.py, the error messages seem to imply that after you've > > committed (or rolled back) a transaction, you need to either get a new > > transaction object or call begin(). Looking at the code, though, > > rollback calls _makeObsolete, whereas commit does not. > > > > This is causing some interesting behavior in my test code, because I > > made some database updates that I hadn't committed and they were > > getting rolled back when the previous, already committed transaction > > was garbage collected. > > > > I can certainly add an explicit rollback in my own code, but I wanted > > to see if there really should be a makeObsolete call in there. > > > > Kevin > > > > > > ------------------------------------------------------- > > SF.Net email is sponsored by: Discover Easy Linux Migration Strategies > > from IBM. Find simple to follow Roadmaps, straightforward articles, > > informative Webcasts and more! Get everything you need to get up to > > speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&op=3Dclick > > _______________________________________________ > > sqlobject-discuss mailing list > > sql...@li... > > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > >=20 > |
From: Oleg B. <ph...@ph...> - 2005-06-29 05:44:28
|
On Thu, Jun 23, 2005 at 01:19:54PM -0700, Daishi Harada wrote: > Exception pysqlite2.dbapi2.ProgrammingError: 'Cannot operate on a > closed database.' in <bound method Transaction.__del__ of > <sqlobject.dbconnection.Transaction object at 0xb6f9ca2c>> ignored Works for me. Your program passed ok in Python 2.3 with PySQLite1 and Python 2.4 with PySQLite2. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <da...@eg...> - 2005-06-30 19:25:11
|
I'm using: python 2.4.1 SQLObject R825 pysqlite2 R824 sqlite 3.1.3 on: Linux/Rocks release 3.2.0 (Shasta) Things appear to be working even with the exceptions, so I'm happy to ignore them, but it did make me a bit uneasy. thx,d On Jun 28, 2005, at 10:41 PM, Oleg Broytmann wrote: > On Thu, Jun 23, 2005 at 01:19:54PM -0700, Daishi Harada wrote: >> Exception pysqlite2.dbapi2.ProgrammingError: 'Cannot operate on a >> closed database.' in <bound method Transaction.__del__ of >> <sqlobject.dbconnection.Transaction object at 0xb6f9ca2c>> ignored > > Works for me. Your program passed ok in Python 2.3 with PySQLite1 > and > Python 2.4 with PySQLite2. > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ > ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > |