--- edz...@volcanomail.com wrote:
> I wonder if an automatic rollback, as described in
> //www.sqlite.org/c3ref/busy_handler.html, is more powerful than a
> rollback programmed in SQL. Particularly if it is able to rollback
> pending queries from other cursors in the same connection. The
> programmed rollback fails here with BusyError: cannot rollback
> transaction - SQL statements in progress.
>
> I tried to find out experimentally but failed to reproduce any automatic
> rollback. It seems another solution is in place. I may be not well
> informed, is anything written about this already?
>
> From the timeline it appears there is ongoing development on the
> subject. Is there any chance that a rollback in SQL will no longer fail
> with pending queries?
Hello again,
The mechanism of an automatic rollback, as described in
//www.sqlite.org/c3ref/busy_handler.html, appears to have disappeared somewhere
between version 3.5.9 and 3.6.5. The following steps should show the different
behaviour.
- Create a table containing about 1 M byte of data
(i hope anybody can imagine this for himself)
- Let one process keep a shared lock
$ sqlite3 test.db "select * from t" | more
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
...
(leave this command unfinished)
- Start a large transaction, first in the old version
$ sqlite-amalgamation-3.5.9/sqlite3 test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> begin;
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
SQL error: disk I/O error
sqlite> commit;
SQL error: cannot commit - no transaction is active
- Same for the current version
$ sqlite3 test.db
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> begin;
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
sqlite> commit;
SQL error: database is locked
The current behaviour looks better, the application may still commit once the
database is unlocked. However it also has a disadvantage which appears from the
process status. At the current point this shows:
$ ps -lp 14440
UID PID PPID CPU PRI NI VSZ RSS COMMAND
501 14440 4281 0 31 0 29936 7088 sqlite3
If the insert step is repeated once again, this becomes:
$ ps -lp 14440
UID PID PPID CPU PRI NI VSZ RSS COMMAND
501 14440 4281 0 31 0 38176 14056 sqlite3
The memory usage increases about 7000 pages which is in line with the amount of
data added (4M). This must become a problem if dealing with gigabytes.
I could not find anything written about the change, except a quote from Dan
Kennedy in the mailing list:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg42059.html. But then the
busy_handler description must be out of date.
This derived a bit from the subject, I still would like the more powerful
rollback in SQL.
Tnanks, Edzard
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
