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