Yang Zhang wrote: > Roger Binns wrote: > >> Yang Zhang wrote: >> >>> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM >>> shelf ORDER BY ROWID')): >>> >> You are converting the key which is an integer into a string for no >> apparent reason. >> > > I copied and pasted this code straight from my actual application, which > uses blobs instead of integers, which I need to convert into strings > (since Python interfaces with blobs using the `buffer` type, not `str`). > > >> If you also ask for the ROWID you will see that what is happening is a >> new rowid is generated for the replaced row so that if you are iterating >> over the table while modifying it then you effectively have an infinite >> length table. >> > > This is unusual for a RDBMS that claims to provide ACID properties - in > particular, this is not even as strong an isolation level as snapshot > isolation, as a reader transaction is able to see a concurrent writer > transaction's effects. In fact, this is weaker than the weakest > isolation level in (say) Postgresql, which is READ COMMITTED (in which > any statement is guaranteed to not see the effects of a transaction that > is committed after the query has started execution).
As I am not an expert in the Python wrapper, I could be incorrect; however, your code as written appears to be equivalent to the following: begin transaction for select(.... insert stuff end commit rather than your intended: s = select(... begin transaction for s... insert stuff end commit I say this because your example implies that the Python wrapper starts the transaction automatically inside the execute, and I would not be surprised if it did so BEFORE executing the SQL parameter. In other words, you seem to be doing a select from INSIDE the transaction, not OUTSIDE. If I understand correctly, that should indeed make the altered table visible to the select. I believe your intention can be expressed with this example in Ruby (corrected to have a primary key as Igor noted): ---------------------------- require 'sqlite3' require 'erb' db = SQLite3::Database.new(':memory:') db.execute_batch(ERB.new(<<eof, nil, '<>').result(binding)) begin transaction; create table shelf ( key integer primary key autoincrement, value integer not null); insert into shelf values (1, 1); insert into shelf values (2, 2); insert into shelf values (3, 3); commit; eof sel = db.execute('select key from shelf order by rowid') db.transaction { sel.each do |i| db.execute('replace into shelf (key, value) values(?,?)', i, i) end } puts 'done' ---------------------------- By executing the select outside of the context of the transaction, I obtain the desired transaction isolation. FWIW, John Elrick Fenestra Technologies _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users