Lothar Behrens wrote: > Am 10.09.2008 um 17:37 schrieb Dennis Cote: > >> Lothar Behrens wrote: >>> What is the function to rollback a transaction or commit ? >>> I want also to break into these functions. If there is no way I try >>> to implement the rollback and commit callbacks. >>> Also the closing of the database would be interesting, or analysing >>> the data in the jornal. >> The journal file is closed when a transaction ends. This is done by >> the function pager_end_transaction() at line 28880 of the >> amalgamation. Note this function is called for both a rollback or a >> commit. >> > > Hi, > > I now have seen that many of my simple select statements automatically > does a rollback on behalv of OP_Halt. > > Also I have seen that an insert, update or delete statement does > automatically a commit in some circumstances as: > > * One VDBE is running only > * the statement hits an ON FAIL and have to commit in that case > * other circumstances I do not understand yet > > If I do understand all this correctly I have one case I may stuck into: > > A select statement (not readonly) is still open (having sqlite3_step() > returning SQLITE_ROW) and then > I have created an insert statement that is committed but the outer > transaction as of the select statement > does a rollback if closed later. > > Thus, this results in readable (just inserted) data but loses these > data because the outer rollback occurs. > > Right ? > > If so, then I have to redesign something as of this may be the case in > my usage of the database API :-) > > My database form opens a statement to select some data and navigates > to one (the first, the next or any other) and > leaves the statement open in a transaction I think (form A, database > A) as of a call to sqlite3_step() returning SQLITE_ROW. > > Then I open another database form (form B, database A) and try to add > some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or > rollback (sqlite3BTreeRollback), so I assume the running transaction > from form A is causing this. > > Then when I close my application the transaction (form A, database A) > is rolled back and this loses my data changes. > > Right ?
That is correct. You are in autocommit mode, so each SQL statement executes in its own transaction. The outer select starts a transaction. The insert does not start a transaction, since a transaction is already open (sqlite only has a single transaction open at any time). The application can see all the changes to the database (i.e. it can see the uncommitted data). If you now close the database before you reset or finalize the select query (which will commit the transaction it started), then the open transaction will be rolled back and the changes will be lost. > > So my solution would be this: > > Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try > to finish until SQLITE_DONE to close the transaction. > > I can do this because: > > * I mostly read only the primary keys of a table (there it is done > automatically) to prepare for lazy load (pattern). > * I read the full data row for a specific primary key as of any cursor > activity. (That way I have simulated full cursor support) > > I hope with that I get solved this problem. > > Please comment, If there is something still wrong in my understanding. > That should work fine. You don't have to let the select run to completion if you don't want to. If you reset or finalize the select after it returns the last desired row, it will also close the transaction. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
