Database locking thoughts

I'm thinking of adding a locking feature to Run BASIC and SQLite. Currently each opened SQLite database connection is independent of every other one. To make it easier to prevent trouble with database lock errors, it would be good to have an eXclusive gated database accessor for each database.
sqliteconnectx #myDB, "database.db"
'do one or more SQL statements
#myDB disconnect()
While that connection to database.db is open, if another running application want to open another eXclusive connection to that database it will be queued up to take its turn. Used correctly, this should help prevent database lock errors.

Thoughts?

-Carl

This would help immensely in creating multi user systems with hundreds of users, thanks.

I think a queue might be weak. In the golden olden days you asked if a database was free prior to locking it. In fact you locked a record if it was free, not an entire database.

So should a calling program know to wait? or should you just stack up a million writes?

I'd vote for a busy flag that allowed the program to manage a busy situation rather than overflow the queue.

SQLite does not lock rows unfortunately. It always locks the entire file.

Queuing up requests is just a tool. Your code needs to be written conservatively. These are hard problems that Run BASIC cannot be expected to solve completely.

Having a busy flag is something that can be done independently of having an exclusive access queue. Would you care to describe how the use of a busy flag would work?

-Carl

Solving locking errors will be a great enhancement but queuing writes puts the problem back in Run BASIC's lap. ( I assume here that there will be a finite limit to the queue) If you can flag that the file is busy it is up to the programmer to solve the conflicts. Each solution will be driven by the application.

It may be enough to accept the open/write command and queue it but to also respond with a flag that states the file is currently in use.

It should be up to the programmer to prioritise and or delay the next write. So they ignore the flag at their peril.

Don't get me wrong now.... but wouldn't it be more usefull to rather add MySQL connectivity to support real multiuser enviroments than trying to make SQLite a bit more multiuser proof(what will still be very limited)?
SQLite obviously istn't really intended for heavy traffic and heavy multiuser-DB-write-activities....

The use of this mechanism would be completely optional. If anyone wants to solve this problem for his self he is free to do so.

I'm not sure why you would want to do that. So you get a return value that says the result is in queue and will not be processed until... when? An application that needs to wait for data persistence before it can do anything else might as well just be forced to wait.

You can currently just use ON ERROR to trap the database error, but I really think this just makes the programmer's job a lot harder.

-Carl

We will add support for MySQL and other databases, but I don't think that MySQL is going to make database operations deadlock-proof (try Googling mysql deadlock). The mechanism I propose will be helpful also for apps running on MySQL, PostgreSQL, Oracle, etc. and it's probably not going to be all that hard to develop.

True, and that is all the more reason to add this sort of facility.

-Carl

After some more thinking, I would not queue the open requests, instead I would fail the connection.
The reason for this is that two or more users may write to the same DB entry based on outdated data requests, if the writes are queued.
Failing the connection could be taken care of by the programmer.
This way the program can read the data once more, compare with the previous data and decide, if writing the changed data is OK.

I can see that for lite use it will seemlessly enhance database operations. But how will the queue fail when things get busy?

Does this look like it will happen.

SQLite does not like me very well. It lurks under my PC and attacks.
I just spent all day trying to solve a very simple problem.
I go through a DB, and based on what it finds INSERTS stuff into it.
Everything is a read, except the INSERT. The very first INSERT tells me the database is locked.
I've tried different connections to the same DB, and finally read stuff into a MEMORY SQLite DB, did the work in MEMORY and tried to INSERT without luck.
I've disconnected / connected before the INSERT without luck..
I've tested the INSERT in a standalone program and it is ok.

I guess my next question is when will RB be available with managed databases. I normally use mySQL. I got worried when SUN bought it. Now Oracle owns it. So I'm now thinking Postgres.
Most of my data cannot be lost. I use Replication. Servers with hot swap are not safe enough.
I don't see any reason why RB cannot be setup with Fail Over.

My check will be in the mail as soon as you announce managed DB's.
The time I've spent trying to get SQLite to work has already paid for it several times.. And my boss (me) pays really low wages.

Thanks for the help..

The SQLite locking feature I described is planned for v1.0.2. We will also be supporting MySQL in the v1.0.2 Personal Server license. The PostgreSQL and other database accessors will be in the Pro license. Testing for the personal license should start first. This is still at least a couple of months away.

-Carl

Hi all

Sorry if I've got it wrong but is this of interest

www.sqlitening.com/
planetsquires.com/support/index.php?topic=2704.0
planetsquires.com/support/index.php?topic=2929.0

I'm not clever enough to know if the dll's could be useful

regards

metro

Hmm not only are you not clever your not interesting maybe keep your thoughts to yourself, but hey then you definitely won't learn anything.

SQLitening looks cool, but it is Windows only. :(

-Carl