Run BASIC - db locking

« db locking »

Welcome Guest. Please Login or Register.
Dec 8, 2009, 6:04pm

Run BASIC
Run BASIC Community Wiki
Easy Web Programming with Run BASIC

Run BASIC :: Run BASIC Programming :: Database Discussion :: db locking

Author

Topic: db locking (Read 115 times)

kokenge
Full Member

member is offline

Joined: Jan 2009
Gender: Male

Posts: 153
Karma: 2

db locking
« Thread Started on Jun 28, 2009, 2:14pm »

AAAAGGGGHHHH! :-/

Frustrations with SQLite.!!

I post this in the hopes that it can prevent others from having the same problems I"ve had with SQLite.

SQLite has it's place for holding and looking up information.

I figured I could work around the fact that it locks the entire DB when it does a write. But that is not the case. The real problem is SQLite's inability to free them up.

At times, I've had to read the DB into memory so it would think it had 2 databases.. But for some reason that confuses SQLite and many times it still locks up the system.. You would think that when reading from the memory file and writing to the disk DB file that wouldn't be the case?

Here for example is a code snippit of a very simple routine.. It reads a trace table, and a couple other tables. It needs to INSERT records based on some aggregate informations it finds.
However no matter if you connect the DB immediately before the insert and disconnect immediately after -- it locks the DB. The only out of the situation is to kill the RB server and restart the Console.
This happens to be one of the files I tried to move to memory so it reads from one DB and writes to another. But it still locks..

Can't wait for mySQL..
Hope this helps someone...

Code:
sqliteconnect #sql, runINVdb$ ' Connect to the DB

for tn    = 1 to numWpo
    thisWpoNum    = val(word$(wpoList$,tn,","))
    prefStorageId$    = ""
    pretStorageId$    = ""

    sql$    = "
    SELECT
    workTrace.wpoNum     as wpoNum,
    workTrace.fstorageId    as fstorageId,
    workTrace.fPreWpoNum    as fPreWpo,
    workTrace.fPreFromTo    as fPreFT,
    workTrace.tstorageId    as tstorageId,
    workTrace.tPreWpoNum    as fPreWpo,
    workTrace.tPreFromTo    as fPreFT,
    workTrace.tendQty     as endQty,
    wpoVariety.varietyNum    as varietyNum,
    wpoVariety.areaId     as areaId,
    wpoVariety.year        as year,
    sum(wpoVariety.pcnt)    as sumPcnt,
    avg(wpoVariety.pcnt)    as avgPcnt,
    sum(wpoVariety.qty)    as sumQty,
    sum(wpoVariety.glQty)    as sumGlQty,
    sum(wpoVariety.yield)    as sumYield,
    sum(wpoVariety.yield * workTrace.fmovQty * (wpoVariety.storageId = workTrace.fstorageId)) as fyq,
    sum(wpoVariety.yield * workTrace.tmovQty * (wpoVariety.storageId = workTrace.tstorageId)) as tyq

    FROM workTrace
    LEFT JOIN wpoVariety
    ON    wpoVariety.wpoNum = fPreWpoNum
    AND    wpoVariety.fromTo = fPreFromTo
    AND    wpoVariety.storageId = fstorageId

    OR    wpoVariety.wpoNum = tPreWpoNum
    AND    wpoVariety.fromTo = tPreFromTo
    AND    wpoVariety.storageId = tstorageId

    WHERE    workTrace.wpoNum    = ";thisWpoNum;"
    GROUP BY workTrace.wpoNum,workTrace.tstorageId,
    wpoVariety.varietyNum,wpoVariety.areaId,wpoVariety.year"

    #sql execute(sql$)
    WHILE #sql hasanswer()
        #row = #sql #nextrow()
        wpoNum = #row wpoNum()
        year = #row year()
        sumPcnt = #row sumPcnt()
        avgPcnt = #row avgPcnt()
        sumQty = #row sumQty()
        sumGlQty = #row sumGlQty()
        sumYield = #row sumYield()
        fyq = #row fyq()
        tyq = #row tyq()

sql1$    = "INSERT INTO wpoVariety VALUES(";_
wpoNum;",'";_
year;",";_
qty;",";_
sumGlQty;",";_
gaugeGlQty;",";_
concQty;",";_
avgPcnt;",";_
sumYield;")"

sqliteconnect #sql1, runINVdb$ ' Connect to the DB
#sql1 execute(sql1$)
#sql1 disconnect()

    WEND
next tn
#sql disconnect()
wait

Have a great day..
I'm going out in the back yard and eat worms..
Dan

Link to Post - Back to Top IP:

Logged

melvin2001
New Member

member is offline

Joined: Mar 2008
Gender: Male

Posts: 15
Karma: 0

Re: db locking
« Reply #1 on Aug 4, 2009, 4:29pm »

Is there an effective way of checking to see if a database is locked... or if a query actually succeeds? is there a way to force an unlock of a locked database WITHOUT rebooting the server?

Link to Post - Back to Top IP:

Logged

kokenge
Full Member

member is offline

Joined: Jan 2009
Gender: Male

Posts: 153
Karma: 2

Re: db locking
« Reply #2 on Aug 4, 2009, 7:04pm »

Being new to SQLite may be why I'm having problems. So I'm probably not the right person to answer this. But I'll give you my experience if it can help.

If there is another way to unlock, I haven't found it.. Maybe someone knows something here.

I like SQLite. It's small and stand alone and uses flat files.
If you have a simple application without update complications, I'd go with SQLite.

It's not the size or number of tables that causes the problem, It's the application. I've had small databases with only 10 or 20 tables cause more problems than larger databases.

If you have lots of people updating tables, or have the DB opened multiple times in a application it will cause problems.

I've tried all kinds of ways to solve this. I even tried to write my own DB manager where all DB action is done with a call to a single program.

Basically, I've come to the conclusion it just isn't worth the effort. So I've been writing all my applications knowing I will have to move to mySQL.

I tried DB triggers, and it's just impossible to know who or what is locking the DB once you have DB triggers competing with your applications.. And as you know triggers can save you lots of code.

With the other problems and staying away from triggers, you write a lot more code for SQLite than of other DB's.

You can do on error traps. You get errors when it fails for many reason. For example duplicate keys. And depending on what you are doing you may want to begin and end a rollback.

Another BIG problem with SQLite is that you cannot do replication. I've already tested replication using mySQL so I'm ready when the day comes.

Hope this helps a little.
Dan

Link to Post - Back to Top IP:

Logged

Click Here To Make This Board Ad-Free

This Board Hosted For FREE By ProBoards
Get Your Own Free Message Boards & Free Forums!