Database locking redux

[David den Haring]

You're not doing any vacuums are you? That will flip you from WAL back to DELETE mode.

[kokenge]


You're not doing any vacuums are you? That will flip you from WAL back to DELETE mode.


No - not using Vacuum.


Have you tried the WAL_CHECKPOINT pragma? That gives you control over when a checkpoint happens. I believe a checkpoint locks the database file.


Yes I have tried that and it did not help.
However setting synchronous = 0 did stop the locks.
I'll be checking different combination of writes/updates/inserts/reads to see what happens.
So far it looks good and I haven't gotten any locks.
I'm a little reluctant to say it fixed just yet, cause I've been here before..
But so far it seems to have stopped all locks that I've had.
If I run into another lock situation I'll post it here.

I'm using the following database open:

[connectSql]
sqliteconnect    #sql,  DbPath$  ' Connect to the DB
#sql  execute("PRAGMA locking_mode=NORMAL")
#sql  execute("PRAGMA synchronous=0")
#sql  execute("PRAGMA journal_mode=WAL")
RETURN


Lets hope this is the answer!!
Dan

[kokenge]

Update on SQLite Locks. I think it has been solved.

Good News.. You can eliminate all your SQLite locking problems.

After using this open statement, I have finally eliminated my lock problems.

[connectSql]
sqliteconnect #sql, DbPath$ ' Connect to the DB
#sql execute("PRAGMA locking_mode=NORMAL")
#sql execute("PRAGMA synchronous=0")
#sql execute("PRAGMA journal_mode=WAL")
RETURN

In rare cases you still get locks. However I found 2 work arounds.
1. Close and open the DB in your code where you have locks.
2. Sometimes another open with a different handle works.. For example use #sql handle for reading and #sql1 for writing and updates.

I can't speak for everyone, but it has cleared up all my lock problems.
My biggest problem was when reading a file and updating back into the same file.

For the average DB you shouldn't have any problems.

My DB is unusual and somewhat complex, so SQLite still does not work for me because of other issues such as replication..

HTH..
Dan