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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
