headers
Bert Nelsen | 1 Jan 2010 14:37

Re: Mozilla's method

Hello Olaf!
Thanks again for the message.
By "corrupted" (the meaning of this word seems to differ depending on the
user's perspective) you mean that the db is really destroyed and cannot be
opened anymore?
Currently I am working on an application that tracks something like a GPS
signal, and if a signal is not saved to the db it's not a big problem. But
db corruption would be.
Another question would be: When I use transactions, and I said ".BeginTrans"
and insert new records and then, before saying ".CommitTrans", I query the
records, they seem to be already saved. Can you tell me why this is so? Does
a select command automatically trigger a ".CommitTrans"?
Wishing you a happy new year!

On Fri, Jan 1, 2010 at 9:11 AM, Olaf Schmidt <sss@...> wrote:

>
> "Bert Nelsen" <bert.nelsen@...> schrieb
> im Newsbeitrag
> news:a5ffd530912311004p26a7cc5k1f1bf6f671befdd5@...
>
> > Your .Sychronous = False property does everything
> > as fast as I want, and I am not afraid of losing some
> > user data (it's not a critical application) but
> > I am very much afraid of having a corrupted db.
> > Can anybody please confirm
> > that there is no chance of getting my db corrupted?
>
> Ah, I see now, where the "confusion" came from.
> The wrappers Synchronous-Property has nothing to do
(Continue reading)

Permalink | Reply |
headers
Max Vlasov | 1 Jan 2010 15:46
Picon

Re: Mozilla's method

> Another question would be: When I use transactions, and I said
> ".BeginTrans"
> and insert new records and then, before saying ".CommitTrans", I query the
> records, they seem to be already saved. Can you tell me why this is so?
> Does
> a select command automatically trigger a ".CommitTrans"?
> Wishing you a happy new year!
>
>
I think it comes from the nature of transaction itself, it is not an
isolated database inside the database, it's an "undo" feature. Otherwise
many operations would have unpredictable results.
So in the example below

sqlite> Create Table [TestTable] ([Value] INTEGER);
sqlite> Begin transaction;
sqlite> INSERT INTO TestTable (Value) VALUES (11);
sqlite> SELECT Count(*) FROM TestTable;
1
sqlite> SELECT Max(rowid) FROM TestTable;
1
sqlite> SELECT * FROM TestTable;
11
sqlite> End transaction;

.. all three selects should return the same results regardless of begin/end
transaction existence in the sequence of commands and it makes sense I
think.

Max
(Continue reading)

Permalink | Reply |
headers
Igor Tandetnik | 1 Jan 2010 17:04
Favicon

Re: Mozilla's method

Bert Nelsen wrote:
> By "corrupted" (the meaning of this word seems to differ depending on the
> user's perspective) you mean that the db is really destroyed and cannot be
> opened anymore?

Yes it's possible, if the power loss occurs at just the wrong moment.

> Another question would be: When I use transactions, and I said ".BeginTrans"
> and insert new records and then, before saying ".CommitTrans", I query the
> records, they seem to be already saved.

A transaction can always see the changes it iself made. They are not necessarily "saved" in the sense of
being written to the disk surface: modified records may come from in-memory cache.

Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
Bert Nelsen | 1 Jan 2010 18:11

Re: Mozilla's method

So SQLite looks at both the database on the disk and in memory?
Wouldn't that be difficult???
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
Igor Tandetnik | 1 Jan 2010 18:26
Favicon

Re: Mozilla's method

Bert Nelsen wrote:
> So SQLite looks at both the database on the disk and in memory?
> Wouldn't that be difficult???

The in-memory cache is an integral part of the architecture. The whole transaction model depends on it in
large part. For details, see

http://www.sqlite.org/arch.html
http://www.sqlite.org/lockingv3.html

Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
Max Vlasov | 1 Jan 2010 19:50
Picon

Re: Mozilla's method

On Fri, Jan 1, 2010 at 8:11 PM, Bert Nelsen <bert.nelsen@...>wrote:

> So SQLite looks at both the database on the disk and in memory?
> Wouldn't that be difficult???
>

I don't think that only the memory can be used. Imagine you can have a very
big transaction, 1,000,000 inserts. As long as I understand the
architecture, all affected sectors are saved in .db3-journal file so the db3
file itself always reflects the current state of the database. For example
you have a table with 100 records and you're inserting 1,000,000 records, so
at the final stage, right before Commit, your db3 file contains all these
1,000,100 records and the indexes changed accordingly, while the
corresponding db-journal consists of the data needed to restore the db file
to the state it was before the transaction began
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
Olaf Schmidt | 1 Jan 2010 23:33
Picon

Re: Mozilla's method


"Bert Nelsen" <bert.nelsen@...> schrieb
im Newsbeitrag
news:a5ffd531001010911r3de60ec1o44e2c14bce7a75dd@...

> So SQLite looks at both the database on the disk
> and in memory?
> Wouldn't that be difficult???

Of course... ;-)
And what's so amazing with the SQLite-engine -
is, that all that comes in such a small package.

Regarding "aggregated writes" and transactions again...

What SQLites async-writer thread does, is to
implement something like a "delayed write" at
the DB-engine-level (instead to rely on such a
feature to be implemented at the filesystem-level).

Delayed writes can ensure better performance, since
the new gathered (to be written) data can be grouped
(sometimes also reordered) into larger chunks,
to let the "real disk actions" happen within a more
optimal (more efficient) "operation-window", so to say.

In case of your (smaller) VB-based application you can
achieve something like that also with an "always opened"
transaction, gathering the new to be written data (records)
using SQLites internal transaction-cache-mechanisms -
(Continue reading)

Permalink | Reply |
headers
Artur Reilin | 1 Jan 2010 23:50
Picon
Favicon

Re: Mozilla's method

But that means, if there is a power off or an system crash, your data  
which you send at this moment, goes nirvana. But indeed it would be faster  
and also would save the hard disc from some writing operations.

Am 01.01.2010, 23:33 Uhr, schrieb Olaf Schmidt <sss@...>:

>
> "Bert Nelsen" <bert.nelsen@...> schrieb
> im Newsbeitrag
> news:a5ffd531001010911r3de60ec1o44e2c14bce7a75dd@...
>
>> So SQLite looks at both the database on the disk
>> and in memory?
>> Wouldn't that be difficult???
>
> Of course... ;-)
> And what's so amazing with the SQLite-engine -
> is, that all that comes in such a small package.
>
> Regarding "aggregated writes" and transactions again...
>
> What SQLites async-writer thread does, is to
> implement something like a "delayed write" at
> the DB-engine-level (instead to rely on such a
> feature to be implemented at the filesystem-level).
>
> Delayed writes can ensure better performance, since
> the new gathered (to be written) data can be grouped
> (sometimes also reordered) into larger chunks,
> to let the "real disk actions" happen within a more
(Continue reading)

Permalink | Reply |
headers
Olaf Schmidt | 2 Jan 2010 00:14
Picon

Re: Mozilla's method


"Artur Reilin" <sqlite@...> schrieb im
Newsbeitrag news:op.u5vlqcps1pqt5o <at> rear...

> But that means, if there is a power off or an system crash,
> your data which you send at this moment, goes nirvana.

Yep, as I wrote at the end of my post:
  "...in case of an unexpected Close of the App (due to
   whatever reason), you will lose only the new data which
   was gathered within the last timer-interval."

The Timer-interval in question should therefore not be
too large - also with regards to "palpable App-Blocking"
in the continously (timer-triggered) "syncing Events" ... but also
not too small, to achieve the expected "performance effect" -
so, at least "more than only one single new log-record" should
be gathered (on average) within the interval, to work with a
somewhat better write-efficiency.

Would require some experimenting first, which timer-interval
works best (depends somewhat on the frequency and size of
the incoming new data-records, but also on the underlying
storage-media, the DB is hosted on - be it flash-based media,
as USB-sticks for example - or "real Hard-Disks").

Olaf Schmidt

_______________________________________________
sqlite-users mailing list
(Continue reading)

Permalink | Reply |
headers
Artur Reilin | 2 Jan 2010 00:32
Picon
Favicon

Re: Mozilla's method

If you are using something like an log system it would be better in this  
way, but in apps like an shop what wouldn't be so great. (thinking about  
ebay with the bets and such..)

But the idea itself is nice.

Am 02.01.2010, 00:14 Uhr, schrieb Olaf Schmidt <sss@...>:

>
> "Artur Reilin" <sqlite@...> schrieb im
> Newsbeitrag news:op.u5vlqcps1pqt5o <at> rear...
>
>> But that means, if there is a power off or an system crash,
>> your data which you send at this moment, goes nirvana.
>
> Yep, as I wrote at the end of my post:
>   "...in case of an unexpected Close of the App (due to
>    whatever reason), you will lose only the new data which
>    was gathered within the last timer-interval."
>
> The Timer-interval in question should therefore not be
> too large - also with regards to "palpable App-Blocking"
> in the continously (timer-triggered) "syncing Events" ... but also
> not too small, to achieve the expected "performance effect" -
> so, at least "more than only one single new log-record" should
> be gathered (on average) within the interval, to work with a
> somewhat better write-efficiency.
>
> Would require some experimenting first, which timer-interval
> works best (depends somewhat on the frequency and size of
(Continue reading)

Permalink | Reply |
headers
Olaf Schmidt | 2 Jan 2010 00:49
Picon

Re: Mozilla's method


"Artur Reilin" <sqlite@...> schrieb im
Newsbeitrag news:op.u5vno6hp1pqt5o <at> rear...

> If you are using something like an log system it
> would be better in this way, but in apps like an
> shop what wouldn't be so great. (thinking about
> ebay with the bets and such..)

Of course, but I think I made that already clear,
that the approach should not to be misunderstood as a
"general recommendation" - it really should be used only within
smaller Apps, which don't need e.g. "stacked transactions",
or "complex transactions which could fail" ... Apps which
also only work singlethreaded within a single process ...
...the timer-based transaction-syncing then only an
"easier applicable workaround" in environments which
cannot - (or don't want to) make use of the more efficient
working async-writer-thread implementation of the SQLite-engine).

Olaf Schmidt

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
Bert Nelsen | 2 Jan 2010 09:44

Re: Mozilla's method

Thanks Olaf.

On Sat, Jan 2, 2010 at 12:49 AM, Olaf Schmidt <sss@...> wrote:

>
> "Artur Reilin" <sqlite@...> schrieb im
> Newsbeitrag news:op.u5vno6hp1pqt5o <at> rear...
>
> > If you are using something like an log system it
> > would be better in this way, but in apps like an
> > shop what wouldn't be so great. (thinking about
> > ebay with the bets and such..)
>
> Of course, but I think I made that already clear,
> that the approach should not to be misunderstood as a
> "general recommendation" - it really should be used only within
> smaller Apps, which don't need e.g. "stacked transactions",
> or "complex transactions which could fail" ... Apps which
> also only work singlethreaded within a single process ...
> ...the timer-based transaction-syncing then only an
> "easier applicable workaround" in environments which
> cannot - (or don't want to) make use of the more efficient
> working async-writer-thread implementation of the SQLite-engine).
>
> Olaf Schmidt
>
>
>
>
> _______________________________________________
(Continue reading)

Permalink | Reply |

Gmane