SQLite (with APSW) and transaction separate

[DurumDara]

Hi !

Dennis Lee Bieber írta:
> On Wed, 19 Apr 2006 17:29:28 +0200, Christian Stooker
> <durumdara at mailpont.hu> declaimed the following in comp.lang.python:
>
>   
>> Please answer me: it is wrong I write about, or that is seems to be not 
>> working in SQLite ?
>>
>>     
> 	I don't think the feature you want to use is available -- heck, it
> may ONLY be a firebird feature... (I've not seen anything similar in any
> of the MySQL back-ends, or if there, I've not encountered it; and there
> aren't enough easily read documents for MaxDB [aka SAP-DB] in print to
> see if it has such a feature. My MSDE books don't mention it either, so
> I suspect M$ SQL Server may not support such).
>   
Sorry, but I don't think that this feature is exists in only Firebird.
Good RDMBS systems ***must have*** many transaction isolation levels.
The higher isolation level guaranteed a safely view for actual user.
READ COMMITTED, and REPEATABLE READ isolation level makes me sure, that
if I have a snapshot from the database, I get it same datas *everytime*
while my transaction opened. Until trs. is closed and reopened, I can
see the new modifications created by another user(s).
But when I keep alive my transaction, never I see any modifications,
only what I created. In this mode I don't see any user's updates -
vainly he or she do many commits (As I see in SQLite, the another user's
committed records visible for me - this is corrupting my view !)
That is the only way to I get good reports from an invoice manager
application, because I see current state of the database - the
sum(subtotals) everytime equal with total. In lower isolation level I
can see the new committed records - and that is bad thing, because in
this time the sum(subtotals) sometimes not equal with total.

That problem is just like thread synch. problem, where I need to protect
my subresources with locks/semaphores to avoid corrupting of data.
In Delphi I must protect string vars, because when every thread want to
write/read from this string, they easily make corrupted string from the
source...

> 	SQLite is a "file server" style database; Firebird is a
> client/server model.
>
> 	In Firebird, the server can track independent connections and
> maintain state for each. SQLite runs "locally"; each connection
> considers itself to be the only user of the database file (and locking
> for updates is on a file basis, not table or record). Once you commit a
> transaction in SQLite, the file itself is fully modified, and any other
> connections see that modified file -- there is no temporary session
> journal for a connection that holds a snapshot of that connection's
> data.
>   

So I cannot use a transaction number that identify transactions, and
make same isolation effect what I said about before ?

Thanx for help:
dd