473,554 Members | 2,100 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQLite (with APSW) and transaction separate

Part one:
======

Hi !

I want to use SQLite database like the FireBird database: with big
isolation level.
What's that meaning ?

I have an application that periodically check some input directory,
process the elements in it, and copy them into a global database.
It is like a daemon, working in the background.

The end-user uses an another GUI application, that can create reports
from this database.

Ok. In FireBird I can do it fine that the user's app use another
transaction with READ REPEATABLE style, and the daemon use another
transaction too, with same style.

This style is correctly separate two transactions, the user see full
consistent state in database.
If daemon has been finished the work, commit and close it's transaction
- but that is not confused the user's reporter program.

Example:
I open the database for create reports from a master-detail table.
Example bills.
The code get every bills.
If daemon see a new data, it is get it, and put to the database.
Now it is see a new data. It is make insert/update sqls.

The reporter tool read the bill 002 head.
The daemon write this head, and append a new subitem to bill's subitems
table. Commit.
If ISOLATION LEVEL is low, the report can see the new subitems of the
bill, but the bill's head is not same - inconsistency we get.
Example: Bills head have 3 items, the total charge is 600$. In low Isol.
level when the daemon is add new records to this bill, the head total is
remaining 600$, but calculated subtotals are bigger than this.

In the big isolation level (repeatable read) the reporter not see the
any changes in the database only what it makes.

I can do same effect if I create "transactio n aliasing code". I need to
extend my tables with recversion field that containing a timestamp value.
And every SQL I need to extend with where timestamp is...
But it is very ugly solution...

Please help me: have the SQLite same tr. isol. mechanism like FireBird ?
http://www.dotnetfirebird.org/transa...olation-levels
http://www.ibphoenix.c om/main.nfs?a=ibph oenix&l=;KNOWLE DGEBASE;ID='377 '
http://www.ibphoenix.c om/main.nfs?a=ibph oenix&l=;KNOWLE DGEBASE;ID='128 '
http://www.ibphoenix.c om/main.nfs?a=ibph oenix&l=;KNOWLE DGEBASE;ID='129 '

Thanx for your help:
dd
Part two:
=========

dr*@hwaci.com írta:
DurumDara <du*******@gmai l.com> wrote:
I want to use SQLite database like the FireBird database: with big
isolation level.


SQLite transactions are SERIALIZABLE.

It is possible to force SQLite transactions to be
READ UNCOMMITTED under some special circumstances, but
you have to work very hard to make this happen. By
default, transactions are always SERIALIZABLE.

SERIALIZABLE gives you everything that REPEATABLE READ
gives you in terms of isolation.

--
D. Richard Hipp <dr*@hwaci.co m>

Hi !

I see you don't understand what I saw about.
In FireBird every transactions have a tr. number, an integer.
If isolation level is high, the transaction with lesser number does not
see the modifications of the later opened transaction, because this
transaction have greater number (ID), and every transaction see only
modifications created by transactions are have lesser or equal ID.
So:
2. transaction is see 1. and 2., but does not see the 3., 4. etc,
because every records are versioned ("stamped" with it's creator's number).
Why it is good ?
When you are a reader, and some other people are writer, you don't see
any changes in database, because you see only your version of the database.
That's caused consistency. The later opened transactions can write
anything to the database, if you don't reopen your transaction, you
don't see any changes in it.
That is what I wroted about: the bill and it's items are reserved - if
anyone change the subtotals, your total and subtotals are not changed.

Only this way makes good result in the reporter applications, because if
anyone commit changes in databases, you don't see it, it is not confused
the database, and reports. If you have 6. items in a bill, and anyone
delete one of them, you don't see it. I you can see it, your bill's
total is not equal with sum of subelements !

I maked a try with Python APSW and SQLite. This is a double threaded
app, because it need to simulate the isolation between reader and writer.

It is use queue to send signals, and for control main/subthread.

############### ############### ##
import threading,apsw, Queue

class TestThr(threadi ng.Thread):
def __init__(self):
threading.Threa d.__init__(self )
self.IQ=Queue.Q ueue()
self.OQ=Queue.Q ueue()

def run(self):
try:
print "*THREAD: Thread started"
while self.IQ.empty() : pass
self.IQ.get()
print "*THREAD: <<< Prepare database"
con=apsw.Connec tion('test.db')
c=con.cursor()
try:
c.execute('crea te table a(a integer)')
c.execute('end' )
except:
pass
c.execute('begi n')
c.execute('dele te from a')
c.execute('end' )
print "*THREAD: >>> Prepare database"
self.OQ.put(1)
while self.IQ.empty() : pass
self.IQ.get()
print "*THREAD: <<< Fillup 1000 values"
c.execute('begi n')
print "*THREAD: Trans. started"
for i in range(1000):
c.execute('inse rt into a values(%d)'%i)
print "*THREAD: >>> Fillup 1000 values"
self.OQ.put(1)
while self.IQ.empty() : pass
self.IQ.get()
c.execute('end' )
print "*THREAD: Trans. finished"
self.OQ.put(1)
while self.IQ.empty() : pass
self.IQ.get()
print "*THREAD: <<< Fillup 1000 values"
c.execute('begi n')
print "Trans. started"
for i in range(1000,2000 ):
c.execute('inse rt into a values(%d)'%i)
print "*THREAD: >>> Fillup 1000 values"
c.execute('end' )
print "*THREAD: Trans. finished"
self.OQ.put(1)
while self.IQ.empty() : pass
self.IQ.get()
print "*THREAD: Thread end"
self.OQ.put(1)
except:
print sys.exc_info()
sys.exit()

con=apsw.Connec tion('test.db')
c=con.cursor()

t=TestThr()
t.IQ.put(1)
t.start()
while t.OQ.empty(): pass
t.OQ.get()
#c.execute('beg in')
def ReadLastRec():
rec=None
for rec in c.execute('sele ct * from a'): pass
print "- MAIN: Read last record",rec
ReadLastRec()
t.IQ.put(1)
while t.OQ.empty(): pass
t.OQ.get()
ReadLastRec()
t.IQ.put(1)
while t.OQ.empty(): pass
t.OQ.get()
ReadLastRec()
t.IQ.put(1)
while t.OQ.empty(): pass
t.OQ.get()
ReadLastRec()
t.IQ.put(1)
while t.OQ.empty(): pass
#c.execute('end ')

print "\n- MAIN: Finished"

############### ############### ##
Commandline: C:\Python24\pyt hon.exe G:\dev\INFOGU~1 \TESTPY~1\TESTR E~1.PY
Workingdirector y: G:\dev\InfoGuar d\testpysqlite
Timeout: 0 ms

*THREAD: Thread started
*THREAD: <<< Prepare database
*THREAD: >>> Prepare database
- MAIN: Read last record None
*THREAD: <<< Fillup 1000 values
*THREAD: Trans. started
*THREAD: >>> Fillup 1000 values
- MAIN: Read last record None
*THREAD: Trans. finished
- MAIN: Read last record (999,)
*THREAD: <<< Fillup 1000 values
Trans. started
*THREAD: >>> Fillup 1000 values
*THREAD: Trans. finished
- MAIN: Read last record (1999,)
*THREAD: Thread end

- MAIN: Finished

Process "Pyhton Interpeter" terminated, ExitCode: 00000000
############### ############### ##

If you can see, I simulate the reading (main thread) in one table when
the writer demon is working in the background (subthread).
I repeats the readings, and while I do this, the writer thr makes
changes to database.
As you see, I can read the another process'(thread 's) writings - the
transactions are not separated like in FireBird.
It is caused that when I need to read from table I CAN GET ANOTHER
RECORD COUNTS, AND VALUES - AND THAT IS WHAT I WANT TO AVOIDING !

In FireBird the writer thread's changes not visible in the reader
thread, so I can see consistent data structure - a snapshot that not
changing before I reopen the reader transaction.

Please answer me: it is wrong I write about, or that is seems to be not
working in SQLite ?

Thanx for your help:
dd
Apr 19 '06 #1
2 3781
Hi !

Dennis Lee Bieber írta:
On Wed, 19 Apr 2006 17:29:28 +0200, Christian Stooker
<du*******@mail pont.hu> declaimed the following in comp.lang.pytho n:

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

Apr 20 '06 #2
Dennis Lee Bieber wrote:

Isolation levels I've seen, but not in the RDBMs I tend to have most experience
with (and for those I have documentation of, "The Firebird Book" is the
only one that seems to be explicit about multiple generations of updates
based on transactions; others seem to imply that updates block all other
transactions until committed -- at which point other transactions would
see them).


Take a look here for some PostgreSQL documentation on transaction
isolation levels:

http://www.postgresql.org/docs/7.4/s...ction-iso.html

Paul

Apr 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2408
by: Michael Goettsche | last post by:
Hello guys, I succeeded in convincing my CS teacher to use Python and Sqlite instead of Microsoft Access to get started with databases. We are working on a windows terminal server to which I have no admin access, so I'd like to ask you which module is best suited to use Sqlite with Python under windows. The best would be a module which is...
1
1196
by: DurumDara | last post by:
Hi ! I want to process many data with python, and want to store in database. In the prior version of my code I create a simple thing that delete the old results, recreate the database and fill up it. But that is not too flexible, because when an power supply or hardware problem occured, all of the processed items are lost. Then I...
12
8510
by: Julien ARNOUX | last post by:
Hi, I'd like to use regular expressions in sqlite query, I using apsw module but it doesn't work...Can you help me ? My script: import apsw import re path = 'db/db.db3'
12
2465
by: John Salerno | last post by:
I've been looking around and reading, and I have a few more questions about SQLite in particular, as it relates to Python. 1. What is the current module to use for sqlite? sqlite3? or is that not out until Python 2.5? 2. What's the difference between sqlite and pysqlite? Do you need both, just one, or is one an older version of the same...
4
7957
by: mahdaeng | last post by:
I have a little Windows application written in C# with a SQLite back- end. I'm using the System.Data.SQLite provider. One of the features the application provides is a database back-up, which just basically copies the S3DB file to a specified location. See the code below: //------------------------------------------------...
20
5376
by: Rob Stevens | last post by:
Can someone tell me how to import the sqlite3.dll file into c#. I am pretty new to this, and I want to use sqlite. The problem is I don't have a clue on how to import the dll file so i can call the references to it. Thanks
0
1389
by: Guilherme Polo | last post by:
On Tue, Jul 1, 2008 at 9:51 PM, Joe Goldthwaite <joe@goldthwaites.comwrote: You need sqlite itself (www.sqlite.org) and bindings, pysqlite 2 (http://oss.itsystementwicklung.de/trac/pysqlite/) or aspw (http://code.google.com/p/apsw/) You are confused, yes. The sqlite3 package that comes with python2.5 is actually pysqlite2, which...
0
2565
by: Joe Goldthwaite | last post by:
Thanks Guilherme. That helped. I guess I was thinking that pysqlite would automatically come with some version of sqlite. The fact that it doesn't is what was causing me to get the strange results. I downloaded the Windows version of the SQLite3.dll. I didn't know where to put it so I first put it in its own directory and tried to...
0
1457
by: =?ISO-8859-1?Q?Gerhard_H=E4ring?= | last post by:
egbert wrote: No, it's just like every other database error - the command fails but the connection is left untouched. Sure, the successful ones ;-) FWIW, this restriction is not any longer true in Python 2.6 and 3.0 btw.
0
7530
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8047
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7570
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6156
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5441
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5162
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2022
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1141
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
845
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.