database is locked code:5

I have been testing my new web application. I used a REALSQLdatabase to store the servers configuration.
The web application can have multiple sessions open and each session opens up the database to retrieve configuration information.
However it seems that if two sessions have the database open or (perhaps) if one doesn’t close the database connection after use, then the next time session gets a table locked error.

Is this the case? That only one connection to the database is possible at a time?

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY.

http://www.sqlite.org/faq.html#q5

http://www.sqlite.org/atomiccommit.html#rdlck

“Client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database.”

Is each session in a web xojo web app a separate process?

I don’t know for sure how they implemented websessions, but I know for sure that multiple users have separated data spaces and do asynchronous simultaneous accesses from their user spaces.

I looked through the docs to see if I could find anything useful, I found this under RecordSet.Edit

Now it says “lock the current record”, to be honest I dont know the scope of this, It could me the row, the table, or the whole database. And it seems to only apply when you are editing a row for an update.
I also found this under SQLiteDatabase

[quote] Transactions

By default, SQLite does a Commit after each SQL command that changes the database or its data.

To ensure data integrity and to improve performance, you should create a transaction and do your database changes in the transaction. To start a transaction use this command:

db.SQLExecute("BEGIN TRANSACTION") // db is an instance of SQLiteDatabase

When you are finished with the database changes, you can close the transaction by calling Commit to make the changes permanent:

db.Commit

[/quote]

Also, regarding scope, I would assume your have a global var that holds the database(Such as App.MyDatabase), and that you call connect on it one time during the applications open event?

So search a bit more I found this from olivier vidal

[quote]Each user (session) must have its own connection to the database. It must be done in the Session.Open event (SQLITEdatabase.connect).

If you enable WAL mode (SQLITEdatabase.multiuser=true), SQLITE can read and write data at the same time. There may be many readers at the same time, but only one writer. This requires that the write transactions are short and fast.[/quote]

Info about WAL mode can be found here.

Probably this is the Brian’s problem. :wink:

Thanks all.