Locking with SQLite

[Carl Gundel - admin]

Okay I have a system working which allows for locking a SQLite database in order to prevent the database is locked errors. The idea is that applications which use a SQLite database can optionally lock that database using a semaphore.

sqliteconnect #db, "mydata.db", lock

This causes a lock to be registered for the instance of the program that invokes it, and on mydata.db. If another program also connects to that database using the lock option it will sit and wait until the first program unlocks it by calling disconnect().

If any program attempts to connect to a database that is has already a connection to, a runtime error will occur and also the connections and locks for that program will be released.

To use this mechanism effectively your Run BASIC program should always connect to a database only when it is about to read or write to it and then disconnect as soon as it is done. It won't work to have a connection across web pages or in between user actions, which wouldn't be recommended in any case even if you're using Oracle or some other database server.

This option may be made available for other database types as they become available.

-Carl

[kokenge]

Any possibility of a UNLOCK database command.

According to SQLite, a database is locked when someone does a write. However in fact it sometimes locks on reads.
I just completed a rewrite of a application because of this problem.
However knowing that mySQL will be available on the next release, I haven't been to concerned, and realize I will need to convert all my applications to that DB or any DB besides SQLite.

The program I rewrote did several things.
1. It created 2 memory databases.
2. It then reads the database and traces data to load the memory tables with what it found.
3. Reading the memory table, it determines what records to read in the SQLite table and gather more information.
4. Then it does a update or insert into the SQLite table as it reads information from it.
THIS IS WHERE IT LOCKS.

As you can see - there is only one write. So nothing should be locked. However it says the database is locked on the very first write it tries. I could maybe believe it on the second time it tries it, because it could maybe be locked from the first update.

I deliberately stayed away from DB triggers because I just didn't want another layer to worry about.

This is the first time I've tried real applications with SQLite, and have come to realize it should only be used for LITE stuff.

[Carl Gundel - admin]

This sounds like a bug in SQLite. Have you tried the latest version of the SQLite library? Have you posted on their mailing list to see if someone knows what to do about this?

-Carl

May 13, 2009 6:51:43 GMT -5 kokenge said:

Any possibility of a UNLOCK database command.

According to SQLite, a database is locked when someone does a write. However in fact it sometimes locks on reads.
I just completed a rewrite of a application because of this problem.
However knowing that mySQL will be available on the next release, I haven't been to concerned, and realize I will need to convert all my applications to that DB or any DB besides SQLite.

The program I rewrote did several things.
1. It created 2 memory databases.
2. It then reads the database and traces data to load the memory tables with what it found.
3. Reading the memory table, it determines what records to read in the SQLite table and gather more information.
4. Then it does a update or insert into the SQLite table as it reads information from it.
THIS IS WHERE IT LOCKS.

As you can see - there is only one write. So nothing should be locked. However it says the database is locked on the very first write it tries. I could maybe believe it on the second time it tries it, because it could maybe be locked from the first update.

I deliberately stayed away from DB triggers because I just didn't want another layer to worry about.

This is the first time I've tried real applications with SQLite, and have come to realize it should only be used for LITE stuff.

[kokenge]

Yes it is a SQLite problem.

I'm just trying to point out that if you try and use RB and SQLite to do a application that needs a database - don't. Or at least make sure you go in knowing what the problems are.

You don't even want to know the discussions I've had with SQLite. It's one of those situations where you end up going in circles.

My current application only has about 150+ tables, so it's very simple. In the application I'm talking about, I have the database opened 3 times plus 2 memory databases. I cannot disconnect the database to unlock it because I lose my outer loop looking up stuff with another open of the same database to insert/update it.

So to get around it, I have to basically create a subset of the database in memory and read it as the outer loop and update/insert using that information..

I have used many databases, most with over 4000 tables. Using mySQL, Oracle, Sybase, DB2, Informix and others, I have never had problems with even the most complex queries and updates.

Using SQLite, overall has cause me to write more complex code with a lot of extra lines. When in fact using a managed DB, I don't even think about it..

HTH..
Have a great day..

[BillSturm]

Do you really consider 150 tables small? I think that is a significant size, unless you mean only 150 records?

[Carl Gundel - admin]

May 13, 2009 11:48:53 GMT -5 BillSturm said:

Do you really consider 150 tables small? I think that is a significant size, unless you mean only 150 records?

I'd have to agree. Most people won't create applications with more than a few tables. SQLite is perfect for such systems. In any case I'm glad to be adding support for other databases to Run BASIC.

Dan does some pretty heavy duty database stuff that is pretty well over my head, but I'm not a database programmer. I guess it shouldn't be a surprise that SQLite isn't up to the task.

-Carl

[kokenge]

It doesn't take long to get a lot of tables.

Consider a manufacturing system. You need Item masters, Bills of material, Users, User Security, Schedules, Work Centers, Customers, Vendors, Shipping methods, Purchase orders, Sales orders, Accounts receivables, History of all kinds of stuff, Calendars, cross reference for stuff like state ID for States, Item types, categories, user types - the list goes on.

I worked with PeopleSoft and SAP. The last HR systems I installed for PeopleSoft had about 3500 talbes, and SAP about 7500 tables. They had roughly about 50,000 current people on file each. Remember they have insurance, government records, and history. The database was in the millions of records. Some sql statements take over 6 hours to execute.

Anyway take a look at a winery management system I currently have on hold because I'm waiting for mySQL.
Go to www.dkokenge.com:8008/seaside/go/runbasicpersonal?app=runINV
Login in as admin/admin. You can't hurt anything since I'm waiting to rewrite it. But you will quickly see why 150 tables is not too large. My server is in the kitchen and should be up most of the time. And it has lots of bugs because of the SQLite problems.

Have a great day..


Stefan: added URL tags to get the whole address as a link, not only the part before the colon

[Carl Gundel - admin]

May 13, 2009 14:35:54 GMT -5 kokenge said:

It doesn't take long to get a lot of tables.

I agree, but it depends on the sort of application.

Consider a manufacturing system. You need Item masters, Bills of material, Users, User Security, Schedules, Work Centers, Customers, Vendors, Shipping methods, Purchase orders, Sales orders, Accounts receivables, History of all kinds of stuff, Calendars, cross reference for stuff like state ID for States, Item types, categories, user types - the list goes on.

I worked with PeopleSoft and SAP. The last HR systems I installed for PeopleSoft had about 3500 talbes, and SAP about 7500 tables. They had roughly about 50,000 current people on file each. Remember they have insurance, government records, and history. The database was in the millions of records. Some sql statements take over 6 hours to execute.

I developed a manufacturing system for a circuitboard factory in Smalltalk from '88 to '95. The requirements there were much simpler than the enterprise scale you describe above. There was no database server because it was all in memory with lazy paging to disk. I did write my own mini SQL-style reporting system for it. If I had decided to use a database it probably would have had 20+ tables. Depending on the style of design it could easily have had more. I bet Run BASIC would make a great platform to redevelop that system in but sadly I'll not get the chance since the company closed its doors in '99.

Right now I'm working with a company on a product that only has a couple of dozen tables, and this product has been under development for 10 years. So, it depends.

Anyway take a look at a winery management system I currently have on hold because I'm waiting for mySQL.
Go to www.dkokenge.com:8008/seaside/go/runbasicpersonal?app=runINV
Login in as admin/admin. You can't hurt anything since I'm waiting to rewrite it. But you will quickly see why 150 tables is not too large. My server is in the kitchen and should be up most of the time. And it has lots of bugs because of the SQLite problems.

I'll let you know as soon as I have something you can test out. I would already be there, but moving to newer versions of Smalltalk (for the MySQL support) and Seaside (to improve uploading of large files) have caused some predictable instability that I need to stamp out.

Have a great day..

You too.

-Carl