This tutorial explains some of the issues you may run into when using the SQLite database engine and when working with an SQLite database using Lua Server Pages (LSP). When using SQLite with LSP it is unfortunately very easy to end up with the BUSY error code. One way to prevent this is to use one persistent DB connection object for all DB interactions. The following explains how to use one DB connection object without corrupting the database and/or server.
The standard method of using an SQLite database file from an LSP page is as follows:
local env = luasql.sqlite()
local conn = assert(env:connect("My-DB-NAME.db"))
.... Perform SQL commands
conn:close()
env:close()
In other words, for each web page request, you create an environment object, open a connection object, perform the database operation, close the connection, and then finally close the environment. The connection object is only kept open during the ephemeral life cycle of the LSP page. However, unlike an LSP page which is automatically closed at the end of the request/response, a database connection must be explicitly closed. See the section Command (Request/Response) Environment for an introduction to the LSP environment and life cycle.
SQLite allows multiple concurrent read operations, but allows only one DB connection object at a time to update (write to) the database. If the database is busy with an update operation, any other SQL command will fail with the error code BUSY. You may also forget to close the database connection object, thus locking and preventing further updates until the Lua's garbage collector claims the connection object and releases the database lock.
It is, for this reason, tempting to create a connection object that is kept permanent for the lifetime of the Lua application running in the server. Although this is possible, a deeper understanding of how the server works is required to prevent corruption of the database and/or server.
SQlite is a multithreaded database, but only one thread may use an open DB connection object. Two or more threads using the same DB connection object may corrupt the system and/or database. SQlite allows multiple concurrent threads, but each thread must have its own DB connection object.
LSP pages may run in separate threads and one can, for this reason, not open a persistent connection object and then use this object in all LSP pages. Also, one LSP page may be serving two concurrent requests, and using one connection object for multiple requests may corrupt the connection and/or database or even make the server crash. An introduction to threading can be found in section Thread Mapping and Coroutines.
Note: the busy error code returned by the SQlite Lua bindings is the string "BUSY" and this error code comes from the SQlite error SQLITE_BUSY. See the SQLite transaction management introduction for more information.
Using one DB connection object for all DB interactions eliminates the possibility of getting the BUSY error code. However, only one dedicated thread can use the DB connection object safely. What we need to do is to make sure all LSP pages delegate the DB interactions to one thread and that this thread is the only thread that operates on the database. This can easily be solved by using the Lua thread library. The following code snippet creates a dedicated thread for DB interaction, by calling ba.thread.create(), and provides a global function dbexec() that can be used by any LSP page or any other code that wants to interact with the database.
local dbthread=ba.thread.create() function dbexec(doit) dbthread:run(doit) end
An LSP page can then delegate execution of a callback function to the thread as follows:
<?lsp
local function doit()
conn:execute("INSERT INTO list (element) VALUES ('hello')")
end
dbexec(doit) -- Insert callback into thread queue
?>
In the above code snippet, an LSP page creates a callback function 'doit' and calls the global dbexec function, passing in the callback. The function 'doit' will then execute in the context of the 'dbthread' and not in the context of the LSP page. The thread library provides a queuing mechanism and concurrent LSP page requests are queued in the internal thread queue. Each callback function is executed by the thread in order, thus making sure that only one thread operates on the DB connection object.
When opening an SQlite DB connection from Lua, the connection initially requires that you call method setautocommit on the connection object and thereafter method commit with the required transaction type. To make sure we never run into having a locked database, the transaction can be set to EXCLUSIVE. This will prevent opening of new DB connection objects -- attempting to open a new connection will simply return the error BUSY. If you set the transaction type to EXCLUSIVE, then all DB operations must be via the one persistent DB connection object. However, SQlite is very fast at reading from a database and slow at updating (writing to) a database. Having both read and write operations performed by our dedicated DB connection thread slows down read operations since read operations will have to wait for any queued write operations. Recall that a thread has an internal queue and that all callbacks are removed and executed in order by the dedicated thread.
The example we have prepared is instead setting the transaction type to IMMEDIATE, which only requires an SQLite DB lock for each active DB write operation. The example is using one persistent DB connection object and one dedicated thread for all write operations, but allows LSP pages to open their own ephemeral read connection object. This makes sure that reading from a database is fast and can be performed directly inside the LSP page. Note that by allowing multiple DB connection objects, we still run the risk of getting the BUSY error code, however, we have designed the example code such that it will mitigate and retry the DB operation until it succeeds. Having one dedicated connection for write operations and any number of connection objects for read operations follows the SQLite philosophy/limitations in that the DB engine supports one write and many concurrent reads.
The example consists of a .preload script that opens a persistent DB connection object when the server starts. This connection object is kept open until the server is terminated (stopped). The example includes an index.lsp page that opens its own DB connection object that reads from the database when the browser sends an HTTP GET request. The content of the database is read and sent as a response to the GET request. The page includes an HTML form and submitting data sends a POST request to the server. When the LSP page receives a POST request, the page delegates insertion of the POSTed data to the dedicated DB thread.
A ready to use example can be downloaded from GitHub.
We tested the application in auto post mode by having ten browser tabs in two different browsers, a total of 20 concurrent requests. The application prints data to the trace buffer which is echoed to the console window. The application worked without a hitch, but once in a great while, we noticed the printout of a BUSY message in the console when we stress tested the application. However, the application is designed to recover from this and all DB operations are designed to succeed. See the .preload and index.lsp code for details.