Thanks Michael for looking into it. I've verified that I get the same results (works with 0.4.8, rolls back with 0.5.2) on two additional Windows client machines. Different OS's and python versions, so it's a stable result on my end.
I'm not sure the best way to dig into this further. I'm going to take a look at some tools I saw recommended recently for looking at what is going across the wire to see if I can learn something there. (I did try the mssql://<dsn> for 0.5.2, but got the same results.) David On Mar 21, 7:45 pm, Michael Bayer <[email protected]> wrote: > Ive booted up my virtual windows machine and it all works for me. > note that "dsn" is now the default interpretation of host in 0.5, i.e. > connect using mssql://<dsn>. perhaps you've got multiple database > instances being accesed across SQLA versions. > > On Mar 21, 2009, at 8:01 PM, davidlmontgomery wrote: > > > > > Thanks Mike and Michael for your suggestions. > > >> remove() will roll back any existing transaction > > > Yes, this is what I am seeing. > > >> but the commit() call > >> previously will issue a COMMIT for any pending data. > > > And this COMMIT is succeeding with 0.4.8, but it is not > > succeeding with 0.5.2. > > >> the COMMIT will > >> show up in your SQL logs so check those out. > > > I see the COMMIT in the logs for both 0.4.8 and > > 0.5.2. With 0.4.8 it works, and the table is unlocked > > immediately after the COMMIT. In 0.5.2, the table > > stays locked after the COMMIT, until the call to > > session.remove(). Something is going awry with the > > 0.5.2 COMMIT. > > >> theres no logic in the > >> MSSQL engine that would specifically affect this. > > > I guess I'll set a breakpoint where I issue the > > COMMIT -- or perhaps near where the COMMIT log > > message is issued, if I can easily find that -- and see > > if I can come to some understanding of the difference. > > > Any hints for my spelunking would be appreciated. > > If I can't figure it out fairly soon I'll just need to revert > > to 0.4.8. > > > Here is a revised test script: > > > --- test script --- > > import datetime > > import sqlalchemy > > from sqlalchemy.orm import scoped_session, sessionmaker > > > print "sqlalchemy version:", sqlalchemy.__version__ > > > conn_str = 'mssql://<user>:<password>@<server>/<database>?dsn=<dsn>' > > mssql_engine = sqlalchemy.create_engine(conn_str, echo=True) > > mssql_metadata = sqlalchemy.MetaData(bind=mssql_engine) > > MSSQLSession = scoped_session(sessionmaker(bind=mssql_metadata.bind)) > > > data = 'bingo' > > now = datetime.datetime.now() > > time = now.strftime('%Y-%m-%d %H:%M:%S') > > command = "INSERT INTO sa_test (data, time) VALUES ('%s', '%s')" % > > (data, time) > > > session = MSSQLSession() > > session.execute(command) > > session.commit() > > > command = "SELECT COUNT(*) FROM sa_test WHERE data='%s'" % data > > > session = MSSQLSession() > > count = session.execute(command).scalar() > > print "count:", count > > > MSSQLSession.remove() > > --- end test script --- > > > This gets the same behavior as before. I'm seeing the > > same behavior with INSERTs, UPDATEs, and (in the > > context of the source application) sprocs that themselves > > do a bunch of INSERTs and/or UPDATEs. > > > Here are sample runs (I've wrapped the output lines > > manually): > > > --- first run with 0.5.2 --- > > sqlalchemy version: 0.5.2 > > 2009-03-21 16:22:51,403 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > BEGIN > > 2009-03-21 16:22:51,434 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > INSERT INTO sa_test (data, time) > > VALUES ('bingo', '2009-03-21 16:22:51') > > 2009-03-21 16:22:51,436 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > [] > > 2009-03-21 16:22:51,453 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > COMMIT > > 2009-03-21 16:22:51,470 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > BEGIN > > 2009-03-21 16:22:51,502 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > SELECT COUNT(*) FROM sa_test WHERE data='bingo' > > 2009-03-21 16:22:51,503 INFO > > sqlalchemy.engine.base.Engine.0x...fb30 > > [] > > count: 1 > > > [1 would be correct, but table actually still has 0] > > > --- second run with 0.5.2 --- > > sqlalchemy version: 0.5.2 > > 2009-03-21 16:22:53,035 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > BEGIN > > 2009-03-21 16:22:53,065 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > INSERT INTO sa_test (data, time) > > VALUES ('bingo', '2009-03-21 16:22:52') > > 2009-03-21 16:22:53,069 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > [] > > 2009-03-21 16:22:53,085 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > COMMIT > > 2009-03-21 16:22:53,101 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > BEGIN > > 2009-03-21 16:22:53,132 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > SELECT COUNT(*) FROM sa_test WHERE data='bingo' > > 2009-03-21 16:22:53,135 INFO > > sqlalchemy.engine.base.Engine.0x...0b30 > > [] > > count: 1 > > > [table still has 0 -- 0.5.2 is always rolling back] > > > --- first run with 0.4.8 --- > > sqlalchemy version: 0.4.8 > > 2009-03-21 16:23:46,415 INFO > > sqlalchemy.engine.base.Engine.0b30 > > BEGIN > > 2009-03-21 16:23:46,417 INFO > > sqlalchemy.engine.base.Engine.0b30 > > INSERT INTO sa_test (data, time) > > VALUES ('bingo', '2009-03-21 16:23:46') > > 2009-03-21 16:23:46,420 INFO > > sqlalchemy.engine.base.Engine.0b30 > > {} > > 2009-03-21 16:23:46,448 INFO > > sqlalchemy.engine.base.Engine.0b30 > > COMMIT > > 2009-03-21 16:23:46,469 INFO > > sqlalchemy.engine.base.Engine.0b30 > > BEGIN > > 2009-03-21 16:23:46,470 INFO > > sqlalchemy.engine.base.Engine.0b30 > > SELECT COUNT(*) FROM sa_test WHERE data='bingo' > > 2009-03-21 16:23:46,473 INFO > > sqlalchemy.engine.base.Engine.0b30 > > {} > > count: 1 > > > [correct: table now does have 1] > > > --- first run with 0.4.8 --- > > sqlalchemy version: 0.4.8 > > 2009-03-21 16:25:27,819 INFO > > sqlalchemy.engine.base.Engine.0b30 > > BEGIN > > 2009-03-21 16:25:27,821 INFO > > sqlalchemy.engine.base.Engine.0b30 > > INSERT INTO sa_test (data, time) > > VALUES ('bingo', '2009-03-21 16:25:27') > > 2009-03-21 16:25:27,822 INFO > > sqlalchemy.engine.base.Engine.0b30 > > {} > > 2009-03-21 16:25:27,839 INFO > > sqlalchemy.engine.base.Engine.0b30 > > COMMIT > > 2009-03-21 16:25:27,858 INFO > > sqlalchemy.engine.base.Engine.0b30 > > BEGIN > > 2009-03-21 16:25:27,859 INFO > > sqlalchemy.engine.base.Engine.0b30 > > SELECT COUNT(*) FROM sa_test WHERE data='bingo' > > 2009-03-21 16:25:27,861 INFO > > sqlalchemy.engine.base.Engine.0b30 > > {} > > count: 2 > > > [correct: table now does have 2] --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
