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 "transaction 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/transaction-isolation-levels http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='377' http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='128' http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='129' Thanx for your help: dd Part two: ========= [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 -- http://mail.python.org/mailman/listinfo/python-list
