[EMAIL PROTECTED] írta:
DurumDara <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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(threading.Thread):
def __init__(self):
threading.Thread.__init__(self)
self.IQ=Queue.Queue()
self.OQ=Queue.Queue()
def run(self):
try:
print "*THREAD: Thread started"
while self.IQ.empty(): pass
self.IQ.get()
print "*THREAD: <<< Prepare database"
con=apsw.Connection('test.db')
c=con.cursor()
try:
c.execute('create table a(a integer)')
c.execute('end')
except:
pass
c.execute('begin')
c.execute('delete 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('begin')
print "*THREAD: Trans. started"
for i in range(1000):
c.execute('insert 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('begin')
print "Trans. started"
for i in range(1000,2000):
c.execute('insert 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.Connection('test.db')
c=con.cursor()
t=TestThr()
t.IQ.put(1)
t.start()
while t.OQ.empty(): pass
t.OQ.get()
#c.execute('begin')
def ReadLastRec():
rec=None
for rec in c.execute('select * 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\python.exe G:\dev\INFOGU~1\TESTPY~1\TESTRE~1.PY
Workingdirectory: G:\dev\InfoGuard\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
