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
-~----------~----~----~----~------~----~------~--~---
