Database locking redux

[David den Haring]

Has anyone tried updating the version of SQLite in their Run BASIC installation?

I did it today and tested it using the Buggs example project. In other words, I just did some quick and dirty testing.

Why would you want to do this? Well, you'd be moving from version 3.4.0 to 3.7.4. Among other new features, you get WAL (Write Ahead Logging). Here's why WAL is wonderful:

• WAL is significantly faster in most scenarios.
  
• WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
  
• Disk I/O operations tends to be more sequential using WAL.
  
• WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.
  

You can read more at sqlite.org/wal.html

I'm wondering if this will help with the Run Basic SQLite locking issues. I haven't done enough testing yet to know for sure if the upgrade will work reliably with Run Basic.

In any case, here are the steps to upgrade SQLite and change your databases to use WAL transactions:

• Get the latest versions of sqlite3.dll, sqlite3.def and sqlite3.exe from the sqlite website. Backup your current files and copy the new ones into your Run BASIC folder.
  
• Use the PRAGMA command to change the journal mode. The command is PRAGMA journal_mode=wal.
  

Switching your database to WAL is persistent. Doing a VACUUM, however, will automatically switch it back to the default DELETE transaction mode.

I'm interested to see how more complicated apps fair with databases running in the WAL journal mode. I'm going to keep testing myself.

[kokenge]

I'm still getting lock problems..

I have a program that calculates variety composition of wine.
Basically if you mix one tank of wine with another, it needs to look up the Wine Process Order (WPO) of the tank it came FROM ant the WPO of the tank it went TO. Based on the dates of the WPO's it needs to look up the variety information of the previous variety in the FROM and TO tanks and combine it according to the mixture percents. If it's a change in history it then needs to cascade all the varieties forward to the end. Mostly it's a very simple SQL and only involves less than 10K records that are updated.

So it involves looking up variety and updating into the same variety file. When you update into the same file, it somehow gets confused with the SQL that is doing the lookup and the SQL command that is updating into the same file.

Here are the two very simple SQL commands.
First the variety lookup of the information to do the variety calculation..

	SELECT
	workTrace.wpoNum	as wpoNum,
	workTrace.fstorageId	as fs,
	workTrace.fbegQty	as fbq,
	workTrace.fmovQty	as fmq,
	workTrace.fendQty	as feq,
	
	workTrace.tstorageId	as ts,
	workTrace.tbegQty	as tbq,
	workTrace.tmovQty	as tmq,
	workTrace.tendQty	as teq,
	
	wpoVariety.storageId as vstorageId,
	wpoVariety.varietyNum	as vnum,
	wpoVariety.areaId	as vareaId,
	wpoVariety.year		as vyear,
	wpoVariety.pcnt		as vpcnt,
	wpoVariety.yield	as vyield,
	wpo.numFrom		as numFrom,
	
	SUM (((workTrace.tbegQty * wpoVariety.yield) * (.01 * wpoVariety.pcnt)
	* (wpoVariety.wpoNum 	= workTrace.tPreToWpoNum)
	* (wpoVariety.fromTo 	= 'T')
	* (wpoVariety.storageId = workTrace.tstorageId) / numFrom)) as tyieldQty,
	
	SUM (((workTrace.tmovQty * wpoVariety.yield) * (.01 * wpoVariety.pcnt)
	* (wpoVariety.storageId = workTrace.fstorageId)) / numFrom) as fyieldQty,
	
	
	SUM (((workTrace.tbegQty * (.01 * wpoVariety.pcnt))
	* (workTrace.fstorageId	<> workTrace.tstorageId)
	* (wpoVariety.wpoNum 	= workTrace.tPreToWpoNum)
	* (wpoVariety.fromTo 	= 'T')
	* (wpoVariety.storageId = workTrace.tstorageId)) / numFrom) as tvarQty,
	
	SUM (((workTrace.fgaugeGl / wpo.numTo)
	+ workTrace.tgaugeGl
	+ workTrace.tmovGl
	+ (workTrace.tmovQty * (.01 * wpoVariety.pcnt))
	* (wpoVariety.storageId = workTrace.fstorageId)) / numFrom) as fvarQty
	
	
	FROM workTrace
	JOIN	wpo
	ON	wpo.wpoNum	= workTrace.wpoNum
	
	JOIN	wpoVariety
	
	ON	wpoVariety.wpoNum 	= workTrace.wpoNum
	AND 	wpoVariety.fromTo 	= 'F'
	AND	wpoVariety.storageId	= fstorageId
	
	OR (	workTrace.fstopTrace	<> 'Y'
	AND 	wpoVariety.wpoNum 	= workTrace.fPreToWpoNum
	AND 	wpoVariety.fromTo 	= 'T'
	AND 	wpoVariety.storageId 	= workTrace.fstorageId
	AND	workTrace.fbegQty	> 0
	
	OR (	workTrace.tstopTrace	<> 'Y'
	AND	wpoVariety.wpoNum	= workTrace.tPreToWpoNum
	AND	wpoVariety.fromTo	= 'T'
	AND 	wpoVariety.storageId 	= workTrace.tstorageId
	AND	workTrace.tbegQty	> 0
	))
	WHERE	workTrace.wpoNum	= 1
	GROUP BY workTrace.tstorageId,wpoVariety.varietyNum,wpoVariety.areaId,wpoVariety.year

And here is the variety update SQL commands:
After a couple inserts it gets locked.

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)|

NSERT INTO wpoVariety VALUES(1,'M1008','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1008','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1009','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)|

[metro]

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)|

NSERT INTO wpoVariety VALUES(1,'M1008','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1007','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1008','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)

INSERT INTO wpoVariety VALUES(1,'M1009','T',60,'988',2008,10001.0,0,0,0,100.0,110.0)|

Dan , wouldn't be something as simple as your new key word " NSERT " one line 3 above?
or is this just a cut and paste error

regards

Laurie

[kokenge]

OOPS - fat fingers!!.
Thanks Laurie..
It's a cut and paste problem. That would have given a "systax error".


There may be an answer to the DB lock, 0.0d error, the Upload, and replication problem.
I'm looking into couchDB. Because of the holidays, I probably won't get much none for a couple weeks.

But!!!
1. It's a database. You can use it like a relational DB.
2. It solves the Replication problem since it handles that very well.
3. It can hold binary such as photos, video and music.
-- Not that other database can't do this, but it's messy and you are better off holding them as files and storing the file name in the db.
4. The interface to couchDB is not handled by RunBasic so the 0.0d problem goes away.
5. You can use javaScript as the interface to couchDB. You could then use a html <INPUT type=file with a JS onChange trigger. Therefore you can have the triggered JS insert the selected file into the DB. Since the DB can be on the Server side you basically did a UPLOAD..

Stay tuned..
Dan

[David den Haring]

Dan, just to double check, after running this test can you verify that you are in WAL journal mode by running the following command: "PRAGMA journal_mode;"? It should return "wal".

Each database file will probably also have two supporting files with the "-shm" and "-wal" extensions.

[David den Haring]

Dan, are you saying the CouchDB interface is a web service?

[kokenge]

David,
I double checked the PRAGMA journal_mode.
Originally it was "memory" but changed it to "wal"

I did other tests and some of my locks went away, and others took longer to lock.

The docs on journal-entry=wal;
www.sqlite.org/draft/wal.html
says readers don't lock writers and writers don't lock readers.
And I have a feeling they are saying if you have one program doing reading it is not locked by someone doing writes, and the other way around. But if you have concurrent reads and writes to the same file it seems to have a problem. - For me anyway.

In any event, even if this worked 100%, for me SQLite is useless. It still has the 0.0d problem, and it does not do replication..
There are other issues that I could go into that also that makes it difficult for me to use.

[kokenge]

David

 are you saying the CouchDB interface is a web service?

A qualified YES since I'm in the dummies mode right now.

I just got a book for couchDB from Amazon. They like to say many systems are for the web, but this is of the web.

When you see how couchDB works, you can see where they can eliminate the traditional OS file system and use couchDB. Ubuntu is starting to sue it for all the updates and services. Google is a big time user of couchDB.

You can download it from Apache.
They have a CURL interface and you can type html commands to do stuff. I also tried some JS from RunBasic and it worked.
That's about all I can say personally.

You can use javaScript or html to update the database. There are interfaces for the major languages like C, PHP etc..

Anyway it looks interesting.
It's basically bullet proof, and you can have multiple couchDB managers somewhere on the web. Replication is a snap.
Schemas can vary and are not fixed - ala OS file system.
I'm just starting to understand it, but it looks interesting and powerful.

[David den Haring]

In any event, even if this worked 100%, for me SQLite is useless. It still has the 0.0d problem, and it does not do replication..
There are other issues that I could go into that also that makes it difficult for me to use.

The "0.0d" problem is a Run Basic issue, I think. For replication, are you just looking for one-way, two-way or multi-way replication? SQLite sort of supports one-way with its backup API. I haven't used it yet myself so I'm not sure it's even accessible from Run Basic.

Anyway, good luck with your CouchDB idea. I'd like to hear how it works out for you.

[kokenge]

The "0.0d" problem is a Run Basic issue,

Yes it is a RB issue. But it still causes lots of problems and makes SQLite useless for me.

For replication, are you just looking for one-way, two-way or multi-way replication? 

I need replication where you can select the data replicated and the direction.
I have warehouses that receive orders and other data for their site, and the site can receive data from headquarters.
For example a site may receive orders. Orders are sent to the warehouse db. The orders are also replicated up to headquarters.
Headquarters can also receive orders for the site. It has to send their orders to the site, who then sends them to their warehouses.
Headquarters sends all their data to the number one backup (replication). This includes all data received from the sites. The number one backup machine also sends all their data to the number two backup. So basically headquarters has all data from all sites. There are also two backups. All machines are in separate locations just in case something happens like a fire at one location.
If any site, warehouse, headquarters loses their database for any reason, then it fails over the the next machine above it, and it only sees their data. When the problem with the DB is fixed then it automatically brings it's data back into sync from the machine above..

Down time is not acceptable. The system can never come down for backup or recovery and must be operational 24x7x365.

Originally I had to write my own replication. But the newer BD's handle just about any replication you can define...

couchDB
 I'd like to hear how it works out for you.

Just got started with it. I got it to work with simple stuff with Run Basic and REBOL.
Right now it's a little busy for me, so it may take a few weeks.
I'll post what I find out.

Hope this helps..
Dan

[kokenge]

Update on DB locking.
Took another try. I have always gotten into a database lock problem when reading and updating/inserting/writing into the same file.

I used the following DataBase connection with PRAGMA. Tried some of my systems that have always immediately locked, and they now work.
I've only tested about 10 of my programs that do reads and writes to the same file, so have no guarantee this is the complete answer. But it looks good so far.

I use the following routines to open

gosub [connectSql]        ' Open Database for reads
gosub [connectSql1]        ' Open Database for write/update/insert

' ----------------------------------------
' Database connection and PRAGMA setup
' ----------------------------------------
[connectSql]
sqliteconnect    #sql,  dbPath$  ' Connect to the DB
sql$ = "PRAGMA locking_mode=NORMAL"
#sql  execute(sql$)

sql$ = "PRAGMA synchronous=1"
#sql  execute(sql$)

sql$ = "PRAGMA journal_mode=WAL"
#sql  execute(sql$)
RETURN

' ----------------------------------------
' Database connection and PRAGMA setup
' ----------------------------------------
[connectSql1]
sqliteconnect    #sql1,  dbPath$  ' Connect to the DB
sql1$ = "PRAGMA locking_mode=NORMAL"
#sql1  execute(sql1$)

sql1$ = "PRAGMA synchronous=1"
#sql1  execute(sql1$)

sql1$ = "PRAGMA journal_mode=WAL"
#sql1  execute(sql1$)
RETURN


Hope this helps.

[Carl Gundel - admin]

That's encouraging. What version of SQLite are you using now?

-Carl

Jan 31, 2011 7:44:59 GMT -5 kokenge said:

Update on DB locking.
Took another try. I have always gotten into a database lock problem when reading and updating/inserting/writing into the same file.

I used the following DataBase connection with PRAGMA. Tried some of my systems that have always immediately locked, and they now work.
I've only tested about 10 of my programs that do reads and writes to the same file, so have no guarantee this is the complete answer. But it looks good so far.

I use the following routines to open

gosub [connectSql]        ' Open Database for reads
gosub [connectSql1]        ' Open Database for write/update/insert

' ----------------------------------------
' Database connection and PRAGMA setup
' ----------------------------------------
[connectSql]
sqliteconnect    #sql,  dbPath$  ' Connect to the DB
sql$ = "PRAGMA locking_mode=NORMAL"
#sql  execute(sql$)

sql$ = "PRAGMA synchronous=1"
#sql  execute(sql$)

sql$ = "PRAGMA journal_mode=WAL"
#sql  execute(sql$)
RETURN

' ----------------------------------------
' Database connection and PRAGMA setup
' ----------------------------------------
[connectSql1]
sqliteconnect    #sql1,  dbPath$  ' Connect to the DB
sql1$ = "PRAGMA locking_mode=NORMAL"
#sql1  execute(sql1$)

sql1$ = "PRAGMA synchronous=1"
#sql1  execute(sql1$)

sql1$ = "PRAGMA journal_mode=WAL"
#sql1  execute(sql1$)
RETURN


Hope this helps.

[kokenge]

That's encouraging. What version of SQLite are you using now?

Version 3.7.4.

You can get the download here:
www.sqlite.org/download.html

[kokenge]

Update on database locking.
Even though the problem happens less frequently, the lock problem still exist.

I just spent 4 hours on one lock problem. I tried many alternatives but I cannot get around the lock.
I did Database connect and disconnect before and after the DB command.
I tried opening the DB with several handles and using different combinations of handles for reads, updates, and writes.
I tried BEGIN and COMMIT transactions at different places in the program..

This particular problem happens when I do a SELECT of information, and based on the information I need to UPDATE information in the same file. The UPDATE command locks the DB.
I have to do this several times. The first UPDATE works, but the second gives me a "database is locked" error.

In the past you had to Stop Run Basic and start it again to get around the lock. If there is any good news, you no longer have to do that.

Hope this helps.

[David den Haring]

Dan,

I'm glad to hear that you've made some progress at least. I think that WAL reduces but does not ellminate the locks. I'm still studying it myself.

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

BTW, Sqlite 3.7.5 was just released.