2

Given this piece of code:

record = session.query(Foo).filter(Foo.id == 1).first()   
session.delete(record)
session.flush()
has_record = session.query(Foo).filter(Foo.id == 1).first()

I think the 'has_record' should be None here, but it turns out to be the same row as record.

Did I miss something to get the assumed result. Or is there any way that can make the delete take effect without commit?

Mysql would behave in a different way under similar process.

start transaction;
select * from Foo where id = 1;  # Hit one record
delete from Foo where id = 1;    # Nothing goes to the disk
select * from Foo where id = 1;  # Empty set
commit;                          # Everything geos to the disk
G.O.Battle
  • 33
  • 7
  • be sure to read this wonderful answer: http://stackoverflow.com/questions/4201455/sqlalchemy-whats-the-difference-between-flush-and-commit (also check the comment by zzzeek) – jbub Nov 13 '13 at 18:35
  • I've read that, and it says that "flush will communicate the delete to the db, and when I query again, the result would combine the db's data and the flushed data in this transaction" which means the 'has_record' should be None, right? I'm not sure whether I missed something, really confused. – G.O.Battle Nov 14 '13 at 08:16
  • Well, thing is flush is not commiting the transaction, so the data is not yet persisted to disk. When you run the second query, you are hitting the database again, so the data is not there yet, thats my understanding. – jbub Nov 14 '13 at 08:31
  • Um..check this out "The changes aren't persisted permanently to disk, or visible to OTHER transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does)." In my case, the delete and afterwards query are in the same transaction. – G.O.Battle Nov 14 '13 at 08:49
  • which db are you running ? i need to simulate this – jbub Nov 14 '13 at 09:00
  • MySQL，I've add an example in the question. – G.O.Battle Nov 14 '13 at 09:12
  • Ok, so i have just tested it and it worked as expected, returned `None`. My environment is: SQLAlchemy==0.8.3, MySQL-python==1.2.4 on Python 2.7.6. This is the code i used: https://dpaste.de/zjOD – jbub Nov 14 '13 at 18:18
  • So..Really thanks for your time, I made a stupid mistake here. The session I'm using is a routing session, which has a master/slave session behind it. The fact might be that the delete is flushed to master and the query still goes to slave, so of course I can query the record again. Thx again. – G.O.Battle Nov 15 '13 at 07:16

1 Answers1

0

I made a stupid mistake here. The session I'm using is a routing session, which has a master/slave session behind it. The fact might be that the delete is flushed to master and the query still goes to slave, so of course I can query the record again.

G.O.Battle
  • 33
  • 7