Re: [sqlite] Transaction isolation

[Yang Zhang]

Roger Binns wrote:
> Yang Zhang wrote:
>> Actually, this is only because Python 3 str is Python 2 unicode.  Python 
>> 2 (which I'm currently using, and which I believe most of the world is 
>> using) str is a physical string of bytes, not a logical/decoded 
>> character string.  Python 2.6 introduces bytes as a synonym for str, but 
>> I am using Python 2.5 at the moment.
> 
> This is all pedantically true, but it is still a really bad way to
> structure your program?  Did you read the Joel Unicode and character
> sets link?

I *have* in fact read that article a very, very long time ago, but that 
is besides the point.  I am aware of character encoding issues, thanks.

> 
> It was because Python 2 messed up on bytes versus strings versus unicode
> that they had to clean it up in Python 3.  It is also why the SQLite
> wrappers in Python 2 return blobs as the buffer type so that there is no
> accidental mingling of bytes and strings.  (Disclosure: I am the author
> of the APSW wrapper)  SQLite *only* supports Unicode strings.  Other
> databases do support non-Unicode strings, character set conversions and
> all that other complexity.

I require str because that is what cPickle.loads() requires; you cannot 
pass it a buffer.  I need to store pickled objects in the database 
because I am implementing an SQLite backend for the Python 2 shelve module.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Yang Zhang wrote:
> Actually, this is only because Python 3 str is Python 2 unicode.  Python 
> 2 (which I'm currently using, and which I believe most of the world is 
> using) str is a physical string of bytes, not a logical/decoded 
> character string.  Python 2.6 introduces bytes as a synonym for str, but 
> I am using Python 2.5 at the moment.

This is all pedantically true, but it is still a really bad way to
structure your program?  Did you read the Joel Unicode and character
sets link?

It was because Python 2 messed up on bytes versus strings versus unicode
that they had to clean it up in Python 3.  It is also why the SQLite
wrappers in Python 2 return blobs as the buffer type so that there is no
accidental mingling of bytes and strings.  (Disclosure: I am the author
of the APSW wrapper)  SQLite *only* supports Unicode strings.  Other
databases do support non-Unicode strings, character set conversions and
all that other complexity.

It is your code and you can do whatever pleases you.  However the advice
still stands - keep your strings and bytes/blobs separate, and using the
buffer type in Python 2 (and bytes in Python 3) is an excellent way of
doing that.  The wrappers already do this because it is good practise.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoR10IACgkQmOOfHg372QQyjwCfXTwHMBsdAznHfDZ8CeaQIGNH
T64Anj5qvy6MjjL/K08xi5CPY7pxueEi
=Zet9
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Igor Tandetnik wrote:
> Yang Zhang  wrote:
>> Pavel Ivanov wrote:
>>> BTW, ACID that you mentioned has nothing to do with snapshot
>>> isolation that you want to achieve. AFAIK only Oracle supports this
>>> kind of statement isolation level.
>> Actually, Oracle, Postgresql, SQL Server, Firebird, and others support
>> snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation
> 
> ... but not between two statements running within _the same 
> transaction_. Isolation level (snapshot or otherwise) describes how two 
> transactions are isolated from each other. In your example, you only 
> have one transaction, so any discussion of isolation levels is moot.

Right, I mean the whole reason why I originally wrote to this list was 
because I was under the (incorrect) impression that I was working with 
two separate transactions.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Igor Tandetnik]

Yang Zhang  wrote:
> Pavel Ivanov wrote:
>> BTW, ACID that you mentioned has nothing to do with snapshot
>> isolation that you want to achieve. AFAIK only Oracle supports this
>> kind of statement isolation level.
>
> Actually, Oracle, Postgresql, SQL Server, Firebird, and others support
> snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation

... but not between two statements running within _the same 
transaction_. Isolation level (snapshot or otherwise) describes how two 
transactions are isolated from each other. In your example, you only 
have one transaction, so any discussion of isolation levels is moot.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Pavel Ivanov wrote:
> BTW, ACID that you mentioned has nothing to do with snapshot isolation
> that you want to achieve. AFAIK only Oracle supports this kind of
> statement isolation level.

Actually, Oracle, Postgresql, SQL Server, Firebird, and others support 
snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation

And I certainly hope I did not convey that ACID implies snapshot isolation.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Roger Binns wrote:
> Yang Zhang wrote:
>> 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`).
> 
> And for very good reason.  Blobs are buckets of bytes and those are not
> strings.  In your example there was no need to do the conversion since
> you can supply buffers as values too.  (In Python 3 the bytes type is used.)

Actually, this is only because Python 3 str is Python 2 unicode.  Python 
2 (which I'm currently using, and which I believe most of the world is 
using) str is a physical string of bytes, not a logical/decoded 
character string.  Python 2.6 introduces bytes as a synonym for str, but 
I am using Python 2.5 at the moment.

 From http://mail.python.org/pipermail/python-list/2009-January/696449.html:

> In Python 2.x, str means "string of bytes". This has been renamed "bytes" 
> in Python 3.
> 
> In Python 2.x, unicode means "string of characters". This has been 
> renamed "str" in Python 3.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[John Elrick]

D. Richard Hipp wrote:
> On May 18, 2009, at 1:13 PM, John Elrick wrote:
>
>   
>> John Elrick wrote:
>> 
>>> SNIP
>>>
>>>   
> 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.
>
>
>   
 The cursor() method that I call on the conn for the SELECT should  
 give
 me a separate transaction.

 
>>> Are you certain the wrapper is behaving that way?  As an experiment I
>>> altered my Ruby example to try to force it to go into an endless loop
>>> and failed (see below).  My experiments seem to confirm that Sqlite  
>>> is
>>> behaving as you expect, perhaps it is the wrapper which is not?
>>>
>>>   
>> Attempting this closer to the metal, it appears I was mistaken.  A
>> select from outside a transaction does indeed have visibility to rows
>> added inside the transaction.  I would not have expected this  
>> either, Yang.
>> 
>
>
> Double-check your findings, please John.
>
> The changes within an SQLite transaction are not visible to other  
> database connections until the transaction commits.  (However, they  
> are visible within the same database connection.)
>
> An exception to the previous paragraph is if you are using shared  
> cache mode and you do a PRAGMA read_uncommitted=ON;
>   

If I am understanding you correctly, we are saying the same things.  I 
have a single connection.  I am doing the following in order:

prepare select
start transaction
step select
replace...
loop until no more in select (in practice never terminates)
commit

The code is in Delphi (custom wrapper, heavily unit tested), so I 
started creating a version truly using bare metal calls, until I noticed 
your caveat "...changes within...not visible to other database 
CONNECTIONS...".

 From Yang's original code description he was attempting this within a 
single connection as is my test code.

Am I misunderstanding or is the above Working As Designed?  If I am 
misunderstanding and the above should terminate I will continue creating 
a test version using all direct calls to verify the result, as that 
would imply a bug in our Delphi wrapper.

Thanks,



John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Yang Zhang wrote:
> 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`).

And for very good reason.  Blobs are buckets of bytes and those are not
strings.  In your example there was no need to do the conversion since
you can supply buffers as values too.  (In Python 3 the bytes type is used.)

Converting blobs to str is asking for future problems.  For example
SQLite does not verify that a string value is in fact legal UTF8.  Other
developers have played fast and loose ending up supplying a str when
they really meant a buffer/blob which then ended up in the database as a
string, but was invalid UTF8 when read back and causes an exception.

I also strongly recommend reading this article:

  http://www.joelonsoftware.com/articles/Unicode.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRoFUACgkQmOOfHg372QSIagCg5nllDS/Q5hsgfc+WzjS94Ubk
5jAAoONyqlKsczc1f+q01JqR6/Ysih2q
=TODs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

John Elrick wrote:
> Are you certain the wrapper is behaving that way?  

The pysqlite wrapper (available as a sqlite3 Python module) by default
parses your SQL and starts and ends transactions behind your back, in
order to make it look like SQLite complies with some (IMHO pointless)
part of the Python DBAPI spec.

Fortunately it can be turned off which is what the isolation level stuff
in the docs are all about.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRnrIACgkQmOOfHg372QRhMwCeI52EqZ2/MR8AZK+/TPrF1nJt
RMAAn3P3MJ9J6hZHsZCzUQju1nbHr+aU
=Gnpl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[D. Richard Hipp]

On May 18, 2009, at 1:13 PM, John Elrick wrote:

> John Elrick wrote:
>> SNIP
>>
 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.


>>> The cursor() method that I call on the conn for the SELECT should  
>>> give
>>> me a separate transaction.
>>>
>>
>> Are you certain the wrapper is behaving that way?  As an experiment I
>> altered my Ruby example to try to force it to go into an endless loop
>> and failed (see below).  My experiments seem to confirm that Sqlite  
>> is
>> behaving as you expect, perhaps it is the wrapper which is not?
>>
>
> Attempting this closer to the metal, it appears I was mistaken.  A
> select from outside a transaction does indeed have visibility to rows
> added inside the transaction.  I would not have expected this  
> either, Yang.


Double-check your findings, please John.

The changes within an SQLite transaction are not visible to other  
database connections until the transaction commits.  (However, they  
are visible within the same database connection.)

An exception to the previous paragraph is if you are using shared  
cache mode and you do a PRAGMA read_uncommitted=ON;

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[John Elrick]

John Elrick wrote:
> SNIP
>   
>>> 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.
>>> 
>>>   
>> The cursor() method that I call on the conn for the SELECT should give 
>> me a separate transaction.
>> 
>
> Are you certain the wrapper is behaving that way?  As an experiment I 
> altered my Ruby example to try to force it to go into an endless loop 
> and failed (see below).  My experiments seem to confirm that Sqlite is 
> behaving as you expect, perhaps it is the wrapper which is not?
>   

Attempting this closer to the metal, it appears I was mistaken.  A 
select from outside a transaction does indeed have visibility to rows 
added inside the transaction.  I would not have expected this either, Yang.


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[John Elrick]

SNIP
>> 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.
>> 
>
> The cursor() method that I call on the conn for the SELECT should give 
> me a separate transaction.

Are you certain the wrapper is behaving that way?  As an experiment I 
altered my Ruby example to try to force it to go into an endless loop 
and failed (see below).  My experiments seem to confirm that Sqlite is 
behaving as you expect, perhaps it is the wrapper which is not?


John

-
require 'sqlite3'
require 'erb'

db = SQLite3::Database.new(':memory:')

db.execute_batch(ERB.new(<').result(binding))
begin transaction;
create table shelf (
  key integer not null,
  value integer not null);

insert into shelf values (1, 1);
insert into shelf values (2, 2);
insert into shelf values (3, 3);

commit;
eof


SAFETY = 10
count = 0
db.transaction {
  db.execute('select key from shelf order by rowid').each do |i|
db.execute('replace into shelf (key, value) values(?,?)', i, i)
count += 1
abort if count == SAFETY
  end
}

puts db.execute('select * from shelf')
puts 'done'


1
1
2
2
3
3
1
1
2
2
3
3
done

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Pavel Ivanov]

SQLite doesn't support several simultaneous transactions on the same
connection to database. So in fact your select and insert statements
execute in the same transaction. And even more: your inserts are not
committed until your select is completely finished. So for your task
you should use different connections for select and inserts. But it
will not work anyway, because SQLite doesn't support row-level
locking, it locks entire database. Thus your select statement on one
connection will block execution of inserts on another connection. So
you should stick with one of the solutions mentioned or switch to
another database engine that will fit you better.

BTW, ACID that you mentioned has nothing to do with snapshot isolation
that you want to achieve. AFAIK only Oracle supports this kind of
statement isolation level.


Pavel

On Mon, May 18, 2009 at 12:41 PM, Yang Zhang  wrote:
> John Elrick wrote:
>> 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.
>
> The cursor() method that I call on the conn for the SELECT should give
> me a separate transaction.
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Yang Zhang wrote:
> John Elrick wrote:
>> 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.
> 
> The cursor() method that I call on the conn for the SELECT should give 
> me a separate transaction.

I also tried using separate connections, but that just ends up blocking 
and failing with a timeout on the lock acquisition because it appears 
that SQLite only has full-table locking, and not MVCC/snapshot 
isolation.  Do I need to manually extract out all the data first into 
another store, and then iterate over that to operate on original database?
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

John Elrick wrote:
> 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.

The cursor() method that I call on the conn for the SELECT should give 
me a separate transaction.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[John Elrick]

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(<').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

Re: [sqlite] Transaction isolation

[Yang Zhang]

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).

> 
>> Any way to solve this problem?  
> 
> You currently have the SELECT results being read back one at a time
> (lazily) on each iteration of the for loop.  The simplest solution is to
> read them all in first.  Add .fetchall() after the execute.

Unfortunately in the original application the table is large (many GBs). 
  Any way to solve this problem without first reading everything into 
memory, and without manually creating a second copy of the table?  Is 
there no way to request a stronger isolation level, such as snapshot 
isolation?
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Igor Tandetnik]

"Roger Binns"  wrote in
message news:4a1127e1.1040...@rogerbinns.com
> 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.

Note further that, since there are no UNIQUE or PRIMARY KEY constraints 
on any columns, REPLACE INTO is equivalent to INSERT INTO. In other 
words, no rows are being deleted, just new ones added.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

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.

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.

> Any way to solve this problem?  

You currently have the SELECT results being read back one at a time
(lazily) on each iteration of the for loop.  The simplest solution is to
read them all in first.  Add .fetchall() after the execute.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRJ9kACgkQmOOfHg372QT/JgCfRImM5e85JCgn3bmp45zGm6j6
uQMAn11x9OfWdBUMwq/6zZdvSCSuGGGS
=ABo+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transaction isolation

[Yang Zhang]

Hi, for some reason the following program will loop forever:

#!/usr/bin/env python

import sqlite3

conn = sqlite3.connect(':memory:')
conn.text_factory = bytes
conn.execute('CREATE TABLE shelf (key INTEGER NOT NULL, value INTEGER 
NOT NULL)')
for i in xrange(3):
   conn.execute('INSERT INTO shelf (key, value) VALUES (?,?)', (i, i))
conn.commit()

for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
shelf ORDER BY ROWID')):
   conn.execute('REPLACE INTO shelf (key, value) VALUES (?,?)', (i, i))
   conn.commit()
   print i

Anybody understand why?  I thought the REPLACE and SELECT transactions 
should be (snapshot) isolated from each other, so why does the SELECT 
keep getting updated rows from the REPLACE?  Any way to solve this 
problem?  So far all I've found are commands that can change the 
connection-level isolation/locking, but not the cursor-level 
(transaction-level) isolation.

Thanks in advance for any answers!
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users